Oracle Corporations version 10 of its flagship RDBMS product.
14
votes
7answers
4k views
Oracle - Any way to view uncommited changes to a particular table?
I'm debugging through a batch proess 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 while the transaction ...
9
votes
1answer
419 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 ...
9
votes
1answer
179 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 ...
7
votes
3answers
6k 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 ...
7
votes
5answers
1k 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 ...
7
votes
2answers
55 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
229 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 ...
6
votes
4answers
7k 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 ...
6
votes
2answers
2k 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
2answers
943 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 ...
5
votes
3answers
4k 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:
...
5
votes
1answer
944 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
1answer
388 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 ...
5
votes
1answer
135 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
2answers
600 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
340 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 ...
4
votes
2answers
349 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
2answers
3k 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 ...
4
votes
2answers
1k 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 ...
4
votes
3answers
8k 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 ...
4
votes
1answer
685 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
221 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
2k 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', ...
4
votes
1answer
40 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
7k 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 ...
4
votes
2answers
204 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 ...
4
votes
1answer
219 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
2answers
329 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
127 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.
...
3
votes
2answers
5k 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
275 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
4answers
3k 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 ...
3
votes
2answers
2k 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 ...
3
votes
3answers
255 views
When are Archived Redo Logs generated?
We have an instance in Oracle 10g. Only one schema is used for a web application (Internet-facing), which is only user for reading information, no adding or modifying data. The rest of schemas are ...
3
votes
1answer
92 views
Using Inteval Datatype to store result of calculation
I want to create a table which has hiredate and Interval of hiredate and sysdate. How to create a column that automaticlaly stores the calculation.
3
votes
1answer
276 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 ...
3
votes
1answer
3k 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 ...
3
votes
1answer
320 views
How to get the Oracle Text Index's underlying Virtual XML Document?
The Oracle Text Reference 10g states that when we index our table using multi-column datastore, the system concatenates the text columns, tagging the column text, and indexes the text as a virtual XML ...
3
votes
1answer
1k 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 ...
3
votes
2answers
2k 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 ...
3
votes
1answer
589 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 ...
3
votes
1answer
131 views
Can this be accomplished with Oracles Fine Grained Auditing?
We need to audit when a table is accessed (either select, update or delete) and I know Oracles Fine Grained Auditing can handle that. The issue we are having is that all of our users use the same ...
3
votes
1answer
1k 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
2answers
2k 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] ...
3
votes
0answers
277 views
Convert Oracle Query for Date to Mysql queries
Need to convert the following oracle queries to MYSQL queries.
1)
select to_char(to_date(?,'DD-MM-YY'))
from dual;
2)
select to_char(next_day(to_date(?,'DD-MM-YY'),'FRIDAY'))
from dual;
3)
...
3
votes
0answers
126 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 ...
2
votes
2answers
2k 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 ...
2
votes
2answers
695 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 ...
2
votes
2answers
100 views
How do I pull a backup of an oracle database so I can send it to a vendor
First of all, I know squat about Oracle databases.
A vendor who is looking at converting our data from an older piece of software with an Oracle DB back-end wants a dump of our database to see what ...
2
votes
1answer
425 views
ORA-00984: column not allowed here
I am running Oracle 10g. I am writing a script (a delta script over a created database) that will be executed in SQL*Plus. It is needed to fix a bug.
I need to know if a row exist in a table, and if ...
