Oracle is a RDBMS (Relational Database Management System) created by Oracle Corporation. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java. Also included is support for C (Pro*C, OCI and EXTPROC), C++ (OCCI) and Java (JDBC) ...

learn more… | top users | synonyms

2
votes
2answers
4k views

In Oracle how do you move a Schema to a different Tablespace?

I noticed that a Schema has been created in the USERS Tablespace but our local rule is that it should be in a Tablespace by itself. In Oracle how do you move a Schema to a different Tablespace?
3
votes
1answer
3k views

Oracle: How to create a not null column in a view

Given a table like: CREATE TABLE "MyTable" ( "MyColumn" NUMBER NOT NULL ); I want to create a view like: CREATE VIEW "MyView" AS SELECT CAST("MyColumn" AS BINARY_DOUBLE) AS "MyColumn" FROM ...
2
votes
2answers
726 views

enq: TX - row lock contention creating session waits

The database I'm working on has session waits due to DMLs on numerous tables in a procedure. The DBA identifies this as enq: TX - row lock contention. How can I resolve this issue?
2
votes
1answer
10k views

How do I grant execute permission to an Oracle database user?

I'm trying to call a Java function from Oracle database that does the following: Process p = Runtime.getRuntime().exec("..."); When I try to run it, I get the following error: ORA-29532: Java call ...
1
vote
1answer
386 views

This refactoring from cursor on a dblink'ed Oracle table OK?

The data is being compared from the local version of the table to one in the contracts linked server. I'm not certain what the goal of this code is in the first place, but I'm trying to get a handle ...
2
votes
1answer
491 views

How to Connect R to Oracle?

I need to connect R to oracle and I have been unsuccessful so far. I downloaded two packages: RODBC & RODM. This is the statement that I've been using: DB <- ...
4
votes
2answers
274 views

What are the risks of the parameter PRESERVE_COMMIT_ORDER of Oracle's DBMS_LOGSTDBY?

We have the following setting, with Oracle 10g: One primary database One logical standby database with setup with data guard. The logical standby is only used for generating reports and alike. ...
1
vote
1answer
361 views

Oracle - query by two column combination

I have two tables: product_table: ------------------------------------ product | market | category | other ------------------------------------ | | | ...
5
votes
1answer
301 views

SQL Server Linked server query problem (group by)

I have been using an old SQL Server 2000 installation for a long time that is linked to several Oracle servers, and it all works very well. I am trying to migrate to SQL Server 2012 via SQL Server ...
0
votes
3answers
459 views

Can a RDBMS connect to a remote server, execute a select query and copy them into a local table just with SQL commands?

I'm sorry it may sound stupid but I've been wondering about how multiple databases are automated before the virtualization era. Can a DB Adminsitrator make a stored procedure to get data from a remote ...
1
vote
1answer
116 views

Change Oracle IP

If I change Oracle database server IP , what problem may exist and how to solve? modify hosts file modify tnsnames.ora file modify listener.ora file modify clients' tnsnames.ora files As I know ...
2
votes
1answer
224 views

ARCHIVELOG mode

Can someone describe in 30 words what ARCHIVEMODE is for? I found this: 'When you enable this mode redo logs will be archived instead of overwritten.' but have no idea, what can I write more about ...
1
vote
1answer
659 views

Simple date calculation to show specified old records

How to select from v$session records which logon_time is less than 2 days before current date? SELECT * FROM v$session WHERE logon_time + 2 <= currentDate (???)
3
votes
1answer
532 views

Locking order in cursors (Oracle)

I wonder if the order in which records are locked is guaranteed when I use SELECT ... FOR UPDATE in cursors ? For example, DECLARE CURSOR test_cursor IS SELECT field1, field2 FROM table1 ORDER ...
2
votes
2answers
3k views

Setting NLS params in logon trigger don't work in oracle 11g

We have a trigger setup so that when a specific user logs in, their NLS session parameters are modified. This used to work on Oracle 10g. We've just migrated to Oracle 11g RAC, and the session ...
1
vote
2answers
872 views

Remote to a remote oracle server and get running instances?

I need to make a shell script that takes a list of servers and and check the running instances then run sql script in each instance! now I do not want it to ask me for a password! it's redhat and ...
10
votes
1answer
422 views

Disable explicit commits in JDBC, detect them in SQL, or put the database in a readonly state

Background: I'm working on http://sqlfiddle.com (my site), and am trying to prevent one avenue of abuse possible there. I'm hoping that by asking about a problem I'm currently addressing, I don't ...
-1
votes
1answer
76 views

Index Proposals

As a theoretical database question, if you are asked 'which indexes would you propose and why?' are the answers from primary, clustering and secondary index? How would we know if we should introduce ...
1
vote
1answer
413 views

How to sync a table between oracle and sybase

I have a table in Sybase (destination) that I would like to keep it synced with a table in Oracle (source) I cannot truncate and populate the table in Sybase because it is used by an application, I ...
4
votes
2answers
337 views

Change Location of VALIDATE RECOVERY FILES work area

Is there a way to tell the VALIDATE RECOVERY FILES command to use a different location with more available disk space? When I run the command in RMAN, it fails with the following error stack: ...
3
votes
5answers
473 views

Query optimization [Oracle]

The Query: select JOIN_RESULT.batch_id as BATCH_ID, JOIN_RESULT.unique_doc_id as DOCUMENT_ID, JOIN_RESULT.brand as BRAND, JOIN_RESULT.message_date_time as UPLOAD_DATE, ...
5
votes
1answer
3k views

How can I get the Oracle TNSListener to start after changing my host name?

When running lsnrctl status I can see that it is using the old host name, but I don't know where it is pulling that value from. My Oracle installation is 11.2.0 on Windows7 x64, if that makes any ...
2
votes
1answer
582 views

How to calculate query that access system view cost in Oracle?

For a query that returns if there are any active transactions within current session , SELECT COUNT(*) FROM v$transaction t INNER JOIN v$session s ON (t.ses_addr = s.saddr ) INNER ...
2
votes
2answers
96 views

Incremental backups - differences

What is the differene between: BACKUP INCREMENTAL LEVEL 0 DATABASE; and BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE; ? I read some manuals, but I don't get the idea beacuse of my poor ...
3
votes
2answers
424 views

What exactly is “pinning” in relation to indexes?

When one talks about "pinning" in indexes, what exactly is this? Is there some other word/term I can search for, as google has not provided any solutions. It is part of a test question, where ...
4
votes
1answer
956 views

Comments in EXPDB parameter file

Is there a way to put comments in a data pump export parameter file?
3
votes
2answers
17k views

How do you get the explain plan?

Can some one please explain a bit about explain plan, and how does one get the explain plan for a query. Also, if there is any sql script which can be used as utility for this, that would be great.
1
vote
1answer
528 views

Enterprise Manager Database Control on host with an underscore in it's name

I know it sounds awkward, but Enterprise Manager is refusing to be set up because of an underscore in the server name: C:\>emca -deconfig dbcontrol db -repos drop STARTED EMCA um 30.05.2012 ...
5
votes
2answers
21k views

How to troubleshoot enq: TX - row lock contention?

I have the following situation. I have RAC. On both nodes there are the locks. On the First Node SID EVENT USERNAME BLOCKING_SESSION ROW_WAIT_OBJ# OBJECT_NAME ...
1
vote
0answers
113 views

Problem with SDO_AGGR_CONCAT_LINES in Oracle

I have a line consisting of 3 segments A, B & C A touches B at just one point, likewise for B & C The SDO_AGGR_CONCAT_LINES function returns a MULTILINE object (GType = 2006) rather than a ...
1
vote
3answers
19k views

Move data from one oracle db to another db using toad

I am new to oracle and this might be a simple question for many. I need to take data from one db and insert it into other db. The databases are on the same version and edition. I am currently ...
1
vote
1answer
1k views

Decode query truncates a date

I am not sure if I am looking at this problem the right way: I have a query like this on an Oracle database SELECT DECODE( TO_CHAR(MIN(DATE), 'YYYYMMDD') , '' , NULL ...
0
votes
1answer
277 views

How to troubleshoot/debug Oracle permission problems

An application is connecting to Oracle to do stuff. It's not working but I don't get any ORA errors in the application itself. I'm thinking its a permission problem. So my question is what is the best ...
1
vote
1answer
73 views

Oracle DB administrator-specific security features

Yesterday I was presenting an internet banking solution to a potential client management. My offer is based on using MS SQL Server 2008 RDBMS. One of the client's officers mentioned, that Oracle's ...
9
votes
1answer
297 views

SQL substitution feature in Oracle 10g

Three or four years ago I read in an Oracle blog somewhere that a DBA had used for an emergency incident resolution an Oracle 10g feature of real time SQL substitution. Basically, he configured Oracle ...
2
votes
2answers
5k views

Return many rows on a plsql Oracle10g

I'm doing a little project and I need to do a plsql to return many rows. I have someting like: Table(id, data1, data2, from) And the user gives me the value of from and I have to return all that. ...
3
votes
2answers
9k views

Using Same CASE WHEN Conditions For Multiple Query Columns

Is there a "better" way to rewrite a SELECT clause where multiple columns use the same CASE WHEN conditions so that the conditions are only checked once? See the example below. SELECT CASE ...
3
votes
2answers
151 views

How can I mimic functionality of SQL Server `TYPE` in Oracle?

I apologize if question title is vague, but I was not able to formulate it better. What I'd like to have is an alternative to TSQL CREATE TYPE, so I can use it in column definition. For instance, I ...
2
votes
1answer
1k views

Can RMAN duplicate be used to “replace” an existing database?

I have an unused database B, not valuable. I'd like to make a copy of database A, but do not want to go through all the manual steps RMAN requires to set up the auxiliary database. Can I use RMAN ...
1
vote
1answer
393 views

When using expdp to create a snapshot of an oracle schema, how do I ensure that no post-export additions are retained upon using impdp to revert?

I want to create a snapshot of an Oracle schema that I can revert back to after testing. Impdp and expdp seem to be the best fit as far as how I'd like to accomplish it, but the problem is that impdp ...
1
vote
1answer
594 views

Backing up an Oracle 10G Express on Windows 2000 Server

Oracle 10G Express installed on Windows 2000 Server Enterprise Edition. I am trying to automate backups and on Oracle's website I have found the following documentation. ...
2
votes
1answer
1k views

Oracle, error while exporting schema

I'm using a command: $ exp userid=apex_public_user/apex_public_user OWNER=apex_public_user DIRECT=Y CONSISTENT=Y FILE=/home/oracle/apex_public_user.dmp and getting following output: Export: ...
2
votes
1answer
736 views

Creating Bitmap Indexes on a Materialized View

I've been reading this article, discussing the benefits, tradeoffs, and potential problems with Bitmapped Indexes. It is mentioned: Also, remember that bitmap indexes are only suitable for static ...
3
votes
1answer
363 views

SQL unpivoting multiple rows/columns, but keeping the rows grouped together, and in the same order they were selected

This is a complicated explanation, so sorry that it is going to be a wall of text. I'm dealing with pre-existing code and am trying to work out a solution, so I can't alter the core way any of this ...
5
votes
3answers
498 views

What methods are available to create a snapshot of an oracle database for testing purposes?

I am new to databases and have just been assigned to work with Oracle. We want to create a snapshot of the database for testing purposes that will allow us to revert back once we're done. I'm a bit ...
0
votes
1answer
372 views

Tools for synchronizing database schema with Oracle, SQL-Server and Netezza databases

I'm looking for software tools to move database schema for Oracle, SQL Server and Netezza. We use Oracle for our ODS, Netezza for our Data Warehouse (for it's bulk loading capabilities) and SQL ...
0
votes
2answers
3k views

Copy complete structure of a table in Oracle without using Backup

When you create do a CTAS (create table as select) of a table you only get the structure, but lose the index, PK, FK, etc. Example: create table t1 select * from table2; How can a copy of the ...
1
vote
1answer
316 views

DML operation against synonym oracle

I have been reading the GoldenGate 11g R2 installation guide and it states: Non-supported objects and operations in Oracle DML: [...] Synonyms [...] But I see in the next section that ...
1
vote
1answer
74 views

Filter On Indexed, Formatted DATE

How do I write my calling query (without modifying V_SPECIAL_SAMPLES) to use the index on sample.sample_date (filtering by date first)? I believe it's the TO_CHAR and TO_DATE conversions that prevent ...
0
votes
1answer
322 views

getting oracle error ora-00103 when I try to execute a package in oracle 10g

I have to delete and repopulate tables on a remote database.To access the remote tables I have created synonyms. my package is as follows create or replace PACKAGE eco_push IS PROCEDURE ...