ORACLE系統資料表待用資料字典，常用user視圖表及其常用用法

Oracle中的資料字典有靜態和動態之分。待用資料字典主要是在使用者訪 問資料字典時不會發生改變的，但動態資料字典是依賴資料庫啟動並執行效能的，反映資料庫啟動並執行一些內在資訊，所以在訪問這類資料字典時往往不是一成不變的。

待用資料字典主要是由表和視圖組成，應該注意的是，資料字典中的表是不能直接被訪問的，但是可以訪問資料字典中的視圖。待用資料字典中的視圖分為三類，它們分別由三個首碼夠成：user_*、all_*、dba_*。
這裡主要介紹user的常用視圖及其用法。 user的常用視圖有 user_objects 使用者物件資訊 user_source 資料庫使用者的所有資來源物件資訊 user_tables 使用者的表對象資訊 user_tab_columns 使用者的表列資訊 user_constraints 使用者的對象約束資訊 user_tab_privs 目前使用者的對象許可權資訊 user_ind_columns 使用者的索引對應的表列資訊 user_clusters 使用者的所有簇資訊 user_clu_columns 使用者的簇所包含的內容資訊 user_cluster_hash_expressions 散列簇的資訊 user_user

主要描述目前使用者的資訊，主要包括目前使用者名、帳戶id、帳戶狀態、資料表空間名、建立時間等。例如執行下列命令即可返回這些資訊。 user_objects （使用者物件資訊）

主要描述目前使用者擁有的所有對象的資訊，對象包括表、視圖、預存程序、觸發器、包、索引、序列等。該視圖比user_tables視圖更加全面。

欄位名	解釋
object_name	對象名稱
object_id	對象id
created	對象的建立時間
last_ddl_time	跟對象相關的（不一定是改變對象的結構，也可以是其他相關，如，如果此對象是表，則將該表的select許可權賦給其他使用者也會改變這個欄位的值）最最近ddl操作的執行時間
timestamp	對象自身結構發生改變的時間，比如刪除欄位、添加欄位等
object_type	物件類型
status	狀態
……

user_tables （使用者的表對象資訊）

主要描述目前使用者擁有的所有表的資訊，主要包括表名、資料表空間名、簇名等。通過此視圖可以清楚瞭解目前使用者可以操作的表有哪些。

欄位名	解釋
TABLE_NAME	表名
TABLESPACE_NAME
CLUSTER_NAME
NUM_ROWS	表中的資料行數
STATUS
PCT_FREE
PCT_USED
INI_TRANS
MAX_TRANS
INITIAL_EXTENT
……

user_tab_comments（使用者的表注釋）

欄位名	解釋
table_name	表名或視圖名
Table_type	類型（TABLE/VIEW）
comments	已經為該列輸入的注釋

user_col_comments (使用者的表列資訊)

欄位名	解釋
table_name	表名或視圖名
column_name	列名
comments	已經為該列輸入的注釋

user_sys_privs （目前使用者的系統許可權資訊） user_tab_privs （目前使用者的對象許可權資訊 ）

該視圖主要是儲存目前使用者下對所有表的許可權資訊。目前使用者對該表的許可權就可以清楚的知道，哪些操作可以執行，哪些操作不能執行。 user_col_privs 目前使用者的表列許可權資訊 user_role_privs 目前使用者的角色許可權資訊 user_indexes 使用者的索引資訊 user_ind_columns 使用者的索引對應的表列資訊 user_constraints 使用者的對象約束資訊 user_cons_columns 使用者的約束對應的表列資訊

user_constraints是資料表條件約束的視圖,描述的是約束類型(constraint_type)是什麼,屬於哪些表(table_name),如果約束的類型為R(外鍵)的話,那麼r_constraint_name欄位存放的就是被引用主表中的主鍵約束名。
user_cons_columns是資料表條件約束欄位的視圖,說明表中的和約束相關的列參與了哪些約束。這些約束有主鍵約束,外鍵約束,索引約束.
兩者可以通過(owner,constraint_name,table_name)關聯 常用用法

利用資料字典，輔助開發和實施，是極好的~~~
偷懶必學，尤其是做資料移轉，批量處理資料或者資料表大範圍整改時。 1、備份表注釋

select 'comment on table '||table_name||' is '''||comments||''';' from user_tab_comments where comments is not null;

2、備份所有的欄位注釋

select 'comment on column '||table_name||'.'||column_name||' is '''||comments||''';' from user_col_comments where comments is not null;

3、查詢主鍵/外鍵

select c.constraint_name index_name,       decode(t.constraint_type, 'P', 1, 0) type,       c.table_name,       c.column_name  from user_constraints t, user_cons_columns c where c.constraint_name = t.constraint_name   and c.column_name not like 'SYS_NC%$'   and t.constraint_type in ('P', 'U');

其中constraint_type的值為P時 表示Primary Key，即主鍵，R表示Referential AKA Foreign Key，即外鍵，U表示Unique Key，即唯一鍵。 4、欄位轉為大寫

select 'alter table ' || t.TABLE_NAME || ' rename column "' || column_name ||'" to "' || upper(column_name) || '";'  from user_tab_columns t;

5、查看某對象的狀態

select object_type,status from user_objects where object_name=upper('package1'); 

注意upper的使用，資料字典裡的所有對象均為大寫形式，而PL/SQL裡不是大小寫敏感的，所以在實際操作中一定要注意大小寫匹配。 6、CLOB轉BLOB

首先建一個clob欄位名+_1的欄位，用於替代

select 'alter table ' || table_name ||' add '||column_name||'_1 blob;' from user_tab_columns where data_type='CLOB';

然後通過一個轉換方法把Clob欄位的資料轉為BLOB並且存到建立欄位中

select 'update '||table_name||' set ' ||column_name || '_1=clob_to_blob('|| column_name ||');' from user_tab_columns where data_type='CLOB';

這時候可以把原來的clob欄位刪除了

select 'alter table ' || table_name ||' drop column '||column_name||';' from user_tab_columns where data_type='CLOB';

最後把建立欄位改為原來欄位一樣的名字，即把_1去掉

select 'alter table '||table_name||' rename column '||column_name||' to '||replace(column_name,'_1','')||';' from user_tab_columns where data_type='BLOB';

7、查看目前使用者的預設資料表空間

select username,default_tablespace from user_users;

8、查看名稱包含log字元的對象

SQL>select object_name,object_id from user_objectswhere instr(object_name,'LOG')>0; 

後續使用時再不斷補充……