當前位置:成語大全網 - 漢語詞典 - (轉)如何有效利用數據字典進行查詢?

(轉)如何有效利用數據字典進行查詢?

數據字典名稱是大寫的英文字符。數據字典包含用戶信息、用戶權限信息、所有數據對象信息、表約束、統計分析數據庫視圖等。我們不能手動修改數據字典中的信息。1.用戶查看默認表空間SQL Select當前用戶的用戶名,default _ tablespace from user _ users;查看當前用戶的角色SQLselect * from user _ role _ privs查看當前用戶的系統權限和表級權限SQLselect * from user _ sys _ privsSQLselect * from user _ tab _ privs二、表查看用戶SQLselect * from user_tables下的所有表;檢查SQL select object _ name,object _ id from user _ objects where instr(object _ name,' log') 0,其名稱包含日誌字符;檢查從user _ objects創建的表SQL select object _ name的創建時間,其中object _ name = upper(' &;table _ name’);檢查表的大小SQL select sum(bytes)/(1024 * 1024)as " size(m)" from user _ segments其中segment _ name = upper(' &;table _ name’);看表SQL select table _ name,cache from user _ tables where instr(cache,' y') 0在ORACLE的內存區域;三、索引視圖索引號和類別SQL select index _ name,index _ type,table _ name from user _ indexes order by table _ name;檢查索引字段SQL select * from user _ ind _ columns,其中index _ name = upper(' &;index _ name’);檢查索引大小SQL select sum(bytes)/(1024 * 1024)作為“size(m)”from user _ segments其中segment _ name = upper(' & amp;index _ name’);四、序列號看序列號,last_number是當前值SQLselect * from user _ sequences五、視圖視圖名稱SQL Select View _ Name From User _ Views;查看創建視圖的user _ views中的select語句sqlsetview _ name,text _ length。SQLset long 2000描述:可以根據視圖的text_length值設置set long的大小。SQL select text from user _ views where view _ name = upper(' &;view _ name ');六、同義詞查看同義詞名稱SQLselect * from user _ synonyms七。約束條件查看表的約束條件SQL Select Constraint _ Name,Constraint _ Type,Search _ Condition,R _ Constraint _ Name from User _ Constraints其中Table _ Name = Upper(' &;table _ name’);SQLselect c.constraint_name,c.constraint_type,cc . column _ name from user _ constraints c,user _ cons _ columns cc where c . owner = upper(& amp;table_owner ')和c . table _ name = upper(& amp;table_name ')和c.owner = cc.owner和c . constraint _ name = cc . constraint _ name order by cc . position;八、存儲函數和過程查看函數和過程的狀態SQL select object _ name,status from user _ objects where object _ type = ' function ';SQLselect object_name,status from user _ objects where object _ type = ' PROCEDURE ';檢查函數和過程的源代碼SQL select text from all _ source其中owner = user,name = upper(' &;plsql _ name ');九、觸發器視圖觸發器設置長50000;關閉航向;設置pagesize 2000select ' create or replace trigger " ' | | trigger _ name | | ' " ' | | chr(10)| | decode(substr(trigger _ type,1,1),' A ',' AFTER ',' B ',' BEFORE ',' I ',' INSTEAD ')| | chr(10)| | trigging _ event | | chr(10)| | ' ON " ' | | table _ owner | | '。" ' | | table _ name | | ' " ' || chr(10)| | decode(instr(trigger _ type,'每行' ),0,null,'對每行')| | chr(10),trigger _ body from user _ triggers