oracle園地

這裡是到處搜括來的ORACLE技術及文章,也有自己發表的文章,希望大家可以互相討論! PS-若有侵權,請告知哦.

/*顯示當前用戶*/

show user

在sql plus中可用，在pl sql中不可用

/*查看所有用戶名*/　

select username,user_id,created from all_users;

/*查看當前用戶的用戶詳情*/　

select username,user_id,account_status,lock_date,expiry_date,default_tablespace,temporary_tablespace,created,initial_rsrc_consumer_group,external_name from user_users;

/*查看資料庫的版本*/　

select product,version,status from product_component_version;

/*查看當前用戶的用戶許可權，系統許可權和表級許可權*/　

select username,granted_role,admin_option,default_role,os_granted from user_role_privs;

select username,privilege,admin_option from user_sys_privs;

select grantee,owner,table_name,grantor,priviege,granttable,hierarchy from user_tab_privs;

查看當前用戶的缺省表空間

SQL>select username,default_tablespace from user_users;

　　查看當前用戶的角色

SQL>select * from user_role_privs;

　　查看當前用戶的系統許可權和表級許可權

SQL>select * from user_sys_privs;

SQL>select * from user_tab_privs;

　　查看用戶下所有的表

SQL>select * from user_tables;

　　顯示用戶資訊(所屬表空間)

select default_tablespace,temporary_tablespace

from dba_users where username='GAME';

　　1、用戶

　　查看當前用戶的缺省表空間

SQL>select username,default_tablespace from user_users;

　　查看當前用戶的角色

SQL>select * from user_role_privs;

　　查看當前用戶的系統許可權和表級許可權

SQL>select * from user_sys_privs;

SQL>select * from user_tab_privs;

　　顯示當前會話所具有的許可權

SQL>select * from session_privs;

　　顯示指定用戶所具有的系統許可權

SQL>select * from dba_sys_privs where grantee='GAME';

　　顯示特權用戶

select * from v$pwfile_users;

　　顯示用戶資訊(所屬表空間)

select default_tablespace,temporary_tablespace

from dba_users where username='GAME';

　　顯示用戶的PROFILE

select profile from dba_users where username='GAME';

2、表

　　查看用戶下所有的表

SQL>select * from user_tables;

　　查看名稱包含log字元的表

SQL>select object_name,object_id from user_objects

where instr(object_name,'LOG')>0;

　　查看某表的創建時間

SQL>select object_name,created from user_objects where object_name=upper('&table_name');

　　查看某表的大小

SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments

where segment_name=upper('&table_name');

　　查看放在Oracle的記憶體區裏的表

SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;

　　3、索引

　　查看索引個數和類別

SQL>select index_name,index_type,table_name from user_indexes order by table_name;

　　查看索引被索引的欄位

SQL>select * from user_ind_columns where index_name=upper('&index_name');

　　查看索引的大小

SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments

where segment_name=upper('&index_name');

　　4、序列號

　　查看序列號，last_number是當前值

SQL>select * from user_sequences;

　　5、視圖

　　查看視圖的名稱

SQL>select view_name from user_views;

　　查看創建視圖的select語句

SQL>set view_name,text_length from user_views;

SQL>set long 2000; 說明：可以根據視圖的text_length值設定set long 的大小

SQL>select text from user_views where view_name=upper('&view_name');

　　6、同義詞

　　查看同義詞的名稱

SQL>select * from user_synonyms;

　　7、約束條件

　　查看某表的約束條件

SQL>select constraint_name, constraint_type,search_condition, r_constraint_name

from user_constraints where table_name = upper('&table_name');

　　SQL>select c.constraint_name,c.constraint_type,cc.column_name

from user_constraints c,user_cons_columns cc

where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')

and c.owner = cc.owner and c.constraint_name = cc.constraint_name

order by cc.position;

　　8、存儲函數和過程

　　查看函數和過程的狀態

SQL>select object_name,status from user_objects where object_type='FUNCTION';

SQL>select object_name,status from user_objects where object_type='PROCEDURE';

　　9.查看函數和過程的源代碼

SQL>select text from all_source where owner=user and name=upper('&plsql_name');

10.通過v$sqlarea,v$sql查詢最佔用資源的查詢
-----------------------
v$sqlarea,v$sql
-----------------------
從V$SQLAREA中查詢最佔用資源的查詢
select b.username username,a.disk_reads reads,
a.executions exec,a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,
a.sql_text Statement
from v$sqlarea a,dba_users b
where a.parsing_user_id=b.user_id
and a.disk_reads > 100000
order by a.disk_reads desc;
用buffer_gets列來替換disk_reads列可以得到佔用最多記憶體的sql語句的相關資訊。

V$SQL是記憶體共用SQL區域中已經解析的SQL語句。

列出使用頻率最高的5個查詢：
select sql_text,executions
from (select sql_text,executions,
   rank() over
    (order by executions desc) exec_rank
   from v$sql)
where exec_rank <=5;
消耗磁片讀取最多的sql top5：
select disk_reads,sql_text
from (select sql_text,disk_reads,
   dense_rank() over
     (order by disk_reads desc) disk_reads_rank
   from v$sql)
where disk_reads_rank <=5;

找出需要大量緩衝讀取（邏輯讀）操作的查詢：
select buffer_gets,sql_text
from (select sql_text,buffer_gets,
   dense_rank() over
     (order by buffer_gets desc) buffer_gets_rank
   from v$sql)
where buffer_gets_rank<=5;

-- Start Approvers Information ---
1) SELECT function_id FROM fnd_form_functions WHERE function_name = 'POS_SUPPLIER_SUMM_MAIN';
2) SELECT application_id FROM fnd_application WHERE application_short_name = 'PER';
3) SELECT profile_option_id FROM fnd_profile_options WHERE profile_option_name = 'PER_SECURITY_PROFILE_ID';
4) SELECT vendor_id FROM po_vendors WHERE vendor_name = '&NameOfSupplier>'
5) SELECT user_id FROM fnd_user WHERE user_name = UPPER('&login_name_of_Supplier_user_who_created_the_Site')
-- Get users based on results from above
DBA
https://metalink2.oracle.com/metalink/plsql/f?p=130:14:4262907587689873236::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,131704.1,1,1,1,helvetica