Oracle 系统表常用SQL

标签：常用 name -- 用户 dba user SQL Oracle select

Oracle中的数据字典区分静态和动态。静态是在用户访问数据字典时不发生改变的，动态是依赖数据库运行的性能的，反映数据库运行的信息。
数据字典视图是由SYS（系统用户）所拥有的，默认只有SYS和拥有DBA系统权限的用户可以看到所有的视图。没有DBA权限的用户只能看到user_和all_视图。如果没有被授予相关的SELECT权限的话，是不能看到 dba_*视图的。

静态数据字典中的视图分为三类，分别由三个前缀够成：
- user_* 存储关于当前用户所拥有的对象的信息。
- all_*存储了当前用户能够访问的对象的信息，具有访问该对象的权限即可。
- dba_* 存储了数据库中所有对象的信息，必须具有管理员权限。
动态数据字典,提供了关于内存和磁盘的运行情况，只能对其进行只读访问。
- 动态性能视图都是以v$开头的视图.
- 从Oracle8开始，GV$视图开始被引入，其含义为Global V$。除了一些特例以外，每个V$视图都有一个对应的GV$视图存在。
- GV$视图的产生是为了满足RAC环境(RAC,Real Application Cluster,实时应用集群，以前称作OPS,Oracle Parallel Server) 的需要，在RAC环境中，查询GV$视图返回所有实例信息，而每个V$视图基于GV$视图，增加了INST_ID列判断后建立，只包含当前连接实例信息。

1.用户&角色&权限

--查询系统用户
select * from all_users;
select * from dba_users;
--当前用户的信息，主要包括当前用户名、帐户id、帐户状态、表空间名、创建时间等
select * from user_users;
--查看当前连接用户
select * from v$session;
--查看当前用户默认表空间
select default_tablespace from dba_users where username='MC';
--查看所有角色：
select * from dba_roles;
--全部用户被授予的角色
select * from dba_role_privs;
--查看当前用户被授予的角色
select * from user_role_privs;
--当前用户被激活的全部角色
select * from session_roles;


--查看当前用户的系统权限和表级权限
select * from user_sys_privs;
select * from user_tab_privs;

--当前用户所拥有的全部权限
select * from session_privs;
--当前用户的系统权限
select * from user_sys_privs;
--当前用户的表级权限
select * from user_tab_privs;
--查询某个用户所拥有的系统权限
select * from dba_sys_privs;
--查看角色(只能查看登陆用户拥有的角色)所包含的权限
select * from role_sys_privs;
--查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
--查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from V$PWFILE_USERS

--查看Oracle提供的系统权限
select name from sys.system_privilege_map
--查看一个用户的所有系统权限(包含角色的系统权限)
select privilege from dba_sys_privs where grantee='DATAUSER'  
union
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='DATAUSER' );

2.表空间

2.1 查询表空间

--查询所有表空间
select tablespace_name from dba_tablespaces;（DBA权限下）
select tablespace_name from user_tablespaces；
--查看表空间及表空间数据存放位置
SELECT t1.name,t2.name FROM v$tablespace t1,v$datafile t2 WHERE t1.ts# = t2.ts#;（DBA权限下）
--查询使用过的表空间
select distinct tablespace_name from dba_all_tables;
select distinct tablespace_name from user_all_tables;
--查询表空间中所有表的名称
select table_name from dba_all_tables where tablespace_name = tablespacename
--删除表空间，同时删除数据文件：
drop tablespace test_data including contents and datafiles;
--查看表空间的使用情况
SELECT B.FILE_NAME 物理文件名,
       B.TABLESPACE_NAME 表空间,
       B.BYTES / 1024 / 1024 大小M,
       (B.BYTES - SUM(NVL(A.BYTES, 0))) / 1024 / 1024 已使用M,
       SUBSTR((B.BYTES - SUM(NVL(A.BYTES, 0))) / (B.BYTES) * 100, 1, 5) 利用率
  --DBA_FREE_SPACE 空闲表空间 DBA_DATA_FILES 表空间对应的数据文件
  FROM DBA_FREE_SPACE A, DBA_DATA_FILES B
WHERE A.FILE_ID = B.FILE_ID
GROUP BY B.TABLESPACE_NAME, B.FILE_NAME, B.BYTES
ORDER BY B.TABLESPACE_NAME;
--查看表占用表空间的大小，查看各表数据的行数
SELECT T.TABLE_NAME, T.NUM_ROWS, S.BYTES, T.OWNER, T.TABLESPACE_NAME
  FROM DBA_TABLES T, USER_SEGMENTS S
WHERE T.TABLE_NAME = S.SEGMENT_NAME
ORDER BY 3 DESC;
--表空间满数据查询
select b.tablespace_name "表空间",b.bytes/1024/1024 "大小M",(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 "已使用M",substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) "利用率" from dba_free_space a,dba_data_files b
where a.file_id=b.file_id and b.tablespace_name='SYSTEM' group by b.tablespace_name,b.file_name,b.bytes order by b.tablespace_name;

2.2 创建表空间

--创建表空间
/*第1步：创建临时表空间  */
create temporary tablespace yuhang_temp
tempfile 'D:\oracledata\yuhang_temp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
/*第2步：创建数据表空间  */
create tablespace yuhang_data
logging
datafile 'D:\oracledata\yuhang_data.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
/*第3步：创建用户并指定表空间  */
create user yuhang identified by yuhang
default tablespace yuhang_data
temporary tablespace yuhang_temp;
/*第4步：给用户授予权限  */
grant connect,resource,dba to yuhang;

--异常解决
----无法通过8192在表空间中扩展
原因:数据库的表空间不够
解决：sys用户登录数据库
--查看表空间及表空间数据存放位置
SELECT t1.name,t2.name FROM v$tablespace t1,v$datafile t2 WHERE t1.ts# = t2.ts#;（DBA权限下）
--新建一个大小为4G的dbf文件给sg_demo使用
ALTER TABLESPACE sg_demo ADD DATAFILE 'E:\TABLESPACE\SG_DEMO1.DBF' SIZE 4096M;

3.数据库对象

user_objects : 记录了用户的所有对象，包含表、索引、过程、视图等信息，以及创建时间，状态是否有效等信息，是非DBA用户的大本营。想知道自己有哪些对象，往这里查。

select * from dba_objects;
--数据库是否存在某张表（表名和字段名一定要大写）
select count(*) from user_objects where  object_name = '表名';

4.表

select * from dba_tables;
select distinct table_name from user_tab_columns where column_name='SO_TYPE_ID';
--数据库是否存在某字段
SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = '表名' AND COLUMN_NAME = '字段名';

5.索引

--索引，包括主键索引    
select * from dba_indexes;
--索引列
select * from dba_ind_columns;
--联接使用
select i.index_name,i.uniqueness,c.column_name from user_indexes i,user_ind_columns c where i.index_name=c.index_name and i.table_name ='ACC_NBR';

6.同义词

select * from dba_synonyms where table_owner='SPGROUP';

7.函数和存储过程

user_source :包含了系统中对象的原码，如存储过程，FUNCTION、PROCEDURE、PACKAGE等信息

--查看所有的函数和存储过程（其中TYPE包括：PROCEDURE、FUNCTION）
select * from user_source

8.动态数据字典视图锁表查询和解锁方法

锁表查询和解锁方法

--锁表查询
SELECT
  'alter system kill session ''' || C.SID || ',' || C.SERIAL# || ',@' ||C.INST_ID || ''' immediate;' AS kill_session_scripts,
  B.OWNER, --所属用户											 
  B.OBJECT_NAME, --名称	                   
  A.XIDUSN,  
  A.XIDSLOT,  
  A.XIDSQN,
  A.SESSION_ID,--锁表用户的session
  A.ORACLE_USERNAME,--锁表用户的Oracle用户名
  A.OS_USER_NAME, --锁表用户的操作系统登陆用户名                  
  A.PROCESS,  
  A.LOCKED_MODE,
  C.MACHINE,--锁表用户的计算机名称										
  C.STATUS,  --锁表状态                  
  C.SERVER,
  C.SID,
  C.SERIAL#,
  C.PROGRAM, --锁表用户所用的数据库管理工具
	D.SQL_TEXT
FROM
  --表的DML锁，DDL锁用dba_ddl_locks视图
  GV$LOCKED_OBJECT A
  --数据库对象信息
  INNER JOIN DBA_OBJECTS B ON A.OBJECT_ID = B.OBJECT_ID
  --会话信息
  INNER JOIN SYS.GV_$SESSION C ON A.PROCESS = C.PROCESS AND A.SESSION_ID = C.SID AND A.INST_ID = C.INST_ID
  --当前查询过的sql语句访问过的资源及相关的信息
  INNER JOIN GV$SQLAREA D ON C.SQL_ID = D.SQL_ID
WHERE 1 = 1
--AND D.SQL_TEXT LIKE '%TEST%';
--AND C.STATUS='ACTIVE'

--杀掉锁表进程
执行上步查询中第一列的脚本

抓下数据库在执行SQL

SELECT B.SID          ORACLEID,
       B.USERNAME     登录ORACLE用户名,
       B.SID,
       B.SERIAL#,
       PADDR,
       C.SQL_TEXT     正在执行的SQL,
       C.SQL_FULLTEXT,
       B.MACHINE      计算机名
  FROM V$PROCESS A, V$SESSION B, V$SQLAREA C
WHERE A.ADDR = B.PADDR
   AND B.SQL_HASH_VALUE = C.HASH_VALUE
   AND B.USERNAME = 'GXNTJT'
ORDER BY c.SQL_TEXT;

标签：常用,name,--,用户,dba,user,SQL,Oracle,select
From： https://www.cnblogs.com/juedingsheng/p/17873993.html