Oracle Database 10g (not 10g R2). Please also tag oracle for search purposes.

learn more… | top users | synonyms (1)

20
votes
7answers
18k views

Oracle - Any way to view uncommited changes to a particular table?

I'm debugging through a batch process currently that does a lot of DML statements, but doesn't do a commit right away. It would be nice to be able to view the "pending" changes from another session ...
12
votes
1answer
2k views

Commit vs Fast Commit vs Commit Cleanout in Oracle Database

I was wondering whether someone could verify my understanding regarding the differences between those 3 terms as pertaining to Oracle Databases. Many sources confuse these terms and do not explain ...
11
votes
3answers
13k views

Tracing, debugging and fixing Row Lock Contentions

Off late, I've been facing a lot of row lock contentions. The table in contention seems to be a particular table. This is generally what happens - Developer 1 starts a transaction from Oracle ...
11
votes
1answer
54k views

How do I declare and use variables in Oracle?

My main skills are with SQL Server, but I have been asked to do some tuning of an Oracle query. I have written the following SQL: declare @startDate int select @startDate = 20110501 And I get this ...
10
votes
5answers
19k views

Cannot drop nonexistent constraint and cannot create it either

While testing some migration scripts with a copy of production data (scripts run fine with development data) I found a curious situation. A CONSTRAINT has changed so I'm issuing DROP + ADD commands: ...
9
votes
1answer
33k views

Why Oracle sessions are still alive after two weeks being Inactive

DB: Oracle 10g O/S: Windows Server 2003 64 bits I query list of Oracle sessions generated by web applications (filter by program=w3wp.exe) select * from V$session where UPPER(program) LIKE '%W3%' ...
9
votes
1answer
443 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 ...
8
votes
5answers
43k views

create incremental number in oracle sql query

how to create incremental number in oracle sql query without create any table ? I have tried using "with" clause, but I failed to get the expected result. I am using oracle 10g here is the code that ...
8
votes
5answers
5k views

Oracle sort varchar2 column with special characters last

How can I sort in Oracle a Varchar2 or NVarchar2 column to be in my own custom defined order. Or are any existing options available that will put letters first, then numbers, then all special ...
8
votes
2answers
21k views

How do I find an object that doesn't seem to exist in the objects table?

I have an object called cot_ntn_pi_v. I was told this was a synonym. It doesn't appear in the all_synonyms table. It looks like a view or table but I can't find it in the all objects table. I can ...
7
votes
2answers
3k views

Oracle 10g - How to prevent one user from hogging all resources?

I am wondering if there is any way to limit how much resources one user can take on my Oracle system. I want to limit some queries and systems from being able to hog all system resources when they run ...
7
votes
2answers
60 views

Oracle 10g: Is it possible to designate a undo point?

I was wondering if it is possible to designate a point in time, then later "roll-it-back" to that state (undo DDL and DML changes). Maybe using a record of changes and then executing the reverse? Does ...
7
votes
2answers
276 views

Why is Oracle using the index here?

Name Null Type -------- -------- --------- ID NOT NULL NUMBER(4) GROUP_ID NUMBER(4) TEXT CLOB There is a btree index on group_id. Here's how many ...
7
votes
1answer
1k views

Can I anchor my data type to system type?

The following code: create or replace type sqlids_t is table of sys.v_$sql.sql_id%type; ...gives me... Error(2,31): PLS-00329: schema-level type has illegal reference to SYS.V_$SQL I get the ...
6
votes
3answers
14k views

Oracle 10g: Properly using ora_rowscn to detect table row changes (ie, inserts, updates, deletes)

Some research into checking when a table's records were last updated, modified, or deleted has lead me to the pseudo column known as ora_rowscn. First, I do this: select max(ora_rowscn) from ...
6
votes
2answers
4k views

How Long Would Take to Upgrade an 1T Oracle DB from 10g to 11g?

How long would take to upgrade an Oracle DB which has 1T data from 10g to 11g usually/roughly? I need to estimate the down time for it since it is a prod db. Thanks much!
6
votes
3answers
30k 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 ...
6
votes
1answer
930 views

sqlplus consuming 99% CPU

Oracle's installed on one server, I'm running sqlplus on a different (application) server. I've recently encountered a problem getting sqlplus to do anything; it simply spins, consuming 99% CPU even ...
6
votes
1answer
540 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 ...
5
votes
4answers
11k views

Sqlplus parameterized query via file

I would like to issue a parameterized to Oracle via sqlplus with all the the parameters provided in a text file. Is there a way to do this? The query has more parameters than is reasonable for a ...
5
votes
2answers
2k views

Rebuild large Oracle 10g index with insufficient TEMP space?

One of my (Oracle 10g) tables has grown over time to about 840 million rows. An index (DATE, NUMBER(38)) on it occupies 38GB at present. I'd like to change the index (I think it could make good use of ...
5
votes
2answers
32k 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 ...
5
votes
1answer
7k views

Getting the “ORA-00911” error when I am trying to insert more than 1 row

Lets say I have the follow insert statements. INSERT INTO E_PRODUCT VALUES ('PCD2', 'PC Dual Core', 499, 22, 475, 'PC', NULL); INSERT INTO E_PRODUCT VALUES ('PCL4', 'Laptop PC', 599, 9, 225, 'PC', ...
5
votes
1answer
2k views

Oracle 10g express database link to Oracle 8i

I upgraded a 9i install to 11g with only one problem. Database links between 11g and our legacy 8i database are no longer supported. I have to transfer data from 11g to 8i two or three times a day ...
5
votes
2answers
2k views

Oracle View Ignoring External WHERE Clause

Good day, This one has me stumped. I have a rather nasty developer query that I would like to store in a non-materialized Oracle view. The text for the view itself is a bit long to list here, but ...
5
votes
1answer
11k views

How can I remove locks on an object?

One of my queries is running in to a Row Lock Contention. I've tracked down which table is causing that - so now what's the next step ? Which session is causing this table to get locked ? How do I ...
5
votes
1answer
169 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
411 views

Help with Oracle Pipelined function

I have a pivot where I have a contract with tank numbers separated by ; and I've been given this example to pivot them down into rows (which works): SELECT oks_c.contract_number, ...
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 ...
4
votes
2answers
32k views

Delete an oracle instance, the right way

I want to delete an oracle instance (oracle 10.2.0.4.0) created in the AIX 6.0 OS. I know i can delete all the dbf and ctl files in the Terminal but i thinks that's no the best way to do it. I think ...
4
votes
2answers
615 views

Learning Oracle, does it matter if I install 10g or 11g?

I'm taking a class in Databases, they're using Oracle 10g in class, but I'm having some trouble installing the database here in Windows 8 (auth problems), so I thought I'd attempt 11g. Is 11g ...
4
votes
1answer
839 views

Why does it take a long time to drop a function based index in Oracle?

I have a function based index that takes 25 minutes to create on a table of 94 Million rows. When I drop the index, it takes 18 minutes. Why does it take so long? I would have thought the drop would ...
4
votes
2answers
6k views

Generate sample data for an Oracle database [closed]

Good morning, What free [or trial] software can I use to generate sample data for an Oracle database? I've been trying with various tools, but none work. Thanks for all suggestions, Alec Taylor
4
votes
1answer
8k views

Referencing PL/SQL variables in FOR loop

I have written a PL/SQL script to find the size of a long column in a table. Just to make the script generic I am passing the table Name and column name as variables , but I am getting an error saying ...
4
votes
1answer
2k views

Oracle subquery column aliasing syntax?

I have something like: SELECT a, b INTO v_a, v_b FROM ( SELECT x, y FROM whatever UNION SELECT z, w FROM something ) AS subquery (a, b) Which of course doesn't work in Oracle ...
4
votes
3answers
462 views

How can I fix the Oracle syntax of this update by the result of a select in Oracle 10g?

I have a problem with an update statement, that I developed under 11g, when running under 10g. I reduced the problem to: Create table test ( col1 number(10) ); insert into test values (1); ...
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 ...
4
votes
1answer
1k views

Oracle 10.2.0.1.0 listener process stopped

When I am checking my servers I see that there is an error about listener process stopped. I logged in and checked the status of listenere. please see the below status; LSNRCTL> status Connecting ...
4
votes
1answer
547 views

Oracle pivot on a column with delimited data

My data is like: keycol,col1,col2,col3 1,a;b;c,some data,some other data 2,x,some data,some other data 3,y;z,some data,some other data Where a column is delimited in the source system with ...
4
votes
1answer
575 views

Isssue with Database Links converting OEM Job to DBMS_Scheduler Job

We had an OEM job that would run a PL/SQL block using a particular database users credentials. The credentials are the same for two different databases, so there was no problem for the block to use a ...
4
votes
2answers
6k views

Export Oracle tables based on owner + all users from database

I would like to export all user and roles objects from Oracle 10g database to another. I googled it but I cannot find out how to do it. First I use this command: exp system/root@[DB_Name] ...
4
votes
1answer
623 views

Can Oracle Text query return the terms found?

I am querying an Oracle 10g database. The database contains a table with documents that has an oracle text index. I query the database using a query like SELECT SCORE(1), title from news WHERE ...
4
votes
1answer
598 views

Installing oracle RAC 10.2.0.1 install and patch to 10.2.0.5 directly or install CRS ASM and RDBMS then patch?

I'm installing my first RAC and I was wondering if I should install CRS 10.2.0.1 (we are still at 10g for compatibility reasons) then patch to 10.2.0.5 before I install ASM (then patch) and finally ...
4
votes
2answers
448 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. ...
4
votes
1answer
319 views

changing Virtual IP interface names on RAC nodes - how to reconfigure

We are adding an extra interface on our nodes and we are going to bond these so the VIP interface will change from eth5 to bond3. Can someone point on documentation on what to do to reconfigure the ...
4
votes
0answers
403 views

What do I need to do to avoid ORA-32696 errors? [closed]

I have a SELECT query using several WITH blocks. Sometimes it runs successfully. Very often it fails with the error "Lookup Error - Oracle Database Error: ORA-32696: HTI: No free slot". I Googled the ...
3
votes
2answers
8k views

Oracle 10g dump file push into Oracle 11g database

Currently when executing the imp command with the following parameters: file=my.dmp log=my.log fromuser=myuser touser=myuser The following errors occur: IMP-00058: ORACLE error 12560 ...
3
votes
2answers
515 views

How can I change three hundred procedures at once?

I need to change about 300 procedures and packages in the database, due to a migration that will accomplish this weekend. We will do a migration from one server to Exadata. However the database has ...
3
votes
2answers
334 views

How to retrieve disk spaces by removing tables from tablespaces in Oracle 10g?

I'm using a Oracle 10g database. I have a big tablespace MYTBS made of a dozen data files (MYTBS_01, MYTBS_02, ...) of different sizes which have been added through the years. Now i have removed very ...
3
votes
3answers
4k views

Oracle - abort within a “before insert” trigger without throwing an exception

I know that this is an awful thing to do for many reasons, but I am in Get Things Done mode and we have a piece of software, which we can't modify, that writes records to a table. Some of these ...