数据库导出导入总结

Oracle数据库导出导入总结

--查询当前所有表空间
select tablespace_name,file_id,file_name,bytes from dba_data_files order by file_id;

--查询表空间详情
select * from dba_tablespaces;

--查询所有用户表空间
select username,user_id,default_tablespace,temporary_tablespace,created from dba_users;

--删除指定表空间
drop tablespace TTBS_SCOTT including contents and datafiles;

--创建表空间
create tablespace TBS_SCOTT logging datafile 'D:\Oracle\oradata\orcl\scott_data.dbf'
size 10m autoextend on next 10m maxsize 20480m
extent management local;

--创建临时表空间
create temporary tablespace TTBS_SCOTT tempfile 'D:\Oracle\oradata\orcl\scott_temp.dbf'
size 10m autoextend on next 10m maxsize 20480m
extent management local;

--创建用户并指定表空间和临时表空间
create user scott identified by ll123456
default tablespace TBS_SCOTT
temporary tablespace TTBS_SCOTT;

--修改用户默认表空间
alter user scott quota unlimited on TBS_SCOTT;

--查询用户表空间
select * from dba_users where username='scott'

--授予用户最高权限
grant connect,resource,dba to scott;

--查看数据库当前版本
select * from v$version

-- D:\Oracle\admin\orcl\dpdump 数据库默认文件位置
select * from dba_directories;

--允许空表导出
select 'alter table '|| table_name ||' allocate extent;'
from user_tables where segment_created='NO';
--导出
exp scott/123456@localhost:1521/orcl file=D:\scott.dmp owner=scott
--导入
imp scott/123456@localhost:1521/orcl file=D:\scott.dmp ignore=y full=y;
--创建数据泵
create directory DATA_PUMP_DIR as '/home/oracle/';
grant read,write on directory DATA_PUMP_DIR to system;
select * from dba_directories;
--数据泵导出
EXPDP scott/123456@orcl schemas=scott directory=DATA_PUMP_DIR dumpfile=scott.dmp logfile=scott_EXPDP_日志文件.log version=11.2.0.4.0
--数据泵导入
IMPDP scott/123456@localhost:1521/orcl remap_schema=scott:scott remap_tablespace=TBS_SCOTT:TBS_SCOTT dumpfile=scott.dmp logfile=日志文件.log directory=DATA_PUMP_DIR version=11.2.0.4.0