Shulou(Shulou.com)06/01 Report--

First, import the table structure:

Imp userid=xxx/password file yyy.dmp full=y rows=n log=yyy.log

The above statement does not import data, only the structure, but several errors may occur:

1. The tablespace does not exist and objects cannot be created. This can create a tablespace, or modify the tablespace after the error is reported and then execute it again.

2. If you export by user, you can change full=y to fromuser=fff touser=xxx and then import.

Do the corresponding processing according to the log file. At the same time, the log will display information such as the relevant character set to determine whether the string length needs to be modified.

The second step (optional), modify the object tablespace

1. Generate a statement to modify the table space:

Select 'alter table' | | table_name | | 'move tablespace xxx_base;' from user_tables

2. Generate a statement that modifies the index table space:

Select 'alter index' | | index_name | | 'rebuild tablespace xxx_base;' from user_indexes

Step 3 (optional), drop trigger

Generate the statement for the drop trigger:

Select 'drop trigger' | | trigger_name | |'; 'from user_triggers

The fourth step is to modify the length of the table field string type field.

If a functional index is established on a column with a modified length, you need to back up the index definition and delete it, and then modify the field length before rebuilding the functional index.

Set heading off

Set space 0

Set pagesize 0

Set trimout on

Set trimspool on

Set linesize 2500

Spool modify_len.sql

Select 'alter table' | | table_name | | 'modify (' | column_name | |''| data_type | |'('| | ceil (data_length*1.5) | |');'

From user_tab_columns where data_type in ('VARCHAR2','CHAR','NCHAR','NVARCHAR2') and table_name not like' BIN%' and table_name like 'DSJ%'

Spool off

@ modify_len.sql

Fifth, before importing data, you need to block all foreign keys.

Select 'alter table' | | t.table_name | | 'disable constraint' | | t.constraint_name | |';'; 'from user_constraints t where t.intact where thanks to R' order by t.table_name

Step 6, import data

Imp userid=xxx/password file yyy.dmp full=y data_only=y log=yyy1.log

Step 7, after importing the data, you need to enable all foreign keys

Select 'alter table' | | t.table_name | | 'enable constraint' | | t.constraint_name | |';'; 'from user_constraints t where t.intact where thanks to R' order by t.table_name

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.