Oracle-数据库还原和备份

数据还原

imp hsopics/123456@128.8.100.101/OPICS full=y file=/home/oracle/dmp/opics_20180104.dmp ignore=y
imp fms/single_qlb@128.8.100.101/OPICS full=y file=/home/oracle/dmp/fms_20160830.dmp ignore=y
imp quote_qd/123456@128.8.100.101/OPICS full=y file=/home/oracle/dmp/position20170831.dmp ignore=y

数据库备份

exp sl_trd_sh/123456@128.8.100.101/IFBM file=/home/oracle/databak/ifbmbak20180416.dmp full=y 上海银行数据库
imp tygl/123456@128.8.100.101/IFBM full=y file=/home/oracle/dmp/20180424.dmp ignore=y

导入导出

exp VOLET_DEV_SL/123456@128.8.100.101/IFBM file=/home/oracle/databak/VOLET_DEV_SL20200303.dmp full=y

imp VOLET_DEV_SL/123456@192.168.2.23:1521/ORCL file=/mnt/hgfs/vmshare/VOLET_DEV_SL20200303.dmp fromuser=VOLET_DEV_SL touser=VOLET_DEV_SL ignore=y buffer=5400000

cat '/mnt/hgfs/vmshare/VOLET_DEV_SL20200303.dmp' |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6

–导出、生成当前用户的所有表和视图的注释

SELECT 'comment on table ' || a.table_name || ' is ' || '''' || a.comments || '''' || ';'
from user_tab_comments a
where a.table_type in ('TABLE', 'VIEW')
AND comments IS NOT NULL;

–导出、生成当前用户的所有表字段的注释

SELECT 'comment on column ' || t.table_name || '.' || t.column_name ||
' is ' || '''' || t1.comments || '''' || ';'
FROM User_Tab_Cols t, User_Col_Comments t1
WHERE t.TABLE_NAME = t1.table_name
AND t.column_name = t1.column_name
AND t1.comments IS NOT NULL;

1. 查看所有表空间大小

select tablespace_name,sum(bytes)/1024/1024 from dba_data_files

group by tablespace_name;

2. 未使用的表空间大小

select tablespace_name,sum(bytes)/1024/1024 from dba_free_space

group by tab