oracle数据逻辑备份
来源:互联网 发布:七夕电影全部数据 编辑:程序博客网 时间:2024/06/14 01:26
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;
阅读全文
0 0
- oracle数据逻辑备份
- Oracle逻辑备份之数据泵(1)
- Oracle逻辑备份之数据泵(2)
- Oracle数据库逻辑备份
- oracle逻辑备份脚本
- Oracle---逻辑备份
- Oracle简单的逻辑备份
- oracle数据库逻辑备份脚本
- Oracle逻辑备份与恢复
- oracle 逻辑备份与导入
- ORACLE逻辑备份与恢复
- oracle 逻辑自动备份脚本
- oracle逻辑备份和恢复
- Oracle逻辑备份与物理备份
- oracle物理备份和逻辑备份理解
- Oracle10g数据库备份与恢复总结3--Oracle备份与恢复之逻辑备份--数据泵技术
- oracle数据库备份:物理备份和逻辑备份
- Oracle中关于逻辑备份与恢复
- JNI 技巧
- 正则表达式学习笔记
- lightoj-1289(数论+素数打表)
- 软件测试黑马工程师-----QTP自动化测试
- [Scala]Scala学习笔记九 提取器
- oracle数据逻辑备份
- 经典的同步问题(银行取钱)
- POJ-3685---Matrix (二分)
- C
- HDU 6060 RXD and dividing【斯坦纳树】
- Centos7 Oracle 11g安装文档
- LeetCode No.19 Remove Nth Node From End of List
- JQuery动画
- click、onclick、on事件执行优先级