1. create data table space, index table space 2. create user jerry and authorize 3. create a table, index, and insert data for user jerry. export data of jerry 5. create user peter and authorize 6. import the data of jerry to the peter user (the original tablespace exists and does not exist) 7. join user peter to view the tablespace conclusion: when the original tablespace exists, import it to the original tablespace. When the original tablespace does not exist, the default tablespace imported to the new user (peter), that is, the original data table space and the original index tablespace correspond to the new user's data table space. Therefore, when exporting and importing data, it is best to create the tablespace of the original data in advance.
-- 1. create a data table space and index the tablespace conn/as sysdbacreate tablespace jerry_data datafile '+ DGDATA1/orcl11g/jerry_data01.dbf' size 10 m autoextend on next 10 m maxsize 100 m; create tablespace jerry_indx datafile '+ DGDATA1/orcl11g/jerry_indx01.dbf' size 10 m autoextend on next 10 m maxsize 100 m;
-- 2. create user jerry and authorize drop user jerry cascade; create user jerry identified by jerry; grant connect, resource to jerry;
-- 3. create a table and index for user jerry, and insert data conn jerry/jerrycreate table t1 (id number, name varchar2 (10) tablespace jerry_data; create index t1_id on t1 (id) tablespace jerry_indx; insert into t1 values (1, 'aaaaa'); commit;
-- 4. Export jerry's data! Exp jerry/jerry file = '/tmp/jerry_full.dmp' log = '/tmp/jerry_full.log ';
-- 5. create user peter and authorize conn/as sysdbadrop user peter cascade; create user peter identified by peter; grant connect, resource, imp_full_database to peter;
-- 6. Import jerry's data to the peter user (when the original tablespace does not exist) drop tablespace jerry_data including contents and datafiles; drop tablespace jerry_indx including contents and datafiles ;! Imp peter/peter fromuser = jerry touser = peter buffer = 100000 ignore = y file = '/tmp/jerry_full.dmp' log = '/tmp/jerry_full_imp.log ';
-- 7. Connect to the user peter to view the tablespace conn peter/peterselect table_name, tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME ------------------------------ T1 USERS
Select index_name, tablespace_name from user_indexes; INDEX_NAME TABLESPACE_NAME ------------------------------ T1_ID USERS
-- 8. import jerry's data to the peter user (when the original tablespace exists) create tablespace jerry_data datafile '+ DGDATA1/orcl11g/jerry_data01.dbf' size 10 m autoextend on next 10 m maxsize 100 m; create tablespace jerry_indx datafile '+ DGDATA1/orcl11g/jerry_indx01.dbf' size 10 m autoextend on next 10 m maxsize 100 m ;! Imp peter/peter fromuser = jerry touser = peter buffer = 100000 ignore = y file = '/tmp/jerry_full.dmp' log = '/tmp/jerry_full_imp.log ';
-- 9. Connection user peter to view the tablespace conn peter/peterselect table_name, tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME ------------------------------ T1 JERRY_DATA
Select index_name, tablespace_name from user_indexes;
INDEX_NAME TABLESPACE_NAME ------------------------------ T1_ID JERRY_INDX