Oracle Corporations version 10 of its flagship RDBMS product.

learn more… | top users | synonyms (1)

4
votes
1answer
18k 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 ...
3
votes
2answers
7k 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 ...
2
votes
3answers
9k views

Clone / Copy Oracle 10g & 11g

Hi I am absolute begginer in Oracle domain. I would like to learn way how to clone copy oracle database to som backup, use this backup on another server. Oracle is installed on Windows server 2003. ...
2
votes
2answers
3k views

How to debug “IO Error: End of TNS data channel” errors?

I am seeing sporadic reports of an application failing certain queries with an "End of TNS data channel" error from the Oracle (10g) server. Are there any queries I can run (e.g., with SQL*Plus or ...
6
votes
4answers
17k 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 ...
3
votes
4answers
5k 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 ...
16
votes
7answers
7k 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 ...
2
votes
1answer
2k views

Oracle Create Materialized View Script - Takes 12 minutes in DEV Schema, 5+ days in QA Schema

I have a materialized view in my Oracle 10g database that I need to rebuild. So I'm doing something like this... drop materialized view ViewName; CREATE MATERIALIZED VIEW ViewName NOCACHE LOGGING ...
0
votes
0answers
299 views

Reconfigure the registry for new Oracle installation

I have the following oracle homes installed on my Windows machine Oracle Developer Suite Oracle Discoverer Oracle 10G Client. I installed Oracle 10G database manually by following the following ...
2
votes
2answers
506 views

Encountering exception ORA-01555

I was given a problem to solve , in which there is table called Scenarios in the Master Db which contains the details of all the Tablespace for which I have to find the size. The O/P should contain ...
2
votes
1answer
1k views

Tips for Optimizing Oracle Warehouse Builder 11.1 with Oracle DB 10.2

Are there any tips for improving performance of Control Center and Design Center in Oracle Warehouse Builder? At the client side it is very slow to open and refresh Design center and control center ...
3
votes
1answer
842 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 ...
2
votes
2answers
311 views

Recreate indexes and constraint when redefining table with DBMS_REDEFINITON

I have a table A witht the following characteristics: multiple indexes constraints (null, unique, check, foreign key) 2 triggers (before update) primary key If I were to redefine the table with ...
4
votes
3answers
284 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); ...
3
votes
1answer
5k 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
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 ...
9
votes
3answers
8k 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 ...
1
vote
1answer
484 views

Drop Oracle data file with 0 bytes and in offline (needs recovery) state

What can I do to drop a data file mistakenly created and currently with 0 byte and in offline (needs recovery) state?
4
votes
1answer
3k 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', ...
2
votes
2answers
5k views

Execute PL/SQL script block in C#

I am trying to execute a PL/Sql block by using the OracleClientProvider in .Net . The language I am using is c#, the DB is oracle10g What I am actually doing is the following : //ConnectionSting ...
5
votes
3answers
15k 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 ...
5
votes
1answer
414 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
1answer
9k 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 ...
3
votes
1answer
381 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 ...