系统的数据字典
<h1>常见信息</h1>
<p><a href="#view">常见视图</a></p>
<ul>
<li>
<p>查看视图(user_views)
<code>SELECT view_name,text_length,text FROM user_views ;</code></p>
</li>
<li>
<p>查看序列(user_sequences)
<code>SELECT * FROM user_sequences WHERE sequence_name='MYSEQ';</code></p>
</li>
<li>
<p>查看所有表名(tab)
<code>SELECT * FROM tab ;</code></p>
</li>
<li>
<p>查看同义词(user_synonyms)
<code>select * from user_synonyms;</code></p>
</li>
<li>
<p>查看索引</p>
<pre><code>SELECT index_name,index_type,table_owner,table_name,uniqueness,status FROM user_indexes WHERE index_name IN('EMP_SAL_IND','PK_DEPT','PK_EMP','EMP_HIREDATE_IND_DESC','EMP_ENAME_IND','EMP_DEPTNO_IND') ;</code></pre>
</li>
<li>
<p>查看索引，能看到索引对应的列</p>
<pre><code>SELECT * FROM user_ind_columns WHERE index_name IN('EMP_SAL_IND','PK_DEPT','PK_EMP','EMP_HIREDATE_IND_DESC','EMP_ENAME_IND','EMP_DEPTNO_IND') ;</code></pre>
</li>
<li>
<p>查看oracle的垃圾桶</p>
<pre><code>SELECT * FROM tab; --TNAME=BIN$sEgXe3cogxfgU0IgEqy4xA==$0 的是垃圾
select object_name,original_name,operation,type from recyclebin;
purge recyclebin ; --清空整个垃圾桶，可以指定删除</code></pre>
</li>
<li>
<p>表及字段的注释查看</p>
<pre><code>select * from user_tab_comments where table_name='COUNTRIES';--查看某张表的注释
comment on table member is '表注释的内容' ; --设置表的注释
select * from user_col_comments where table_name='COUNTRIES'; --查看表中各列的注释
comment on column member.mid is '表.列注释的内容';--设置某表的某列的注释</code></pre>
</li>
<li>
<p>查看用户信息，lock_date是锁定时间，创建非锁定用户该字段为null
<code>SELECT * FROM dba_users WHERE username='MLDNUSER';</code></p>
</li>
<li>
<p>查看dba_tablespaces表空间
<code>SELECT * FROM dba_tablespaces ;</code></p>
</li>
<li>
<p>查看profiles文件数据字典dba_profiles
<code>select * from dba_profiles where profile='MLDN_PROFILE'</code></p>
</li>
<li>
<p>查看dba_users 数据字典，看用户的定义
<code>select * from dba_users where username in ('MLDNJAVA','MLDNUSER')</code></p>
</li>
<li>
<p>通过 dba_ts_quotas 数据字典查看mldnuser用户新的表空间配额
<code>select * from dba_ts_quotas where username='MLDNUSER';</code></p>
</li>
<li>
<p>查看 dba_sys_privs 数据字典，查看用户的权限
<code>select * from dba_sys_privs where grantee in('MLDNUSER','MLDNJAVA') order by grantee desc;</code></p>
</li>
<li>
<p>查看当前登陆用户所有对象权限，只能看整个对象，不能看指定的列。
<code>select * from user_tab_privs_recd ;</code></p>
</li>
<li>
<p>查看当前登陆用户对象的列的权限user_col_privs_recd
<code>select * from user_col_privs_recd;</code></p>
</li>
<li>
<p>查看数据库对象分配了哪些权限出去，暂时看不懂</p>
<pre><code>select * from user_tab_privs_made;
select * from user_col_privs_made;</code></pre>
</li>
<li>
<p>查看角色是不有密码 dba_roles
<code>select * from dba_roles where role in ('MLDN_ROLE_A','MLDN_ROLE_B');</code></p>
</li>
<li>
<p>查看角色权限 role_sys_privs
<code>select * from role_sys_privs where role in ('MLDN_ROLE_A','MLDN_ROLE_B') order by role ;</code></p>
</li>
<li>
<p>查看当前用户的权限，授权后，要重新登陆session_privs
<code>select * from session_privs ;</code></p>
</li>
<li>
<p>查看用户拥有的权限
<code>SELECT * FROM dba_sys_privs ;</code></p>
</li>
<li>
<p>查看用户拥有的角色
<code>select * FROM dba_role_privs ;</code></p>
</li>
<li>
<p>查看角色拥有的权限
<code>SELECT * FROM dba_sys_privs ;</code></p>
</li>
<li>
<p>查看有sysdba 角色的用户
<code>select * from v$pwfile_users;</code></p>
</li>
<li>视图查看变量
<code>SELECT name, value FROM v$parameter;</code>
<code>show parameter [undo\sga]</code></li>
<li>
<p>查看日期格式
<code>SELECT name, value FROM v$parameter where name='nls_date_format';</code></p>
</li>
<li>
<p>动态性能视图：用法示例</p>
<pre><code>select sql_text,executions from v$sql where cpu_time > 200000 ;
select * from v$session where machine = 'EDXX9P1' and logon_time > sysdate - 1 ;
select sid,ctime from v$lock where block > 0 ;</code></pre>
</li>
<li>
<p>查看所有视图名称</p>
<pre><code>select * from v$fixed_table ;
SELECT * FROM dictionary;</code></pre>
</li>
<li>062PPT里找到的一些没什么用的东西：说是视图使用示例
<pre><code>select table_name,tablespace_name from user_tables ;
select sequence_name,min_value,max_value,increment_by from all_sequences where sequence_owner in ('MDSYS','XDB');
select username,account_status from dba_users where account_status = 'OPEN';
describe dba_indexes;</code></pre></li>
<li>查看后台进程：
<code>select pid,username,program from v$process where background = '1' order by program ;</code></li>
</ul>
<h2><div id=view>常见视图</div></h2>
<p>v$parameter
dba_tablespaces
dba_data_files
v$undostat
dba_rollback_segs
v$rollname
v$rollstat
v$session
v$transaction
v$tempundostat</p>