Oracle 数据字典

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
    df.tablespace_name AS "Tablespace",
    totalusedspace AS "Used MB",
    (df.totalspace - tu.totalusedspace) AS "Free MB",
    df.totalspace AS "Total MB",
    ROUND(100 * (totalusedspace / totalspace)) AS "Used Percentage"
FROM
    (SELECT
         tablespace_name,
         ROUND(SUM(bytes) / 1048576) TotalSpace
     FROM
         dba_data_files
     GROUP BY
         tablespace_name) df,
    (SELECT
         ROUND(SUM(bytes)/(1024*1024)) totalusedspace,
         tablespace_name
     FROM
         dba_segments
     GROUP BY
         tablespace_name) tu
WHERE
    df.tablespace_name = tu.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;
--查询表空间数据文件的自动扩展设置
SELECT
	df.file_name,
	df.AUTOEXTENSIBLE,
	TRUNC(df.MAXBYTES / 1024 / 1024 / 1024)||'G' AS MAX_GB,
	TRUNC(df.INCREMENT_BY * ts.BLOCK_SIZE / 1024 / 1024) || 'M' AS INCREMENT_MB
FROM
	dba_data_files df,
	dba_tablespaces ts
WHERE
	df.tablespace_name = ts.tablespace_name
	AND df.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

posted @ 2023-12-03 22:54 丁生· 阅读(34) 评论(0) 编辑收藏举报

刷新页面返回顶部

备忘录