Archive for the ‘Oracle Database’ Category

sql>connect / as sysdba
sql>shutdown immediate;
sql>startup migrate;
sql> @$ORACLE_HOME/rdbms/admin/utlirp.sql
sql>shutdown immediate;
sql> startup
sql> @$ORACLE_HOME/rdbms/admin/utlrp.sql
sql> connect system/password

Happy times…

Problem Description
Drop undo tablespace fails with error Ora-01548 .
SQL> drop Tablespace UNDOTBS1;
drop Tablespace UNDOTBS1
ERROR at line 1:
ORA-01548: active rollback segment ‘_SYSSMU1$’ found, terminate dropping

Cause of The Problem
An attempt was made to drop a tablespace that contains active rollback segments.

Solution of The Problem
In order to get rid of this error you have to follow following steps.
1)Create pfile if you started with database with spfile.

2)Edit pfile and set undo management to manual.
undo_management = manual

3)Put the entries of the undo segments in the pfile by using the following statement in the pfile:


4)Mount the database using new pfile.
Startup mount pfile=’fullpath to pfile’

5)Drop the datafiles,
sql>Alter Database datafile ‘&filename’ offline drop;

6)Open the database.
sql>Alter Database Open;

7)Drop the undo segments,

sql>Drop Rollback Segment “_SYSSMU1$”;
8)Drop Old undo tablespace.
sql>Drop Tablespace old_tablespace_name Including Contents;

9)Add the new undo tablespace.

10) Shutdown Immediate;

11) Change the pfile parameters back to Undo_management = AUTO and modify the parameter Undo_tablespace=new_undo_tablespace_name and remove the _offline_rollback_segments parameter.

12) Startup the Database.


1. Dump a trace of the controlfile.
Use SQL*Plus (or Server Manager) and enter:
SQL> alter database backup controlfile to trace ;
2. Modify the created trace file (located in the user_dump_dest directory):
a) remove from the first line to the line before “STARTUP NOMOUNT”
b) remove line “ALTER DATABASE OPEN” to the end of the file
c) change value of maxdatafiles, maxlogfiles and other parameters as desired
d) rename the file to something .sql (such as cntl.sql)
3. Shutdown the database.
4. Backup the database.
5. Run Server Manager/SQLPLUS and do the following:
SVRMGR> connect internal or connect /as sysdba
SVRMGR> @cntl.sql (The SQL script that you created in step 2) or SQLPLUS>@cntl.sql
SVRMGR> alter database open noresetlogs ; or SQLPLUS> alter database open noresetlogs

SQLPLUS if you are using 9i SVRMGRL if you are using 8i or below.

Also Please read the following article from metalink…

Doc ID: Note:1012929.6
Subject: How to Recreate the Controlfile
Content Type: TEXT/PLAIN
Creation Date: 04-OCT-1995
Last Revision Date: 14-JAN-2005

This article describes how you can recreate your controlfile.

For DBAs who need to recreate the controlfile.


You should only need to recreate your control file under very special

– All current copies of the control file have been lost or are corrupted.

– You need to change a “hard” database parameter that was set when the
database was first created, such as MAXDATAFILES, MAXLOGFILES,

– You are restoring a backup in which the control file is corrupted or

– Oracle Customer Support advises you to do so.

– If you are moving your database to another machine which is
running the same operating system but the location of the datafiles,
logfiles is not the same.

ORA-00202: controlfile: ‘C:\ORACLE\ORADATA\ORACLE91\CONTROL02.CTL’

ORA-27091: skgfqio: unable to queue I/O

ORA-27070: skgfdisp: async read/write failed

OSD-04006: ReadFile() failure, unable to read from file

O/S-Error: (OS 23) Data error (cyclic redundancy check).

i find this problem on one of my server database. this problem will make oracle fail to mount and open. if you try to connect via sqlplus, you will get error message : Oracle is on initialization startup/shutdown. so you can not enter the database.

below is the step that i do, to resolve the problem.(oracle ver 9i and higher)

1. connect to database using sqlplus with SYS as SYSDBA

2. shutdown immediate the database.

3. create a backup copy of all control file(usually consist of 3 control file with extension CTL) on folder ORADATA\

4. rename CONTROL02.CTL (corrupt control file) to CONTROL02.bak

5. rename one of working control file to become CONTROL02.CTL and then copy this file to folder ORADATA\

6.then startup the database via sqlplus using SYS as SYSDBA

this step only working if one of controlfile is corrupt and need to be replace. and also your controlfile is multiplex. Make sure that the error message is exactly the same with what is written above.


Minh Tran

When exporting data from Oracle DB 9i, encountered by the error:

. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user QLNS
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user QLNS
EXP-00008: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00024: Export views not installed, please notify your DBA
EXP-00000: Export terminated unsuccessfully


Run catalog.sql,catproc.sql and catexp.sql ( logged in as sys).

Then you should export as normal.


Minh Tran

1. To get currently used character set, issue:

SELECT * FROM nls_database_parameters WHERE parameter LIKE ‘%CHARACTERSET%’.

nls_database_parameters is a view.

2. Check and update, for example, to set nls_characterset = UTF8,nls_nchar_characterset = UTF8, use table props$:



There you go…

Minh Tran

Wanna “clear” the temp default tablespace, huh? Answer is NO WAY!

Luckily you can create a new TEMPORARY TABLESPACE and set it as default.

Then  Drop the old one…

increase size for the new TEMP TABLESPACE.

And there you go…