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語句。
-----------------------
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 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;
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
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
產生短網址
痞客邦新服務7Headlines
部落格文章搜尋
近期文章
最新迴響
- 10/03 訪客：幫到我了 感謝
- 09/17 訪客：版大你好 小弟是出社會一年的...
- 07/17 訪客：非常感谢，非常实用的整理！
- 03/27 francis namo：HELLO EVERYONE, UNIVERSITY ...
- 02/27 YT：這是個很實用的分享，謝謝您...
- 02/22 Qoo：很清楚的說明，謝謝您的分享
- 12/25 SQL大菜鳥：真是一篇好文章，幫助很大，...
- 02/08 版主回覆：沒有這個tbale .這是客製出來的.
- 02/08 版主回覆：不會.助人為快樂之本.
- 02/08 版主回覆：不知道吔.知道的話請告訴我.謝謝
文章彙整
文章分類
ORACLE JDeveloper (1)
Oracle Tools (4)
Oracle SQL (3)
ORACLE ERP (8)
教學影音檔 (1)
我的連結
ORACLE ERP
ORACLE 技術網站
- ORACLE FLOW
- SQL 指令教學網站
- oracle 培訓資料-簡體文
- ORACLE 探索者
- hb0xf0wk的博客
- 好喜愛學習網
- Aloz 的Oracle ERP天地
- 昭知.天翔
- EBS 常用解釋
- EBS 模組設定
- EXCEL 轉入 REPORT的作法
- JOHN'S BLOG ORACLE
- ERP -SCHOOL
- Oracle 9i SQL*Loader
- SQL Loader
- 夏普視野
- 韓國FORM的應用
- ORACLE SQL基本語法詳解
- SQL 語法收集
- SQL 語法收集
- Oracle9i SQL Reference
- FORM ERROR_CODE
- 基礎FORM 開發教程
- ORACLE 線上文件
- ORACLE DB 指令集
- ORACLE R12 體驗網址
- ORACLE 開發文件下載
- OE操作圖示
- ORACLE FAQ
誰來我家
參觀人氣
- 本日人氣：92
- 累積人氣：380535
姓名學
JJ
QR Code
