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

2
votes
2answers
2k views

Oracle - How can I find the session id of a shell script (Linux)?

I have a Linux script run periodically from crontab which executes some sql scripts. How can I get/identify the session of that running job? thanks
5
votes
1answer
176 views

Retrieve Indices of Associative Array as Collection

In PL/SQL, suppose I have some associative array defined as follows: declare type a_arr_t is table of PLS_INTEGER index by PLS_INTEGER; a_arr a_arr_t; I then, sparsely, populate the ...
4
votes
1answer
393 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" - ...
1
vote
1answer
803 views

How can I import Create Table Statement from SQL Developer into Visio to create an Entity Relationship Diagram?

I have the following Create table statement from SQL Developer and I am trying to generate an ERD Diagram using 2003 Visio. I was wondering what I need to do to convert the Create Table from SQL ...
1
vote
1answer
605 views

Testing an Oracle DB connection before it is made - ERROR?

I tried to make a new oracle DB connection with user name and password. I entered the connection name, user name and password in the new connection window. When I click the "test" button or the ...
3
votes
3answers
6k views

Revert to old plan in Oracle

A weekly, scheduled, schema wide stats update was run on our Oracle 11R2 RAC cluster on Sunday. One of the queries had no apparent data or code change but now runs at 3 hours instead of 20 minutes. ...
6
votes
3answers
7k views

ALTER SYSTEM DISCONNECT SESSION doesn't work

Oracle 11g. The devs leave sessions connected overnight, sometimes certain cron jobs fail because of that. I am trying to kill all user sessions, but I can't. I login as: sqlplus system/[password] ...
5
votes
2answers
5k views

Session in “Sql*net message from client”, but is active

I've Googled around, and my impression is that Sql*net message from client suggests the Oracle DBMS is waiting for the client to send new commands to the DBMS, and therefore any time spent in ...
2
votes
1answer
466 views

Oracle 10.2g Character Set Migration

I am in the process of changing the character set for our database. I have a couple questions with regard to the in-place migration using the CSALTER script and performing the full import/export. ...
2
votes
2answers
67 views

Does my associated ASM Instance have any play in Re-naming my SID?

Greetings DBA Experts, I have changed my Oracle SID in the past by updating the init.ora file, and updating my environment (vars, tns, lintener, etc..), yet not since we moved to 11g with ASM. I ...
1
vote
1answer
17k views

How do I find out the “redo log file max size” in Oracle 11R2

I'm working with Oracle 11R2 and I want to see the max size of my redo log files (I didn't install Oracle so I'm not aware of the configuration). I have searched the Internet but all I have found ...
2
votes
4answers
671 views

Oracle hint failure

When I run the PL/SQL block after the CREATE statement, I receive the duplicate key error I would expect if I did not add the hint. Any ideas to why the hint does not work? CREATE TABLE Sandbox.Clue ...
4
votes
1answer
4k views

Atypical problem with ORA-01445

I have problem with following query Q1: SELECT ADDRESS.STREET FROM MYSALES.MYADDRESS ADDRESS INNER JOIN MYSALES.MYCOUNTRY MCOUNTRY ON MCOUNTRY.MYCOUNTRY_ID = ADDRESS.MYCOUNTRY_ID INNER JOIN ...
1
vote
1answer
127 views

Verify if I can use a given directory

Admin creates an Oracle directory: CREATE OR REPLACE DIRECTORY EXAMPLE AS '\\server\share\test'; GRANT READ, WRITE ON DIRECTORY EXAMPLE TO JOHN; Admin can test whether the directory exists: SELECT ...
1
vote
1answer
578 views

Oracle Streams Setup Error

I am running Oracle 11g on a Linux box. I am trying to run the following command to set my streams user grants and other authorizations: BEGIN DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE( grantee ...
2
votes
2answers
622 views

simulate sequence on mysql in preparation for move to oracle

I've been developing an application against MySQL while a new development server has been slowly being set up. The new dev server will use oracle, and since we're getting close to it being ready, I ...
9
votes
3answers
296 views

Can I rely on functions being executed first in SQL

Please consider the following script: create or replace function f(p_limit in integer) return integer as begin set_global_context ('limit', p_limit); return p_limit; end; / create view v as ...
1
vote
1answer
4k views

ORA-12541: TNS:no listener using EXPDP

I need to export an Oracle 11g database to an Oracle 10g instance. I have the command, which I believe is correct: expdp user/pass@192.168.10.8 full=Y VERSION=10.2 directory=m_dump ...
1
vote
1answer
271 views

How to create a new DB with same data from another DB in oracle 10g

I trying to copy entire data from old database to newly created database in oracle 10g. please any one tell me the steps to do the process.
0
votes
2answers
2k views

Why won't Oracle alter the size of a column that is used for sub partitioning?

I am trying to resize a varchar2 column, when I get an ORA-14265 error: ORA-14265: data type or length of a table subpartitioning column may not be changed With Oracle providing the unhelpful: ...
0
votes
1answer
3k views

Oracle big datafile restore fails using Netbackup (ORA-19511, ORA-27190)

I am restoring datafiles about 1000GB in size. I am using Netbackup as a backup server. After 2 hours reading Netbackup fails with the following error: 7/25/2012 3:27:50 PM - Error bpbrm(pid=1176) ...
2
votes
2answers
5k views

Why does dbms_scheduler max_run_duration not raise event JOB_OVER_MAX_DUR?

Starting from this question and this example I've tried to create an event based dbms_scheduler job, which waits for event JOB_OVER_MAX_DUR raised from jobs, which exceeded their max_run_duration. ...
4
votes
2answers
22k views

Oracle 11G - Index Rebuild VS GATHER_TABLE_STATS

Quick question, after populating my table, I noticed through my explain plan that indexes, in most cases, only seems to take effect after I perform GATHER_TABLE_STATS: Exec ...
1
vote
1answer
110 views

Stuck with Oracle 9i

This is my first time installing Oracle 9i. I want to create a simple database. When I go to "Oracle Enterprise Manager Console" and add a database to the tree, I cannot access the database as I ...
4
votes
1answer
7k 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 ...
1
vote
1answer
3k views

ORA-00379 no free buffers

I'm trying to do collection_manager in OVPI but I'm getting db error ORA-00379 no free buffers available in buffer pool default for block size 32k oracle 10.2 Thanks for any help in advance
2
votes
2answers
3k views

Advanced Replication vs Oracle Streams

I'd like to know what is advantages of Streams over Advanced Replication. I wanted to use updatable materialized views on my project, but on one forum I read comment that if we don't use very old ...
3
votes
2answers
8k views

Troubleshooting Oracle ORA-08103 - object no longer exists

I am getting this error from Oracle intermittently. A trigger is truncating a partition from a partitioned table while a long-running query is running against it. Since I'm truncating a different ...
5
votes
3answers
14k views

Understanding Oracle's ALL_TAB_COLUMNS

I'm new to Oracle and database administration in general. As context, I want to create a Java class that will give me the information I would get using DESC SOME_TABLE. I cannot find any specific ...
3
votes
1answer
8k views

Is it possible to do a datapump import with a different user than did the export?

I have one db dump below is the log file for that dump Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** ...
0
votes
1answer
132 views

Similar queries taking different times to complete

I had the following query: SELECT nvl(sum(adjust1),0) FROM ( SELECT ManyOperationsOnFieldX adjust1, a, b, c, d, e FROM ( SELECT a, b, c, d, e, SubStr(balance, ...
-4
votes
1answer
3k views

Single query to copy data from 3 tables into a single empty table

In an interview today, I was asked if I could write a single query to copy data from 3 source tables to an empty destination table. I started saying "I will use a temp table or table variables", ...
3
votes
1answer
544 views

Oracle: Interval partitioning and dependant tables

I'm not a DBA, but I've been asked to look at a database which is expected to handle up to 80M records in one of it's tables. This table also has two tables linking to it via foreign keys with up to ...
1
vote
3answers
21k views

Shutdown one node in RAC

I want to shutdown one node in RAC. I did the following steps: Shutdown database instance export ORACLE_SID=mydb1 sqlplus / as sysdba shutdown immediate; shutdown ASM instance . oraenv +ASM1 ...
1
vote
0answers
152 views

XML parsing failed

I'm using a query on a oracle db which extracts values from clobs using the xmltype function and an xpath. Unfortunately the query fails and reports a 'XML Parsing failed'. I assume that one of the ...
2
votes
0answers
1k views

oracle crash - not starting

We have a Windows Server 2008 R2 Enterprise x64 Oracle 10.2.0.3 Adempiere - ERP We had a power breakdown at 03.00 AM. At 07.30 AM we started the server At 08.00 our colleagues observed that the ...
1
vote
1answer
482 views

Ora - 00088 error: command cannot be executed by shared server

I get this error from the database when I run the query: select status, error from v$archive_dest where dest_id=2; It's a dataguard environment. I have never seen this problem before. I'm not ...
0
votes
0answers
42 views

Resource to learn writing most efficient and highly optimized sql queries [duplicate]

Possible Duplicate: Learning to Optimize SQL Queries and Understand Execution Plans - Resources? Can some one point me to a great book/great books or some really solid reference/references ...
5
votes
2answers
5k views

Does order matter for composite primary key in Oracle or any database?

I have a Composite Primary key which is combination of Column1, and Column2. What would be the difference if I have Composite key with Column2, and Column1. Does it even really matter which order ...
0
votes
1answer
779 views

Unable to restore database in Oracle 10g

I created a database backup using the expdp command in Oracle 10g. The backup appears to be created successfully. However, while restoring using impdp command I get the following error message. ...
0
votes
1answer
5k views

Oracle Forms Runtime error

When I am running a form in Forms 11g http://rahulsingh-pc:8888/forms/frmservlet I'm getting an error: Failure of server APACHE bridge: No backend server available for connection: timed out after ...
1
vote
1answer
624 views

Help with getters & setters in PL/SQL

I am not a db guy, and I have a very beginner's knowledge bout PL/SQL. That being clear now the question: I have 2 tables one file level & another batch level. In a day, only 1 file_id is ...
2
votes
1answer
1k views

Are multiple dbf files better than one?

Is it better to distribute a large database over several dbf files than over a single one?
4
votes
2answers
3k views

How I should fix the following update statement to make it run faster?

I have the following DML: UPDATE rc_usutb011 u SET cod_ant=( SELECT cod_hst FROM rc_pdptb101 p WHERE ocip_hst=ocip_ant AND p.sector=u.sector ); The explain plan ...
0
votes
1answer
5k views

How to take oracle 10g backup? [duplicate]

Possible Duplicate: How to take oracle 10g backup? I followed this http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php link to create a db backup. I used this command to create ...
1
vote
1answer
16k views

Resolving ORA-01033 “initialization or shutdown in progress”

I followed the instructions at http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php to create a database export. I used: expdp scott/tiger@db10g full=Y directory=TEST_DIR ...
1
vote
2answers
2k views

In Oracle, how can I give one user privileges to alter the schema of another user?

I have an Oracle DB (10.2), and I have a consultant (Dogbert) that I want to do some work on it. In particular they need to alter the table structures of tables in a schema (we'll call it Wally) that ...
1
vote
1answer
588 views

Oracle out-of-place upgrade on same host: impdp issues

I am testing an out-of-place Oracle upgrade from 11.2.0.2 to 11.2.0.3 on the same host. From what I've read, expdp and impdp is the way to go. My problem is that since I am importing to another ...
5
votes
2answers
7k views

Clean trace files on Oracle 11g

The partition containing my Oracle 11g installation began to fill up with trace files and when I went looking for what are these I've found this disturbing post about how to disable the trace file ...
3
votes
1answer
3k views

how to find oracle calculated/computed column list using data dictionary views

I would like to find computed column list in oracle database using Oracle Data Dictionary Views. I would like to add more information. Suppose I have following computed/calculated column in ...