Oracle Db Dump

Export Dump:
Open Terminal:

ssh devel@<ip>
su - oracle
cd $ORACLE_HOME
./bin/exp
<databaseUser>/<databaseUserPass>

When prompt for dump name (expdat.dmp) > /tmp/<specifyName>.dmp

cd /tmp
tar -jcvf <specifyName>.tar.bz2 <specifyName>
ctrl+D ctrl+D
scp root@db:/tmp/<specifyName>.tar.bz2 /tmp

Import Dump:

1. Kill tomcat (bcz, some projects are connected to that databaseUser)
2. Login to databaseUser system/sistem through isqlplus
3. Execute this query:

<databaseUser> and <databaseUserPass> will be specified in<projectDirectory>/src/db.properties

DROP USER <databaseUser> CASCADE;

OR

Begin
for s in (select sequence_name from user_sequences) loop
execute immediate ('drop SEQUENCE '||s.sequence_name);
end loop;
for c in (select table_name from user_tables) loop
execute immediate ('drop table '||c.table_name||' cascade constraints');
end loop;
End;

OR (NOT TESTED)

DROP USER <databaseUser> CASCADE;
ALTER TABLESPACE <databaseUser> OFFLINE;
DROP TABLESPACE <databaseUser> INCLUDING CONTENTS;
ALTER TABLESPACE <databaseUser>_INDEX OFFLINE;
DROP TABLESPACE <databaseUser>_INDEX INCLUDING CONTENTS;

Execute this query:

create user <databaseUser> identified by <databaseUserPass> default tablespace users temporary tablespace temp;
grant connect, resource to <databaseUser> with admin option;
grant create role to <databaseUser>;
grant select_catalog_role to <databaseUser>;
grant create any view to <databaseUser>;

Open Terminal:

scp /tmp/<specifyName>.tar.bz2 root@db:/tmp
ssh root@db
tar -jxvf <specifyName>.tar.bz2
cd $ORACLE_HOME
./bin/imp
When prompt for dump name (expdat.dmp) > /tmp/<specifyName>.dmp
When prompt for entire file(no) > yes
ctrl+D ctrl+D
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License