alter tablespace vgsm
add datafile 'c:\oracle\product\10.2.0\oradata\vgsm\vgsm_01.dbf'
size 1024M autoextend on next 50M maxsize unlimited;
--允许已存在的数据文件自动增长
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF'
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
--删除数据文件语法
ALTER TABLESPACE 表空间名 DROP DATAFILE 数据文件名;
--查看表空间的名称及大小
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;
--查看表空间物理文件的名称及大小
SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;
--统计数据所有数据表数据量
select 'select '''||t.owner||'.'||t.table_name ||'''tname ,'||'count(*) c from '||t.owner||'.'||t.table_name || ' union ' from all_all_tables t where t.table_name like '%ZB%' and t.owner like 'SJZ_%'
--查询数据表所有列名称
select c.OWNER,c.TABLE_NAME, c.COLUMN_NAME from all_tab_columns c
where c.owner like 'SJZ_%' and c.COLUMN_NAME like '%MXMC%'order by c.TABLE_NAME
--查询所有表描述信息
select * from all_tab_comments c
where c.owner like 'SJZ_%'
and c.comments like '%方案%'
order by c.TABLE_NAME
--查询所有列描述信息
select * from all_col_comments c
where c.owner like 'SJZ_%'
and c.comments like '%风险分值%'
order by c.TABLE_NAME
--oracle12g以下版本导入
IMPDP USERID='CGTAX_V_30_HC/cgtax_db' REMAP_SCHEMA=c##cgtax_v_30_hc:cgtax_v_30_hc directory=DATA_FILE_DIR dumpfile=CGTAX_08_04.DMP version=10.2.0
--oracle12g版本导入
impdp cgtax_v_30_hc/cgtax_db directory=DATA_FILE_DIR dumpfile=data.dmp REMAP_SCHEMA=c##cgtax_v_30_hc:cgtax_v_30_hc
导出语句:
EXPDP USERID='sys/sysdba@orcl as sysdba'schemas=dcops directory=DATA_FILE_DIR dumpfile=dcops.dmp logfile=dcops.logversion=10.2.0
--创建DATA_FILE_DIR目录:
create OR REPLACE directory DATA_FILE_DIR as 'E:\oracle\demo\schema\sales_history';
Grant read,write on directory DATA_FILE_DIR to dcops;
---授权
Grant read,write on directory DATA_FILE_DIR to dcops;
--oracle自动备份脚本,每天备份删除7天前
set year=%DATE:~0,4%
set month=%DATE:~5,2%
set day=%DATE:~8,2%
EXPDP USERID='nltcp/nltcp@orcl' schemas=nltcp directory=DATA_FILE_DIR dumpfile=nltcp_%year%%month%%day%.dmp logfile=nltcp_%year%%month%%day%.log version=10.2.0.1.0
forfiles /p dumpfile /s /m *.dmp /d -7 -c "cmd /c del /f /q @path"
exit