SQL> select distinct owner "user" from dba_tables where tablespace_name='SYSAUX';
user
------------------------------
MDSYS
DMSYS
TSMSYS
CTXSYS
OLAPSYS
SYSTEM
EXFSYS
DBSNMP
ORDSYS
SYSMAN
XDB
user
------------------------------
SYS
WMSYS
13 rows selected.
SQL> drop user mdsys cascade;
User dropped.
SQL> drop user dmsys cascade;
User dropped.
SQL> drop user tsmsys cascade;
User dropped.
SQL> drop user ctxsys cascade;
User dropped.
SQL> drop user olapsys cascade;
User dropped.
SQL> drop user exfsys cascade;
User dropped.
SQL> drop user dbsnmp cascade;
User dropped.
SQL> drop user ordsys cascade;
User dropped.
SQL> drop user sysman cascade;
User dropped.
SQL> drop user xdb cascade;
User dropped.
SQL> select distinct owner "user" from dba_tables where tablespace_name='SYSAUX';
user
------------------------------
SYSTEM
SYS
WMSYS
SQL> drop user wmsys cascade;
User dropped.
SQL> drop tablespace sysaux;
drop tablespace sysaux
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
SQL> drop tablespace sysaux INCLUDING CONTENTS;
drop tablespace sysaux INCLUDING CONTENTS
*
ERROR at line 1:
ORA-25183: index-organized table top index segment is in a different tablespace
SQL> drop tablespace sysaux INCLUDING CONTENTS;
drop tablespace sysaux INCLUDING CONTENTS
*
ERROR at line 1:
ORA-25183: index-organized table top index segment is in a different tablespace
SQL> select distinct IOT_TYPE from dba_tables;
IOT_TYPE
------------
IOT_OVERFLOW
IOT
select 'drop '||owner||'.'||table_name||';' from dba_tables where tablespace_name='SYSAUX';
SQL> select 'drop table '||owner||'.'||table_name||' cascade constraints;' from dba_tables where tablespace_name='SYSAUX';
Table dropped.
drop table SYS.SYS_IOT_OVER_9692 cascade constraints
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
drop table SYS.SYS$SERVICE_METRICS_TAB cascade constraints
*
ERROR at line 1:
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables
drop table SYS.AQ$_SYS$SERVICE_METRICS_TAB_S cascade constraints
*
ERROR at line 1:
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables
drop table SYS.SYS_IOT_OVER_10165 cascade constraints
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
Table dropped.
set long 999999
set linesize 150
select dbms_metadata.get_ddl('TABLE', table_name, owner) from dba_tables where iot_type like '%IOT%';
select 'drop table '||owner||'.'||table_name||' cascade constraints;' from dba_tables where iot_type like '%IOT%'and tablespace_name='SYSAUX';
6
SQL> select 'drop table '||owner||'.'||table_name||' cascade constraints;' from dba_tables where iot_type like '%IOT%'and tablespace_name='SYSAUX';
drop table SYS.SYS_IOT_OVER_4478 cascade constraints;
drop table SYS.SYS_IOT_OVER_4484 cascade constraints;
drop table SYS.SYS_IOT_OVER_4488 cascade constraints;
drop table SYS.SYS_IOT_OVER_8799 cascade constraints;
drop table SYS.SYS_IOT_OVER_9692 cascade constraints;
drop table SYS.SYS_IOT_OVER_10165 cascade constraints;
6 rows selected.
SQL> drop table SYS.SYS_IOT_OVER_4478 cascade constraints;
drop table SYS.SYS_IOT_OVER_4478 cascade constraints
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
SQL> drop table SYS.SYS_IOT_OVER_4484 cascade constraints;
drop table SYS.SYS_IOT_OVER_4484 cascade constraints
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
SQL> drop table SYS.SYS_IOT_OVER_4488 cascade constraints;
drop table SYS.SYS_IOT_OVER_4488 cascade constraints
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
SQL> drop table SYS.SYS_IOT_OVER_8799 cascade constraints;
drop table SYS.SYS_IOT_OVER_8799 cascade constraints
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
SQL> drop table SYS.SYS_IOT_OVER_9692 cascade constraints;
drop table SYS.SYS_IOT_OVER_9692 cascade constraints
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
SQL> drop table SYS.SYS_IOT_OVER_10165 cascade constraints;
drop table SYS.SYS_IOT_OVER_10165 cascade constraints
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
select 'drop table '||owner||'.'||table_name||' PURGE;' from dba_tables where iot_type like '%IOT%';
SQL> drop tablespace sysaux including contents and datafiles cascade constraints;
drop tablespace sysaux including contents and datafiles cascade constraints
*
ERROR at line 1:
ORA-24008: queue table SYS.ALERT_QT must be dropped first
SQL> startup migrate
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 71303848 bytes
Database Buffers 213909504 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL> drop tablespace sysaux including contents and datafiles cascade constraints;
drop tablespace sysaux including contents and datafiles cascade constraints
*
ERROR at line 1:
ORA-24008: queue table SYS.ALERT_QT must be dropped first
SQL> drop table SYS.ALERT_QT;
drop table SYS.ALERT_QT
*
ERROR at line 1:
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE('SYS.ALERT_QT');
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE('SYS.ALERT_QT'); END;
*
ERROR at line 1:
ORA-24012: cannot drop QUEUE_TABLE, some queues in SYS.ALERT_QT have not been
dropped
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4084
ORA-06512: at "SYS.DBMS_AQADM", line 197
ORA-06512: at line 1
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE('SYS.ALERT_QT') immediate;
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE('SYS.ALERT_QT') immediate; END;
*
ERROR at line 1:
ORA-06550: line 1, column 51:
PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the
following:
:= . ( % ;
The symbol ":=" was substituted for "IMMEDIATE" to continue.
SQL> exec dbms_aqadm.drop_queue_table(queue_table=>'SYS.ALERT_QT',force =>true);
BEGIN dbms_aqadm.drop_queue_table(queue_table=>'SYS.ALERT_QT',force =>true); END;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4084
ORA-06512: at "SYS.DBMS_AQADM", line 197
ORA-06512: at line 1
select object_name,object_type from dba_objects where owner='SYS' and object_name like '%AQ%';
IF EXISTS(SELECT NULL
FROM SYS.ALERT_QT) THEN
DROP TABLE SYS.ALERT_QT;
END IF;
begin
for r in ( select table_name from user_tables )
loop
execute immediate 'drop table '||r.table_name
||' cascade constraints';
end loop;
end;
Note 203225.1 - How to Manually Cleanup Advanced Queuing Tables
Note 236898.1 - Procedures to Remove Orphan AQ Objects which Fail to Drop via
DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'<QUEUE_TABLE>',force => TRUE);
exec dbms_aqadm.drop_queue_table(queue_table=>'SYS.ALERT_QT',force =>true);
SELECT object_name, object_type FROM user_objects WHERE object_name like '%ALERT_QT%';
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
ALERT_QT TABLE
AQ$ALERT_QT VIEW
AQ$ALERT_QT_R VIEW
AQ$ALERT_QT_S VIEW
AQ$_ALERT_QT_E QUEUE
AQ$_ALERT_QT_F VIEW
AQ$_ALERT_QT_G TABLE
AQ$_ALERT_QT_H TABLE
AQ$_ALERT_QT_I TABLE
AQ$_ALERT_QT_N SEQUENCE
AQ$_ALERT_QT_S TABLE
AQ$_ALERT_QT_T TABLE
AQ$_ALERT_QT_V EVALUATION CONTEXT
13 rows selected.
SQL> SELECT 'drop '||object_type||' '||object_name||' cascade constraints;' FROM user_objects WHERE object_name like '%ALERT_QT%';
'DROP'||OBJECT_TYPE||''||OBJECT_NAME||'CASCADECONSTRAINTS;'
--------------------------------------------------------------------------------
drop TABLE ALERT_QT cascade constraints;
drop VIEW AQ$ALERT_QT cascade constraints;
drop VIEW AQ$ALERT_QT_R cascade constraints;
drop VIEW AQ$ALERT_QT_S cascade constraints;
drop QUEUE AQ$_ALERT_QT_E cascade constraints;
drop VIEW AQ$_ALERT_QT_F cascade constraints;
drop TABLE AQ$_ALERT_QT_G cascade constraints;
drop TABLE AQ$_ALERT_QT_H cascade constraints;
drop TABLE AQ$_ALERT_QT_I cascade constraints;
drop SEQUENCE AQ$_ALERT_QT_N cascade constraints;
drop TABLE AQ$_ALERT_QT_S cascade constraints;
drop TABLE AQ$_ALERT_QT_T cascade constraints;
drop EVALUATION CONTEXT AQ$_ALERT_QT_V cascade constraints;
13 rows selected.
SQL> SELECT 'drop '||object_type||' '||object_name||' cascade constraints;' FROM user_objects WHERE object_name like '%ALERT_QT%';
'DROP'||OBJECT_TYPE||''||OBJECT_NAME||'CASCADECONSTRAINTS;'
--------------------------------------------------------------------------------
drop TABLE ALERT_QT cascade constraints;
drop VIEW AQ$ALERT_QT cascade constraints;
drop VIEW AQ$ALERT_QT_R cascade constraints;
drop VIEW AQ$ALERT_QT_S cascade constraints;
drop QUEUE AQ$_ALERT_QT_E cascade constraints;
drop VIEW AQ$_ALERT_QT_F cascade constraints;
drop TABLE AQ$_ALERT_QT_G cascade constraints;
drop TABLE AQ$_ALERT_QT_H cascade constraints;
drop TABLE AQ$_ALERT_QT_I cascade constraints;
drop SEQUENCE AQ$_ALERT_QT_N cascade constraints;
drop TABLE AQ$_ALERT_QT_S cascade constraints;
drop TABLE AQ$_ALERT_QT_T cascade constraints;
drop EVALUATION CONTEXT AQ$_ALERT_QT_V cascade constraints;
13 rows selected.
SQL> drop VIEW AQ$ALERT_QT cascade constraints;
View dropped.
SQL> drop VIEW AQ$ALERT_QT_R cascade constraints;
View dropped.
SQL> drop VIEW AQ$ALERT_QT_S cascade constraints;
View dropped.
SQL> drop QUEUE AQ$_ALERT_QT_E cascade constraints;
drop QUEUE AQ$_ALERT_QT_E cascade constraints
*
ERROR at line 1:
ORA-00950: invalid DROP option
SQL> drop VIEW AQ$_ALERT_QT_F cascade constraints;
View dropped.
SQL> drop TABLE AQ$_ALERT_QT_G cascade constraints;
Table dropped.
SQL> drop TABLE AQ$_ALERT_QT_H cascade constraints;
Table dropped.
SQL> drop TABLE AQ$_ALERT_QT_I cascade constraints;
Table dropped.
SQL> drop SEQUENCE AQ$_ALERT_QT_N cascade constraints;
drop SEQUENCE AQ$_ALERT_QT_N cascade constraints
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> drop TABLE AQ$_ALERT_QT_S cascade constraints;
Table dropped.
SQL> drop TABLE AQ$_ALERT_QT_T cascade constraints;
Table dropped.
SQL> drop EVALUATION CONTEXT AQ$_ALERT_QT_V cascade constraints;
drop EVALUATION CONTEXT AQ$_ALERT_QT_V cascade constraints
*
ERROR at line 1:
ORA-00950: invalid DROP option
SQL> drop TABLE ALERT_QT cascade constraints;
Table dropped.
SQL> drop tablespace sysaux including contents and datafiles cascade constraints;
Tablespace dropped.
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 71303848 bytes
Database Buffers 213909504 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
USERS
EXAMPLE
SWAMY
PERFSTAT
7 rows selected.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
------------------------------------------------------------------------
USERS
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
UNDOTBS1
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
SYSTEM
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
EXAMPLE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
SWAMY
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SWAMI01.DBF
PERFSTAT
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\PERF01.DBF
6 rows selected.
CREATE TABLESPACE sysaux
DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF' SIZE 300m
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
SQL> startup
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 71303848 bytes
Database Buffers 213909504 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
USERS
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
UNDOTBS1
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
SYSTEM
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
EXAMPLE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
SWAMY
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SWAMI01.DBF
PERFSTAT
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\PERF01.DBF
6 rows selected.
SQL> CREATE TABLESPACE sysaux
2 DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF' SIZE 300m
3 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
F:\>exp system/manager file=s111.dmp owner=scott
Export: Release 10.2.0.1.0 - Production on Sat May 21 20:34:24 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified users ...
. exporting pre-schema procedural objects and actions
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'EXFSYS.DBMS_EXPFIL_DEPASEXP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling EXFSYS.DBMS_EXPFIL_DEPASEXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.LT_EXPORT_PKG"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.LT_EXPORT_PKG.schema_info_exp
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'DMSYS.DBMS_DM_MODEL_EXP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling DMSYS.DBMS_DM_MODEL_EXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_LOGREP_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_EXP"
ORA-06512: at "SYS.DBMS_FILE_GROUP_EXP", line 634
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_FILE_GROUP_EXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_LOGREP_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_EXP"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_LOGREP_EXP.schema_info_exp
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table EMP 14 rows exported
. . exporting table EMP_BKP 14 rows exported
. . exporting table EMP_ORG 14 rows exported
. . exporting table SALGRADE 5 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'EXFSYS.DBMS_EXPFIL_DEPASEXP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling EXFSYS.DBMS_EXPFIL_DEPASEXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.LT_EXPORT_PKG"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.LT_EXPORT_PKG.schema_info_exp
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'DMSYS.DBMS_DM_MODEL_EXP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling DMSYS.DBMS_DM_MODEL_EXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_LOGREP_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_EXP"
ORA-06512: at "SYS.DBMS_FILE_GROUP_EXP", line 634
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_FILE_GROUP_EXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_LOGREP_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_EXP"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_LOGREP_EXP.schema_info_exp
. exporting statistics
Export terminated successfully with warnings.
@%ORACLE_HOME%\rdbms\admin\catexp.sql
login as SYS
run catmetx.sql then
run utlrp.sql
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'EXFSYS.DBMS_EXPFIL_DEPASEXP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling EXFSYS.DBMS_EXPFIL_DEPASEXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.LT_EXPORT_PKG"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.LT_EXPORT_PKG.schema_info_exp
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'DMSYS.DBMS_DM_MODEL_EXP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling DMSYS.DBMS_DM_MODEL_EXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_LOGREP_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_EXP"
ORA-06512: at "SYS.DBMS_FILE_GROUP_EXP", line 634
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_FILE_GROUP_EXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_LOGREP_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_EXP"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_LOGREP_EXP.schema_info_exp
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table EMP 14 rows exported
. . exporting table EMP_BKP 14 rows exported
. . exporting table EMP_ORG 14 rows exported
. . exporting table SALGRADE 5 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'EXFSYS.DBMS_EXPFIL_DEPASEXP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling EXFSYS.DBMS_EXPFIL_DEPASEXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.LT_EXPORT_PKG"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.LT_EXPORT_PKG.schema_info_exp
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'DMSYS.DBMS_DM_MODEL_EXP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling DMSYS.DBMS_DM_MODEL_EXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_LOGREP_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_EXP"
ORA-06512: at "SYS.DBMS_FILE_GROUP_EXP", line 634
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_FILE_GROUP_EXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_LOGREP_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_EXP"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_LOGREP_EXP.schema_info_exp
. exporting statistics
Export terminated successfully with warnings.
F:\export>
@%ORACLE_HOME%\rdbms\admin\catalog.sql
@%ORACLE_HOME%\rdbms\admin\catproc.sql
conn system/manager
@%ORACLE_HOME%\sqlplus\admin\pupbld.sql
BEGIN
*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_STATS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_REGISTRY", line 560
ORA-06512: at "SYS.DBMS_REGISTRY", line 612
ORA-06512: at line 4
Connected.
SP2-0310: unable to open file "C:\oracle\product\10.2.0\db_1\rdbms\admin\pupbld.sql"
SQL>
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.LT_EXPORT_PKG"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.LT_EXPORT_PKG.schema_info_exp
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'EXFSYS.DBMS_EXPFIL_DEPASEXP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling EXFSYS.DBMS_EXPFIL_DEPASEXP.schema_info_exp
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'DMSYS.DBMS_DM_MODEL_EXP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling DMSYS.DBMS_DM_MODEL_EXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_LOGREP_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_EXP"
ORA-06512: at "SYS.DBMS_FILE_GROUP_EXP", line 634
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_FILE_GROUP_EXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_LOGREP_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_EXP"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_LOGREP_EXP.schema_info_exp
. exporting statistics
Export terminated successfully with warnings.
Next Plan
=========
Need to startup Upgrade and then upgrade the database by catupgrd.sql
then
cat102s.sql
then
utlrp.sql
Function created.
No errors.
SELECT version_script AS file_name FROM DUAL
*
ERROR at line 1:
ORA-20000: Upgrade re-run not supported from version
ORA-06512: at "SYS.VERSION_SCRIPT", line 45
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
F:\export>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat May 21 22:21:44 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 113246888 bytes
Database Buffers 171966464 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL>
user
------------------------------
MDSYS
DMSYS
TSMSYS
CTXSYS
OLAPSYS
SYSTEM
EXFSYS
DBSNMP
ORDSYS
SYSMAN
XDB
user
------------------------------
SYS
WMSYS
13 rows selected.
SQL> drop user mdsys cascade;
User dropped.
SQL> drop user dmsys cascade;
User dropped.
SQL> drop user tsmsys cascade;
User dropped.
SQL> drop user ctxsys cascade;
User dropped.
SQL> drop user olapsys cascade;
User dropped.
SQL> drop user exfsys cascade;
User dropped.
SQL> drop user dbsnmp cascade;
User dropped.
SQL> drop user ordsys cascade;
User dropped.
SQL> drop user sysman cascade;
User dropped.
SQL> drop user xdb cascade;
User dropped.
SQL> select distinct owner "user" from dba_tables where tablespace_name='SYSAUX';
user
------------------------------
SYSTEM
SYS
WMSYS
SQL> drop user wmsys cascade;
User dropped.
SQL> drop tablespace sysaux;
drop tablespace sysaux
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
SQL> drop tablespace sysaux INCLUDING CONTENTS;
drop tablespace sysaux INCLUDING CONTENTS
*
ERROR at line 1:
ORA-25183: index-organized table top index segment is in a different tablespace
SQL> drop tablespace sysaux INCLUDING CONTENTS;
drop tablespace sysaux INCLUDING CONTENTS
*
ERROR at line 1:
ORA-25183: index-organized table top index segment is in a different tablespace
SQL> select distinct IOT_TYPE from dba_tables;
IOT_TYPE
------------
IOT_OVERFLOW
IOT
select 'drop '||owner||'.'||table_name||';' from dba_tables where tablespace_name='SYSAUX';
SQL> select 'drop table '||owner||'.'||table_name||' cascade constraints;' from dba_tables where tablespace_name='SYSAUX';
Table dropped.
drop table SYS.SYS_IOT_OVER_9692 cascade constraints
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
drop table SYS.SYS$SERVICE_METRICS_TAB cascade constraints
*
ERROR at line 1:
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables
drop table SYS.AQ$_SYS$SERVICE_METRICS_TAB_S cascade constraints
*
ERROR at line 1:
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables
drop table SYS.SYS_IOT_OVER_10165 cascade constraints
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
Table dropped.
set long 999999
set linesize 150
select dbms_metadata.get_ddl('TABLE', table_name, owner) from dba_tables where iot_type like '%IOT%';
select 'drop table '||owner||'.'||table_name||' cascade constraints;' from dba_tables where iot_type like '%IOT%'and tablespace_name='SYSAUX';
6
SQL> select 'drop table '||owner||'.'||table_name||' cascade constraints;' from dba_tables where iot_type like '%IOT%'and tablespace_name='SYSAUX';
drop table SYS.SYS_IOT_OVER_4478 cascade constraints;
drop table SYS.SYS_IOT_OVER_4484 cascade constraints;
drop table SYS.SYS_IOT_OVER_4488 cascade constraints;
drop table SYS.SYS_IOT_OVER_8799 cascade constraints;
drop table SYS.SYS_IOT_OVER_9692 cascade constraints;
drop table SYS.SYS_IOT_OVER_10165 cascade constraints;
6 rows selected.
SQL> drop table SYS.SYS_IOT_OVER_4478 cascade constraints;
drop table SYS.SYS_IOT_OVER_4478 cascade constraints
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
SQL> drop table SYS.SYS_IOT_OVER_4484 cascade constraints;
drop table SYS.SYS_IOT_OVER_4484 cascade constraints
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
SQL> drop table SYS.SYS_IOT_OVER_4488 cascade constraints;
drop table SYS.SYS_IOT_OVER_4488 cascade constraints
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
SQL> drop table SYS.SYS_IOT_OVER_8799 cascade constraints;
drop table SYS.SYS_IOT_OVER_8799 cascade constraints
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
SQL> drop table SYS.SYS_IOT_OVER_9692 cascade constraints;
drop table SYS.SYS_IOT_OVER_9692 cascade constraints
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
SQL> drop table SYS.SYS_IOT_OVER_10165 cascade constraints;
drop table SYS.SYS_IOT_OVER_10165 cascade constraints
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
select 'drop table '||owner||'.'||table_name||' PURGE;' from dba_tables where iot_type like '%IOT%';
SQL> drop tablespace sysaux including contents and datafiles cascade constraints;
drop tablespace sysaux including contents and datafiles cascade constraints
*
ERROR at line 1:
ORA-24008: queue table SYS.ALERT_QT must be dropped first
SQL> startup migrate
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 71303848 bytes
Database Buffers 213909504 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL> drop tablespace sysaux including contents and datafiles cascade constraints;
drop tablespace sysaux including contents and datafiles cascade constraints
*
ERROR at line 1:
ORA-24008: queue table SYS.ALERT_QT must be dropped first
SQL> drop table SYS.ALERT_QT;
drop table SYS.ALERT_QT
*
ERROR at line 1:
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE('SYS.ALERT_QT');
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE('SYS.ALERT_QT'); END;
*
ERROR at line 1:
ORA-24012: cannot drop QUEUE_TABLE, some queues in SYS.ALERT_QT have not been
dropped
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4084
ORA-06512: at "SYS.DBMS_AQADM", line 197
ORA-06512: at line 1
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE('SYS.ALERT_QT') immediate;
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE('SYS.ALERT_QT') immediate; END;
*
ERROR at line 1:
ORA-06550: line 1, column 51:
PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the
following:
:= . ( % ;
The symbol ":=" was substituted for "IMMEDIATE" to continue.
SQL> exec dbms_aqadm.drop_queue_table(queue_table=>'SYS.ALERT_QT',force =>true);
BEGIN dbms_aqadm.drop_queue_table(queue_table=>'SYS.ALERT_QT',force =>true); END;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4084
ORA-06512: at "SYS.DBMS_AQADM", line 197
ORA-06512: at line 1
select object_name,object_type from dba_objects where owner='SYS' and object_name like '%AQ%';
IF EXISTS(SELECT NULL
FROM SYS.ALERT_QT) THEN
DROP TABLE SYS.ALERT_QT;
END IF;
begin
for r in ( select table_name from user_tables )
loop
execute immediate 'drop table '||r.table_name
||' cascade constraints';
end loop;
end;
Note 203225.1 - How to Manually Cleanup Advanced Queuing Tables
Note 236898.1 - Procedures to Remove Orphan AQ Objects which Fail to Drop via
DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'<QUEUE_TABLE>',force => TRUE);
exec dbms_aqadm.drop_queue_table(queue_table=>'SYS.ALERT_QT',force =>true);
SELECT object_name, object_type FROM user_objects WHERE object_name like '%ALERT_QT%';
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
ALERT_QT TABLE
AQ$ALERT_QT VIEW
AQ$ALERT_QT_R VIEW
AQ$ALERT_QT_S VIEW
AQ$_ALERT_QT_E QUEUE
AQ$_ALERT_QT_F VIEW
AQ$_ALERT_QT_G TABLE
AQ$_ALERT_QT_H TABLE
AQ$_ALERT_QT_I TABLE
AQ$_ALERT_QT_N SEQUENCE
AQ$_ALERT_QT_S TABLE
AQ$_ALERT_QT_T TABLE
AQ$_ALERT_QT_V EVALUATION CONTEXT
13 rows selected.
SQL> SELECT 'drop '||object_type||' '||object_name||' cascade constraints;' FROM user_objects WHERE object_name like '%ALERT_QT%';
'DROP'||OBJECT_TYPE||''||OBJECT_NAME||'CASCADECONSTRAINTS;'
--------------------------------------------------------------------------------
drop TABLE ALERT_QT cascade constraints;
drop VIEW AQ$ALERT_QT cascade constraints;
drop VIEW AQ$ALERT_QT_R cascade constraints;
drop VIEW AQ$ALERT_QT_S cascade constraints;
drop QUEUE AQ$_ALERT_QT_E cascade constraints;
drop VIEW AQ$_ALERT_QT_F cascade constraints;
drop TABLE AQ$_ALERT_QT_G cascade constraints;
drop TABLE AQ$_ALERT_QT_H cascade constraints;
drop TABLE AQ$_ALERT_QT_I cascade constraints;
drop SEQUENCE AQ$_ALERT_QT_N cascade constraints;
drop TABLE AQ$_ALERT_QT_S cascade constraints;
drop TABLE AQ$_ALERT_QT_T cascade constraints;
drop EVALUATION CONTEXT AQ$_ALERT_QT_V cascade constraints;
13 rows selected.
SQL> SELECT 'drop '||object_type||' '||object_name||' cascade constraints;' FROM user_objects WHERE object_name like '%ALERT_QT%';
'DROP'||OBJECT_TYPE||''||OBJECT_NAME||'CASCADECONSTRAINTS;'
--------------------------------------------------------------------------------
drop TABLE ALERT_QT cascade constraints;
drop VIEW AQ$ALERT_QT cascade constraints;
drop VIEW AQ$ALERT_QT_R cascade constraints;
drop VIEW AQ$ALERT_QT_S cascade constraints;
drop QUEUE AQ$_ALERT_QT_E cascade constraints;
drop VIEW AQ$_ALERT_QT_F cascade constraints;
drop TABLE AQ$_ALERT_QT_G cascade constraints;
drop TABLE AQ$_ALERT_QT_H cascade constraints;
drop TABLE AQ$_ALERT_QT_I cascade constraints;
drop SEQUENCE AQ$_ALERT_QT_N cascade constraints;
drop TABLE AQ$_ALERT_QT_S cascade constraints;
drop TABLE AQ$_ALERT_QT_T cascade constraints;
drop EVALUATION CONTEXT AQ$_ALERT_QT_V cascade constraints;
13 rows selected.
SQL> drop VIEW AQ$ALERT_QT cascade constraints;
View dropped.
SQL> drop VIEW AQ$ALERT_QT_R cascade constraints;
View dropped.
SQL> drop VIEW AQ$ALERT_QT_S cascade constraints;
View dropped.
SQL> drop QUEUE AQ$_ALERT_QT_E cascade constraints;
drop QUEUE AQ$_ALERT_QT_E cascade constraints
*
ERROR at line 1:
ORA-00950: invalid DROP option
SQL> drop VIEW AQ$_ALERT_QT_F cascade constraints;
View dropped.
SQL> drop TABLE AQ$_ALERT_QT_G cascade constraints;
Table dropped.
SQL> drop TABLE AQ$_ALERT_QT_H cascade constraints;
Table dropped.
SQL> drop TABLE AQ$_ALERT_QT_I cascade constraints;
Table dropped.
SQL> drop SEQUENCE AQ$_ALERT_QT_N cascade constraints;
drop SEQUENCE AQ$_ALERT_QT_N cascade constraints
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> drop TABLE AQ$_ALERT_QT_S cascade constraints;
Table dropped.
SQL> drop TABLE AQ$_ALERT_QT_T cascade constraints;
Table dropped.
SQL> drop EVALUATION CONTEXT AQ$_ALERT_QT_V cascade constraints;
drop EVALUATION CONTEXT AQ$_ALERT_QT_V cascade constraints
*
ERROR at line 1:
ORA-00950: invalid DROP option
SQL> drop TABLE ALERT_QT cascade constraints;
Table dropped.
SQL> drop tablespace sysaux including contents and datafiles cascade constraints;
Tablespace dropped.
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 71303848 bytes
Database Buffers 213909504 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
USERS
EXAMPLE
SWAMY
PERFSTAT
7 rows selected.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
------------------------------------------------------------------------
USERS
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
UNDOTBS1
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
SYSTEM
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
EXAMPLE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
SWAMY
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SWAMI01.DBF
PERFSTAT
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\PERF01.DBF
6 rows selected.
CREATE TABLESPACE sysaux
DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF' SIZE 300m
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
SQL> startup
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 71303848 bytes
Database Buffers 213909504 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
USERS
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
UNDOTBS1
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
SYSTEM
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
EXAMPLE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
SWAMY
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SWAMI01.DBF
PERFSTAT
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\PERF01.DBF
6 rows selected.
SQL> CREATE TABLESPACE sysaux
2 DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF' SIZE 300m
3 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
F:\>exp system/manager file=s111.dmp owner=scott
Export: Release 10.2.0.1.0 - Production on Sat May 21 20:34:24 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified users ...
. exporting pre-schema procedural objects and actions
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'EXFSYS.DBMS_EXPFIL_DEPASEXP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling EXFSYS.DBMS_EXPFIL_DEPASEXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.LT_EXPORT_PKG"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.LT_EXPORT_PKG.schema_info_exp
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'DMSYS.DBMS_DM_MODEL_EXP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling DMSYS.DBMS_DM_MODEL_EXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_LOGREP_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_EXP"
ORA-06512: at "SYS.DBMS_FILE_GROUP_EXP", line 634
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_FILE_GROUP_EXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_LOGREP_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_EXP"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_LOGREP_EXP.schema_info_exp
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table EMP 14 rows exported
. . exporting table EMP_BKP 14 rows exported
. . exporting table EMP_ORG 14 rows exported
. . exporting table SALGRADE 5 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'EXFSYS.DBMS_EXPFIL_DEPASEXP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling EXFSYS.DBMS_EXPFIL_DEPASEXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.LT_EXPORT_PKG"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.LT_EXPORT_PKG.schema_info_exp
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'DMSYS.DBMS_DM_MODEL_EXP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling DMSYS.DBMS_DM_MODEL_EXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_LOGREP_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_EXP"
ORA-06512: at "SYS.DBMS_FILE_GROUP_EXP", line 634
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_FILE_GROUP_EXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_LOGREP_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_EXP"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_LOGREP_EXP.schema_info_exp
. exporting statistics
Export terminated successfully with warnings.
@%ORACLE_HOME%\rdbms\admin\catexp.sql
login as SYS
run catmetx.sql then
run utlrp.sql
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'EXFSYS.DBMS_EXPFIL_DEPASEXP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling EXFSYS.DBMS_EXPFIL_DEPASEXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.LT_EXPORT_PKG"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.LT_EXPORT_PKG.schema_info_exp
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'DMSYS.DBMS_DM_MODEL_EXP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling DMSYS.DBMS_DM_MODEL_EXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_LOGREP_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_EXP"
ORA-06512: at "SYS.DBMS_FILE_GROUP_EXP", line 634
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_FILE_GROUP_EXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_LOGREP_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_EXP"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_LOGREP_EXP.schema_info_exp
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table EMP 14 rows exported
. . exporting table EMP_BKP 14 rows exported
. . exporting table EMP_ORG 14 rows exported
. . exporting table SALGRADE 5 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'EXFSYS.DBMS_EXPFIL_DEPASEXP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling EXFSYS.DBMS_EXPFIL_DEPASEXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.LT_EXPORT_PKG"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.LT_EXPORT_PKG.schema_info_exp
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'DMSYS.DBMS_DM_MODEL_EXP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling DMSYS.DBMS_DM_MODEL_EXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_LOGREP_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_EXP"
ORA-06512: at "SYS.DBMS_FILE_GROUP_EXP", line 634
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_FILE_GROUP_EXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_LOGREP_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_EXP"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_LOGREP_EXP.schema_info_exp
. exporting statistics
Export terminated successfully with warnings.
F:\export>
@%ORACLE_HOME%\rdbms\admin\catalog.sql
@%ORACLE_HOME%\rdbms\admin\catproc.sql
conn system/manager
@%ORACLE_HOME%\sqlplus\admin\pupbld.sql
BEGIN
*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_STATS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_REGISTRY", line 560
ORA-06512: at "SYS.DBMS_REGISTRY", line 612
ORA-06512: at line 4
Connected.
SP2-0310: unable to open file "C:\oracle\product\10.2.0\db_1\rdbms\admin\pupbld.sql"
SQL>
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.LT_EXPORT_PKG"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.LT_EXPORT_PKG.schema_info_exp
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'EXFSYS.DBMS_EXPFIL_DEPASEXP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling EXFSYS.DBMS_EXPFIL_DEPASEXP.schema_info_exp
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'DMSYS.DBMS_DM_MODEL_EXP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling DMSYS.DBMS_DM_MODEL_EXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_LOGREP_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_EXP"
ORA-06512: at "SYS.DBMS_FILE_GROUP_EXP", line 634
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_FILE_GROUP_EXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_LOGREP_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_EXP"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_LOGREP_EXP.schema_info_exp
. exporting statistics
Export terminated successfully with warnings.
Next Plan
=========
Need to startup Upgrade and then upgrade the database by catupgrd.sql
then
cat102s.sql
then
utlrp.sql
Function created.
No errors.
SELECT version_script AS file_name FROM DUAL
*
ERROR at line 1:
ORA-20000: Upgrade re-run not supported from version
ORA-06512: at "SYS.VERSION_SCRIPT", line 45
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
F:\export>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat May 21 22:21:44 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 113246888 bytes
Database Buffers 171966464 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL>
Hi shekar,
ReplyDeleteThis is vinothkanna.After I recreated sysaux
tablespace so many sys objects were INVALID state.
SQL> select COMP_NAME,status from dba_registry;
COMP_NAME STATUS
---------------------------------------- -----------
Oracle Workspace Manager VALID
Oracle Enterprise Manager LOADED
Oracle Database Catalog Views VALID
Oracle Database Packages and Types INVALID
JServer JAVA Virtual Machine VALID
Oracle XDK VALID
Oracle Database Java Packages INVALID
OLAP Analytic Workspace INVALID
Oracle OLAP API VALID
9 rows selected.
please let me know how to valid all components and one of the important package DBMS_STATS is INVALID state.I tried to reloaded that but i can't.(using [ID 1310365.1])It shows the error msg,
PLS-00905: object SYS.DBMS_STATS_INTERNAL is invalid