sqlplus / as sysdba
select username,default_tablespace,temporary_tablespace from dba_users where username='ZDZ';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
---------- ------------------- --------------------
ZDZ ZDZ_TS TEMP
select * from dba_directories;
root# cd /
root# mkdir /backup/zdz -p
root# cd /
root# ls -rtl
drwxr-xr-x. 2 root root 4096 6鏈 28 2011 srv
drwxr-xr-x. 2 root root 4096 6鏈 28 2011 media
drwx------. 2 root root 16384 4鏈 5 04:03 lost+found
drwxr-xr-x. 2 root root 4096 4鏈 5 04:09 selinux
dr-xr-xr-x. 2 root root 4096 4鏈 5 04:10 bin
dr-xr-xr-x. 2 root root 4096 4鏈 5 04:11 sbin
dr-xr-xr-x. 5 root root 1024 4鏈 5 04:11 boot
drwxr-xr-x. 3 root root 4096 4鏈 5 04:59 mnt
drwxr-xr-x. 3 root root 4096 4鏈 6 17:07 opt
dr-xr-xr-x. 9 root root 12288 4鏈 6 18:36 lib64
dr-xr-xr-x. 10 root root 4096 4鏈 6 18:36 lib
drwxr-xr-x. 5 oracle oinstall 4096 4鏈 6 19:48 u01
drwxr-xr-x. 4 root root 4096 4鏈 8 06:16 home
dr-xr-x---. 2 root root 4096 4鏈 8 06:48 root
drwxr-xr-x. 14 root root 4096 4鏈 8 06:48 usr
drwxr-xr-x 13 patrol patrol 4096 4鏈 8 06:51 bmc
drwxr-xr-x. 4 root root 4096 4鏈 28 09:41 oradata
drwxr-xr-x. 18 root root 4096 4鏈 28 10:06 var
dr-xr-xr-x 138 root root 0 5鏈 7 05:03 proc
drwxr-xr-x 13 root root 0 5鏈 7 05:03 sys
drwxr-xr-x. 10 oracle oinstall 4096 5鏈 7 05:03 tmp
drwxr-xr-x. 77 root root 4096 5鏈 7 05:04 etc
drwxr-xr-x 18 root root 3740 5鏈 7 05:04 dev
drwxr-xr-x 3 root root 4096 5鏈 7 06:28 backup
root# chmod 777 -R /backup/
root# ls -rtl
drwxr-xr-x. 2 root root 4096 6鏈 28 2011 srv
drwxr-xr-x. 2 root root 4096 6鏈 28 2011 media
drwx------. 2 root root 16384 4鏈 5 04:03 lost+found
drwxr-xr-x. 2 root root 4096 4鏈 5 04:09 selinux
dr-xr-xr-x. 2 root root 4096 4鏈 5 04:10 bin
dr-xr-xr-x. 2 root root 4096 4鏈 5 04:11 sbin
dr-xr-xr-x. 5 root root 1024 4鏈 5 04:11 boot
drwxr-xr-x. 3 root root 4096 4鏈 5 04:59 mnt
drwxr-xr-x. 3 root root 4096 4鏈 6 17:07 opt
dr-xr-xr-x. 9 root root 12288 4鏈 6 18:36 lib64
dr-xr-xr-x. 10 root root 4096 4鏈 6 18:36 lib
drwxr-xr-x. 5 oracle oinstall 4096 4鏈 6 19:48 u01
drwxr-xr-x. 4 root root 4096 4鏈 8 06:16 home
dr-xr-x---. 2 root root 4096 4鏈 8 06:48 root
drwxr-xr-x. 14 root root 4096 4鏈 8 06:48 usr
drwxr-xr-x 13 patrol patrol 4096 4鏈 8 06:51 bmc
drwxr-xr-x. 4 root root 4096 4鏈 28 09:41 oradata
drwxr-xr-x. 18 root root 4096 4鏈 28 10:06 var
dr-xr-xr-x 138 root root 0 5鏈 7 05:03 proc
drwxr-xr-x 13 root root 0 5鏈 7 05:03 sys
drwxr-xr-x. 10 oracle oinstall 4096 5鏈 7 05:03 tmp
drwxr-xr-x. 77 root root 4096 5鏈 7 05:04 etc
drwxr-xr-x 18 root root 3740 5鏈 7 05:04 dev
drwxrwxrwx 3 oracle oinstall 4096 5鏈 7 06:28 backup
su - oracle
sqlplus / as sysdba
create directory beifen as '/backup/zdz';
select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------- --------------- --------------
SYS BENFEI /backup/zdz
oracle# expdp system/123456 dumpfile=20170510.dmp logfile=20170510.log directory=benfei full=y 全库备份
expdp system/123456 dumpfile=20170510_emp2.dmp logfile=20170510_emp2.log directory=benfei tables=zdz.emp2; 表备份
expdp system/123456 dumpfile=20170510_zdz.dmp logfile=20170510_zdz.log directory=benfei schemas=zdz; 用户备份
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:/backup/zdz/20170510.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" completed with 1 error(s) at 06:44:24
ORA-39139: Data Pump does not support XMLSchema objects. TABLE_DATA:"OE"."PURCHASEORDER" will be skipped.
注释:这种问题只能通知开发处理,意思是表OE不支持对象XMLSchema格式!
impdp system/Aa123456 dumpfile=20170510.dmp logfile=impdp20170510.log directory=huanyuan schemas=zdz 全库导入
impdp system/Aa123456 dumpfile=20170510_emp2.dmp logfile=20170510_emp2.log directory=huanyuan tables=zdz.emp2; 表导入
impdp system/Aa123456 dumpfile=20170510_zdz.dmp logfile=impdp20170510_zdz.log directory=huanyuan schemas=zdz; 用户导入
create tablespace zdz_ts datafile '/home/oracle/app/oradata/jctest/zdz_ts.dbf' size 150M autoextend on; 一般情况下,导入数据时会报错,因为新的数据库中没有对应的表空间,这是需要新建表空间,且表空间大小要足够大
select * from dba_tables where OWNER='ZDZ';
select OWNER,TABLE_NAME from dba_tables where table_name='EMP1';
文件目录赋权
chown - R oracle:oinstall /backup/
//
select username from dba_users;
select table_name
from all_all_tables
where owner='大写用户名';
查询所有表空间
SELECT A.TABLESPACE_NAME "TABLESPACE_NAME",TOTAL/(1024*1024) "TOTAL M",
FREE/(1024*1024) "FREE M", (TOTAL-FREE)/(1024*1024) "USED M",
ROUND((TOTAL-FREE)/TOTAL,4)*100 "USED %"
FROM (SELECT TABLESPACE_NAME,SUM(BYTES) FREE FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME,SUM(BYTES) TOTAL FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME;
查询表空间下的所有数据文件
select
b.file_name file_name,
b.tablespace_name tablespace_name,
b.bytes/1024/1024 "TOTAL M",
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 "USED %",
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) "USED %"
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
#and b.tablespace_name=('BPPM_DATA')
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name;