Oracle Corporations version 10 of its flagship RDBMS product.
1
vote
1answer
2k views
Cleaning Oracle In-Active Sessions
There a are huge number of in-active sessions persisting in our database.How can we clean these in-active sessions?
Database - Oracle 10g R2 with ASM
DB Size - 5TB
SGA - 78GB
PGA - 10GB
Maximum ...
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 ...
2
votes
1answer
360 views
Oracle Database Block Size
One of our Production database has a rather peculiar block size of 16KB. This database was created about 2 years ago and at that time the size of the db was approx 1TB only.However over the past 2 ...
1
vote
1answer
287 views
Oracle Backup and Recovery
My backed up archivelog file format is 'al_%U'. During recovery it shows the following error:
RMAN-06054: media recovery requesting unknown log: thread 1 seq 17 lowscn 671075
How to determine which ...
2
votes
2answers
1k 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
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 ...
1
vote
2answers
6k 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
2k 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
6k 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
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 ...
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 ...
2
votes
1answer
1k 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
...
1
vote
1answer
256 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
455 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
948 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
575 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
220 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
219 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
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 ...
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 ...
7
votes
3answers
5k 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
361 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
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', ...
2
votes
2answers
4k 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 ...
4
votes
3answers
7k 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
330 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
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 ...
3
votes
1answer
310 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 ...
