DBA实用操作技巧
[表空间以及表分区相关操作]
查看临时表空间大小：
SELECT COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_TYPE='TEMPORARY';
查看所有表和索引大小：
SELECT SEGMENT_TYPE,SEGMENT_NAME, TABLESPACE_NAME, SUM(bytes)/(1024*1024) sumblock FROM USER_SEGMENTS GROUP BY SEGMENT_TYPE, SEGMENT_NAME, TABLESPACE_NAME ORDER BY 1,4 DESC ;
查看某天的所有分区大小：
SELECT SEGMENT_TYPE,SEGMENT_NAME, PARTITION_NAME, TABLESPACE_NAME, SUM(bytes)/(1024*1024) sumblock FROM USER_SEGMENTS WHERE PARTITION_NAME like 'P20070220%' GROUP BY SEGMENT_TYPE,SEGMENT_NAME,PARTITION_NAME, TABLESPACE_NAME ORDER BY 1,2 DESC ;
查看所有索引的小小：
SELECT SEGMENT_TYPE,SEGMENT_NAME, SUM(bytes)/(1024*1024) sumblock FROM USER_SEGMENTS WHERE SEGMENT_TYPE like 'INDEX%' GROUP BY SEGMENT_TYPE,SEGMENT_NAME ORDER BY 1,3 DESC ;
查看某个表的所有分区大小：
SELECT SEGMENT_TYPE,SEGMENT_NAME, PARTITION_NAME, SUM(bytes)/(1024*1024) sumblock FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'ANALOG_RES_PERL2QUERYTRACE' GROUP BY SEGMENT_TYPE,SEGMENT_NAME,PARTITION_NAME ORDER BY 1,3 DESC ;
查看某表空间中所有表的大小：
SELECT SEGMENT_TYPE, SEGMENT_NAME, TABLESPACE_NAME, SUM(bytes)/(1024*1024) sumblock FROM USER_SEGMENTS WHERE TABLESPACE_NAME = 'CNWEBLOG_RES_DW' GROUP BY SEGMENT_TYPE,SEGMENT_NAME,TABLESPACE_NAME ORDER BY 1, 4 DESC;
更新视图：
create or replace view ANALOG_VIEW_PERL2QUERYTRACE
as
select b.report_time,a.query_id,a.QUERY_REPORT_ID, b.pageview
from analog_dim_perl2querytrace a,
analog_res_perl2querytrace b
WHERE a.query_name=b.QUERY_NAME ;
据备份，创建按月表分区：
select * from analog_res_perl2querytrace where rownum<10 order by report_time;
REPORT_TIME QUERY_NAME PAGEVIEW
----------- --------------------------------------------- ----------
20050501 news=daohang 2255
20050501 offerdetail=sellofferpv-brand 475
20050501 alishop=offerlisttrustscore 1552
20050501 alishopself=companysamplebutton 19054
20050501 twograde=sellerbanner1 70
20050501 info=alitalk 84
20050501 cnalibaba1=5 612
20050501 cnalibaba1=16 490
20050501 info=rednews 438
9 rows selected.
Elapsed: 00:00:00.01
CREATE TABLE analog_res_perl2querytrace_1 ( REPORT_TIME NUMBER, QUERY_NAME VARCHAR2(256), PAGEVIEW NUMBER ) PARTITION BY RANGE (REPORT_TIME) ( PARTITION P200505 VALUES less than (20050601), PARTITION P200506 VALUES less than (20050701), PARTITION P200507 VALUES less than (20050801), PARTITION P200508 VALUES less than (20050901), PARTITION P200509 VALUES less than (20051001), PARTITION P200510 VALUES less than (20051101), PARTITION P200511 VALUES less than (20051201), PARTITION P200512 VALUES less than (20060101), PARTITION P200601 VALUES less than (20060201), PARTITION P200602 VALUES less than (20060301), PARTITION P200603 VALUES less than (20060401), PARTITION P200604 VALUES less than (20060501), PARTITION P200605 VALUES less than (20060601), PARTITION P200606 VALUES less than (20060701), PARTITION P200607 VALUES less than (20060801), PARTITION P200608 VALUES less than (20060901), PARTITION P200609 VALUES less than (20061001), PARTITION P200610 VALUES less than (20061101), PARTITION P200611 VALUES less than (20061201), PARTITION P200612 VALUES less than (20070101), PARTITION P200701 VALUES less than (20070201), PARTITION P200702 VALUES less than (20070301), PARTITION P200703 VALUES less than (20070401), PARTITION P200704 VALUES less than (20070501), PARTITION P200705 VALUES less than (20070601), PARTITION P200706 VALUES less than (20070701), PARTITION P200707 VALUES less than (20070801), PARTITION P200708 VALUES less than (20070901), PARTITION P200709 VALUES less than (20071001), PARTITION P200710 VALUES less than (20071101), PARTITION P200711 VALUES less than (20071201), PARTITION P200712 VALUES less than (20080101) );
insert /*+append*/ into analog_res_perl2querytrace_1 select * from analog_res_perl2querytrace; commit; rename analog_res_perl2querytrace to analog_res_perl2querytrace_2; rename analog_res_perl2querytrace_1 to analog_res_perl2querytrace; analyze table analog_res_perl2querytrace compute statistics; grant select on analog_res_perl2querytrace to rnd;
动增加表分区：
1、以oracle用户进入cn_logdb
2、“crontab -l”可以查看当前运行了哪些程序
3、/home/oracle/admin/kevin/add_partition.sh自动添加一个月的表分区（每天一个分区）
add_partition.sh脚本主要是靠add_partition函数起作用。
SQL> select text from all_source where name = upper('add_partition')
TEXT
-----------------------------------------------------------------------------------------------------------------------------
FUNCTION add_partition
(
months VARCHAR2 DEFAULT TO_CHAR(ADD_MONTHS(SYSDATE,1),'yyyymm')
)
RETURN CLOB
AS
CURSOR to_add IS
SELECT OWNER,SEGMENT_NAME,TABLESPACE_NAME,MAX(PARTITION_NAME)
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE='TABLE PARTITION'
AND owner not in ('SYS','SYSTEM','TEST','SH','HRODS')
AND SEGMENT_NAME NOT LIKE '%HIS'
AND SEGMENT_NAME NOT LIKE '%HISTORY'
AND TABLESPACE_NAME NOT LIKE '%HIS'
AND TABLESPACE_NAME NOT LIKE '%HISTORY'
AND (OWNER,SEGMENT_NAME,PARTITION_NAME) IN (SELECT OWNER,SEGMENT_NAME,MAX(PARTITION_NAME)
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE='TABLE PARTITION'
AND owner not in ('SYS','SYSTEM','TEST','SH','HRODS')
AND SEGMENT_NAME NOT LIKE '%HIS'
AND SEGMENT_NAME NOT LIKE '%HISTORY'
AND TABLESPACE_NAME NOT LIKE '%HIS'
AND TABLESPACE_NAME NOT LIKE '%HISTORY'
GROUP BY owner,SEGMENT_NAME)
--AND SEGMENT_NAME IN ('IM_USER_FATDT0','IM_ACTIVE_DAILY') -- ?è??im_user_fatdt0 IM_ACTIVE_DAILY???óè?·???
GROUP BY owner,SEGMENT_NAME,TABLESPACE_NAME;
CURSOR add_day IS
SELECT YYYYMMDD, NEXT_YYYYMMDD
FROM pub.times
WHERE CAL_YYYYMM=months
ORDER BY 1;
partition_column_type VARCHAR2(20);
part_sqlstr1 VARCHAR2(100);
part_sqlstr2 VARCHAR2(100);
sqlstr VARCHAR2(32767);
errormsg CLOB;
TYPE varray_type IS VARRAY(1000) OF VARCHAR2(600);
sqlvar varray_type;
BEGIN
FOR l_c IN to_add LOOP
SELECT DATA_TYPE
INTO partition_column_type
FROM DBA_TAB_COLUMNS a,
DBA_PART_KEY_COLUMNS b
WHERE a.OWNER=b.OWNER
AND a.TABLE_NAME=b.NAME
AND a.COLUMN_NAME=b.COLUMN_NAME
AND a.owner=l_c.OWNER
AND a.TABLE_NAME=l_c.SEGMENT_NAME;
IF partition_column_type='DATE'
THEN
part_sqlstr1 := 'TO_DATE(''';
part_sqlstr2 := ''',''YYYYMMDD'')) tablespace '||l_c.TABLESPACE_NAME ;
ELSIF partition_column_type='NUMBER'
THEN
part_sqlstr1 := '';
part_sqlstr2 := ') tablespace '||l_c.TABLESPACE_NAME ;
ELSE
part_sqlstr1 := '''';
part_sqlstr2 := ''') tablespace '||l_c.TABLESPACE_NAME ;
END IF;
FOR l_d IN add_day LOOP
sqlstr:='alter table '||l_c.owner||'.'
||l_c.SEGMENT_NAME||' add partition p'||L_D.YYYYMMDD
||' values less than ('
||part_sqlstr1
||L_D.NEXT_YYYYMMDD
||part_sqlstr2;
BEGIN
exec_ddl(sqlstr);
EXCEPTION
WHEN OTHERS THEN
errormsg:=errormsg||l_c.SEGMENT_NAME||'.'||L_D.YYYYMMDD||chr(10)||SQLCODE;
END;
END LOOP;
END LOOP;
RETURN errormsg;
END;
查看某个特定的表分区情况：
1、同上目录
2、sh partition.sh analog_res_perl2querytrace
如下运行情况：
[oracle@oracle-rac1 kevin]$ sh par* analog_res_perl2querytrace
SQL*Plus: Release 10.1.0.3.0 - Production on Fri Dec 28 20:15:31 2007
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6
OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME SUMBLOCK
-------- ------------------ -------------------------------- -------------------- ------------------------- ----------
CNWEBLOG INDEX IDX_ANALOG_RES_PERL2QUERYTRACE CNWEBLOG_BIDX_DW 2688
CNWEBLOG TABLE ANALOG_RES_PERL2QUERYTRACE_2 CNWEBLOG_RES_DW 2176
CNWEBLOG TABLE PARTITION ANALOG_RES_PERL2QUERYTRACE P200505 CNWEBLOG_RES_DW 32
CNWEBLOG TABLE PARTITION ANALOG_RES_PERL2QUERYTRACE P200506 CNWEBLOG_RES_DW 32
CNWEBLOG TABLE PARTITION ANALOG_RES_PERL2QUERYTRACE P200507 CNWEBLOG_RES_DW 32
CNWEBLOG TABLE PARTITION ANALOG_RES_PERL2QUERYTRACE P200508 CNWEBLOG_RES_DW 32
CNWEBLOG TABLE PARTITION ANALOG_RES_PERL2QUERYTRACE P200509 CNWEBLOG_RES_DW 32
CNWEBLOG TABLE PARTITION ANALOG_RES_PERL2QUERYTRACE P200510 CNWEBLOG_RES_DW 32
CNWEBLOG TABLE PARTITION ANALOG_RES_PERL2QUERYTRACE P200511 CNWEBLOG_RES_DW 32
CNWEBLOG TABLE PARTITION ANALOG_RES_PERL2QUERYTRACE P200512 CNWEBLOG_RES_DW 32
CNWEBLOG TABLE PARTITION ANALOG_RES_PERL2QUERYTRACE P200601 CNWEBLOG_RES_DW 32
CNWEBLOG TABLE PARTITION ANALOG_RES_PERL2QUERYTRACE P200602 CNWEBLOG_RES_DW 32
CNWEBLOG TABLE PARTITION ANALOG_RES_PERL2QUERYTRACE P200603 CNWEBLOG_RES_DW 32
