摘錄地址：
http://daniel-wuz.javaeye.com/blog/145925
1.取得指定用戶的所有表名：
Java代碼
SELECT OWNER AS "對(duì)象所有者",OBJECT_NAME AS "表名",OBJECT_ID AS "對(duì)象編號(hào)" from dba_objects where owner = 'RAXNYB' AND OBJECT_TYPE = 'TABLE' ORDER BY OWNER,OBJECT_TYPE;
或
OWNER AS "對(duì)象所有者",TABLE_NAME AS "表名" from DBA_TABLES where owner = 'RAXNYB' ORDER BY OWNER,TABLE_NAME;
SELECT OWNER AS "對(duì)象所有者",OBJECT_NAME AS "表名",OBJECT_ID AS "對(duì)象編號(hào)" from dba_objects where owner = 'RAXNYB' AND OBJECT_TYPE = 'TABLE' ORDER BY OWNER,OBJECT_TYPE;
或
OWNER AS "對(duì)象所有者",TABLE_NAME AS "表名" from DBA_TABLES where owner = 'RAXNYB' ORDER BY OWNER,TABLE_NAME;
2.取得指定用戶的所有視圖名稱：
Java代碼
SELECT OWNER AS "對(duì)象所有者",VIEW_NAME AS "視圖名稱" from DBA_VIEWS where owner = 'RAXNYB' ORDER BY OWNER,VIEW_NAME;
SELECT OWNER AS "對(duì)象所有者",VIEW_NAME AS "視圖名稱" from DBA_VIEWS where owner = 'RAXNYB' ORDER BY OWNER,VIEW_NAME;
oracle系統(tǒng)表查詢
1.用戶：
Java代碼
select username from dba_users;
select username from dba_users;
改口令
Java代碼
alter user spgroup identified by spgtest;
alter user spgroup identified by spgtest;
2.表空間:
Java代碼
select * from dba_data_files;
select * from dba_tablespaces;//表空間
select tablespace_name,sum(bytes), sum(blocks) from dba_free_space group by tablespace_name;//空閑表空間
select * from dba_data_files where tablespace_name='RBS';//表空間對(duì)應(yīng)的數(shù)據(jù)文件
select * from dba_segments where tablespace_name='INDEXS';
select * from dba_data_files;
select * from dba_tablespaces;//表空間
select tablespace_name,sum(bytes), sum(blocks) from dba_free_space group by tablespace_name;//空閑表空間
select * from dba_data_files where tablespace_name='RBS';//表空間對(duì)應(yīng)的數(shù)據(jù)文件
select * from dba_segments where tablespace_name='INDEXS';
3.數(shù)據(jù)庫(kù)對(duì)象
Java代碼
select * from dba_objects;
CLUSTER、DATABASE LINK、FUNCTION、INDEX、LIBRARY、PACKAGE、PACKAGE BODY、PROCEDURE、SEQUENCE、SYNONYM、TABLE、TRIGGER、TYPE、UNDEFINED、VIEW。
select * from dba_objects;
CLUSTER、DATABASE LINK、FUNCTION、INDEX、LIBRARY、PACKAGE、PACKAGE BODY、PROCEDURE、SEQUENCE、SYNONYM、TABLE、TRIGGER、TYPE、UNDEFINED、VIEW。
4.表
Java代碼
select * from dba_tables;
select extent_id,bytes from dba_extents where segment_name='CUSTOMERS' and segment_type='TABLE' order by extent_id;//表使用的extent的信息。segment_type='ROLLBACK'查看回滾段的空間分配信息
select distinct table_name from user_tab_columns where column_name='SO_TYPE_ID';
select * from dba_tables;
select extent_id,bytes from dba_extents where segment_name='CUSTOMERS' and segment_type='TABLE' order by extent_id;//表使用的extent的信息。segment_type='ROLLBACK'查看回滾段的空間分配信息
select distinct table_name from user_tab_columns where column_name='SO_TYPE_ID';
5.索引
Java代碼
select * from dba_indexes;//索引，包括主鍵索引
select * from dba_ind_columns;//索引列
select i.index_name,i.uniqueness,c.column_name
from user_indexes i,user_ind_columns c
where i.index_name=c.index_name
and i.table_name ='ACC_NBR';//聯(lián)接使用
select * from dba_indexes;//索引，包括主鍵索引
select * from dba_ind_columns;//索引列
select i.index_name,i.uniqueness,c.column_name
from user_indexes i,user_ind_columns c
where i.index_name=c.index_name
and i.table_name ='ACC_NBR';//聯(lián)接使用
6.序列
Java代碼
select * from dba_sequences;
select * from dba_sequences;
7.視圖
Java代碼
select * from dba_views
select * from all_views
text字段 可用于查詢視圖生成的腳本
select * from dba_views
select * from all_views
text字段 可用于查詢視圖生成的腳本
8.聚簇
Java代碼
select * from dba_clusters
select * from dba_clusters
9.快照
Java代碼
select * from dba_snapshots
select * from dba_snapshots
快照、分區(qū)應(yīng)存在相應(yīng)的表空間
10.同義詞
Java代碼
select * from dba_synonyms
select * from dba_synonyms
//if owner is PUBLIC,then the synonyms is a public synonym.
//if owner is one of users,then the synonyms is a private synonym
11.數(shù)據(jù)庫(kù)鏈
Java代碼
select * from dba_db_links
select * from dba_db_links
在spbase下建數(shù)據(jù)庫(kù)鏈:
Java代碼
create database link dbl_spnew
connect to spnew identified by spnew using 'jhhx';
insert into acc_nbr@dbl_spnew select * from acc_nbr where nxx_nbr='237' and line_nbr='8888';
create database link dbl_spnew
connect to spnew identified by spnew using 'jhhx';
insert into acc_nbr@dbl_spnew select * from acc_nbr where nxx_nbr='237' and line_nbr='8888';
12.觸發(fā)器
Java代碼
select * from dba_trigers;
select * from dba_trigers;
存儲(chǔ)過(guò)程，函數(shù)從dba_objects查找
其文本：select text from user_source where name='BOOK_SP_EXAMPLE';
建立出錯(cuò)：select * from user_errors
oracle總是將存儲(chǔ)過(guò)程，函數(shù)等軟件放在SYSTEM表空間。
13.約束
（1）約束是和表關(guān)聯(lián)的，可在create table或alter table table_name add/drop/modify來(lái)建立、修改、刪除約束.
可以臨時(shí)禁止約束，如：
Java代碼
alter table book_example disable constraint book_example_1;
alter table book_example enable constraint book_example_1;
alter table book_example disable constraint book_example_1;
alter table book_example enable constraint book_example_1;
（2）主鍵和外鍵被稱為表約束，而not null和unique之類的約束被稱為列約束。通常將主鍵和外鍵作為單獨(dú)的命名約束放在字段列表下面，而列約束可放在列定義的同一行，這樣更具有可讀性
（3）列約束可從表定義看出，即describe;表約束即主鍵和外鍵，可從dba_constraints和dba_cons_columns 查。
Java代碼
select * from user_constraints where table_name='BOOK_EXAMPLE';
select owner,CONSTRAINT_NAME,TABLE_NAME from user_constraints where constraint_type='R' order by table_name;
select * from user_constraints where table_name='BOOK_EXAMPLE';
select owner,CONSTRAINT_NAME,TABLE_NAME from user_constraints where constraint_type='R' order by table_name;
（4）定義約束可以無(wú)名（系統(tǒng)自動(dòng)生成約束名）和自己定義約束名（特別是主鍵、外鍵） 如：
Java代碼
create table book_example (identifier number not null);
create table book_example (identifier number constranit book_example_1 not null);
create table book_example (identifier number not null);
create table book_example (identifier number constranit book_example_1 not null);
14、回滾段：
在所有的修改結(jié)果存入磁盤前，回滾段中保持恢復(fù)該事務(wù)所需的全部信息，必須以數(shù)據(jù)庫(kù)發(fā)生的事務(wù)來(lái)相應(yīng)確定其大小（DML語(yǔ)句才可回滾，create,drop,truncate等DDL不能回滾）。
回滾段數(shù)量=并發(fā)事務(wù)/4，但不能超過(guò)50；使每個(gè)回滾段大小足夠處理一個(gè)完整的事務(wù)；
Java代碼
create rollback segment r05 tablespace rbs;
create rollback segment rbs_cvt tablespace rbs storage(initial 1M next 500k);
create rollback segment r05 tablespace rbs;
create rollback segment rbs_cvt tablespace rbs storage(initial 1M next 500k);
使回滾段在線
Java代碼
alter rollback segment r04 online;
alter rollback segment r04 online;
用dba_extents,v$rollback_segs監(jiān)測(cè)回滾段的大小和動(dòng)態(tài)增長(zhǎng)。
回滾段的區(qū)間信息
Java代碼
select * from dba_extents where segment_type='ROLLBACK' and segment_name='RB1';
select * from dba_extents where segment_type='ROLLBACK' and segment_name='RB1';
回滾段的段信息,其中bytes顯示目前回滾段的字節(jié)數(shù)
Java代碼
select * from dba_segments where segment_type='ROLLBACK' and segment_name='RB1';
select * from dba_segments where segment_type='ROLLBACK' and segment_name='RB1';
為事物指定回歸段
Java代碼
set transaction use rollback segment rbs_cvt
set transaction use rollback segment rbs_cvt
針對(duì)bytes可以使用回滾段回縮。
Java代碼
alter rollback segment rbs_cvt shrink;
select bytes,extents,max_extents from dba_segments where segment_type='ROLLBACK' and segment_name='RBS_CVT';
alter rollback segment rbs_cvt shrink;
select bytes,extents,max_extents from dba_segments where segment_type='ROLLBACK' and segment_name='RBS_CVT';
回滾段的當(dāng)前狀態(tài)信息：
Java代碼
select * from dba_rollback_segs where segment_name='RB1';
select * from dba_rollback_segs where segment_name='RB1';
比多回滾段狀態(tài)status，回滾段所屬實(shí)例instance_num
查優(yōu)化值optimal
Java代碼
select n.name,s.optsize from v$rollname n,v$rollstat s where n.usn=s.usn;
select n.name,s.optsize from v$rollname n,v$rollstat s where n.usn=s.usn;
回滾段中的數(shù)據(jù)
Java代碼
set transaction use rollback segment rb1;/*回滾段名*/
select n.name,s.writes from v$rollname n,v$rollstat s where n.usn=s.usn;
set transaction use rollback segment rb1;/*回滾段名*/
select n.name,s.writes from v$rollname n,v$rollstat s where n.usn=s.usn;
當(dāng)事務(wù)處理完畢，再次查詢$rollstat，比較writes(回滾段條目字節(jié)數(shù))差值，可確定事務(wù)的大小。
查詢回滾段中的事務(wù)
Java代碼
column rr heading 'RB Segment' format a18
column us heading 'Username' format a15
column os heading 'Os User' format a10
column te heading 'Terminal' format a10
select r.name rr,nvl(s.username,'no transaction') us,s.osuser os,s.terminal te from v$lock l,v$session s,v$rollname r
where l.sid=s.sid(+)
and trunc(l.id1/65536)=R.USN and l.type='TX' and l.lmode=6 order by r.name;
column rr heading 'RB Segment' format a18
column us heading 'Username' format a15
column os heading 'Os User' format a10
column te heading 'Terminal' format a10
select r.name rr,nvl(s.username,'no transaction') us,s.osuser os,s.terminal te from v$lock l,v$session s,v$rollname r
where l.sid=s.sid(+)
and trunc(l.id1/65536)=R.USN and l.type='TX' and l.lmode=6 order by r.name;
15、作業(yè)
查詢作業(yè)信息
Java代碼
select job,broken,next_date,interval,what from user_jobs;
select job,broken,next_date,interval,what from dba_jobs;
select job,broken,next_date,interval,what from user_jobs;
select job,broken,next_date,interval,what from dba_jobs;
查詢正在運(yùn)行的作業(yè)
Java代碼
select * from dba_jobs_running;
select * from dba_jobs_running;
使用包
Java代碼
exec dbms_job.submit(:v_num,'a;',sysdate,'sysdate + (10/(24*60*60))')加入作業(yè)。間隔10秒鐘
exec dbms_job.submit(:v_num,'a;',sysdate,'sysdate + (11/(24*60))')加入作業(yè)。間隔11分鐘使用包exec dbms_job.remove(21)刪除21號(hào)作業(yè)。
exec dbms_job.submit(:v_num,'a;',sysdate,'sysdate + (10/(24*60*60))')加入作業(yè)。間隔10秒鐘
exec dbms_job.submit(:v_num,'a;',sysdate,'sysdate + (11/(24*60))')加入作業(yè)。間隔11分鐘使用包exec dbms_job.remove(21)刪除21號(hào)作業(yè)。
16.批注:
ALL_COL_COMMENTS
歡迎大家訪問(wèn)我的個(gè)人網(wǎng)站
萌萌的IT人