Work and LIfe Balance...

Drop the TableSpace , User ORACLE

                                For dropping the TABLESPACE and USER

1. DROP TABLESPACE <TABLESPACE_NAME> INCLUDING CONTENTS AND DATAFILES;

2. DROP USER <USER_NAME> CASCADE;

Always we need to drop the Tablespace FIRST and then associated user. with this sequence only we can make sure that we erase the data perfectively.

or we would face some issue like the .DBF would not get removed then we need to stop the ORACLE service and then delete the .DBF file manually which is not good way.

Drop unique CONSTRAINT and INDEX not getting DROPPED in Oracle Database

Issue :  When ever I imported the Oracle Database from (.DMP) file. I got unusual exception that is
If an unique CONSTRAINT on a table is been droped then it is not dropping the correspondence INDEX.

Say we have 'X' table and we created unique constrait say 'k'  on it , then what happens is an index is created on table 'X' with same name 'k'.

So now dropping Constraint 'k' should also drop the corresponding INDEX , but this is not happening

syntax being used is
"ALTER TABLE <TABLE NAME> DROP CONSTRAINT <CONSTRAINT NAME>"


Reasoning :

When ever we import the Oracle Database especially , It has it own sequence order for importing
that is say

1. It imports Schema , first then
2. It import Trigger
3. It imports Indexs
....

6. It imports Constraints
....

So above we can see that Indexes are imported before the constraints and the linkage between Constraint and the Index is lost.

If we normally create an Unique Constrait on the TABLE , an index is created on the table with same name and there would be linkage is such a way that if we drop the Constraint , it would automatically  drop the INDEX too.


Solution :

"ALTER TABLE <TABLE NAME> DROP CONSTRAINT <CONSTRAINT NAME> DROP INDEX"

add "DROP INDEX" to current syntax and it would also make sure we drop INDEX created while importing Oracle DMP file.
 

Database Export and Import in Oracle of .DMP (Dump File)

ORACLE

For exporting the Database :

expdp system/Password@SID full=Y dumpfile=CMDB.dmp logfile=CMDB10G.log

For importing the Database :


impdp system/Password@SID full=Y dumpfile=CMDB.dmp logfile=CMDBIMPORT10G.log