oracle常用命令操作

--创建表空间
   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