004-常用

发表于 | 更新于 | 分类于 | 阅读次数：
本文字数： 3.7k | 阅读时长 ≈ 3 分钟

赋权

1
2
3
4
GRANT ALL ON TABLE_NAME TO APPS;
GRANT SELECT ON TABLE_NAME TO APPS;
GRANT EXECUTE ON PKG_NAME TO APPS;
GRANT SELECT ON TABLE_NAME TO APPS WITH GRANT OPTION; --查询权限和赋权的权限都授予APPS用户

ACL

ACL设置

1
2
3
4
5
6
7
BEGIN
  Dbms_Network_Acl_Admin.Assign_Acl(Acl        => '/sys/acls/WebService.xml'
                                   ,Host       => 'erptest001.gz.cvte.cn'
                                   ,Lower_Port => 9081 --允许访问的起始端口号
                                   ,Upper_Port => NULL --允许访问的截止端口号
                                    );
END;

ACL查询

1
2
3
4
5
SELECT Host
      ,Lower_Port
      ,Upper_Port
      ,Acl
  FROM Sys.Dba_Network_Acls;

ACL测试（能查询到数据表示调用成功）

1
SELECT Utl_Http.Request('http://pcmcsb.gz.cvte.cn/csb/config/choices/code') FROM Dual;

查询被锁住对象

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT 'ALTER system kill session ' || '''' || Vs.Sid || ',' || Vs.Serial# || ''';' Execode
      ,Lo.Session_Id   Sid
      ,Vs.Serial#
      ,Lo.Locked_Mode  锁模式
      ,Ao.Object_Name  被锁对象名
      ,Vs.Username     登录用户
      ,Vs.Machine      登录机器名
      ,Lo.Os_User_Name 登录机器用户名
      ,Vs.Terminal     终端用户名
      ,Vs.Logon_Time   登录时间
  FROM V$locked_Object Lo
      ,All_Objects     Ao
      ,V$session       Vs
 WHERE Lo.Object_Id = Ao.Object_Id
   AND Lo.Session_Id = Vs.Sid
 ORDER BY Vs.Sid
         ,Vs.Serial#;

查询DDL锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT s.Sid      Sid
      ,s.Serial#
      ,s.Inst_Id
      ,s.Username Username
      ,s.Machine  Machine
      ,s.Program
      ,l.*
  FROM Dba_Ddl_Locks l
      ,Gv$session    s
WHERE l.Session_Id = s.Sid
   AND l.Owner = 'APPS'
   AND NAME = 'XXGTXP040'
ORDER BY s.Logon_Time;
--
ALTER system kill session '1535,46091';

清除缓存

1
2
3
4
BEGIN
  EXECUTE IMMEDIATE 'ALTER SYSTEM FLUSH SHARED_POOL';
  EXECUTE IMMEDIATE 'ALTER SYSTEM FLUSH BUFFER_CACHE';
END;

查询SQL执行消耗

1
2
3
4
5
6
7
SELECT Rank() Over(ORDER BY Elapsed_Time DESC) Rank_Num
      ,Vs.Sql_Id
      ,Vs.Sql_Text
      ,Vs.Elapsed_Time 总耗时
      ,Vs.Cpu_Time     Cpu耗时
      ,Vs.Disk_Reads   磁盘读取
  FROM V$sql Vs;

查询执行中的SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT 'ALTER system kill session ' || '''' || b.Sid || ',' || b.Serial# || ''';' Execode
      ,b.Sid
      ,b.Serial#
      ,b.Username   登录用户
      ,Spid         操作系统id
      ,c.Sql_Text   正在执行sql
      ,b.Schemaname SCHEMA
      ,b.Username   登录用户
      ,b.Machine    登录机器名
      ,b.Osuser     登录机器用户名
      ,b.Program    发起请求程序
      ,d.Cpu_Time   花费cpu的时间
      ,b.Status     状态
  FROM V$process a
      ,V$session b
      ,V$sqlarea c
      ,V$sql     d
 WHERE a.Addr = b.Paddr
   AND b.Sql_Hash_Value = c.Hash_Value
   AND b.Sql_Address = d.Address(+)
   AND b.Sql_Hash_Value = d.Hash_Value(+);

查询包中参数

1
2
3
4
5
6
7
8
9
10
11
SELECT u.Package_Name  包名
      ,u.Object_Name   方法
      ,u.Argument_Name 参数
      ,u.In_Out        参数模式
      ,u.Data_Type     参数类型
      ,u.Position      参数位置
  FROM Sys.User_Arguments u
 ORDER BY u.Package_Name
         ,u.Object_Name
         ,u.Overload
         ,u.Sequence;

查询表占用空间

1
2
3
4
5
SELECT t.Bytes
      ,t.*
  FROM User_Segments t
 WHERE t.Bytes IS NOT NULL
 ORDER BY t.Bytes DESC;

查询包中是否有某语句

1
SELECT * FROM Sys.All_Source t WHERE Upper(t.Text) LIKE '%%';

查询对象依赖

1
SELECT * FROM Sys.Dba_Dependencies t WHERE t.Referenced_Name = 'XXCST_PAC_INV_RPT';

查询数据库所有对象

1
2
3
4
SELECT t.*
  FROM Sys.Dba_Objects t
 WHERE 1 = 1
   AND t.Object_Name = 'DUAL';

查询表中列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT Sut.Table_Name
      ,Sut.Tablespace_Name
      ,Sutc.Column_Name
      ,Ucc.Comments
      ,Sutc.Data_Type
      ,Sutc.Data_Length
      ,Sutc.Nullable
  FROM Sys.User_Tables       Sut
      ,Sys.User_Tab_Cols     Sutc
      ,Sys.User_Col_Comments Ucc
 WHERE Sut.Table_Name = Sutc.Table_Name
   AND Ucc.Table_Name = Sutc.Table_Name
   AND Ucc.Column_Name = Sutc.Column_Name
 ORDER BY Sutc.Column_Id;

查询表和表描述

1
2
3
4
5
6
7
SELECT t.Table_Name      表名
      ,f.Comments        表描述
      ,t.Tablespace_Name 表空间名
      ,t.Status          状态
  FROM User_Tables t
 INNER JOIN User_Tab_Comments f
    ON t.Table_Name = f.Table_Name;