Shulou(Shulou.com)06/01 Report--

The following are test examples of automatically deleting, creating stored procedures for partitioned child tables, and maintaining partitioned tables:

1. Add a partition child table at a fixed time every month

Create or replace procedure addpartition (sTable_name varchar (100)) is

Declare

Highvalue varchar (50)

Partition_name varchar (50)

Month_name varchar (2)

Year_name varchar (4)

SSql varchar (1000)

PreSql varchar (1000)

PosSql varchar (1000)

Begin

Select substr (max (high_value), 10 Magi 10) into highvalue from all_tab_partitions where table_name=sTable_name and table_owner=' TEST 'and HIGH_VALUE like' DATETIME%'

-print highvalue

Year_name = datepart (year,highvalue)

If datepart (month,highvalue) can free disk space by manually emptying the undo segment. The stored procedure is as follows:

Create or replace procedure remove_space ()

As

Begin

EXECUTE IMMEDIATE 'alter system set' 'UNDO_RETENTION''=1;'

Dbms_lock.sleep (15);-time can be set according to the actual situation

EXECUTE IMMEDIATE 'alter system set' 'UNDO_RETENTION''=900;'

End

5. Test example:

-1Mel-create the partition table ET_AIRPORTCONTROL for the test

CREATE TABLE "ET_AIRPORTCONTROL"

(

"PURGE_DAT" TIMESTAMP (0)

"LKEY" NUMBER (16jin0) NOT NULL

"TKNB" CHAR (13) NOT NULL

"CNBR" NUMBER (3jin0) NOT NULL

"ACAL" CHAR (3)

ASAC CHAR (16)

"TEXT" VARCHAR2

PARTITION BY RANGE ("PURGE_DAT")

(

PARTITION "Please 201505" VALUES LESS THAN (DATETIME'2015-06-01 0000 STORAGE (ON "MAIN", CLUSTERBTR)

PARTITION "Please 201506" VALUES LESS THAN (DATETIME'2015-07-01 0000 STORAGE (ON "MAIN", CLUSTERBTR)

PARTITION "ON 201507" VALUES LESS THAN (ON "MAIN", CLUSTERBTR)

PARTITION "Please 201508" VALUES LESS THAN (DATETIME'2015-09-01 0000 STORAGE (ON "MAIN", CLUSTERBTR)

PARTITION "ON 201509" VALUES LESS THAN (ON "MAIN", CLUSTERBTR)

PARTITION "Please 201510" VALUES LESS THAN (DATETIME'2015-11-01 0000 STORAGE (ON "MAIN", CLUSTERBTR)

PARTITION "Please 201511" VALUES LESS THAN (DATETIME'2015-12-01 0000 STORAGE (ON "MAIN", CLUSTERBTR)

PARTITION "Please 201512" VALUES LESS THAN (DATETIME'2016-01-01 0000 STORAGE (ON "MAIN", CLUSTERBTR)

PARTITION "Please 201601" VALUES LESS THAN (DATETIME'2016-02-01 0000 STORAGE (ON "MAIN", CLUSTERBTR)

PARTITION "ON 201602" VALUES LESS THAN (ON "MAIN", CLUSTERBTR)

PARTITION "ON 201603" VALUES LESS THAN (ON "MAIN", CLUSTERBTR)

PARTITION "ON 201604" VALUES LESS THAN (ON "MAIN", CLUSTERBTR)

PARTITION "ON 201605" VALUES LESS THAN (ON "MAIN", CLUSTERBTR)

PARTITION "Please 201606" VALUES LESS THAN (DATETIME'2016-07-01 0000 STORAGE (ON "MAIN", CLUSTERBTR)

PARTITION "ON 201607" VALUES LESS THAN (ON "MAIN", CLUSTERBTR)

PARTITION "ON 201608" VALUES LESS THAN (ON "MAIN", CLUSTERBTR)

PARTITION "ON 201609" VALUES LESS THAN (ON "MAIN", CLUSTERBTR)

PARTITION "Please 201610" VALUES LESS THAN (DATETIME'2016-11-01 0000 STORAGE (ON "MAIN", CLUSTERBTR)

PARTITION "Please 201611" VALUES LESS THAN (DATETIME'2016-12-01 0000 STORAGE (ON "MAIN", CLUSTERBTR)

PARTITION "Please 201612" VALUES LESS THAN (DATETIME'2017-01-01 0000 STORAGE (ON "MAIN", CLUSTERBTR)

PARTITION "ON 201701" VALUES LESS THAN (ON "MAIN", CLUSTERBTR)

PARTITION "ON 201702" VALUES LESS THAN (ON "MAIN", CLUSTERBTR)

PARTITION "ON 201703" VALUES LESS THAN (ON "MAIN", CLUSTERBTR)

PARTITION "ON 201704" VALUES LESS THAN (ON "MAIN", CLUSTERBTR)

PARTITION "Please 201705" VALUES LESS THAN (DATETIME'2017-06-01 0000 STORAGE (ON "MAIN", CLUSTERBTR)

PARTITION "Please 201706" VALUES LESS THAN (DATETIME'2017-07-01 0000 STORAGE (ON "MAIN", CLUSTERBTR)

PARTITION "ON 201707" VALUES LESS THAN (ON "MAIN", CLUSTERBTR)

PARTITION "ON 299999" VALUES LESS THAN (MAXVALUE) STORAGE (ON "MAIN", CLUSTERBTR)

) STORAGE (ON "MAIN", CLUSTERBTR)

-2mer-create a stored procedure

-1 > add partitions regularly

Create or replace

Procedure addpartition

(sTable_name varchar)

Is

Declare

Highvalue varchar (50)

Partition_name varchar (50)

Month_name varchar (2)

Year_name varchar (4)

SSql varchar (1000)

PreSql varchar (1000)

PosSql varchar (1000)

Begin

Select

Substr (max (high_value), 10,10)

Into

Highvalue

From

All_tab_partitions

Where

Table_name = sTable_name

The table_owner in the and table_owner='SYSDBA'-statement can be modified to specify the schema name

And HIGH_VALUE like 'DATETIME%'

-print highvalue

Year_name = datepart (year, highvalue)

If datepart (month, highvalue) deletes partitions regularly

Create or replace procedure delpartition

(sTable_name varchar)

Is

Declare

Cursor del_part_name

Highvalue varchar (50)

Partt_name varchar (50)

DelSql varchar (500)

Begin

Select

Substr (max (high_value), 10,10)

Into

Highvalue

From

All_tab_partitions

Where

Table_name = sTable_name

And table_owner='SYSDBA'-the table_owner in the statement can be modified to specify the schema name

And HIGH_VALUE like 'DATETIME%'

-the number of reserved partition child tables can be defined here, excluding the default partition child table

Highvalue = ADD_MONTHS (highvalue,-13)

-print highvalue

-the table_owner in the statement can be modified to specify the schema name

Open del_part_name for select partition_name from all_tab_partitions where table_name=sTable_name and table_owner='SYSDBA' and substr (high_value, 10, 10) to view the partition child table information of ET_AIRPORTCONTROL

Select partition_name from all_tab_partitions where table_name='ET_AIRPORTCONTROL' and table_owner='SYSDBA' order by 1

/ *

P_201505

P_201506

P_201507

P_201508

P_201509

P_201510

P_201511

P_201512

P_201601

P_201602

P_201603

P_201604

P_201605

P_201606

P_201607

P_201608

P_201609

P_201610

P_201611

P_201612

P_201701

P_201702

P_201703

P_201704

P_201705

P_201706

P_201707

P_299999

/

Select count () from all_tab_partitions where table_name='ET_AIRPORTCONTROL' and table_owner='SYSDBA' order by 1

/ 28/

-2 > call the delete partition stored procedure, and the number of reserved partitions can be defined in the stored procedure

Call SYSDBA. "DELPARTITION" ('ET_AIRPORTCONTROL')

-3 > query the number of existing child tables and child tables of the ET_AIRPORTCONTROL partition table

Select partition_name from all_tab_partitions where table_name='ET_AIRPORTCONTROL' and table_owner='SYSDBA' order by 1

/ *

P_201607

P_201608

P_201609

P_201610

P_201611

P_201612

P_201701

P_201702

P_201703

P_201704

P_201705

P_201706

P_201707

P_299999

/

Select count () from all_tab_partitions where table_name='ET_AIRPORTCONTROL' and table_owner='SYSDBA' order by 1

/ 14/

-4 > call add partition stored procedure. Add one partition at a time by default

Call SYSDBA. "ADDPARTITION" ('ET_AIRPORTCONTROL')

-5 > query the number of existing child tables and child tables of the ET_AIRPORTCONTROL partition table

Select partition_name from all_tab_partitions where table_name='ET_AIRPORTCONTROL' and table_owner='SYSDBA' order by 1

/ *

P_201607

P_201608

P_201609

P_201610

P_201611

P_201612

P_201701

P_201702

P_201703

P_201704

P_201705

P_201706

P_201707

P_201708

P_299999

/

Select count () from all_tab_partitions where table_name='ET_AIRPORTCONTROL' and table_owner='SYSDBA' order by 1

/ 15/

-6 > backup partition child tables to be deleted

-you can use logical import and export for backup, but it is required that the partition child table to be restored must exist and cannot be dropped by drop, otherwise a normal table will be created in the current mode by default to restore:

-1-dexp ET_AIRPORTCONTROL_P_ 201601 subtable

Dexp USERID=SYSDBA/sysdb1:5236 FILE=ET_AIRPORTCONTROL_P_201601.dmp LOG=ET_AIRPORTCONTROL_P_201601_dexp.log TABLES=OETHIS_TV.ET_AIRPORTCONTROL_P_201601 directory=/dmdb/backup_hisopenet/test

-2-truncate table

Select count () from OETHIS_TV.ET_AIRPORTCONTROL_P_201601

/

16556

, /

Truncate table OETHIS_TV.ET_AIRPORTCONTROL_P_201601

Select count (*) from OETHIS_TV.ET_AIRPORTCONTROL_P_201601

/ 0/

-3-dimp ET_AIRPORTCONTROL_P_201601 subtable

Dimp USERID=SYSDBA/sysdb1:5236 FILE=ET_AIRPORTCONTROL_P_201601.dmp LOG=ET_AIRPORTCONTROL_P_201601_dimp.log TABLES=OETHIS_TV.ET_AIRPORTCONTROL_P_201601 directory=/dmdb/backup_hisopenet/test TABLE_EXISTS_ACTION=append EXCLUDE=INDEXES

-Table building errors and index creation errors need to be ignored here. You can specify the TABLE_EXISTS_ACTION=append EXCLUDE=INDEXES parameter.

-4-check whether the data is correct and whether the index is invalid

Select count () from OETHIS_TV.ET_AIRPORTCONTROL_P_201601

/

16556

, /

Select name,valid from "SYSOBJECTS" where valid ='N'

-NULL

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.