Oracle FAQ Page

Data Storage

Q: I want to move a database file to another directory. What should I do?

A: Perform the following procedure.

First, shutdown the instance: shutdown
Move the file to the new location.
Mount the database: startup mount
Notify Oracle that you’ve moved the file:alter database rename file '' to '';
For example: you moved the file usr1.ora from d:oradatabaseorcl to e:oradatabaseorcl. In this case, executealter database rename file 'd:oradatabaseorclusr1.ora' to 'e:oradatabaseorclusr1.ora';
Open the database: alter database open;

Q: How do I resize a datafile?

A: alter database datafile '' resize ;
For example: you want to resize d:oradatabaseorclusr1.ora to 400MB. In this case, executealter database datafile 'd:oradatabaseorclusr1.ora' resize 400M;

Q: How do I make a datafile autoextending?

A: alter database datafile '' autoextend on;

Q: How can I see the number of free extents in each tablespace?

A: select tablespace_name, sum(bytes) from dba_free_space group by tablespace_name;

Q: I’ve restored a database from tape, and it seems I forgot to backup the control files. I can’t mount the database without one. What should I do?

A: Simple, create a new control file:

Start the Server Manager (svrmgr) and connect as internal user: connect internal
Start the instance without mounting the database: startup nomount
Execute the CREATE CONTROLFILE command. It’s almost the same as the CREATE DATABASE command, but in this case you have to specify all the datafiles. In the example below, the database name is ‘oracle’, there are 2 log files, and 4 datafiles.CREATE CONTROLFILE DATABASE oracle
LOGFILE 'd:oradatabaseorcllog1orcl.ora',
'd:oradatabaseorcllog2orcl.ora' NORESETLOGS
DATAFILE 'd:oradatabaseorclsys1orcl.ora' SIZE 10M REUSE, 'd:oradatabaseorclrbs1orcl.ora' SIZE 50M REUSE,
'd:oradatabaseorcltmp1orcl.ora' SIZE 20M REUSE,
'd:oradatabaseorclusr1orcl.ora' SIZE 400M REUSE;
Now you can open the database: alter database open;

Network Issues

Q: Connecting to Oracle 7.3 though SQL*Net 2.3 takes longer every time. What’s going wrong?

A: By default, every time you connect to a database, Oracle saves connect trace info in
$ORACLE_HOME/otrace/admin/redid.dat - and -
$ORACLE_HOME/otrace/admin/process.dat
Because the info is added to the end of the file, Oracle has to plough sequentially through the files searching for the end. The larger the file, the longer it takes.
Workarounds:
* Form time to tome, execute: otrccref
to create new files with the default size; and/or
* Set the environment variable EPC_DISABLED to TRUE
to turn off tracing.
The problem has been solved in Oracle version 7.3.3

Q: I can TNSPING my database, but SQL*Plus gives the error message ORA-03113 end-of-file on communication channel

A: On the server, logon as oracle user
stop de listener: lsnrctl stop
start de listener: lsnrctl startNow you should be able to connect to the database.
On the server, logout as oracle user.
If you can’t connect to the database anymore, do the following:
On the server, logon als oracle user again
stop de listener: lsnrctl stop
start de listener met nohup: nohup lsnrctl start
Now you can logout as oracle user, without killing the listener. Usually, you don’t need to start the listener with nohup, there’s a bug in some version (I only encountered it in version 7.3 for SCO OS).
I've had a similar problem with Oracle 7.3.4 on NT with Server Manager (svrmgr). I could connect as internal, but when I tried to startup the instance, I got message ORA-03113. In this case, in init.ora, there were several parameters defined twice. After correcting this, I could startup the instance.

Q: I get the message ORA-12612: TNS Connection busy. What's wrong?

A: The are several bugs in Oracle (8.0.4) that may cause this message. Ask Oracle for a patch or wait for the next version. Or use this work-around:
Open the file $ORACLE_HOME/network/admin/sqlnet.ora with a text editor. Add or change:sqlnet.expire_time = 0
Note: This will turn off the dead connection detection!

Q: I’ve powered off my PC while a SQL statement was running. Although my PC is powered off, the table on which the SQL statement was running is still locked. What should I do?

A: To prevent this:
Some OS’s automatically detect when an client has died. In this case, you don’t have to do anything to automatically kill an Oracle process.
However, there are OS’s (like Windows NT) which don’t have this feature; in that case try the following.

Open the file ORANTNETWORKADMINSQLNET.ORA on the server with a text editor (e.g. notepad).
Change or add:
sqlnet.expire_time = n
in which n is a positive integer unequal to zero.
Save the file and close the editor.
Reboot the server.

Every n minutes, the server will send a small packet to the client,. If the server doesn’t get an answer, it assumes the client is dead, and it will ROLLBACK its transactions and then kill the session).
To kill a process:

Make sure all the users are logged out.
Start any SQL tool you like.
Connect to the database as user sys or system.
Execute the following query.SELECT * FROM v$session;
Note down the SID and SERIAL# of the remaining sessions.
For every session you want to kill, execute the following statement.ALTER SYSTEM KILL SESSION ',';
Don’t forget the qoutes!
Query v$session again. The sessions should either have been disappeared or have the STATUS 'KILLED'.

Q: I get the message ORA-1013: user requested cancel of current operation. What's wrong?

A:

If this also happens in SQL*Plus: Open the file $ORACLE_HOME/network/admin/sqlnet.ora with a text editor. Add or change:sqlnet.expire_time = 0
Note: This will turn off the dead connection detection!
If this only happens while connected via ODBC, open the ODBC Administrator and double-click on the DSN. Uncheck the checkbox 'Enable Query Timeout'.

Installing and Starting Oracle

Q: I try to startup an instance, but I get the error message indicating that there are no semaphores available (e.g. ORA-07279).

A: By default each Oracle instance requires 50 semaphores. You can check this by executing:ipcs -a
You can set the numbers of semaphores in the file init.ora. Change the value of PROCESSES. The default value is 50. Change it to: 6 + maximum number of concurrent connections.
So to solve the problem, shutdown unneeded instances, or decrease the number of PROCESSES of each instance.
If you can’t shutdown instances and/or reduce the number of PROCESSES, you have to rebuild the kernel with more semaphores.

Q: I've just installed Oracle WGS 7.3.2. Everything works fine, exept lsnrctl. When I execute lsnrctl start the listener starts, but I don't return to my command prompt. I don't get a status list either.

A: This is a bug in this version of Oracle.
You can bypass the problem by removing the file $ORACLE_HOME/network/admin/snmp.ora. After that lsnrctl start should work fine.

Q: I've just installed Oracle WGS on a UNIX platform, but dbshut and dbstart both return errors.

A: This is a bug in the scripts.
Open dbshut with any text editor you like. Replace the version checking block with VERSION="7", and replace sqldba with svrmrgl. Save and close the file.
Do the same with the dbstart script. Make sure you replace every occurence of sqldba.

Q: I have Oracle running on an NT server. When I try to connect to the Oracle database, I get error message Error: ORA-12500: TNS:listener failed to start a dedicated server process. What should I do?

A: On the NT server, open Control Panel and start the Services applet. Search for 'OracleService' in the list. Click on the Start button to start this service. If it was started already, click Stop and then Start.

Performance Tuning

Q: How can I see Oracle's execution plan of a certain statement?

A: First, run a script called something like utlxplan.sql. The exact name depends on the operating system. This script creates a table called plan_table. Now you can execute the follwing command:EXPLAIN PLAN FOR ;
where is the statement to be examined. The EXPLAIN PLAN command fills the table plan_table. By executing the following statement, you can see the execution plan:SELECT LPAD(' ',2*(LEVEL-1))||operation operation, options, object_name, position
FROM plan_table
START WITH id = 0
CONNECT BY PRIOR id = parent_id;
This creates an output like

OPERATION            OPTIONS         OBJECT_NAME      POSITION

-------------------- --------------- ---------------- ---------

SELECT STATEMENT                                              1

  INDEX              RANGE SCAN      TEST_IDX1                1

The example above indicates that Oracle uses index TEST_IDX1 while executing the statement in the EXPLAIN PLAN command.

Q: How can I see Oracle's index statistics?

A: Execute the follwing command:SELECT index_name, table_name, blevel, leaf_blocks, distinct_keys
FROM dba_indexes
WHERE table_name = '

';
where

is the name of the table of which you want to see the statistics.

Q: How can I update the statistics e.g. after a bulk load?

A: Execute the follwing command:analyze table

compute statistics;
where

is the name of the table of which you want to update the statistics.
You can delete the statistics by executinganalyze table

delete statistics;

Q: I have Oracle 8.0.3 installed. I've executed an 'ANALYZE TABLE' statement. When I try to execute a SELECT statement involving that analyzed table, my query tool stops responding. In the alert and trace files I see the error message ORA-07445: exception encountered: core dump

A: This is a bug in Oracle. Upgrade to version 8.0.4 or delete the statistics by executing:analyze table delete statistics;

Interesting Oracle Links