Oracle导出表空间的创建语句、导入、导出dmp文件
发布时间:2020-12-12 13:33:54 所属栏目:百科 来源:网络整理
导读:begin for c in ( select t.NAME,d.NAME as name2 from v$tablespace t inner join v$datafile d on t."TS#" = d."TS#" where t."TS#" 4 )loopdbms_output.put_line( ‘ create tablespace ‘ || c.NAME || ‘ DATAFILE ‘ || ‘‘‘‘ || c.name2 || ‘‘‘
begin for c in (select t.NAME,d.NAME as name2 from v$tablespace t inner join v$datafile d on t."TS#"=d."TS#" where t."TS#">4) loop dbms_output.put_line( ‘create tablespace ‘ ||c.NAME || ‘ DATAFILE ‘||‘‘‘‘||c.name2||‘‘‘‘||‘ size 20M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;‘ ); end loop; end; ? 结果如: create tablespace EXAMPLE DATAFILE ‘D:ORACLEORADATAORCLEXAMPLE01.DBF‘ size 20M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED; create tablespace HCZZ_WEB_DATA DATAFILE ‘D:ORACLEORADATAORCLDBF_HCZZ_WEB_DATA.DBF‘ size 20M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED; create tablespace HCZZ_WEB_INDEX DATAFILE ‘D:ORACLEORADATAORCLDBF_HCZZ_WEB_INDEX.DBF‘ size 20M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED; 数据库导出: --第一次新建数据库导入使用 impdp hczz_web/password@localhost/orcl dumpfile=HCZZ_WEB_20170706.dmp impdp hczz_web/password@221.234.25.77/orcl dumpfile=HCZZ_WEB_20180502.dmp impdp hczz_web/password dumpfile=HCZZ_WEB_20170706.dmp --第二次导入使用 ? 创建表空间 /*分为四步 */ /*第1步:创建临时表空间 */ create temporary tablespace yuhang_temp tempfile ‘D:oracledatayuhang_temp.dbf‘ size 50m autoextend on next 50m maxsize 20480m extent management local; /*第2步:创建数据表空间 */ create tablespace yuhang_data logging datafile ‘D:oracledatayuhang_data.dbf‘ size 50m autoextend on next 50m maxsize 20480m extent management local; /*第3步:创建用户并指定表空间 */ create user yuhang identified by yuhang default tablespace yuhang_data temporary tablespace yuhang_temp; /*第4步:给用户授予权限 */ grant connect,resource,dba to yuhang; 创建用户 DECLARE user_name CONSTANT VARCHAR2 (64) := ‘hczz_web‘; user_password CONSTANT VARCHAR2 (64) := ‘password‘; data_tablespace_name CONSTANT VARCHAR2 (64) := ‘hczz_web_data‘; temp_tablespace_name CONSTANT VARCHAR2 (64) := ‘hczz_web_temp‘; PROCEDURE p_execcmd (v_cmd IN VARCHAR2) AS v_cursorid INTEGER; BEGIN v_cursorid := DBMS_SQL.open_cursor; DBMS_SQL.parse (v_cursorid,v_cmd,DBMS_SQL.native); DBMS_SQL.close_cursor (v_cursorid); EXCEPTION WHEN OTHERS THEN DBMS_SQL.close_cursor (v_cursorid); RAISE; END p_execcmd; BEGIN -------------------1.创建用户----------------------------------------------- p_execcmd ( ‘CREATE USER ‘ || user_name || ‘ PROFILE DEFAULT IDENTIFIED BY ‘ || user_password || ‘ DEFAULT TABLESPACE ‘ || data_tablespace_name || ‘ TEMPORARY TABLESPACE ‘ || temp_tablespace_name || ‘ ACCOUNT UNLOCK‘ ); -------------------2.授权--------------------------------------------------- p_execcmd( ‘GRANT CONNECT TO ‘||user_name||‘ WITH ADMIN OPTION‘ ); p_execcmd( ‘GRANT RESOURCE TO ‘||user_name||‘ WITH ADMIN OPTION‘ ); ---以下慎用,权限过大 p_execcmd( ‘GRANT DBA TO ‘||user_name||‘ WITH ADMIN OPTION‘ ); END; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |