熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

oracle系統表

2013-11-13 16:22:56  來源: Oracle 

  數據字典dict總是屬於Oracle用戶sys的

  用戶

  select username from dba_users;

  改口令

  alter user spgroup identified by spgtest;

  表空間

  select * from dba_data_files;

  select * from dba_tablespaces;//表空間

  select tablespace_namesum(bytes) sum(blocks)

  from dba_free_space group by tablespace_name;//空閒表空間

  select * from dba_data_files

  where tablespace_name=RBS;//表空間對應的數據文件

  select * from dba_segments

  where tablespace_name=INDEXS;

  數據庫對象

  select * from dba_objects;

  CLUSTERDATABASE LINKFUNCTIONINDEXLIBRARYPACKAGEPACKAGE BODY

  PROCEDURESEQUENCESYNONYMTABLETRIGGERTYPEUNDEFINEDVIEW

  

  select * from dba_tables;

  analyze my_table compute statistics;>dba_tables後

  select extent_idbytes 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_indexes;//索引包括主鍵索引

  select * from dba_ind_columns;//索引列

  select iindex_nameiuniquenesslumn_name

  from user_indexes iuser_ind_columns c

  where iindex_name=cindex_name

  and itable_name =ACC_NBR;//聯接使用

  序列

  select * from dba_sequences;

  視圖

  select * from dba_views;

  select * from all_views;

  text 可用於查詢視圖生成的腳本

  聚簇

  select * from dba_clusters;

  快照

  select * from dba_snapshots;

  快照分區應存在相應的表空間

  同義詞

  select * from dba_synonyms

  where table_owner=SPGROUP;

  //if owner is PUBLICthen the synonyms is a public synonym

  if owner is one of usersthen the synonyms is a private synonym

  數據庫鏈

  select * from dba_db_links;

  在spbase下建數據庫鏈

  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= and line_nbr=;

  觸發器

  select * from dba_trigers;

  存儲過程函數從dba_objects查找

  其文本select text from user_source where name=BOOK_SP_EXAMPLE;

  建立出錯select * from user_errors;

  oracle總是將存儲過程函數等軟件放在SYSTEM表空間

  約束

  ()約束是和表關聯的可在create table或alter table table_name add/drop/modify來建立修改刪除約束

  可以臨時禁止約束

  alter table book_example

  disable constraint book_example_;

  alter table book_example

  enable constraint book_example_;

  ()主鍵和外鍵被稱為表約束而not null和unique之類的約束被稱為列約束通常將主鍵和外鍵作為單獨的命名約束放在字段列表下面而列約束可放在列定義的同一行這樣更具有可讀性

  ()列約束可從表定義看出即describe;表約束即主鍵和外鍵可從dba_constraints和dba_cons_columns 查

  select * from user_constraints

  where table_name=BOOK_EXAMPLE;

  select ownerCONSTRAINT_NAMETABLE_NAME

  from user_constraints

  where constraint_type=R

  order by table_name;

  ()定義約束可以無名(系統自動生成約束名)和自己定義約束名(特別是主鍵外鍵)

  如create table book_example

  (identifier number not null);

  create table book_example

  (identifier number constranit book_example_ not null);

  回滾段

  在所有的修改結果存入磁盤前回滾段中保持恢復該事務所需的全部信息必須以數據庫發生的事務來相應確定其大小(DML語句才可回滾createdroptruncate等DDL不能回滾)

  回滾段數量=並發事務/但不能超過使每個回滾段大小足夠處理一個完整的事務

  create rollback segment r

  tablespace rbs;

  create rollback segment rbs_cvt

  tablespace rbs

  storage(initial M next k);

  使回滾段在線

  alter rollback segment r online;

  用dba_extentsv$rollback_segs監測回滾段的大小和動態增長

  回滾段的區間信息

  select * from dba_extents

  where segment_type=ROLLBACK and segment_name=RB;

  回滾段的段信息其中bytes顯示目前回滾段的字節數

  select * from dba_segments

  where segment_type=ROLLBACK and segment_name=RB;

  為事物指定回歸段

  set transaction use rollback segment rbs_cvt

  針對bytes可以使用回滾段回縮

  alter rollback segment rbs_cvt shrink;

  select bytesextentsmax_extents from dba_segments

  where segment_type=ROLLBACK and segment_name=RBS_CVT;

  回滾段的當前狀態信息

  select * from dba_rollback_segs

  where segment_name=RB;

  比多回滾段狀態status回滾段所屬實例instance_num

  查優化值optimal

  select nnamesoptsize

  from v$rollname nv$rollstat s

  where nusn=susn;

  回滾段中的數據

  set transaction use rollback segment rb;/*回滾段名*/

  select nnameswrites

  from v$rollname nv$rollstat s

  where nusn=susn;

  當事務處理完畢再次查詢$rollstat比較writes(回滾段條目字節數)差值可確定事務的大小

  查詢回滾段中的事務

  column rr heading RB Segment format a

  column us heading Username format a

  column os heading Os User format a

  column te heading Terminal format a

  select rname rrnvl(susernameno transaction) ussosuser ossterminal te

  from v$lock lv$session sv$rollname r

  where lsid=ssid(+)

  and trunc(lid/)=RUSN

  and ltype=TX

  and llmode=

  order by rname;

  作業

  查詢作業信息

  select jobbrokennext_dateintervalwhat from user_jobs;

  select jobbrokennext_dateintervalwhat from dba_jobs;

  查詢正在運行的作業

  select * from dba_jobs_running;

  使用包exec dbms_jobsubmit(:v_numa;sysdatesysdate + (/(**)))加入作業間隔秒鐘

  exec dbms_jobsubmit(:v_numa;sysdatesysdate + (/(*)))加入作業間隔分鐘使用包exec dbms_jobremove()


From:http://tw.wingwit.com/Article/program/Oracle/201311/18236.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.