檢視各項Oracle資料庫資訊

swq618發表於2015-04-11
Oracle資料庫
一、資料庫
資料庫名稱 select name from v$database;
資料庫唯一名稱 select db_unique_name from v$database;
資料庫ID select dbid from v$database;
資料庫建立時間 select to_char(created,'yyyy/mm/dd hh24:mi:ss') from v$database;
日誌歸檔模式 select log_mode from v$database;
開啟模式 select open_mode from v$database;
資料庫角色 select database_role from v$database;
伺服器引數檔案 select value from v$parameter where name='spfile';
控制檔案列表 select value from v$parameter2 where name='control_files';
二、資料檔案
表空間列表 select name from v$tablespace;
資料檔案列表 select name from v$datafile;
臨時檔案列表 select name from v$tempfile;
日誌組列表 select group#, bytes/1024/1024||'M' from v$log;
線上日誌檔案列表 select max(length(member)) from v$logfile;
column member format a36
select group#, member from v$logfile;
三、備份檔案
歸檔日誌檔案列表 select count(*) from v$archived_log;
select count(*) from v$archived_log where name is not null;
select name from v$archived_log where name is not null;
備份檔案列表 select count(*) from v$backup_piece;
select count(*) from v$backup_piece where handle is not null;
select handle from v$backup_piece where start_time >= sysdate-1 order by handle;
四、使用者與模式物件
資料庫使用者列表 select username from dba_users order by created;
模式物件數量列表 desc dba_objects
select owner, object_type, count(*)
  from dba_objects
 group by owner, object_type
 order by owner, object_type;
五、例項與會話
主機名稱 select host_name from v$instance;
例項名稱 select instance_name from v$instance;
服務名稱 select value from v$parameter where name='service_names';
資料庫軟體版本 select version from v$instance;
例項啟動時間 select to_char(startup_time,'yyyy/mm/dd hh24:mi:ss') from v$instance;
例項狀態 select status from v$instance;
當前會話列表 select sid, serial#, username from v$session;
檔案的大小與分佈
資料檔案總大小 select sum(bytes)/1024/1024||'M' from v$datafile;
select round(sum(bytes)/1024/1024/1024)||'G' from v$datafile;
臨時檔案總大小 select sum(bytes)/1024/1024||'M' from v$tempfile;
select round(sum(bytes)/1024/1024/1024)||'G' from v$tempfile;
線上日誌檔案總大小 select sum(bytes*members)/1024/1024||'M' from v$log;
select round(sum(bytes*members)/1024/1024/1024)||'G' from v$log;
控制檔案總大小 select sum(block_size*file_size_blks)/1024/1024||'M' from v$controlfile;
歸檔日誌檔案總大小 select count(*) from v$archived_log;
select min(block_size*blocks) from v$archived_log;
select max(block_size*blocks)/1024/1024||'M' from v$archived_log;
select sum(block_size*blocks)/1024/1024/1024||'G' from v$archived_log;
select round(nvl(sum(block_size*blocks),0)/1024/1024/1024)||'G' from v$archived_log where name is not null;
備份檔案總大小 select count(*) from v$backup_piece;
select sum(bytes)/1024/1024/1024||'G' from v$backup_piece;
select round(sum(bytes)/1024/1024/1024)||'G' from v$backup_piece where handle is not null;
資料檔案分佈 select distinct substr(name, 1, instr(name, '/', -1) ) from v$datafile;
臨時檔案分佈 select distinct substr(name, 1, instr(name, '/', -1) ) from v$tempfile;
線上日誌檔案分佈 select distinct substr(member, 1, instr(member, '/', -1) ) from v$logfile;
控制檔案分佈 select distinct substr(name, 1, instr(name, '/', -1) ) from v$controlfile;
歸檔日誌檔案分佈 select distinct substr(name, 1, instr(name, '/', -1) ) from v$archived_log;
備份檔案分佈 select distinct substr(handle, 1, instr(handle, '/', -1) ) from v$backup_piece;
伺服器引數檔案位置 select substr(value, 1, instr(value, '/', -1) ) from v$parameter where name='spfile';
告警日誌位置 select value from v$parameter where name='background_dump_dest';
ho ls -l /opt/app/oracle/admin/DAL2AP/bdump/alert*
後臺跟蹤日誌位置 select value from v$parameter where name='background_dump_dest';
使用者跟蹤日誌位置 select value from v$parameter where name='user_dump_dest';
核心跟蹤日誌位置 select value from v$parameter where name='core_dump_dest';
磁碟空間使用情況
各表空間空間佔用率 column "Total_Space" for a10
column "Free_Space" for a10
column "Used_Space" for a10
column "Free_Percentage" for a6
column "Used_Percentage" for a6
set pagesize 0
select df.tablespace_name,
       round(df.bytes/1024/1024,2)||'M' as "Total_Space",
       round(nvl(fs.bytes,0)/1024/1024,2)||'M' as "Free_Space",
       round((df.bytes-nvl(fs.bytes,0))/1024/1024,2)||'M' as "Used_Space",
       round(nvl(fs.bytes,0)/df.bytes,4)*100||'%' as "Free_Percentage",
       round(1-nvl(fs.bytes,0)/df.bytes,4)*100||'%' as "Used_Percentage"
  from (select tablespace_name, sum(bytes) as bytes
          from dba_data_files
         group by tablespace_name) df,
       (select tablespace_name, sum(bytes) as bytes
          from dba_free_space
         group by tablespace_name) fs
 where df.tablespace_name = fs.tablespace_name(+)
 order by df.tablespace_name;
主機資源使用情況
主機資源使用情況 desc v$resource_limit
select max(length(resource_name)) from v$resource_limit;
column resource_name format a21
select resource_name,
       current_utilization,
       max_utilization,
       initial_allocation,
       limit_value
  from v$resource_limit;
版本
資料庫版本 desc v$version
select banner from v$version;
資料庫選項 desc v$option
select max(length(parameter)), max(length(value)) from v$option;
column parameter format a35
column value format a5
select parameter, value from v$option;
系統引數
最大程式數 select value from v$parameter where name = 'processes';
最大會話數 select value from v$parameter where name = 'sessions';
資料塊大小 select value from v$parameter where name = 'db_block_size';
遊標共享模式 select value from v$parameter where name = 'cursor_sharing';
多塊讀批次 select value from v$parameter where name = 'db_file_multiblock_read_count';
回滾段表空間 select value from v$parameter where name = 'undo_tablespace';
回滾段保留時間 select value from v$parameter where name = 'undo_retention';
回滾段管理模式 select value from v$parameter where name = 'undo_management';
系統全域性區目標大小 select value/1024/1024||'M' from v$parameter where name = 'sga_target';
系統全域性區最大值 select value/1024/1024||'M' from v$parameter where name = 'sga_max_size';
程式全域性區目標和 select value/1024/1024||'M' from v$parameter where name = 'pga_aggregate_target';
時間統計 select value from v$parameter where name = 'timed_statistics';
初始化引數檔案 select value from v$parameter where name = 'ifile';
資料庫名稱 select value from v$parameter where name='db_name';
資料庫唯一名稱 select value from v$parameter where name='db_unique_name';
例項名稱 select value from v$parameter where name='instance_name';
服務名稱 select value from v$parameter where name='service_names';
當前使用者概況
段的總數量 -- 檢視段的數量
select count(*)  -- 段的總數量
  from user_segments;
各型別段的數量 -- 檢視各種型別的段的數量
select segment_type,  -- 段的型別
       count(*)       -- 該型別的段的數量
  from user_segments
 group by segment_type
 order by segment_type;
大段列表 desc user_segments
select max(length(segment_name)) from user_segments;
column segment_name format a30
column mb format a8
select max(length(tablespace_name)) from dba_tablespaces;
column tablespace_name format a13
-- 按型別檢視段的基本資訊
select segment_name,     -- 段的名稱
       tablespace_name,  -- 所在的表空間的名稱
       round(bytes/1024/1024)||'MB' as MB,
       extents           -- 包含的區的數量
  from user_segments
 where segment_type = 'TABLE'
   and bytes >= (1024*1024) * 10
   and rownum <=5
 order by bytes desc;
段的基本資訊 -- 檢視某個段的基本資訊
select segment_name,    -- 段的名稱
       partition_name,
       segment_type,    -- 段的型別
       segment_subtype,
       tablespace_name, -- 表空間的名稱
       bytes,           -- 段的大小
       blocks,          -- 塊的數量
       extents          -- 區的數量
  from user_segments
 where segment_name = 'POSTPAY_BILLED_REVENUE';
區的列表 -- 檢視某個段的區列表
select segment_name,  -- 段的名稱
       extent_id,     -- 區的編號
       bytes          -- 區的大小
  from user_extents
 where segment_name = 'POSTPAY_BILLED_REVENUE'
 order by extent_id;
區的大小分佈 -- 區的大小分佈分析
select segment_name,    -- 段的名稱
       bytes/1024/1024, -- 區的大小
       count(*)         -- 區的數量
  from user_extents
 where segment_name = 'POSTPAY_BILLED_REVENUE'
 group by segment_name, bytes
 order by bytes;
使用者儲存空間消耗 -- 計算整個使用者佔用的儲存空間
select user,   -- 資料庫使用者名稱
       round(sum(bytes/1024/1024/1024),2) as space_GB -- 佔用儲存空間大小
  from user_segments;
前20個大段 -- 列出佔用儲存空間最多的前20個段
select segment_name,    -- 段的名稱
       segment_type,    -- 段的型別
       tablespace_name, -- 所在表空間
       bytes,           -- 段的大小
       round(bytes/1024/1024/1024, 3) as space_GB
  from (
         select segment_name,
                segment_type,
                tablespace_name,
                bytes,
                blocks,
                extents
           from user_segments
          order by bytes desc
        )
 where rownum <= 10;
表的基本資訊 -- 檢視錶的基本資訊
select table_name,      -- 表的名稱
       tablespace_name, -- 表空間的名稱
       num_rows,        -- 記錄數
       avg_row_len,     -- 行的平均長度
       last_analyzed,   -- 統計資訊收集時間
       sample_size      -- 統計資訊收集樣本
  from user_tables
 where table_name = 'POSTPAY_BILLED_REVENUE';
欄位的基本資訊 -- 檢視欄位的基本資訊
select table_name,   -- 表的名稱
       column_id,    -- 欄位的順序
       column_name,  -- 欄位的名稱
       data_type,    -- 資料型別
       data_length,  -- 欄位長度
       nullable,     -- 是否允許空值
       num_distinct, -- 不同值的個數
       num_nulls     -- 空值記錄數
  from user_tab_columns
 where table_name = 'POSTPAY_BILLED_REVENUE'
 order by column_id;
表的備註 -- 檢視錶的備註
select table_name,  -- 名稱
       table_type,  -- 型別
       comments     -- 備註
  from user_tab_comments
 where table_name = 'POSTPAY_BILLED_REVENUE';
欄位的備註 -- 檢視欄位的備註
select table_name,   -- 表的名稱
       column_name,  -- 欄位的名稱
       comments      -- 備註
  from user_col_comments
 where table_name = 'POSTPAY_BILLED_REVENUE';
表的約束 -- 檢視錶的約束
select owner,
       constraint_name, -- 約束的名稱
       constraint_type, -- 約束的型別
       table_name       -- 歸屬表的名稱
  from user_constraints
 where table_name = 'POSTPAY_BILLED_REVENUE'
   and constraint_type = 'P';
欄位的約束 -- 檢視約束的欄位
select owner,
       constraint_name, -- 約束的名稱
       table_name,      -- 歸屬表的名稱
       column_name,     -- 欄位的名稱
       position         -- 欄位的順序
  from user_cons_columns
 where constraint_name = 'POSTPAY_BILLED_REVENUE_PK'
 order by position;
索引的列表 -- 檢視索引的列表
select table_name,      -- 表的名稱
       index_name,      -- 索引的名稱
       index_type,      -- 索引型別
       uniqueness,      -- 是否唯一索引
       tablespace_name, -- 表空間名稱
       leaf_blocks,       -- 葉子塊數量
       distinct_keys,     -- 鍵值數
       clustering_factor, -- 聚合因子
       num_rows,          -- 記錄數
       last_analyzed      -- 統計資訊收集時間
  from user_indexes
 where table_name = 'POSTPAY_BILLED_REVENUE';
索引的欄位 -- 檢視索引的欄位
select table_name,      -- 表名稱
       index_name,      -- 欄位名稱
       column_position, -- 欄位順序
       column_name      -- 欄位名稱
  from user_ind_columns
 where table_name = 'POSTPAY_BILLED_REVENUE'
 order by index_name, column_position;
物件總數量 -- 檢視物件的數量
select count(*)
  from user_objects;
物件分類數量 -- 檢視各種型別物件的數量
select object_type, -- 物件的型別
       count(*)     -- 物件的數量
  from user_objects
 group by object_type
 order by object_type;
資料庫連結列表 -- 檢視資料庫連結
select * from user_db_links;
大物件列表 -- 檢視大物件
select * from user_lobs;
物化檢視列表 -- 檢視物化檢視
select * from user_mviews;
檢視列表 -- 檢視檢視
select * from user_views;
瞭解業務表的例子 --
-- 瞭解某個業務表的例子
--
-- 看一下段有多大
select segment_name,    -- 段的名稱
       segment_type,    -- 段的型別
       tablespace_name, -- 表空間的名稱
       round(bytes/1024/1024/1024,2)||'GB' as space_GB -- 段的大小
  from user_segments
 where segment_name = 'CUSTOMER'
   and segment_type = 'TABLE';

-- 看一下表有多少條記錄
select table_name,      -- 表的名稱
       tablespace_name, -- 表空間的名稱
       num_rows,        -- 記錄數
       last_analyzed    -- 統計資訊收集時間
  from user_tables
 where table_name = 'CUSTOMER';

-- 找出主鍵的名稱和欄位列表
select owner,
       constraint_name, -- 約束的名稱
       constraint_type, -- 約束的型別
       table_name       -- 歸屬表的名稱
  from user_constraints
 where table_name = 'CUSTOMER'
   and constraint_type = 'P';

--
select owner,
       constraint_name, -- 約束的名稱
       table_name,      -- 歸屬表的名稱
       column_name,     -- 欄位的名稱
       position         -- 欄位的順序
  from user_cons_columns
 where constraint_name = 'SYS_C0010843'
   and table_name = 'CUSTOMER'
 order by position;

-- 看看錶有哪些索引以及索引的欄位
select table_name,      -- 表的名稱
       index_name       -- 索引的名稱
  from user_indexes
 where table_name = 'CUSTOMER';

--
select table_name,      -- 表名稱
       index_name,      -- 欄位名稱
       column_position, -- 欄位順序
       column_name      -- 欄位名稱
  from user_ind_columns
 where table_name = 'CUSTOMER'
 order by index_name, column_position;

-- 隨機檢索10條記錄的主鍵欄位
select customer_id
  from customer
 where rownum <= 10;

-- 檢視主鍵欄位的資料型別
select table_name,   -- 表的名稱
       column_name,  -- 欄位的名稱
       data_type,    -- 資料型別
       num_distinct, -- 不同值的個數
       num_nulls     -- 空值記錄數
  from user_tab_columns
 where table_name = 'CUSTOMER'
   and column_name in ('CUSTOMER_ID');

-- 獲取某條記錄的詳細資訊
select *
  from customer
 where customer_id = '-22830735';

--
select customer_id,
       icms_customer_no,
       account_type,
       active_flag,
       icms_subtype,
       line_no,
       sa_create_date
  from customer
 where customer_id = '-22830735';
常用基本指令碼
LONG的顯示設定 -- 設定LONG型別欄位的顯示長度
show long
set long 7763
分頁設定 -- 設定分頁大小，避免頻繁顯示標題
show pagesize
set pagesize 0
檢視長度 -- 檢視檢視定義的長度
select owner, view_name, text_length from dba_views where view_name='&view_name';
檢視定義 -- 從資料字典檢視中直接查詢檢視的定義
select text from dba_views where view_name='&view_name';
檢視定義 -- 透過應用程式程式設計介面獲取檢視的定義
select dbms_metadata.get_ddl('VIEW','&view_name','CAMPAIGN') from dual;
表的歸屬 -- 根據表的名稱，查詢表在哪個使用者下
select owner, table_name from dba_tables where table_name='&table_name';
段的大小 -- 根據段名查詢段的大小
select owner, segment_name, segment_type, bytes from dba_segments where segment_name='&segment_name';
多個段的大小 select owner||'.'||segment_name as segment, segment_type, bytes/1024/1024||'M' as MB
  from dba_segments
 where segment_name in ('&segment_name');
索引的歸屬 -- 根據索引的名稱，查詢相關的表名和使用者名稱
select owner, index_name, table_owner, table_name from dba_indexes where index_name='&index_name';
表的索引 -- 根據表的名稱查詢索引
select owner, index_name, table_owner, table_name from dba_indexes where table_name='&table_name';
表的狀態 select owner, index_name, status, LAST_ANALYZED from dba_indexes where table_name='&table_name';
表的索引欄位 -- 根據表的名稱查詢索引的欄位
select index_name, column_name, column_position from dba_ind_columns where table_name='&table_name';
索引的欄位 select index_name, column_name, column_position from dba_ind_columns where index_name='&index_name';
資料分佈 explain plan for select /* + index(a CCONTACTHIST_IX3) */ contactdatetime from campaign.UA_CONTACTHISTORY a where contactdatetime>systimestamp-200 and rownum=1;
select PLAN_ID, operation, options, object_name, id, parent_id from plan_table;

explain plan for select CONTACTDATETIME,CUSTOMERID from campaign.UA_DTLCONTACTHIST where CONTACTDATETIME>sysdate-&daysbefore and rownum=1;
select PLAN_ID, operation, options, object_name, id, parent_id from plan_table;

select to_char(CONTACTDATETIME,'yyyy-mm-dd') as contactdatetime,CUSTOMERID from campaign.UA_DTLCONTACTHIST where CONTACTDATETIME>sysdate-&daysbefore and rownum=1;

select to_char(CONTACTDATETIME,'yyyy-mm-dd') as contactdatetime,CUSTOMERID from campaign.UA_DTLCONTACTHIST where CONTACTDATETIME 資料分佈 		select to_char(CONTACTDATETIME,'yyyy-mm-dd') as contact_datetime,
       count(CONTACTDATETIME) as contact_count
  from campaign.UA_DTLCONTACTHIST
 where CONTACTDATETIME>sysdate-&daysbefore
   and CONTACTDATETIME group by to_char(CONTACTDATETIME,'yyyy-mm-dd')
 order by contact_datetime desc

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29674916/viewspace-1564171/，如需轉載，請註明出處，否則將追究法律責任。

相關文章