Oracle常用DBA相关语句

1 表空间

1.1 创建表空间

CREATE TABLESPACE &tbsname
DATAFILE 'E:\ORADATA\ESIMLOCA\&tbsname.dbf'
SIZE 100M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

1.2 删除表空间

DROP TABLESPACE &tbsname INCLUDING CONTENTS AND DATAFILES;

1.3 收缩表空间

Alter database datafile 'xx' resize xx

1.4 新增表空间文件

--新增临时表空间文件
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLEDB\ECTMSUP\TEMP02.DBF' SIZE 512M AUTOEXTEND
   ON NEXT 100 MAXSIZE UNLIMITED;
--增大临时文件大小
alter database tempfile 'D:\ORACLEDB\ECTMSUP\TEMP02.DBF'  resize 600m;
--将临时数据文件设为自动扩展
alter database tempfile 'D:\ORACLEDB\ECTMSWZ\TEMP_EA_TMP.ORA' autoextend on next 5m maxsize unlimited;

1.5 查看表空间使用情况

SELECT A.TABLESPACE_NAME, TOTAL, FREE, TOTAL - FREE USED
 FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 TOTAL
         FROM DBA_DATA_FILES
        GROUP BY TABLESPACE_NAME) A,
      (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 FREE
         FROM DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY A.TOTAL DESC;

1.6 查看表所占用的空间大小

SELECT TABLESPACE_NAME,
      OWNER,
      SEGMENT_NAME,
      TO_CHAR(SUM(BYTES) / (1024 * 1024), '999G999D999') CNT_MB
 FROM DBA_EXTENTS
WHERE SEGMENT_TYPE LIKE 'TABLE%'
  AND OWNER IN ('EA_CTMS','EA_CTMS_HIST','EI_CTMSDATA','EA_CUSTOM')
GROUP BY TABLESPACE_NAME, OWNER, SEGMENT_NAME
  ORDER BY SUM(BYTES) DESC;

2 表分区

2.1 查询表分区的创建情况

SELECT *
 FROM ALL_TAB_PARTITIONS
WHERE TABLE_OWNER = 'EA_CTMS'
  AND TABLE_NAME = 'STD_TR_CUST_FUND_BAL';
SELECT * FROM ALL_TAB_SUBPARTITIONS T WHERE T.TABLE_NAME = 'STD_TR_CUST_FUND_BAL';
SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MBYTESE
 FROM USER_SEGMENTS
WHERE SEGMENT_TYPE = 'TABLE'
GROUP BY SEGMENT_NAME;

2.2 查询表时指定分区

SELECT * FROM EA_CTMS_HIST.STD_ESIM_FU_JOUR_CORP_FEE PARTITION(D_20120927) T

3 用户

3.1 创建用户

CREATE USER &username PROFILE DEFAULT IDENTIFIED BY &password
DEFAULT TABLESPACE &tablespacename
TEMPORARY TABLESPACE TEMP
ACCOUNT UNLOCK;

3.2 给用户赋权限

GRANT DBA TO &username;
grant comment any table to &username;
grant create any directory to &username;
GRANT CREATE ANY INDEX TO &username;
grant create synonym to &username;
GRANT EXECUTE ON SYS.DBMS_LOCK TO &username;
grant create any table to &username;
grant select any table to &username;
grant drop any procedure to &username

3.3 删除用户

DROP USER &username CASCADE;

4 导入导出

4.1 导入语句

imp EA_CUSTOM/EA_CUSTOM@ECTMS file=D:\tmp\dmp\EA_CUSTOM.dmp log=D:\tmp\dmp\EA_CUSTOM_imp.log ignore=y full=y buffer=5400000 STATISTICS=none

4.2 导出语句

exp EA_CUSTOM/EA_CUSTOM@ECTMS file=D:\tmp\EA_CUSTOM.dmp owner=(EA_CUSTOM) rows=n log=D:\tmp\EA_CUSTOM.log

4.3 按表生成导入导出语句

--按表生成导出语句
SELECT 'exp %username%/%password%@%sid% tables=(' || T.TABLE_NAME || ') file=%filePath%\' ||
      T.TABLE_NAME || '.dmp log=%filePath%\' || T.TABLE_NAME || '.log'
 FROM ALL_TABLES T
WHERE T.OWNER = 'EI_CTMSDATA'
  AND T.TABLE_NAME LIKE 'ESIM_%'
ORDER BY T.TABLE_NAME;
--按表生成导入语句
SELECT 'imp %username%/%password%@%sid% file=%filePath%\' ||
      T.TABLE_NAME || '.dmp ignore=yes log=%filePath%\IMP_' || T.TABLE_NAME || '.log'
 FROM ALL_TABLES T
WHERE T.OWNER = 'EI_CTMSDATA'
  AND T.TABLE_NAME LIKE 'ESIM_%'
ORDER BY T.TABLE_NAME;

5 分析表

analyze table EA_CTMS.TOTAL_BUSI_CHECK_DATA compute statistics;
--清空缓存
ALTER SYSTEM FLUSH SHARED_POOL;
--分析表
exec dbms_stats.gather_table_stats('EA_CTMS','LOG_FM_FILE_OPR',cascade => false);
exec dbms_stats.gather_table_stats('EA_CTMS','FM_FILE_EXCEP_INFO',cascade => false);
exec dbms_stats.gather_table_stats('EA_CTMS','FM_INTF_FILE_CONTROL',cascade => false);
--重建索引（在线重建）
alter index ea_ctms.IDX_FM_INTF_FILE_CONTROL_T rebuild online ;

6 查询无效对象

select * From dba_objects where status !='VALID';

7 重新编译对象

DECLARE
BEGIN
   SYS.UTL_RECOMP.RECOMP_PARALLEL(0);
END;
/

8 KILL SESSION

SELECT A.SID, B.OWNER, OBJECT_NAME, OBJECT_TYPE, A.*, B.*
 FROM V$LOCK A, ALL_OBJECTS B
WHERE A.ID1 = B.OBJECT_ID;

SELECT sid,serial# FROM v$session WHERE sid = '310';

alter system kill session '310,3926';
alter system kill session '310,3926' IMMEDIATE;

--KILL被锁的SESSION
SELECT SESSION_ID FROM V$LOCKED_OBJECT; --370
SELECT SID, SERIAL#, USERNAME, OSUSER FROM V$SESSION WHERE SID = 370;
ALTER SYSTEM KILL SESSION '370,647';
--修改进程数
alter system set processes = 300 scope = spfile;

9 跟踪存储过程

用命令行或sqlplus，sys用户
alter session set tracefile_identifier='zxz';
alter session set events '10046 trace name context forever,level 12'; --设置跟踪级别
然后执行存储过程
alter session set events '10046 trace name context off'; --关闭监控
show parameter  user_dump_dest ;
看下这个显示的目录，到目录下找刚才zxz开头的一个trc文件，然后发给我看下

tkprof sys=no d:\ectmsnew_ora_9700_zxz.trc d:\zxz.txt  
加上sys=no可以把sys部分的去掉，更好看一点

10 清理缓存

BEGIN
   EXECUTE IMMEDIATE 'alter system flush buffer_cache';
   EXECUTE IMMEDIATE 'alter system flush shared_pool';
END;
/
--给用户解锁
alter user es_dba account unlock;
--修改用户密码
alter user es_dba identified by oracle;
--修改优化器模式
Alter system set optimizer_mode='ALL_ROWS' scope=both;

11 查询已删除的数据

SELECT *
 FROM DBA_SOURCE AS OF TIMESTAMP TO_TIMESTAMP('2015-03-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE OWNER = 'EA_CUSTOM'
  AND NAME = 'P_RPT_CUST_FUND';
SELECT obj# FROM obj$ AS OF TIMESTAMP TO_TIMESTAMP('删除之前的时间', 'YYYY-MM-DD HH24:MI:SS') WHERE NAME = '存储过程名';

12 修改系统进程数

--查询数据库目前的进程数
select count(*) from v$process;
--查询进程数的上限
select value from v$parameter where name = 'processes';
--修改系统进程数
alter system set processes=500 scope=spfile;

13 查看ORACLE字符集

select * from sys.nls_database_parameters t where t.PARAMETER='NLS_CHARACTERSET';