dump导入oracle需要提前建表吗_Oracle使用dump导入数据

导入前准备

建立导入用户

CREATE USER YYBS_IMPIDENTIFIED BY YYBS_IMPDEFAULT TABLESPACE USERSTEMPORARY TABLESPACE TEMPPROFILE DEFAULTACCOUNT UNLOCK;GRANT RESOURCE TO YYBS_IMP;GRANT CONNECT TO YYBS_IMP;GRANT IMP_FULL_DATABASE TO YYBS_IMP;ALTER USER YYBS_IMP DEFAULT ROLE ALL;GRANT UNLIMITED TABLESPACE TO YYBS_IMP;

确认数据库

tnsping stakfdbexport ORACLE_SID=stakfdbsqlplus / as sysdbaselect name,log_mode from v$database; --确认SIDselect utl_inaddr.get_host_address from dual; --确认IP地址

杀进程

select sid,serial#,username,status,osuser,machine,terminal,program from v$session;alter system kill session '861,21309';强杀进程：select spid, osuser, s.program from v$session s,v$process p where s.paddr=p.addr and s.sid=144kill -9 spid锁用户：select 'alter user '||USERNAME||' account lock;' from dba_users where username like 'U%' and created>to_date('20110926','yyyymmdd') order by CREATED;DEMO:alter user UCR_CEN1 ACCOUNT LOCK;

清库

select user_id,USERNAME,ACCOUNT_STATUS,CREATED from dba_users order by CREATED;select 'drop user '||USERNAME||' cascade;' from dba_users where username like 'U%' and created>to_date('20110926','yyyymmdd') order by CREATED;demo:drop user UOP_UIF2 cascade;

建立Directory

sqlplus system/oracle@STAKFDBCREATE OR REPLACE DIRECTORY imp930sta_dir AS '/app/imp930/sta';sqlplus system/oracle@CRMKFDBCREATE OR REPLACE DIRECTORY imp930crm_dir AS '/app/imp930/crm';CREATE OR REPLACE DIRECTORY imp930cen_dir AS '/app/imp930/center';CREATE OR REPLACE DIRECTORY imp930oth_dir AS '/app/imp930/other';

导入脚本

impdp system/oracle@csngstat831 dumpfile=Usta_full.dump logfile=Usta_full.log job_name=Usta_full full=y directory=imp930sta_dir TABLE_EXISTS_ACTION=replace parallel=1impdp system/oracle@csngstat831 dumpfile=sUCR_STA4.dump logfile=sUCR_STA4.log job_name=sUCR_STA4 schemas=UCR_STA4 directory=imp930sta_dir TABLE_EXISTS_ACTION=replace parallel=1

导入过程监控

监控主机性能

nmonvmstatiostat

查看导入进度

select count(0) from all_objects where CREATED > sysdate-1;select * from tab where tname like 'CRM_FULL';

查看IMPDP进度

select * from dba_datapump_jobs;impdp system/oracle@crmkfdb attach=UCR_CRM3helpstatusstart_jostop_jobkill_jobparallel=4

导入后工作

重置密码

select 'alter user '||USERNAME||' identified by test123456;' from dba_users where username like 'U%' and created>to_date('20110926','yyyymmdd') order by CREATED;alter user uif_act1_sta1 identified by test123456;

解锁用户：

alter user UCR_CEN1 ACCOUNT UNLOCK;

安全策略修改

select * from dba_profiles WHERE profile = 'DEFAULT' AND resource_type = 'PASSWORD';alter profile DEFAULT limit password_verify_function null;alter profile DEFAULT limit FAILED_LOGIN_ATTEMPTS UNLIMITED;alter user XXXX profile DEFAULT;

其它

重新导入同义词

table_exists_action=skip content=metadata_onlyimpdp system/oracle@csngcrm831 dumpfile=cUCR_CRM3.dump logfile=cUCR_CRM3.log job_name=cUCR_CRM3 schemas=UCR_CRM3 directory=imp930crm_dir TABLE_EXISTS_ACTION=skip content=metadata_only parallel=1

重建同义词：

select 'create or replace synonym UCR_CRM3.'||synonym_name||' for UCR_CEN1.'||table_name||';'from dba_synonyms where table_owner='UCR_CEN1' and owner='UCR_CRM4';

查看更改表空间

select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_spacefrom dba_data_fileswhere tablespace_name like 'TBS_CRM_DUSR3'order by tablespace_name; --查看表空间

CREATE TABLESPACE TBS_ACT_DEFDATAFILE '/csoradata/csngcrm/TBS_ACT_DEF.dbf' SIZE 1024MUNIFORM SIZE 128k; --建立表空间

CREATE TABLESPACE "TBS_ACT_HIACT07" DATAFILE '/oradata/ngcrm/TBS_ACT_HIACT07.dbf' SIZE 10485760 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO; --建立表空间2

ALTER TABLESPACE "TBS_CRM_IUSR5" ADD DATAFILE '/oradata/ngbil/crm/TBS_CRM_IUSR5_2.dbf' SIZE 10485760 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M ; --增加表空间文件

ALTER DATABASE DATAFILE '/csoradata/csngcrm/TBS_ACT_DEF.dbf'AUTOEXTEND ON NEXT 100MMAXSIZE 24576M; --设定自动扩展

CREATE TEMPORARY TABLESPACE temp_dataTEMPFILE '/oracle/oradata/db/TEMP_DATA.dbf' SIZE 50M --建立临时表空间

ALTER DATABASE DATAFILE '/oradata/ngcrm/TBS_CRM_DUSR3.dbf'RESIZE 12288M; --调表空间ALTER DATABASE TEMPFILE '/oradata/ngcrm/temp1.dbf'RESIZE 12288M; --调临时表空间

移动表空间：alter tablespace TBS_ACT_DEF offline;alter tablespace TBS_ACT_DEF rename datafile '/oradata/ngbil/crm/TBS_ACT_DEF_2.dbf' to '/oradata/ngcrm/TBS_ACT_DEF_2.dbf';alter tablespace TBS_ACT_DEF online;select * from dba_tablespaces where tablespace_name='TBS_ACT_DEF';select * from dba_data_files where tablespace_name='TBS_CRM_DUSR1';

查锁

select * from v$locked_objectselect * from dba_objects where object_id=286655select * from v$session where sid=822;alter system kill session '822,94';