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

數據字典名稱是大寫的英文字符。數據字典包含用戶信息、用戶權限信息、所有數據對象信息、表約束、統計分析數據庫視圖等。我們不能手動修改數據字典中的信息。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