The second and terminal release of Oracle Corporations Oracle 11g database product. 11.2 was released for Linux on September 1st, 2009. New features added include RAC One Node, Improved data compression ratios (up to 20x), Edition Based Redefinition, Recursive Subquery Factoring, Instance Caging, ...

learn more… | top users | synonyms

5
votes
1answer
164 views

how do I configure a new Oracle 11g instance to match the configuration of the old Oracle 10g server

How can I compare two Oracle database servers which are on two different machines? We need to ensure that the new server is setup correctly. For example, one machine is running Oracle 10g on ...
5
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 ...
5
votes
3answers
1k views

How can a datapump export have its resource usage limited on windows?

Doing an export of a number of users causes high CPU and I/O usage. I can mitigate the effects of the CPU usage using Resource Manager and setting the export session to a low priority group, but how ...
5
votes
1answer
723 views

Enforce data integrity with a recursive table

I have an existing oracle 11g database schema that works with a web application. I am planning an expansion to the application so a web service can do data operations on the database. As part of the ...
5
votes
1answer
1k views

DROP PARTITION strategy using LIST-REF composite partitioning

We have a large Oracle 11gR2 instance with some tables that have recently become an annoyance when deleting data. This database powers a large optimization cluster that takes input metadata from the ...
5
votes
2answers
480 views

Should I group by and then join, or join and then group by?

(oracle's optimizer does the same thing for both approaches when just two tables are involved, thus my question involves three tables) Assume three tables t1, t2 and t3 with a pairwise 1:n ...
5
votes
3answers
3k views

Partition pruning with multiple date columns

I have a large table in Oracle 11g database that holds historical data from several years, so I would like to partition it by year. The problem is that the table has multiple date columns and they are ...
5
votes
1answer
1k views

Trying to copy a schema using impdp

I'm trying to copy a SCHEMA in its entirity using oracle's IMPDP tool. I've setup a database link back to itself and the link works fine. Then I'm running this: impdp username/password@instancename ...
5
votes
2answers
2k views

How to install Oracle 10g Express and 11g Enterprise on same machine

I have to continue maintaining an Oracle 8i database that receives information (about 20 Inserts and Updates a day) from a 9i database. I am upgrading the 9i database to 11g which does not support ...
5
votes
1answer
1k views

Ports necessary for Oracle 11g Replication across a firewall?

We have two Oracle 11g Enterprise Edition servers that need to replicate data via Oracle streams. However, the two servers are on untrusted domains and connect via a VPN. On end of the connection is ...
5
votes
1answer
911 views

How do I have Oracle ASM scan for and repair uncorrectable ECC errors (bad sectors)?

I'm running Oracle ASM (11gR2) with normal redundancy on SATA disks (WD Raptors). This is on a physical standby database, if it matters. Occasionally, disk sectors develop read errors (ECC failures). ...
5
votes
1answer
581 views

What must be in place to validate a XMLTYPE against a schema?

I have a procedure that generates an XMLTYPE and I want to validate it against a schema. The problem is that there seems to be a permissions issue running createSchemaBasedXML because when I run the ...
5
votes
3answers
2k views

Return a fully dynamic table from an Oracle function

I'd like to write a function with two IN parameters where the first one is a varchar and the second a list of varchars. Based on these I want to return a table with varying column amounts and names of ...
4
votes
2answers
2k views

What storage engines work with Oracle?

http://en.wikipedia.org/wiki/Database_engine mentions about database engines aka storage engines. What all storage engines can be used with the Oracle database?
4
votes
4answers
7k views

Why does = NULL not work in Oracle?

I recently realized that we need to use a special syntax IS NULL to compare a literal to NULL. Why does = NULL not work here?
4
votes
3answers
5k views

Is (and if how) it possible in Oracle to “insert into <table> values <rowtype-variable>” if <table> has virtual columns

Here's a table create table tq84_virtual_test_without ( col_1 number, col_2 number, col_3 number, col_4 number, col_5 number ); with the rule that col_5's value is the sum of the other ...
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 ...
4
votes
2answers
134 views

Cummulative Sum with Reset Value

Consider the following table: ID | GROUP_ID | ORDER_VAL | RESET_VAL | VAL ---+----------+-----------+-----------+----- 1 | 1 | 1 | (null) | 3 2 | 1 | 2 | (null) ...
4
votes
2answers
232 views

Is there a bug with PL/Scope in combination with associative arrays?

I believe I stumbled upon a bug with PL/Scope in combination with associative arrays, but perhaps I overlook something. I have the following package create or replace package tq84_pkg_c as ...
4
votes
1answer
365 views

Dodgy causes of ORA-12549?

I am dealing with a rather massive ETL processing which keeps throwing me the ORA-12549 error. Have been looking for problems in the database so far and nothing. The DBA says the database is "fine" - ...
4
votes
2answers
1k views

Oracle 11g see transaction or session start time

I want to know when a session or transaction started. The deadlock file doesn't give me this information. Is there some logfile that keeps these records? I've got a transaction id "TX-1234-abcd", a ...
4
votes
3answers
22k views

Oracle 11g - Create Database fails?

I've just installed Oracle 11g and linked it to SQL Developer. I want to create a database for my django install. CREATE database 'django'; Error starting at line 1 in command: create database ...
4
votes
2answers
13k views

Which picture does the ORA-01830 error message refer to?

ORA-01830: date format picture ends before converting entire input string About the error message. I am confused. Which picture is the error message talking about? All I provided was a date.
4
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 ...
4
votes
3answers
6k views

Does an Oracle nvarchar2 take twice as much space as varchar2?

Regarding point 4 of this question (which is in reference to MS SQL Server): VARCHAR does not store Unicode characters. NVARCHAR does store Unicode characters. Today's applications should ...
4
votes
2answers
1k 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 ----------------------------------------- -------- ...
4
votes
2answers
1k views

Identify which parameters were passed to a procedure (Oracle)

Procedures in PL/SQL can have default values for parameters, but the caller can pass values that are identical to the default values. Is there a way from within the PL/SQL to determine whether a ...
4
votes
2answers
722 views

Multiple JOIN of 6 tables via 5 database instances

I'm currently reengineering my corporates user management and created a table which lists all users of all the 5 database instances. Next step is, that I need to write a query which shows me all the ...
4
votes
1answer
6k views

How to find the schema name of a data pump dmp file?

I have been given a DMP data pump export file to import into my local Oracle instance. I've tried running this command line: impdp full=Y directory=DATA_PUMP_DIR dumpfile=MyDumpFile.dmp ...
4
votes
1answer
449 views

How to use simple output parameters?

I'm new to Oracle, so I apologize for such a simple question, but I've already wasted several hours on it. I have the following stored procedure: create or replace procedure MyTestProc (outparam OUT ...
4
votes
2answers
482 views

Oracle build_plan_xml visualizer

Background: Please review my related question that involves SQL Server : Interpreting SQL Server's Showplan XML I now have a very nice looking Execution Plan display for SQL Server (as a result of my ...
4
votes
2answers
662 views

Migrating to Oracle Flashback for Historization on Oracle 11g

Our application built using Oracle 11g requires that we keep historic data. The historization is legally mandated, and is mainly used for manual queries, when investigating problems, etc. Basically ...
4
votes
3answers
726 views

What are the 'free to use' workload generators available for Oracle? [closed]

A couple that I know about are http://www.dominicgiles.com/swingbench.html and http://hammerora.sourceforge.net/ Are there any others? I am looking for tools what can generate random workload ...
4
votes
1answer
420 views

Help with Query Tuning

EDIT - drachenstern: see this question for more information on this problem and on the source of his query below: Group data by non-unique keys by distinct time range Can someone please advise ...
4
votes
2answers
5k views

How to remove all hidden parameters from Oracle Database

How do I remove all hidden parameters from Oracle Database before upgrading? I'm using the SQL query below. So what do you recommended? SELECT name,description from SYS.V$PARAMETER WHERE name LIKE ...
4
votes
1answer
18k views

How to list all users that have grants over a package

I'm looking for some query to list all users that have grants over a package. For example, user Schema_A has been granted to execute package: B.MyPackage. I'm querying views or tables like: ...
4
votes
1answer
9k views

oracle format specifiers: to_number vs to_char

SQL> select TO_NUMBER(123.56,'999.9') from dual; select TO_NUMBER(123.56,'999.9') from dual * ERROR at line 1: ORA-01722: invalid number SQL> select TO_CHAR(123.56,'999.9') ...
4
votes
1answer
2k views

Materialized view: compilation_error

When executing the following code on the SCOTT schema I receive a compilation_error. What am I doing wrong here? I doesn't even tell me what the error is. SQL> create materialized view dept_emp_mv ...
4
votes
2answers
2k views

How many control files should I have?

On the Oracle Database Appliance, the default deployment only gives you a single control file. I find this a little puzzling. The single control file causes a policy violation in the automatically ...
4
votes
1answer
6k views

Is there a timeout for a query over database link?

Edit/Preface: This question has been migrated from SO as I am particularly interested on the question on timeouts on DB link queries. The provided workaround from SO is somewhat OK, but I'm really ...
4
votes
2answers
3k views

Determine Index Compression Candidates Online

By doing the following I can determine if an index will benefit from compression and how many columns should be included in the compression: ANALYZE INDEX Owner.IndexName VALIDATE STRUCTURE OFFLINE; ...
4
votes
3answers
970 views

TNS-12541 Error

I have grid installed in my pc but the problem is LISTENER does not start automatically. I have to start listener manually and then only LISTENER get started. But before starting listener if you check ...
4
votes
1answer
2k views

How to determine Oracle LOB storage footprint?

With SECUREFILE storage I can specify whether I want compression (and the level of it) and deduplication, and it's all a trade-off between time and space. Timing is fairly easy to profile but what's ...
4
votes
2answers
826 views

Multicolumn extended statistiscs, NLS_SORT and NLS_COMP, CHAR columns, and the bane of my existance

This is as a result of this question. So I'm working with a query that looks like this: select count(*) from table1 where col1 = 123 and col2 = '1'; The problem is this: col2 is defined as a ...
4
votes
1answer
6k views

How can I view what NLS settings are active for other session?

I've got another session that I suspect isn't running with the 'proper' NLS settings for us in Oracle 11gR2. What can I query to show the NLS settings for a different active session - all of the ...
4
votes
1answer
3k views

Drop user did not reduce size of tablespace file

I have a development environment in which I frequently copy a certain schema. When I'm done with the copy, I will use OracleEM to drop that user/schema. My problem is that this does not seem to ...
4
votes
2answers
2k views

Oracle extents - How can I have so many with this small size?

This is a different question than the one with the almost-identical title. Again, this is more curiosity about how Oracle Database works than anything I need to change (yes I know multiple extents ...
4
votes
1answer
6k views

Cannot change serivce name for Oracle

I'm trying to change the service name of an Oracle 11.2.0.3 installation on a Windows 2003 server. During installation the service name was defined with the default domain, but we'd like to get rid ...
4
votes
2answers
1k views

Compare two Oracle database roles

I'm trying to re-engineer my corporates user managament and want to compare all the roles with each other to find duplicate ones. Doing it by hand is really tiring and I need to compare like 150 ...
4
votes
1answer
544 views

How to I interleave logs from two different Oracle tables?

I have two log tables, which are populated from two different PL/SQL packages. It's often useful to keep the data separate, but sometimes I'd like to "interleave" the two tables so I can see what's ...