Moving Tablespaces Oracle

2 Jan

source : http://home.galileo.edu/~obonilla/writing/oracle/
Kadangkala pada saat kita membuat / install database, create tablespace-nya pakai default tablespace “USERS”, tetapi kemudian hari karena suatu hal (tablespace users berkembang tanpa kendali) maka diinginkan untuk memindahkan tablespaceke suatu tablespace baru. Atau alasan lain seperti memisahkan skema tablespace “PRODUCTION” ke “DEVELOPMENT”.

Test….test….
1. create tablespace
$ sqlplus / as sysdba
sql> CREATE TABLESPACE development DATAFILE
‘/u01/oradata/test/datafile/development01.dbf’ SIZE 50M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

2. create user
sql> CREATE USER development IDENTIFIED BY password
DEFAULT TABLESPACE development
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
sql> GRANT RESOURCE TO development;
sql> GRANT CONNECT TO development;
sql> ALTER USER development DEFAULT ROLE ALL;
sql> GRANT CREATE ANY VIEW TO development;
sql> GRANT UNLIMITED TABLESPACE TO development;
sql> GRANT ALTER SESSION TO development;
sql> GRANT SELECT ANY DICTIONARY TO development;
sql> EXIT;

3. export dmp
$ exp userid=production/password file=test.dmp log=test.txt

4. edit dmp pada file test.dmp
dimana setiap kata “PRODUCTION” harus diganti “DEVELOPMENT”, tetapi karena keterbatasan untuk membuka file besar, maka harus displit dulu.
$ split -b100m test.dmp
$ ls
test.dmp xad xah xal xap xat xax xbb xbf xbj xbn xbr
xaa xae xai xam xaq xau xay xbc xbg xbk xbo xbs
xab xaf xaj xan xar xav xaz xbd xbh xbl xbp
xac xag xak xao xas xaw xba xbe xbi xbm xbq

a. LINUX editor : emacs
b. Windows editor : NOTEPAD++

rest_dmp1

atau :
c. Windows Tools : A.F.9 http://www.fauland.com
tools cari dan replace, tanpa open file, tools ini yang saya sarankan
thank’s u/ rekanku (Eko Yuliantoro R) atas info nya

rest_dmp21

5. restore dmp
$ imp system/password FROMUSER=production TOUSER=development FILE=test.dmp LOG=test.txt

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: