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

3
votes
2answers
280 views

How to create a user with a particular user_id?

Is it possible to specify the user_id of a user while creating it? SQL> create user user1 identified by user1; User created. SQL> select user_id, username from all_users where ...
3
votes
1answer
3k 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 ...
3
votes
1answer
3k views

How to connect multiple users/schemas in Oracle 11g?

I'm almost new user to databases, so I think I'm doing something wrong. I have the database created. I created 3 users, 1 it's an Admin, 2 are just users. I created 2 roles, 1 for the Admin and 2 ...
3
votes
1answer
834 views

How far should bind variables go in Oracle?

I am currently examining all database users's SQL code in our department for not using bind variables. We do experience an ORA-04031 error within a constant period of time. Now, I have a quite firm ...
3
votes
1answer
627 views

Query taking long on table with low record count

I have an Oracle flat table that grew to just below 800MB. The table was truncated and new records are being inserted. Currently there are <40 records in the table but a select * takes >30secs. ...
3
votes
2answers
493 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 ...
3
votes
3answers
4k 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 ...
3
votes
1answer
585 views

Oracle explain for cascading delete is wrong

My deletes in Oracle 11 are very slow. The tables are linked by foreign keys and every foreign key constraint has a delete cascade set on it. If I hit the explain button on a statement like this ...
3
votes
1answer
3k views

How can I delete a corrupted table space?

Problem: On an Oracle 11.2g DB; We have a corrupted TS that does not allow starting the database instance above mount state. The TS cannot be removed with standard methods (i.e., enterprise manager). ...
3
votes
1answer
4k views

SQL*Loader-605: Non-data dependent ORACLE error occurred

As I try to load data into Oracle, I've encountered SQL*Loader-605: Non-data dependent ORACLE error occurred error. Data is (are?) in .txt file and its size is 95GB. Here are some more details ...
3
votes
1answer
527 views

In a two-server dataguard setup, can I store the recovery catalog in the same database?

We currently have an Oracle 11gR2 database that is backed up with rman, with the rman records stored in the control files. Control file auto-backup is turned on. Backups are to disk (and, ultimately, ...
3
votes
1answer
321 views

Wait time differences in Oracle

I'm trying to track the different wait time classes for current Oracle sessions but I must be misunderstanding something. When I compare the sum(time_waited)/1000 for a given session in ...
3
votes
1answer
1k views

Network design considerations for Oracle Database Appliance

With the introduction of Oracle Engineered Systems the DBA is moved somewhat closer to infrastructure design decisions, and expected to at least have some opinions of the network design requirements ...
3
votes
1answer
336 views

Log rotation of diag logs?

In on of our Oracle 11.2.0 installations the directory database\diag\rdbms\$DB\$DB\alert is filled with hundreds of xml files (log.xml, log1.xml ...). Can Oracle be configured to do log rotation ...
3
votes
1answer
239 views

Oracle 11, incremental backup goes wrong

Got a pretty big instance on a one-week backup cycle with an incremental (differential) strategy .. or rather, that's what it was supposed to be. Somehow the daily differentials seem to come out as ...
3
votes
2answers
430 views

Oracle extents - How can I have so few with this large size?

Note on duplication: This is not a duplicate of "Oracle extents - How can I have so many with this small size?" This is a separate question I entered which happens to be in the same domain. This is ...
3
votes
2answers
2k views

Create unique DB_NAME and DB_DOMAIN for an Oracle 11g instance

I am somewhat confused by these terms in the init.ora file: db_domain db_name instance_name plus the database name which is provided to the create database command. I do not completely understand ...
3
votes
1answer
4k views

Oracle 11g hunting down the deadlock, maybe foreign key?

I am trying to understand why a deadlock occurs in my database. The trace tells me that two updates on the same table are locking. In table is the first table to be written to in an transaction, only ...
3
votes
1answer
603 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 ...
3
votes
1answer
2k 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 ...
3
votes
3answers
156 views

Nested table as condition prevents using index

The following query joining a subquery does a TABLE ACCESS FULL when the master-table uses nested table as condition. When the condition is a simple =, the same query does an INDEX RANGE SCAN as ...
3
votes
1answer
257 views

best way to create multiple similar tables - CTAS vs INSERT

I am creating a few dozen tables as CREATE TABLE AS SELECT (CTAS). The only difference in the SELECT statements is specifying a different value in the WHERE clause for TEAM_ID. I'm an Oracle newbie ...
3
votes
2answers
206 views

Periodic Oracle restarts…intentionally

Taking on a new dba role for an Oracle 11gR2 2 node RAC, I've been told that about 9 times a year, in schedule with other software releases, we intentionally shutdown and restart the database. It ...
3
votes
3answers
318 views

Why does the query cost change so much and how to prevent it

The query causing me the issue is too big, but the piece that seems to be the core is quite simple, I will try to go with that: The query has an structure like: SELECT ... a lot of stuff ... ...
3
votes
1answer
2k 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 ...
3
votes
1answer
710 views

Objects in v$bh but not dba_objects

The Oracle documentation has a section in the 11.2 Performance Tuning Guide with the title Determining Which Segments Have Many Buffers in the Pool. The section lists the following query: SELECT ...
3
votes
1answer
130 views

Thoughts about Oracle backup procedure for 11.2 (beginner question)

Two days ago we "found out" that our old, not taken care of, horribly neglected, Oracle DB server was extremely important to our business. The only person who knew anything about this server was ...
3
votes
2answers
1k views

Oracle 11gR2 expdp table converted to CSV from Linux

With Oracle expdp, I can export the tables I want with expdp INCLUDE=TABLE:table1,table2,table3 I need a way to convert this into CSV on a linux machine. I've seen Windows tools but don't know ...
3
votes
1answer
2k views

Installing 10g client next to 11g database on same computer

I have Oracle 11.2.0.1.0 installed on Windows 7 64 bit I want to import data from warehouse which has Oracle 10.2.0.4.0 installed. When I try to import impdp usr/pass@WAREHOUSE TABLES=TABLE ...
3
votes
1answer
122 views

Why is add timestamp column with default not null so slow in Oracle 11g?

In Oracle I need to add a new column with type timestamp with time zone to a large table (80 million rows). I cannot have more than 30 - 60 seconds of downtime when I run it in production. This ...
3
votes
1answer
82 views

connection issue / Oracle Express 11g in windows7 32bit

I have an issue with Oracle Express 11g in windows7 32bit. Everytime when I try to conncet to the database I get this error message. How can I fix it please!
3
votes
1answer
125 views

Oracle Datapump: modify fully qualified objects when cloning a schema

I am confronted with a problem for which I have not found an elegant solution: I need to clone a schema within the same database, so I use datapump's remap_schema import parameter. The problem is ...
3
votes
1answer
328 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 ...
3
votes
1answer
58 views

INDEX on a table where I'm only filtering on a row_number()

I've got the following table (scaled down to a MWE): Fiddle here. CREATE TABLE weekly ( yearnum int, weeknum int, location varchar2(5), weekly_count int, weekly_sum int); INSERT INTO weekly ...
3
votes
1answer
1k views

Unable to create a tablespace, unusual error

I'm having difficulty creating tablespaces with Oracle 11gR2 on a fiber attached disk array. Permissions are correct and there is ample space available. I can create tablespaces on other mounts that ...
3
votes
1answer
368 views

Is there a way to allow a proxy connection to use passed authentication in a public database link?

I think the answer to this is no, but I could be wrong. If nothing else it might be nice for others to be aware of this limitation of proxy connections.
3
votes
0answers
202 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 ...
3
votes
1answer
502 views

Oracle: How to gather stats in a logical standby database?

I have a Primary and a (logical) Standby Database. The Schema 'APP' gets synced. Now a User has performance issues running a query against some tables in this 'APP' schema of the Standby Database. On ...
3
votes
1answer
1k views

Why ORA-00932: Inconsistent Types error?

Sorry for the meaningless title, but I couldn't think up a better one. I'm using Oracle 11g r2. The following query raise ORA-00932 error: SELECT BBB.FIELD1 FROM TABLE0 AAA JOIN (SELECT * FROM ...
3
votes
1answer
3k views

Add an additional listener to Oracle Database Appliance

After deployment of Oracle Database Appliance with OAK 2.3.0 the standard listeners are configured for the public network on bond0. That includes SCAN listeners and one VIP listener per node. This ...
3
votes
2answers
911 views

Oracle imp crashes without error

I have Oracle 10g XE database locally. I export schema with something like exp %USER%/%PASSWD%@XE statistics=NONE owner=%USER% file=dump.dmp Exp is 10.2.0.1.0 version - so is the DB. Than I take ...
3
votes
2answers
719 views

Increase Oracle buffer cache gets slower

So we're contemplating increasing our Oracle 11gR2 buffer cache. They said they tried it in the past and things appeared to get worse - so they undid the change. They, however, have no idea why ...
2
votes
2answers
2k 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?
2
votes
2answers
4k views

Why can't we specify group functions in Oracle in the where clause?

SQL> desc tab1 Name Null? Type ----------------------------------------- -------- ---------------------------- ID ...
2
votes
3answers
321 views

How does a database use the SYS schema even when we change the password?

From the thread What is the difference between sys and system accounts in Oracle databases?, I understand that sys schema is supposed to be used only by the database. But still an administrator can ...
2
votes
3answers
1k views

How to automate Oracle DB installation (like a 'hands-free' install)?

I installed the Oracle 11g2 'standard' edition using the following zip-files: linux.x64_11gR2_database_1of2.zip linux.x64_11gR2_database_2of2.zip I used the installation guide by Oracle which I ...
2
votes
1answer
628 views

Flashback feature in Oracle 11g?

I am using Oracle 11gR1. As a part of a project, I have to execute multiple update statements by disabling a few constraints (triggers and check constraints) which could result in a corrupted ...
2
votes
1answer
7k views

Getting “ORA-00942: table or view does not exist” while table does exist

I'm fairly new to Oracle database. I have installed Oracle Database 11g R2 on Oracle Linux 6. I have successfully created a new database with dbca and connected to the database using: $ sqlplus "/ as ...
2
votes
2answers
322 views

How make convincing case to DBA to use Oracle external tables for 500 MB to 5 GB files?

In our data feeds, we have a stack of XML files as well as numerous flat files to put into an Oracle11g database. We flatten the XML into delimited files, and load the whole set using SQLLoader. I ...
2
votes
2answers
3k 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?