Oracle

Små minnesanteckningar gällande Oracle.

Export - Import

Lista över åtgärder, inklusive kommandon för att utföra dess, för att enkelt exportera och importera ett schema under annat namn.

1. Create filesystem directory. From command prompt:

mkdir c:\tmpdmp

2. Create database directory handle. In sqlplus

connect system as sysdba
create directory tmpdmp as 'c:\tmpdmp';
grant read, write on directory tmpdmp to public;

3. Export. From command prompt:

expdp myusr/mypwd schemas=oldschema directory=tmpdmp dumpfile=myexport.dmp

4. Create new schema. From sqlplus:

connect system as sysdb
CREATE USER newusr IDENTIFIED BY pwd DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT CONNECT, RESOURCE, CREATE VIEW TO newusr;

5. Import. From command prompt:

impdp newusr/pwd remap_schema=oldschema:newschema directory=tmpdmp dumpfile=myexport.dmp

Run expdp help=y or impdp help=y for all options.