Oracle 常用数据字典

--Oracle 数据字典

--Oracle 数据字典很多,通常我记不住所有的名字,我们可以用以下SQL去查数据字典的名字
如：select distinct object_name from dba_objects where object_name like 'user_%';
--本用户字典
SQL> select distinct object_name from dba_objects where object_name like 'USER_%';
--系统用户字典
SQL> select distinct object_name from dba_objects where object_name like 'DBA_%';
--动态字典
SQL> select distinct object_name from dba_objects where object_name like 'V$_%';
--表
SQL> select * from cat;
SQL> select * from tabs;
SQL> select * from user_tables;
--视图
SQL> select * from user_views;
SQL> select text from user_views where view_name=upper('&view_name');
--索引
SQL> select * from user_indexes;
SQL> select index_name,table_owner,table_name,tablespace_name,status from user_indexes order by table_name;
--快照 select * from user_snapshots;
SQL> select owner,name,master,table_name,last_refresh,next from user_snapshots order by owner,next;
--触发器
SQL> select * from user_triggers;
SQL> select trigger_name,trigger_type,table_owner,table_name,status from user_triggers;
--同义词

SQL> select * from syn;
--序列

SQL> select * from seq;
SQL> select * from user_sequences;

--数据库链路

SQL> select * from user_db_links;
--约束限制

SQL> select * from user_constraints;
SQL> select TABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION,STATUS from user_constraints;

--本用户读取其它用户对象的权限

SQL> select * from user_tab_privs;

--本用户所拥有的系统权限

SQL> select * from user_sys_privs;

--Oracle系统中的所有用户

SQL> select * from all_users;

SQL> select username from all_users;

SQL> select * from all_users order by user_id;

--表空间剩余自由空间情况？

SQL> select tablespace_name,sum(bytes),max(bytes),count(*) from dba_free_space group by tablespace_name；

select tablespace_name,
block_size,
initial_extent,
status,
contents,
logging,
extent_management,
segment_space_management
  from dba_tablespaces;

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
2),
'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1

--数据字典

SQL> select table_name from dict order by table_name;

--锁及资源信息

SQL> select * from v$lock;

--不包括DDL锁数据库字符集？
SQL> select name,value$ from props$ where name='NLS_CHARACTERSET';

--inin.ora参数

SQL> select name,display_value from v$parameter order by name;

--SQL共享池

SQL> select * from v$sqlarea;
SQL> select sql_text from v$sqlarea;

--数据库

SQL> select * from v$database;

--控制文件

SQL> select * from V$controlfile;

--重做日志文件信息

SQL> select * from V$logfile;

--来自控制文件中的日志文件信息

SQL> select * from V$log;

--来自控制文件中的数据文件信息

SQL> select * from V$datafile;

--NLS参数当前值

SQL> select * from V$nls_parameters;

--ORACLE版本信息
SQL> select * from v$version;

--描述oracle后台进程

SQL> select * from v$bgprocess;

--查看oracle版本信息

SQL> select instance_name, host_name from v$instance;

SQL> select * from product_component_version;

----查看oracle 数据库名(DB_NAME)

SQL> select name from v$database;

SQL> show parameter db;

SQL> select value from v$parameter where name='db_name';

----查看oracle 数据库实例名(Instance_name、ORACLE_SID)

SQL> select instance_name from v$instance;

SQL>show parameter instance;

SQL> select value from v$parameter where name='db_name';

方法三：在参数文件中查询

如：$ORACLE_BASE/admin/DB_NAME/pfile/init$ORACLE_SID.ora

----查看oracle 数据库域名、全局数据库名、数据库服务名