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

8
votes
2answers
8k views

How to retrieve foreign key constraints data

I'm looking for a query allowing to retrieve foreign key infos (each line: referrencing table & field, referrenced table & field) of an entire schema. I've found this, but does not gives all ...
1
vote
2answers
2k views

Dropped table not showing up in recycle bin

I tried dropping a table and then when i viewed the recycle bin nothing was there in it . Earlier too i dropped tables many a times but I was surprised to find no objects whatsover in it . What's the ...
3
votes
1answer
10k views

Oracle 11g hunting down the deadlock, maybe foreign key?

I am trying to understand why a deadlock occurs in my database. The trace tells me that two updates on the same table are locking. In table is the first table to be written to in an transaction, only ...
3
votes
2answers
11k views

Oracle 11g SYSDBA login

I installed Oracle 11g on my win xp machine. Now I want to connect to the database. I was told that to connect to the database from command prompt I should use SQLPLUS "/ AS SYSDBA". But I want to ...
0
votes
1answer
224 views

ORA-04020 on Oracle SE1 (11.2.0.1)

We've been experiencing some deadlock issues on our Oracle SE1 (11.2.0.1) database server (Red Hat 5/64bit) Seeing ORA-04020 errors in the logs. Is going to 11.2.0.2 the only way to resolve the ...
3
votes
3answers
621 views

What's the most cost effective or cheapest way for high availabity on Oracle Standard Edition One?

We are currently using Oracle Standard Edition One running on two physical database server. If I wanted to get higher availability, what's the most cost effective way or cheapest way to achieve this? ...
4
votes
2answers
2k views

Copying Oracle user without dump file

I'm looking for a way to copy an Oracle user and its data to a new one without dump file. Google search brings a tip by Steve Karam, which seems to be exactly what I want. However the page is not ...
3
votes
2answers
8k views

How to free space after deleting the millions of rows from a table

I performed a delete operation on my table . It deleted around 10 millions of rows from it How do I free the space associated with the deleted data. I used the query: alter table tablename ...
3
votes
1answer
5k views

Oracle: Read from CLOB column line by line and insert to the table

I have CLOB column, and wanna read its data line by line to insert it in another table. Now let's create the same situation that I have: Data in a Table MyTable_1 OWNER | TABLE_NAME | SQL ----------...
4
votes
1answer
102 views

Relationship between number of transactions and redo generation

If I execute some SQL statements over many transactions instead of just committing once after all statements, will this have an effect on the amount of redo log generated? And hence on the size of the ...
7
votes
3answers
2k views

Oracle's left join and where clauses errors

CREATE TABLE "ATABLE1" ( "COLUMN1" VARCHAR2(20 BYTE), "COLUMN2" VARCHAR2(20 BYTE) ); CREATE TABLE "ATABLE2" ( "COLUMN1" VARCHAR2(20 BYTE), "COLUMN2" VARCHAR2(20 BYTE) ); ...
1
vote
1answer
131 views

Can a Database request automatically remove itself from the queue after a delay?

I'm using table locking in my Oracle database. I have a small program with a function that locks my table, does some things and unlocks it again. If many instances are run and requests are made ...
4
votes
1answer
3k views

Oracle11g password expiry issue

I have a user which has a default profile in oracle db. I want to reproduce the following warning ERROR: ORA-28002: the password will expire within X days In dba_profile I set following values. ...
-3
votes
1answer
299 views

Steps to create instance, parameter file , password file and database [closed]

_Hi everybody _i wanna all steps to create instance, parameter file , password file and database _manually in oracle 10g _i hope from all to help me
0
votes
1answer
155 views

oracle local partition index

I have a table like this: CREATE TABLE employees (employee_id NUMBER(4) NOT NULL, last_name VARCHAR2(10), department_id NUMBER(2)) PARTITION BY RANGE (department_id) (PARTITION employees_part1 ...
6
votes
3answers
3k views

Why performance degradation with query hint FIRST_ROWS(1000)? [closed]

I have a query with user supplied arguments that can produce a very high amount of rows depending on the user's input. In case the user supplies very unselective arguments, the query takes a lot of ...
1
vote
2answers
2k views

oracle global index vs local index on a partitioned table

I have a table that has range partition by month on a date column (created_time). I was wondering what is more efficient of creating a global index on this column or creating a local index on this ...
5
votes
1answer
1k views

How will Oracle fill a datafile added to a tablespace?

I have a tablespace with one datafile which is 50% used. If I add a new datafile to the tablespace - will the first datafile continue to be used until it reaches full capacity and only then will ...
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
3answers
290 views

How do you protect against malicious PREPROCESSOR attacks in Oracle External Tables?

I'm a new DBA and I recently found out about the option of External Tables in Oracle using the PREPROCESSOR feature ( http://download.oracle.com/otndocs/products/database/enterprise_edition/utilities/...
3
votes
2answers
5k views

Why Sqlcode 100 does not come with exception

I have following query in a PL\SQL procedure on Oracle 10.2: This is the code LOOP BEGIN SELECT a.poid_id0 into v_acc_account_poidid0 FROM account_t a WHERE a.poid_id0=i_acct_id0 ...
2
votes
2answers
39k views

ORA-01654: unable to extend index <name of the index> by 128 in tablespace <name of the Index tablespace>

I am not a DBA but have to resolve this issue. There are 2 permanent tablespaces in my Oracle 9i database. One for the tables and the other for the Indexes other than the Temporary and UNDO ...
1
vote
1answer
3k views

unknown SET option beginning “oracle_sid…”

SQL> set oracle_sid=sid SP2-0735: unknown SET option beginning "oracle_sid..." SQL> emctl start dbconsole; SP2-0734: unknown command beginning "emctl star..." - rest of line ignored. SQL> set ...
5
votes
2answers
9k views

Database logon trigger

To block a particular user and IP combination on an Oracle Database , I created the following trigger, and compiled without errors. Create or replace trigger you_may_not_login after logon on database ...
4
votes
1answer
2k views

How Can I Correctly Pass a Table to a Stored Procedure in Oracle in SQL Plus?

Being new to Oracle, I'm almost certain I'm missing something simple here. This is what I'm trying to run: declare type string_table IS TABLE OF VARCHAR2(512); outuser nvarchar2(512); outrole ...
2
votes
2answers
135 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 ...
0
votes
2answers
116 views

ORACLE DB ERROR

We are currently experiencing a problem with Oracle DB 8. It is no longer starting and we get this message: ERROR at line 1: ORA-01113: file 19 needs media recovery ORA-01110: data file 19: 'E:\...
2
votes
0answers
723 views

FREETEMPORARY on blob with no reference [closed]

In Oracle 11g, we are using XMLELEMENT to get records from tables. The problem seems to be, however, that we are calling SELECT XMLELEMENT(...).getClobVal() from DUAL; which is eating our temp ...
3
votes
1answer
3k views

Oracle not using index when > used in where clause

We have a relatively classic scenario where the date clause of the query generated by the application uses a "greater than" for a given date. Because there is no end clause to the date range, oracle ...
1
vote
2answers
737 views

database normalization 2nf, 3nf

I am doing homework about database normalization. I have to decompose the table into 3nf form showing the steps here is the initial table: |-----------------------------------------------------------...
3
votes
1answer
74 views

Why would you need to put a domain name when install a Oracle database?

When you install an Oracle database or instance, you are able to assign a domain name. An Example: SID: orcl Domain: world.com Service: orcl.world.com Why would it be necessary to add or ...
1
vote
0answers
963 views

What can cause [DataDirect][ODBC Oracle Wire Protocol driver]Connection refused.?

The Title speaks for itself. We have this error message on the client side of the application. The devs suspect a problem on the database. The alert log on the database shows no errors at all. The ...
5
votes
2answers
6k views

Restricting database access per IP addresses

I have filtered IP addresses to an access DB server like this: Set the sqlnet.ora tcp.validnode_checking = YES tcp.invited_nodes = (localhost, 192.168.100.130, 192.168.100.186) But in my ...
2
votes
1answer
4k views

Oracle DB hanging after upgrade to 11.2.0.3

The database is hanging randomly (with some leniency I would say almost each hour). Top session is: select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0 or select /*...
0
votes
1answer
466 views

which DBMS should i go for? [closed]

I am interested in learning a DBMS. As a beginner which DBMS should i go for ? I know few DBMS by name like Oracle,Sql Server,My Sql . How are these DBMS different from each other ? As a beginner ...
4
votes
1answer
380 views

Estimate space left in Oracle

I have a large Oracle table that need to be re-partition. I was wonder there is a way to know that I have enough space to create a copy of this table with new partition and swap the new one with the ...
0
votes
1answer
65 views

Oracle: calling udf that modifies the data. How to do it right?

Say I have a function : create or replace function test_function return integer as PRAGMA AUTONOMOUS_TRANSACTION; t_test integer :=1; begin update test1 set id = id+1 ; // table test1 has, for ...
2
votes
1answer
952 views

Is it possible to automate the configuration of dataguard broker?

We deploy a number of oracle databases every year and a good percentage of them require a dataguard configuration. I've developed a script which is able to perform the database synchronization very ...
2
votes
1answer
1k views

Repartition Primary Key Index online in Oracle

I have an 24x7 OLTP (11gR2) application that endures heavy inserts on particular tables, traffic has increased to the point where we are getting heavy index contention. We use sequences for the ...
3
votes
5answers
10k views

Copying Oracle 11g express Database from one server to another

Basically, I am a MS-SQL developer,and relatively new to Oracle. Even after great deal of research, I was unable to find any way of copying entire database from one server instance to another. In ...
4
votes
2answers
2k views

Tracking SQL queries and rows operated on for documentation/audit

Is there a way to record the SQL queries (and rows that were acted upon) in a session for documentation/audit purposes? For example, in deleting duplicate rows from a table I use the following SQL ...
4
votes
1answer
2k views

How to use the METHOD_OPT parameter in DBMS_STATS.gather_table_stats

Hi there I was just wondering what would be the difference between these 2 METHOD_OPT parameter settings: FOR COLUMNS SIZE 5 some_column and FOR COLUMNS some_column SIZE 5 both will create a ...
1
vote
2answers
2k views

Oracle 11. Updating BLOB field. Db file sequential read inappropriately slow?

I've and Oracle (11 Enterprise) schema with a table CREATE TABLE USER.WSP_BUNDLE ( NODE_ID RAW(16) NOT NULL, BUNDLE_DATA BLOB NOT NULL ); CREATE UNIQUE INDEX USER.WSP_BUNDLE_IDX ...
3
votes
3answers
8k views

Oracle Query by Quarter

I have a query that I want to get data from the past 5 quarters, but it doesn't work the way it should: select to_char(eventdate,'YYYY') || ' Q-' || to_char(eventdate, ' Q'), ... from ... where ...
1
vote
1answer
309 views

Is there an Oracle rdbms monitoring API?

We have about a thousand customers with our application installed out of which 500 have an oracle database. Monitoring the database is only one of the requirements. Installing Oracle Enterprise ...
2
votes
4answers
15k views

ORA-09925: Unable to create audit trail file

I'm using Oracle 11.1.0.6.0 db, on Linux 64 bit server on Amazon cloud. I'm getting following error when I run the connect command SQL> connect / as sysdba ERROR: ORA-09925: Unable to create ...
3
votes
2answers
2k views

Question on Oracle rowid column in every table

I want to know how to use the object number that extract from rowid column? It's seems that this oracle number is not the same as object_id in dba_objects view. Anyone can help me with it please? ...
5
votes
2answers
3k views

Where can I find autoextend events?

In Oracle, where can I see the history of DBFs autoextending? Or get the timestamp that a DBF last autoextended, and by how much?
2
votes
2answers
1k views

Logging/profiling an unpredictably slow stored procedure

I have a stored procedure that sometimes runs significantly slower than others. I have two different timers measuring the execution time at different scopes. One is in the client code, measuring only ...
24
votes
2answers
5k views

How to make a transition from SQL Server DBA to Oracle?

As an interest, if I would to transition from a SQL Server DBA to Oracle what will be the major learning or unlearning that I would have to do? I would assume the concepts are the same and the ...