怎么监控oracle数据库表和表空间的现状

可以通过查询v$tablespace、dba_data_files、dba_tablespaces等视图监控Oracle数据库表空间状态。

Oracle数据库表状态可以通过查询user_tables视图进行监控，如select table_name,status from user_tables where table_name='T1'语句。

1.跟踪数据库空间增长

SELECT SUM(MB_ALLOC)/1024 GB_DB_SIZE FROM

(SELECT SUM(ROUND(bytes_used/(1024*1024),2) + ROUND(bytes_free/(1024*1024),2)) MB_ALLOC

FROM V$temp_space_header, dba_temp_files

WHERE V$temp_space_header.file_id (+) = dba_temp_files.file_id

UNION

SELECT SUM(BYTES)/(1024*1024) MB_ALLOC FROM dba_data_files)

2.下面例子除了undo和temp表空间外，将其他表空间的的使用情况记录每周插入db_spaec_hist表，以便查询：

Create the table for database size history create table db_space_hist (

timestampdate,

total_space number(8),

used_space number(8),

free_space number(8),

pct_inusenumber(5,2),

num_db_files number(5)

)

Create the procedure db_space_history CREATE OR REPLACE PROCEDURE db_space_history AS

BEGIN

INSERT INTO db_space_hist

SELECT SYSDATE, total_space,

total_space-NVL(free_space,0) used_space,

NVL(free_space,0) free_space,

((total_space - NVL(free_space,0)) / total_space)*100 pct_inuse,

num_db_files

FROM ( SELECT SUM(bytes)/1024/1024 free_space

FROM sys.DBA_FREE_SPACE WHERE tablespace_name NOT LIKE '%UNDO%') FREE,

( SELECT SUM(bytes)/1024/1024 total_space,

COUNT(*) num_db_files

FROM sys.DBA_DATA_FILES WHERE tablespace_name NOT LIKE '%UNDO%') FULL

COMMIT

END

/

Create the job that runs once in a week DECLARE

X NUMBER

BEGIN

SYS.DBMS_JOB.SUBMIT

(

job=>X

,what =>'SYS.DB_SPACE_HISTORY'

,next_date =>TO_DATE('22/02/2008 19:40:28','dd/mm/yyyy hh24:mi:ss')

,INTERVAL =>'TRUNC(SYSDATE+7)'

,no_parse =>FALSE

)

END

3.做周期性监控

select * from db_space_hist order by timestamp desc

4.查询结果（每月数据库的增长情况），统计的出发条件为createion_time.

SELECT TO_CHAR(creation_time, 'RRRR Month') "Month", round(SUM(bytes)/1024/1024/1024) "Growth in GBytes"

FROM sys.v_$datafile

WHERE creation_time >SYSDATE-365

GROUP BY TO_CHAR(creation_time, 'RRRR Month')

Month Growth in GBytes

-------------- ----------------

2008 December 1331

2008 November 779

2008 October447

2009 April 797

2009 August 344

2009 February 505

2009 January443

2009 July 358

2009 June 650

2009 March 452

2009 May 1787

2009 October255

2009 September 158

创建表，存储每天的表空间占用情况

CREATE TABLE [dbo].[t_rpt_table_spaceinfo](

[table_name] [sysname] NOT NULL,

[record_date] [date] NOT NULL,

[record_time] [time](7) NOT NULL,

[rows_count] [bigint] NULL,

[reserved] [bigint] NULL,

[data_size] [bigint] NULL,

[index_size] [bigint] NULL,

[unused] [bigint] NULL,

CONSTRAINT [PK_t_rpt_table_spaceinfo] PRIMARY KEY CLUSTERED

(

[table_name] ASC,

[record_date] ASC,

[record_time] ASC

)

)

2. 新建作业

新建作业，作业计划每天凌晨运行一次，每天记录表占用的空间情况，存储到上一步建立的表中

作业中执行的T-SQL代码为：

SET NOCOUNT ON

/*创建临时表，存放用户表的空间及数据行数信息*/

CREATE TABLE #tablespaceinfo

(

nameinfo VARCHAR(500) ,

rowsinfo BIGINT ,

reserved VARCHAR(20) ,

datainfo VARCHAR(20) ,

index_size VARCHAR(20) ,

unused VARCHAR(20)

)

DECLARE @tablename VARCHAR(255)

/*使用游标，循环得到表空间使用情况*/

DECLARE Info_cursor CURSOR

---