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