1.普通表空间

  select name, 
         sum(size_mb), 
         sum(free_mb), 
         sum(max_mb) , 
         round(sum(free_mb)*100 /sum(max_mb),2) as pct
    from (select a.NAME,
                 b.TOTAL_SIZE*b.PAGE_SIZE /1024/1024 size_mb,
                 b.FREE_SIZE*b.PAGE_SIZE /1024/1024 free_mb,
                 case when b.MAX_SIZE = 0 then b.TOTAL_SIZE*b.PAGE_SIZE /1024/1024 else b.max_size end as max_mb
            from v$tablespace a,
                 V$DATAFILE b
           where a.ID=b.GROUP_ID) 
group by name;

2.查看每个数据文件

select a.NAME,b.path,
                 b.TOTAL_SIZE*b.PAGE_SIZE /1024/1024 size_mb,
                 b.FREE_SIZE*b.PAGE_SIZE /1024/1024 free_mb,
                 case when b.MAX_SIZE = 0 then b.TOTAL_SIZE*b.PAGE_SIZE /1024/1024 else b.max_size end as max_mb
            from v$tablespace a,
                 V$DATAFILE b
           where a.ID=b.GROUP_ID
           order by name,4 desc;

3.查看用户

select a.username,a.ACCOUNT_STATUS,a.DEFAULT_TABLESPACE,a.DEFAULT_INDEX_TABLESPACE,a.CREATED from dba_users a;


SELECT
        B.USERNAME       ,
        B.ACCOUNT_STATUS ,   /*用户状态*/
        A.FAILED_ATTEMPS ,   /*用户自创建之后或者是用户自上一次成功登录以来的失败登录次数，企业版中只要用户成功登录或解锁，该值就会清零*/
        A.FAILED_NUM     ,   /*用户登录失败次数限制*/
        A.LASTEST_LOCKED     /*用户最后一次的锁定时间，与FAILED_ATTEMPS相同，企业版中只要用户成功登录或解锁，该值就会清空*/
FROM
        SYSUSERS A,
        DBA_USERS B
WHERE
        A.ID      =B.USER_ID
    AND B.USERNAME= 'xxx' ;

CREATE PROFILE new_profile limit 
SESSION_PER_USER  1000,
CONNECT_TIME 1440,
CONNECT_IDLE_TIME 1440,
FAILED_LOGIN_ATTEMPTS 10,
PASSWORD_LIFE_TIME 90,
PASSWORD_REUSE_TIME 365,
PASSWORD_REUSE_MAX 5,
PASSWORD_LOCK_TIME 10,
PASSWORD_GRACE_TIME 10;

select a.pid profileid, b.name profile_name, a.uid userid, c.name user_name
from SYSUSERPROFILES a, SYSOBJECTS b, SYSOBJECTS c
where a.PID = b.id and a.UID = c.ID;

4.查询数据库是否兼容其他数据库模式

 select * from v$dm_ini where "V$DM_INI".PARA_NAME='COMPATIBLE_MODE';
 sp_set_para_value(2,'COMPATIBLE_MODE',4);
 修改以后重启数据库实例服务生效；

5.查看表的占用空间

SELECT TABLE_USED_PAGES('模式名', '表名') * PARA_VALUE / 1024 / 1024 "USER_USED_SPACE(MB)"
  FROM V$DM_INI
 WHERE PARA_NAME LIKE 'GLOBAL_PAGE_SIZE';

6.如何判断哪些参数是动态的那些参数是动态的

7.权限

select * from dba_sys_privs t where t.grantee in ('DSG') order by 1 desc;
select * from dba_role_privs t where t.grantee in ('DSG') order by 1 desc;
select * from dba_tab_privs t where t.grantee in ('DSG') order by 1 desc;


角色是权限的集合，角色使权限管理更加方便。
DBA：具有几乎所有权限（除审计和强制访问控制之外），默认赋给 SYSDBA 用户。
PUBLIC：具有对当前模式下对象的 DML 数据操作权限。
RESOURCE：具有在当前模式下对象定义权限（创建表、索引、视图等）；
SOI：具有查询 sys 开头系统表的权限；
VTI：具有查询 v$开头的动态视图权限（动态视图记录在 v$dynamic_tables，如果没有此权限，DM 管理工具上会报没有查询 v$视图权限）
DB_AUDIT_开头的角色是审计相关角色，具有审计相关权限，默认赋给 SYSAUDITOR。
DB_POLICY_开头的角色是安全相关角色，具有强制访问控制等权限，默认赋给 SYSSSO。

8更新模式下统计信息：

DBMS_STATS.GATHER_SCHEMA_STATS( '模式名', 100, FALSE, 'FOR ALL COLUMNS SIZE AUTO');

更新单表统计信息：

SP_TAB_STAT_INIT('模式名','表名');
 或者 
DBMS_STATS.GATHER_TABLE_STATS('模式名','表名',NULL,100,TRUE,'FOR ALL COLUMNS SIZE AUTO')

9.更新单列统计信息：

STAT 100 ON 表名(列名);
sp_col_stat_init(‘模式名’,‘表名’,‘列名’);
sp_tab_col_stat_init(‘模式名’,‘表名’);

10.查询慢sql:

select datediff(ss,sysdate,last_send_time) a,
       SF_GET_SESSION_SQL(SESS_ID) ,--获取完整sql
        sess_id ,
        sess_seq ,
        sql_text ,
        state ,
        seq_no ,
        user_name ,
        trx_id ,
        create_time,
        clnt_ip
        
from
        v$sessions 
where state='ACTIVE' and sess_id != sessid 
order by a;

11.查询阻塞锁

select * from v$lock where blocked=1;

12.查询锁等待

select * from v$trxwait;

13.按照耗时排序查询慢sql

select timestampdiff(ss,LAST_RECV_TIME,sysdate),sf_get_session_sql(sess_id),*
from SYS.“V$SESSIONS”
where sess_id<>sessid
order by 6 asc,1 desc;

14.查询死锁涉及的事务信息

select
dh.trx_id ,
sh.sess_id,
wm_concat(top_sql_text)
from
V$DEADLOCK_HISTORY dh, V$SQL_HISTORY sh
where
dh.trx_id =sh.trx_id
and dh.sess_id=sh.sess_id
group by
dh.trx_id,
sh.sess_id

15.初始化代理环境：

SP_INIT_JOB_SYS(1);

16.授权激活：

sp_load_lic_info();

17.查询数据库使用的字符集

SELECT sf_get_unicode_flag()

结果为1，表示字符集为utf-8;
结果为0，表示字符集为gbk;

18.查询数据库大小写是否敏感

select sf_get_case_sensitive_flag()

结果为1，表示大小写敏感；
结果为0，表示大小写不敏感；

19.查询varchar类型是否以字符为单位

select sf_get_length_in_char()

结果为1，表示以字符为单位；
结果为0，表示以字节为单位；

20.清理归档：

SF_ARCHIVELOG_DELETE_BEFORE_TIME(SYSDATE - 15);

21.查看用户会话空闲时间，登录失败次数限制

select
a.ID as 用户ID,
b.name as 用户名,
CONN_IDLE_TIME as 用户会话的最大空闲时间,
FAILED_NUM as 用户登录失败次数限制,
SESS_PER_USER,
LOCK_TIME as 用户口令锁定时间
from
SYSUSERS a,sysobjects b
where a.id=b.id

22.查询用户的会话持续期以及会话使用cpu时间上限

select
a.ID as 用户ID,
b.name as 用户名,
a.INFO1 as 会话持续期,
a.CPU_PER_SESSION as 会话使用cpu时间上限
from
SYSRESOURCES a,
sysobjects b
where
a.id=b.id

23.查询数据库有哪些定时任务及信息

select * from SYSJOB.SYSJOBS

24,查询数据库最近的10次定时任务执行情况

select * from SYSJOB.SYSJOBHISTORIES2
order by start_time
limit 10;

25.基本参数

SELECT '实例名称' AS 数据库参数名称,INSTANCE_NAME  数据库参数值 FROM V$INSTANCE UNION ALL
SELECT '数据库版本',  BANNER||'.'||ID_CODE FROM V$VERSION WHERE ROWNUM=1 UNION ALL
SELECT '字符集',CASE SF_GET_UNICODE_FLAG() WHEN '0' THEN 'GBK18030' WHEN '1' THEN 'UTF-8' WHEN '2' THEN 'EUC-KR' END UNION ALL
SELECT '页大小',CAST(PAGE()/1024 AS VARCHAR) UNION ALL
SELECT '簇大小',CAST(SF_GET_EXTENT_SIZE() AS VARCHAR) UNION ALL
SELECT '字符串比较大小写敏感',CAST(SF_GET_CASE_SENSITIVE_FLAG() AS VARCHAR) UNION ALL
SELECT 'VARCHAR类型是否以字符为单位',VALUE FROM V$PARAMETER WHERE NAME='LENGTH_IN_CHAR' UNION ALL
SELECT '空格填充模式',VALUE FROM V$PARAMETER WHERE NAME='BLANK_PAD_MODE' UNION ALL
SELECT '数据库模式',MODE$ FROM V$INSTANCE  UNION ALL
SELECT '数据库端口',VALUE FROM V$PARAMETER WHERE NAME='PORT_NUM' UNION ALL
SELECT '归档模式',ARCH_MODE FROM V$DATABASE UNION ALL
SELECT '唯一魔数',CAST(PERMANENT_MAGIC AS VARCHAR) UNION ALL
SELECT 'LSN',CAST(CUR_LSN AS VARCHAR) FROM V$RLOG ;

26.查询历史耗时SQL

---最近1000条执行时间较长的SQL（默认记录SQL执行时间大于1s的SQL语句，记录总条数和记录执行时间阈值可以手动调整）
select * from v$long_exec_sqls order by 4 desc;

---显示服务器启动以来执行时间最长的20条SQL语句（默认记录SQL执行时间大于1s的SQL语句，记录总条数和记录执行时间阈值可以手动调整）
select * from V$SYSTEM_LONG_EXEC_SQLS order by 5 desc,4 desc;

---查询历史执行SQL语句
select * from v$sql_history order by time_used desc;

27.主外键对应关系

select 
a.owner,
a.table_name PK_TABLE,
a.constraint_name PK_CONSTRAINT,
b.owner,
b.table_name FK_TABLE,
b.CONSTRAINT_NAME FK_CONSTRAINT
 from dba_constraints a,
 dba_constraints b 
 where a.CONSTRAINT_TYPE='P' 
 and b.R_CONSTRAINT_NAME=a.constraint_name 
 and a.owner=b.owner;

28.外键列创建索引

--查找数据库中外键列未创建索引的表并生成创建索引语句
with cons as
 (select owner, table_name, constraint_name
    from dba_constraints
   where constraint_type = 'R'),
idxs as
 (select a.table_owner, a.table_name, a.column_name
    from dba_ind_columns a, dba_indexes b
   where a.table_owner = b.owner
     and a.index_name = b.index_name
     and b.index_type <> 'VIRTUAL'
  ),
fk_cols as
 (select owner,
         table_name,
         constraint_name,
         listagg('"' || column_name || '"', ',') WITHIN GROUP(ORDER BY TABLE_NAME) AS FK_COLUMNS
    from dba_cons_columns
   where (owner, table_name, constraint_name) in (select * from cons)
     and (owner, table_name, column_name) not in
         (select table_owner, table_name, column_name
            from idxs)
   group by owner, table_name, constraint_name)
select *,
       'CREATE INDEX IDX_' || constraint_name || ' ON "' || owner || '"."' ||
       table_name || '"(' || FK_COLUMNS || ');' as CREATE_FK_INDEX_SQL
  from fk_cols
 where owner = 'AAAA' /*指定模式名*/
 --and table_name='TD_GWCL_RETREAT_ORG_REGISTER' /*指定表名*/
 ;

 --或者
SELECT
        'alter table "'
        ||t1.table_NAME
        ||'" modify constraint "'
        ||t1.CONSTRAINT_NAME
        ||'" to foreign key("'
        ||t3.COLUMN_NAME
        ||'") references "'
        ||t2.table_name
        ||'"("'
        ||t4.column_name
        ||'") with index;'
        /*t1.owner             AS FK_OWNER,
        t1.table_name        AS FK_TABLE,
        t1.constraint_name   AS FK      ,
        t3.column_name       AS FK_COL  ,
        T2.OWNER             AS PK_OWNER,
        t2.table_name        AS PK_TABLE,
        t1.r_constraint_name AS PK      ,
        t4.column_name  AS PK_COL,
        T1.DELETE_RULE*/
from
        dba_constraints t1 ,
        dba_constraints t2 ,
        DBA_CONS_COLUMNS t3,
        DBA_CONS_COLUMNS T4
where
        t1.constraint_type='R'
    and t2.constraint_type='P'
    and t2.constraint_name=t1.r_constraint_name
    and t3.constraint_name=t1.constraint_name
    AND T2.constraint_name=T4.constraint_name
    and t2.table_name     ='表名'

29.查看内存中的执行计划，输出到文本

---查询语句执行计划缓存地址
select cache_item from v$cachepln where sqlstr like 'select * from t1 where id%';
---打印内存中对应的执行计划
alter session set events 'immediate trace name plndump level 131111152555,dump_file ''/home/dmdba/sql111.log''';

30.清理内存中执行计划缓存

---清理指定的执行计划
CALL SP_CLEAR_PLAN_CACHE(473546872);
---清理内存中所有执行计划缓存
CALL SP_CLEAR_PLAN_CACHE();

31.查询会话阻塞

---DM8 DSC集群
select * from V$DSC_TRXWAIT;
select s1.instance_name,s1.user_name,s1.curr_sch,s1.sess_id "被阻塞sess_id",s1.sql_text,
s2.instance_name,s2.user_name,s2.curr_sch,s2.sess_id "产生阻塞sess_id",s2.sql_text,w1.wait_time 
from gv$sessions s1,gv$sessions s2,v$dsc_trxwait w1 
where 
s1.trx_id=w1.trx_id 
and s2.trx_id=w1.WAIT_TRX_ID 
;

---DM8单机锁阻塞查询：
select *from v$trxwait;
select s1.user_name,s1.curr_sch,s1.sess_id "被阻塞sess_id",s1.sql_text,
s2.user_name,s2.curr_sch,s2.sess_id "产生阻塞sess_id",s2.sql_text,w1.wait_time 
from v$sessions s1,v$sessions s2,v$trxwait w1 
where 
s1.trx_id=w1.id
and s2.trx_id=w1.WAIT_FOR_ID ;
WITH LOCK_TAB as(SELECT L1.TRX_ID,L1.TID,L1.LMODE, O1.NAME,L1.BLOCKED from V$LOCK L1,SYSOBJECTS O1 where
O1.ID=L1.TABLE_ID AND L1.BLOCKED<>0)
SELECT
L2.NAME WT_TABLE ,
L2.TRX_ID WT_TRXID ,
L2.TID BLK_TRXID ,
S1.SESS_ID WT_SESS ,
S2.SESS_ID BLK_SESS ,
S1.USER_NAME WT_USER_NAME ,
S2.USER_NAME BLK_USER_NAME,
L2.BLOCKED ,
L2.LMODE,
S1.SQL_TEXT ,
S1.CLNT_IP ,
DATEDIFF(SS, S1.LAST_SEND_TIME, SYSDATE) WAIT_TIME
FROM
V$SESSIONS S1,
V$SESSIONS S2,
LOCK_TAB L2
WHERE
L2.TRX_ID=S1.TRX_ID
AND L2.TID =S2.TRX_ID ;

---DM7锁阻塞查询：
WITH LOCK_INFO as 
(SELECT L1.TRX_ID,L1.ROW_IDX,L1.LMODE,L2.TABLE_ID,L1.BLOCKED
FROM V$LOCK L1,V$LOCK L2 
WHERE L1.TRX_ID=L2.TRX_ID AND L1.BLOCKED<>0 AND L2.TABLE_ID<>0)
SELECT
O.NAME WT_TABLE ,
L.TRX_ID WT_TRXID ,
L.ROW_IDX BLK_TRXID ,
S1.SESS_ID WT_SESS ,
S1.SESS_ID BLK_SESS ,
S1.USER_NAME WT_USER_NAME ,
S1.USER_NAME BLK_USER_NAME,
L.BLOCKED ,
L.LMODE,
S1.SQL_TEXT ,
S1.CLNT_IP ,
DATEDIFF(SS, S1.LAST_SEND_TIME, SYSDATE) WAIT_TIME
FROM
V$SESSIONS S1,
V$SESSIONS S2,
LOCK_INFO L,
SYSOBJECTS O
WHERE
L.TRX_ID=S1.TRX_ID
AND L.ROW_IDX =S2.TRX_ID
AND L.TABLE_ID=O.ID ;

32.查询用户拥有的模式:

SELECT B.USERNAME,A.NAME SCHEMA_NAME 
FROM SYSOBJECTS A,DBA_USERS B 
WHERE A.PID=B.USER_ID 
AND A.TYPE$='SCH'
ORDER BY B.USERNAME;
---或者
SELECT B.USERNAME,listagg(A.NAME,',') within group (order by B.USERNAME) as SCHEMAS 
FROM SYSOBJECTS A,DBA_USERS B 
WHERE A.PID=B.USER_ID 
AND A.TYPE$='SCH'
GROUP BY B.USERNAME;

https://eco.dameng.com