Oracle导出表空间的创建语句、导⼊、导出dmp⽂件
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:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF' size 20M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
create tablespace HCZZ_WEB_DATA DATAFILE 'D:\ORACLE\ORADATA\ORCL\DBF_HCZZ_WEB_DATA.DBF' size 20M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
create tablespace HCZZ_WEB_INDEX DATAFILE 'D:\ORACLE\ORADATA\ORCL\DBF_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
--第⼆次导⼊使⽤
impdp hczz_web/password dumpfile=HCZZ_WEB_20170706.dmp table_exists_action=replace
--如果有替换还原新的数据库命，则执⾏----------------------
impdp hczz_web_test1/password@localhost/orcl dumpfile=HCZZ_WEB_20170628.DMP table_exists_action=replace remap_schema=hczz_web:hczz_web_test1 impdp hczz_web_test1/password@localhost/orcl dumpfile=HCZZ_WEB_20170628.DMP table_exists_action=replace remap_schema=hczz_web:hczz_web_test1 impdp hczz_web_test1/password@localhost/orcl dumpfile=备份库.DMP table_exists_action=replace remap_schema=原库名：现库名
创建表空间
/*分为四步 */
/*第1步：创建临时表空间  */
create temporary tablespace yuhang_temp
tempfile 'D:\oracledata\yuhang_temp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
/*第2步：创建数据表空间  */
create tablespace yuhang_data
logging
datafile 'D:\oracledata\yuhang_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.授权---------------------------------------------------
drop table if exists adminp_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;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。