DBA实用操作技巧

最新推荐文章于 2023-01-10 15:26:40 发布

weixin_34311757

最新推荐文章于 2023-01-10 15:26:40 发布

阅读量108

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/include/archive/2010/11/14/1877179.html

版权

[表空间以及表分区相关操作]

查看临时表空间大小：

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

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

转载于:https://www.cnblogs.com/include/archive/2010/11/14/1877179.html