Rebuild database by export import

Rebuild database by export import

SELECT username FROM ALL_USERS ORDER BY username;

COL_SPARX

IND_MOCOQ

IPN_STRESS

SELECT DISTINCT owner 

  FROM DBA_SEGMENTS 

 WHERE tablespace_name IN ('IGT_TABLE','IGT_INDEX');

COL_SPARX

IND_MOCOQ

IPN_STRESS

expdp system/syspass@orainst DIRECTORY=IG_EXP DUMPFILE=expdp_COL_SPARX_20230912.dmp LOGFILE=expdp_COL_SPARX_20230912.log SCHEMAS=COL_SPARX

expdp system/syspass@orainst DIRECTORY=IG_EXP DUMPFILE=expdp_IND_MOCOQ_20230912.dmp LOGFILE=expdp_IND_MOCOQ_20230912.log SCHEMAS=IND_MOCOQ

expdp system/syspass@orainst DIRECTORY=IG_EXP DUMPFILE=expdp_IPN_STRESS_20230912.dmp LOGFILE=expdp_IPN_STRESS_20230912.log SCHEMAS=IPN_STRESS

DROP USER COL_SPARX CASCADE;

DROP USER IPN_STRESS CASCADE;

DROP USER IND_MOCOQ CASCADE;

DROP TABLESPACE IGT_INDEX INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE IGT_TABLE INCLUDING CONTENTS AND DATAFILES;

ERROR at line 1:

ORA-12919: Can not drop the default permanent tablespace

CREATE TABLESPACE IGT_TABLE_TEMP DATAFILE '/oracle_db/db2/db_igt/data_files/igt_table_temp_01.dbf' size 100M AUTOEXTEND ON MAXSIZE 20000M;

ALTER DATABASE DEFAULT TABLESPACE IGT_TABLE_TEMP;

DROP TABLESPACE IGT_TABLE_TEMP INCLUDING CONTENTS AND DATAFILES;

Now, recreate all tablespaces from scratch

CREATE TABLESPACE IGT_TABLE DATAFILE '/oracle_db/db1/db_igt/ora_igt_table_01.dbf' SIZE 1000M AUTOEXTEND ON MAXSIZE 10000M;

CREATE TABLESPACE IGT_INDEX DATAFILE '/oracle_db/db1/db_igt/ora_igt_index_01.dbf' SIZE 1000M AUTOEXTEND ON MAXSIZE 10000M;

ALTER DATABASE DEFAULT TABLESPACE IGT_TABLE;

DROP TABLESPACE IGT_TABLE_TEMP INCLUDING CONTENTS AND DATAFILES;

Optionally, move location for TEMPORARY tablespace 

CREATE TEMPORARY TABLESPACE TEMP_TBS_02 TEMPFILE '/oracle_db/db2/db_igt/data_files/ora_temporary_02.dbf' SIZE 1000M AUTOEXTEND ON MAXSIZE 30000M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_TBS_02;

DROP TABLESPACE TEMPORARY INCLUDING CONTENTS AND DATAFILES;

Optionally, move location for UNDO Tablespace

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/oracle_db/db2/db_igt/data_files/ora_undotbs2_01.dbf' SIZE 100M  AUTOEXTEND ON MAXSIZE 10000M;

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;

DROP TABLESPACE UNDOTBS INCLUDING CONTENTS AND DATAFILES;

Now, run the impdp

impdp system/syspass@orainst DIRECTORY=IG_EXP DUMPFILE=expdp_COL_SPARX_20230912.dmp LOGFILE=impdp_COL_SPARX_20230912.log SCHEMAS=COL_SPARX

impdp system/syspass@orainst DIRECTORY=IG_EXP 

DUMPFILE=expdp_IPN_STRESS_20230912.dmp LOGFILE=impdp_IPN_STRESS_20230912.log SCHEMAS=IPN_STRESS

impdp system/syspass@orainst DIRECTORY=IG_EXP DUMPFILE=expdp_IND_MOCOQ_20230912.dmp LOGFILE=impdp_IND_MOCOQ_20230912.log SCHEMAS=IND_MOCOQ

Last step, run DBMS_STATS.GATHER_SCHEMA_STATS.

BEGIN

  DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'COL_SPARX', 

  estimate_percent => 25);

END;

/

BEGIN

  DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'IPN_STRESS', 

  estimate_percent => 25);

END;

/

BEGIN

  DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'IND_MOCOQ', 

  estimate_percent => 25);

END;

/