All versions of Oracle. Also add a version-specific tag like oracle-11g-r2 if that context is important in the question.

learn more… | top users | synonyms

4
votes
1answer
1k views

Oracle query against SQL Server database giving inconsistent results

We have multiple Oracle 11.2.0.2 databases with og4odbc database links to a SQL Server 2008 database. A particular table in the SQL Server database has two Varchar(36) columns. The following ...
2
votes
1answer
239 views

Why am I not seeing any benefits of parallel execution when parallel clause is used?

I have these two methods of creating a table and populating with rows. The first one uses a parallel (degree 3) clause, so I expected the inserts to be much faster. But I timed the time taken for a) ...
12
votes
3answers
6k views

How do indices impact query performance?

Obviously keeping several different indices has a negative impact on insert and delete performance. How about query performance: Does it make sense at all keeping too many indices on a table? Will the ...
8
votes
2answers
2k views

How to migrate SQL Server Stored Procedures using temporary tables or table variables to Oracle?

C# developer encouraged by management to write SQL Server stored procedures often produce procedures like this create table #t1 (...); insert into #t1 Select ... from table_a where ...; insert into ...
6
votes
1answer
2k views

DBMS_Resource_Manager.Calibrate_IO Usage

DBMS_Resource_Manager has a procedure called Calibrate_IO with a Physical Disks input parameter. The documentation says this value should be set to the... Approximate number of physical disks in ...
16
votes
2answers
4k views

Index vs. partition

Why is it not possible to achieve performance improvements with indexes only, so that other techniques like table partitioning becomes necessary? The question relates only to performance, of course ...
4
votes
2answers
2k views

DBMS_REDEFINITION vs EXCHANGE PARTITION in oracle

In order to partition an existing non-partitioned table it is possible to use either the EXCHANGE PARTITION or DBMS_REDEFINITION. How to decide on which of those possibilities to choose for table ...
6
votes
1answer
10k views

Introduce partitions into an existing non-partitioned table

What has to be done to introduce range partitions into an existing non-partitioned table in oracle? Is there an easy to do and safe way to do this? Especially in cases where there are many ...
10
votes
2answers
594 views

large data movement

I would like to move billions of rows from schema1.table1 to new schema2.table2 where table2 is a refactored one from table1. Hence their table structure is different. both table1 and table2 are ...
2
votes
1answer
624 views

Drop Oracle data file with 0 bytes and in offline (needs recovery) state

What can I do to drop a data file mistakenly created and currently with 0 byte and in offline (needs recovery) state?
4
votes
1answer
5k views

Optimal value for LOG_BUFFER parameter

The wait event “log buffer space” according to the documentation can indicate that the log buffer is too small or the I/O is slow. We have a system seeing this event in which the redo buffer ...
10
votes
1answer
8k views

Why does SQL*PLUS need a slash after CREATE TYPE?

I just had the problem that I defined a Type and tested it in TOAD and all was OK. But running under SQL*PLUS it threw an error. Example: CREATE OR REPLACE TYPE MyType AS OBJECT ( Item1 NUMBER, ...
2
votes
1answer
517 views

oracle monitoring db status

I am looking for a way to monitor Oracle Database status (health) via PHP script executing SQL query. There are system variables containing actual number of connection, sessions, work load, etc... ...
4
votes
2answers
418 views

Searching for a Data Dictionary for SQL or Oracle

I am looking for metadata specifications (data dictionary) for SQL Server and Oracle databases. By metadata map, I mean a definitive description of what objects make up database model and their ...
5
votes
2answers
2k views

Updating rows conditionally with a gapless ascending value

A user has a table they would like to update. Depending on the data in other columns they want a particular column to be updated with an ascending value starting from 1 that is gap-less. The rows ...
3
votes
1answer
2k views

database duplication VS database replication

Is there a difference between duplication and replication with reference to a database? I am not sure if I am correct at all, but duplication means to me that we are copying bit by bit without ...
2
votes
2answers
13k views

SGA_MAX_SIZE and SGA_TARGET

We recently increased sga_max_size to 4g from 2g but didnt increase sga_target, is it ok not to increase sga_target?? reason we wanted to increase sga is to reduce wait time on sequential reads in our ...
3
votes
2answers
4k views

What does 'nls' in nls_date_format stand for?

For example: alter session set nls_date_format = 'DD-MON-YYYY hh24:mi:ss'; changes the date format for the session. What is nls here?
8
votes
3answers
6k views

Multiple Oracle Instances — is this a good practice?

One of my clients has deployed our product's db into a Solaris machine which already has 3 instances of Oracle. So, right now there are 4 instances of Oracle running on the same machine. And now we're ...
3
votes
1answer
142 views

Is there any tool to convert Oracle commands to MySQL?

Is there any tool which I can use to convert Oracle commands to mysql commands?
0
votes
2answers
857 views

Is where x = 0 or x is null to be replaced by where isnull(x, 0) = 0 resp. where NVL(x, 0) = 0

Think of x as a column containing null, 0, 1. Only less than 1 % of the rows contain 1. There is no logical difference between 0 and Null. Is it a good idea to insist, that isnull rather than NVL is ...
6
votes
2answers
753 views

Oracle Advanced Queuing LIFO

With Oracle's Advanced Queuing is there a way to dequeue messages in a LIFO (last in first out) order? There is an indication through a lack of information that this is not an option, but perhaps ...
2
votes
1answer
2k views

What is the purpose of sqlbulkcopy's useinternaltransaction property?

I'm just testing my PowerShellScript to do bulkcopy of multiple tables between sql-server and oracle Databases. When the destination is Oracle, than I can monitor the progress by by executing select ...
2
votes
0answers
301 views

Which are the Oracle basics required for advanced Oracle.DataAccess usage? [closed]

Successful use of [Oracle.DataAccess.Client] is based on proper understanding of three separate parts of Oracle: SQL PL/SQL sql*plus Here on dba.exchange the following questions target this topic: ...
3
votes
2answers
4k views

How can I catch the output of DBMS_OUTPUT.PUT_LINE when executing a block using [Oracle.DataAccess.Client]?

In my oracle Database if have the following table: Create table Test_call_count ( count number(10) ); insert into Test_call_count values (0); Now I can run the following PowerShell script if ...
8
votes
1answer
563 views

How can I rewrite for SQL Server a trigger that writing for Oracle?

How can I rewrite for SQL Server a trigger that was originally written for Oracle ? Here is my Oracle trigger code: CREATE OR REPLACE TRIGGER P000KUL_TEST BEFORE INSERT ON P000KUL REFERENCING NEW AS ...
8
votes
2answers
2k views

What is the difference between terminating an Oracle sql statement with semicolon or with a slash on the next line?

The following two queries seem to be equivalent when executed in sql*plus: 1. select * from user_tables; 2. select * from user_tables / Is there any difference between both versions?
16
votes
3answers
19k views

What is the difference between Oracle's VARCHAR and VARCHAR2 datatypes?

When migrating tables coming from other DBMSs to Oracle, one of the standard tasks is to replace all VARCHAR(n) fields with VARCHAR2(n) fields (provided n <= 4000). Why does Oracle call this ...
5
votes
2answers
22k views

How do I allow users to see grants, view definitions, PL/SQL, etc in a database without granting them access to change those objects

As a developer, I need to be able to access the design of the database/database objects from production so I can ensure I'm working with the latest and greatest version of the objects I'm developing ...
1
vote
1answer
9k views

How to take undo datafile offline ,which is in recovery mode?

I am trying to take undo datafile in offline mode using following command alter database datafile '<datafile path>' offline; this datafile is in recovery mode. Oracle shows message as ...
2
votes
2answers
4k views

In Oracle, how to globally enable parallel execution?

In Oracle 11g, is there a way to tell the database to use parallel execution whenever possible by setting a flag etc, rather than using PARALLEL hints with every SQL statement?
2
votes
2answers
20k views

Where all can the /*+ PARALLEL */ hint be used?

It works with SELECT and UPDATE statements. Are there some more statements with which it works? I was able to use it with create, but am not sure if the hint was actually considered. SQL> create ...
4
votes
4answers
1k views

Best method to give two Oracle databases on the same server access to common data

With a production and a test database on the same server, how should access be provided to some common quarterly update tables for both databases? It is possible to put these tables into a third ...
8
votes
2answers
12k views

Why do we need to backup Oracle archive logs along with datafiles

Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2) says that Archived redo logs are the key to successful media recovery. Back them up regularly. But, I wonder, why backups of ...
6
votes
3answers
69k views

Oracle SQL - CASE in a WHERE clause

Is it possible to somehow do this? WITH T1 AS ( SELECT 1 AS SEQ, 'NOTHING 1' AS SOME_TYPE FROM DUAL UNION ALL SELECT 2 AS SEQ, 'NOTHING 2' AS SOME_TYPE FROM DUAL UNION ALL SELECT 3 AS SEQ, ...
3
votes
2answers
139 views

I'm performing a post-mortem on an Oracle database that's crashed its' host. Where should I begin?

I have an Oracle database (10.2.0.1.0 I believe) on a Windows 2003 server that I believe has successfully crashed the OS itself twice. Debates on platforms and stability aside, the evidence suggests ...
3
votes
3answers
4k views

cursor: pin S wait on X in 11g

This has been one of top5 wait events in our database, even though there were not many waits but we see it as a potential threat, we want to find the root cause and solution. Any advice will be ...
6
votes
3answers
31k views

How to find the actual space being consumed by indexes on a table in Oracle?

I want to find the actual space being consumed by indexes on a table in oracle 10g . I do not intend to include the space reserved by oracle for future usage. (The overhead by oracle should not be ...
5
votes
3answers
2k views

Alternatives to an INSERT Trigger

Looking for an example code in Oracle to do the same. i.e. Remove an insert trigger on table T1 whose job is to create a row in T2 based on the derived data. How would this PL-SQL api look like? ...
14
votes
3answers
11k views

How to differentiate between SQL and PL/SQL?

I know the question might sound too stupid, but I never understood this part. SQL*Plus works with both SQL and PL/SQL. How do I know whether some code is SQL or PL/SQL? If my code has a for ...
5
votes
2answers
4k views

What is the difference between ROLE and PRIVILEGE for Oracle?

Is role a collection of certain privileges?
5
votes
2answers
2k views

What is the difference between these two create table statements?

a) SQL> CREATE TABLE xml_tab1(poDoc XMLTYPE); Table created. SQL> desc xml_tab1; Name Null? Type ----------------------------------------- -------- ...
2
votes
1answer
544 views

SAN Storage & Server for Oracle Replication

I need a 2-3 TB storage server group for Oracle Database. We want SAN Storage & Server for Oracle Replication, but not Microsoft Storage Server. For example : HP DL380 G6 as a server and EMC ...
8
votes
1answer
20k views

How can I import the contents of an Oracle database into Visio to create an Entity Relationship Diagram?

I have an Oracle database, and I would like to create an ERD in Visio 2007 Pro. I have so far looked at DBMS_METADATA.GET_DDL commands to generate DDL. I know that I can import an Access DB into ...
6
votes
1answer
541 views

How can I make the audit system create new audit files if they already exist?

I am trying to make my audit files easier to read, I have audit trail configured as xml,extended. The problem is that if my server gets busy and reuses the pids - the logs are being appended to the ...
1
vote
1answer
3k views

Local package won't compile when remote package recompiles. (Oracle)

We have a package on a 10.2.0.4 database(A) and a package on an 11.2.0.2 database(B) that references A's package. When something on A changes that requires the package on A to do an implicit ...
6
votes
1answer
1k views

Using sys.diana and sys.pidl to parse PL/SQL

It seems to me that it should be possible to parse existing PL/SQL with the packages sys.diana and sys.pidl, but I have found nothing on the internet with the exception of a few scripts that seem to ...
16
votes
6answers
15k views

Oracle Creating ER Diagrams and Data Dictionaries

I am new to Oracle Databases and would like to develop a data dictionary and ER Diagrams for our existing databases. Do you have any tips, scripts, tools for doing so?
3
votes
2answers
34k views

Expired archived logs not deleted from RMAN catalogue

If I issue a LIST EXPIRED command in RMAN, I see a list of files with expired status, that is, they exist in the catalogue, but not on the disk (verified by ls). After issuing CROSSCHECK and DELETE ...
4
votes
3answers
1k views

Detect Index Coalesce

Is there a way to know if a COALESCE has been done on an index? Doing one doesn't seem to update the CREATED, LAST_DDL_TIME, or LAST_ANALYZED attributes. Note: I am not referring to the COALESCE ...