oracle数据库自动按分区号清除数据的脚本

大致思路是由DBA_TAB_PARTITIONS查询TABLE_NAME和HIGH_VALUE，来确定PARTION_NAME，通过SQL生成alter table语句。

由于DBA_TAB_PARTIONS的HIGH_VALUE字段是LONG，这里创建一个自定义包long_help，将LONG转换为VARCHAR2类型。

create or replace package long_help

authid current_user

as

function substr_of

( p_query in varchar2,

p_from  in number,

p_for   in number,

p_name1 in varchar2 default NULL,

p_bind1 in varchar2 default NULL,

p_name2 in varchar2 default NULL,

p_bind2 in varchar2 default NULL,

p_name3 in varchar2 default NULL,

p_bind3 in varchar2 default NULL,

p_name4 in varchar2 default NULL,

p_bind4 in varchar2 default NULL )

return varchar2

end

/

create or replace package body long_help

as

    g_cursor number := dbms_sql.open_cursor

    g_query  varchar2(32765)

procedure bind_variable( p_name in varchar2, p_value in varchar2 )

is

begin

    if ( p_name is not null )

    then

        dbms_sql.bind_variable( g_cursor, p_name, p_value )

    end if

END

FUNCTION substr_of

( p_query in varchar2,

  p_from  in number,

  p_for   in number,

  p_name1 in varchar2 default NULL,

  p_bind1 in varchar2 default NULL,

  p_name2 in varchar2 default NULL,

  p_bind2 in varchar2 default NULL,

  p_name3 in varchar2 default NULL,

  p_bind3 in varchar2 default NULL,

  p_name4 in varchar2 default NULL,

  p_bind4 in varchar2 default NULL )

return varchar2

as

    l_buffer       varchar2(4000)

    l_buffer_len   number

begin

    if ( nvl(p_from,0) <= 0 )

    then

        raise_application_error

        (-20002, 'From must be >= 1 (positive numbers)' )

    end if

    if ( nvl(p_for,0) not between 1 and 4000 )

    then

        raise_application_error

        (-20003, 'For must be between 1 and 4000' )

    end if

    if ( p_query <> g_query or g_query is NULL )

    then

        if ( upper(trim(nvl(p_query,'x'))) not like 'SELECT%')

        then

            raise_application_error

            (-20001, 'This must be a select only' )

        end if

        dbms_sql.parse( g_cursor, p_query, dbms_sql.native )

        g_query := p_query

    end if

    bind_variable( p_name1, p_bind1 )

    bind_variable( p_name2, p_bind2 )

    bind_variable( p_name3, p_bind3 )

    bind_variable( p_name4, p_bind4 )

    dbms_sql.define_column_long(g_cursor, 1)

    if (dbms_sql.execute_and_fetch(g_cursor)>0)

    then

        dbms_sql.column_value_long

        (g_cursor, 1, p_for, p_from-1,

         l_buffer, l_buffer_len )

    end if

    return l_buffer

end substr_of

END

/

创建一个分区表PART_TABLE，插入三条记录分别在三个分区中。

CREATE TABLE PART_TABLE

(

   TABLE_ID NUMBER(8),

   SUB_DATE DATE

)

PARTITION BY RANGE(SUB_DATE)

INTERVAL(NUMTODSINTERVAL(1，'DAY'))

(

  PARTITION P1 VALUES LESS THAN(TO_DATE('2017-12-25','YYYY-MM-DD'))

)

INSERT INTO PART_TABLE VALUES(1,DATE '2017-12-24')

INSERT INTO PART_TABLE VALUES(1,DATE '2017-12-25')

INSERT INTO PART_TABLE VALUES(1,DATE '2017-12-26')

COMMIT

通过SQL生成alter table语句，截断第一个分区。

这里需要注意查询条件是HIGH_VALUE，条件为2017-12-25时，截断的是最大值为2017-12-25的分区。

SELECT 'alter table PART_TABLE truncate partition ' || PARTITION_NAME ||

       ' update INDEXES'

  FROM (SELECT TABLE_OWNER,

                TABLE_NAME,

                PARTITION_NAME,

                LONG_HELP.SUBSTR_OF('SELECT HIGH_VALUE

FROM   DBA_TAB_PARTITIONS WHERE TABLE_OWNER=:TABLE_OWNER 

AND TABLE_NAME=:TABLE_NAME

AND PARTITION_NAME=:PARTITION_NAME', 1, 4000, 'TABLE_OWNER', TABLE_OWNER, 'TABLE_NAME', TABLE_NAME, 'PARTITION_NAME', PARTITION_NAME) HIGH_VALUE

           FROM DBA_TAB_PARTITIONS)

 WHERE TABLE_NAME = 'PART_TABLE'

   AND HIGH_VALUE LIKE '%2017-12-25%'

在一个DB2 实例下,可以创建多个database(具体每个实例下可以创建多少个database,可以通过DB2 GET DBM CFG查看.一旦建立了数据库之后,就可以对其进行 *** 作,然而,在向数据库中建立对象或访问数据库中的对象(数据)之前,必须先知道如何启动和停止数据库.

本小节简单介绍了db2 database通过command line的方式如何实现启动和停止.

激活数据库

如果在database没有激活之前,就在应用中使用connect to database_name或隐式连接,那么应用就必须要进行等待,知道数据库管理器启动了你要连接的数据库.一般第一个应用会引发等待数据库管理器执行数据库启动的所有开销.

我们也可以使用activate database database_name这样的命令启动特定的数据库.这个命令就会免除第一个应用程序连接上来的时候等候数据库初始化所花费的时间。一般情况下,DB2 DBA会提前激活所有必要的数据库.

注意:1)如果发出activate database命令的应用已经与一个database建立了活动的数据库连接,就回返回错误消息.

2)如果需要重新启动(restart)的数据库接受了一个activate database命令，那么这个activate database命令就会象connect to 或隐式连接一样工作.

3)如果数据库配置了

启用的自动重新启动 (AUTORESTART) = ON

那么在activate database命令对其初始化之前,数据库会重新启动.

察看指定数据库的配置参数

--默认

DB2 GET DB CFG FOR study

--分屏显示

DB2 GET DB CFG FOR study | More

--将显示结果存入study.txt 文件中

DB2 GET DB CFG FOR study >study.txt

下面是一个示例,我在当前DB2实例下，创建了3个database,我利用activate命令挨个将他们激活

D:DB2BIN>db2 db2start

DB20000I DB2START命令成功完成。

D:DB2BIN>db2 list database directory

系统数据库目录

目录中的条目数 = 3

数据库 1 条目：

数据库别名 = TEST

数据库名称 = TEST

本地数据库目录 = D:DB2test

数据库发行版级别 = c.00

注释 = test

目录条目类型= 间接

目录数据库分区号 = 0

备用服务器主机名 =

备用服务器端口号 =

数据库 2 条目：

数据库别名 = BANK

数据库名称 = BANK

本地数据库目录 = D:db2bank

数据库发行版级别 = c.00

注释 = bank

目录条目类型 = 间接

目录数据库分区号 = 0

备用服务器主机名 =

备用服务器端口号 =

数据库 3 条目：

数据库别名 = STUDY

数据库名称 = STUDY

本地数据库目录 = D:db2study

数据库发行版级别 = c.00

注释 = STUDY

目录条目类型= 间接

目录数据库分区号 = 0

备用服务器主机名 =

备用服务器端口号 =

D:DB2BIN>db2 list active databases

SQL1611W “数据库系统监视器”没有返回任何数据。

D:DB2BIN>db2 activate database study

DB20000I ACTIVATE DATABASE命令成功完成。

D:DB2BIN>db2 list active databases

活动数据库

数据库名称 = STUDY

当前连接的应用程序= 0

数据库路径 = D:db2studyDB2NODE0000SQL00001

D:DB2BIN>db2 activate database bank

DB20000I ACTIVATE DATABASE命令成功完成。

D:DB2BIN>db2 list active databases

活动数据库

数据库名称 = STUDY

当前连接的应用程序= 0

数据库路径 = D:db2studyDB2NODE0000SQL00001

数据库名称 = BANK

当前连接的应用程序= 0

数据库路径 = D:db2bankDB2NODE0000SQL00001

D:DB2BIN>db2 activate database test

DB20000I ACTIVATE DATABASE命令成功完成。

D:DB2BIN>db2 list active databases

活动数据库

数据库名称 = STUDY

当前连接的应用程序= 0

数据库路径 = D:db2studyDB2NODE0000SQL00001

数据库名称 = BANK

当前连接的应用程序= 0

数据库路径 = D:db2bankDB2NODE0000SQL00001

数据库名称 = TEST

当前连接的应用程序= 0

数据库路径 = D:DB2testDB2NODE0000SQL00001

D:DB2BIN>停止数据库

由activate database命令初始化的数据库可以由deactivate database命令关闭,也可以通过stop database manager(或db2stop)命令终止数据库管理器来关闭.

如果使用activate

database命令初始化一个数据库,那么最后一个与数据库断开连接的应用就不会关闭数据库.必须要使用deactivate

database.在这种情况下,stop database manager(或db2stop)会关闭数据库,也会终止实例.

如果使用connect

to 或隐式地连接启动数据库,然后再由另外一个应用对相同的数据库发出activate database命令，那么就必须使用deactivate

database命令关闭数据库.如果没有使用activate

database启动数据库,那么当最后一个应用断开与数据库连接的时候,数据库就会关闭.

示例一:使用connect to方式启动的数据库

D:DB2BIN>db2 list active databases

SQL1611W “数据库系统监视器”没有返回任何数据。

D:DB2BIN>db2 connect to study --连接到study数据库,将自动激活study数据库

数据库连接信息

数据库服务器 = DB2/NT 9.5.0

SQL 授权标识 = ZHANGRP

本地数据库别名 = STUDY

D:DB2BIN>db2 list active databases

活动数据库

数据库名称 = STUDY

当前连接的应用程序 = 1

数据库路径 = D:db2studyDB2NODE0000SQL00001

说明:数据库被激活,并有一个应用连接上来(就上面的connect to study *** 作)

D:DB2BIN>db2 connect reset --断开连接

DB20000I SQL命令成功完成。

D:DB2BIN>

D:DB2BIN>db2 list active databases

SQL1611W “数据库系统监视器”没有返回任何数据。

D:DB2BIN>

说明:可以发现,上面的连接断开后,数据库study也自动停止(因为这个study数据库是通过connect to 的方式激活的)

示例二:使用activate database方式启动的数据库

D:DB2BIN>db2 list active databases --这个时候没有启动的数据库

SQL1611W “数据库系统监视器”没有返回任何数据。

D:DB2BIN>db2 activate database study--用activate database激活study数据库

DB20000I ACTIVATE DATABASE命令成功完成。

D:DB2BIN>db2 list active databases --可以看到study数据已经激活,但没有应用连接上来

活动数据库

数据库名称 = STUDY

当前连接的应用程序= 0

数据库路径 = D:db2studyDB2NODE0000SQL00001

D:DB2BIN>db2 connect to study --发起一个连接到study数据库

数据库连接信息

数据库服务器 = DB2/NT 9.5.0

SQL 授权标识 = ZHANGRP

本地数据库别名 = STUDY

D:DB2BIN>db2 list active databases --再看一下活动的数据库,发现study 数据库上有一个应用连接上来了

活动数据库

数据库名称 = STUDY

当前连接的应用程序= 1

数据库路径 = D:db2studyDB2NODE0000SQL00001

D:DB2BIN>db2 connect reset --关闭刚才那个连接,看看数据库是不是会自动关闭

DB20000I SQL命令成功完成。

D:DB2BIN>db2 list active databases --数据库依然启动,虽然没有了任何连接

活动数据库

数据库名称 = STUDY

当前连接的应用程序= 0

数据库路径 = D:db2studyDB2NODE0000SQL00001

D:DB2BIN>db2 deactivate database study --使用deactivate database命令关闭数据库

DB20000I DEACTIVATE DATABASE命令成功完成。

D:DB2BIN>

D:DB2BIN>db2 list active databases

SQL1611W “数据库系统监视器”没有返回任何数据。

D:DB2BIN>

---