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

First, query the tables in the database that need to be reorganized:

SELECT d.name.dbid.meme. Currentmbwp. Networkmb as save_mb,round ((p.CURR_MB-p.NET_MB) / p.CURR_MB*100) AS PERCENTAGE

FROM

(SELECT t.owner owner,SUBSTR (t.TABLECTONAMEMAL 1pje 50) TABLE_NAME,t.tablespace_name,t.NUM_ROWS,t.AVG_ROW_LEN ROWLEN

ROUND (BLOCKS*8192/1024/1024,0) CURR_MB

ROUND ((t.AVG_ROW_LEN) * t.NUM_ROWS/1024/1024) / (1-t.pct_free/100), 0) NET_MB

FROM DBA_TABLES t WHERE table_name not like 'BIN$%' and NUM_ROWS IS NOT NULL AND PARTITIONED =' NO' AND (IOT_TYPE! = 'IOT' OR IOT_TYPE IS NULL))

WHERE

CURR_MB! = 0 AND

OWNER NOT IN ('SYS','SYSMAN') AND

Round ((p.CURR_MB-p.NET_MB) / p.CURR_MB*100) > 20 and

P.curr_mb-p.net_mb > 1024

ORDER BY 10,11

In this test environment, assuming that the COSTS table under SH users needs to be reorganized, you can use the following methods to reorganize the table:

SELECT default_tablespace from dba_users where username='SH'

SELECT BLOCKS, EMPTY_BLOCKS,TABLESPACE_NAME FROM DBA_TABLES WHERE TABLE_NAME = 'COSTS' and OWNER='SH'

SELECT sum (bytes) / 1024 Universe 1024 from dba_free_space group by tablespace_name having tablespace_name='USERS'

SELECT INDEX_NAME,INDEX_TYPE,TABLE_NAME,TABLE_TYPE, TABLESPACE_NAME,status,DEGREE from dba_indexes where TABLE_NAME='COSTS' and table_owner='SH'

Select TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE,STATUS,DEFERRED,VALIDATED,INDEX_NAME from DBA_CONSTRAINTS WHERE TABLE_NAME = 'COSTS' and OWNER='SH'

SELECT * from dba_dependencies where name='COSTS' AND OWNER='SH'

SH.COSTS

Check whether it can be redefined

BEGIN

DBMS_REDEFINITION.CAN_REDEF_TABLE ('SH','COSTS'

DBMS_REDEFINITION.CONS_USE_ROWID)

END

/

Create an int table

Create table SH.COSTS_XXXX

(

Prod_id NUMBER not null

Time_id DATE not null

Promo_id NUMBER not null

Channel_id NUMBER not null

Unit_cost NUMBER (10dint 2) not null

Unit_price NUMBER (10dint 2) not null

)

Partition by range (TIME_ID)

(

Partition COSTS_X122995 values less than (TO_DATE ('1996-01-01 00 SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN'))

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Partition COSTS_XX12996 values less than (TO_DATE ('1997-01-01 00 SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN'))

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Partition COSTS_XXH1_1997 values less than (TO_DATE ('1997-07-01 00 SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN'))

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Partition COSTS_XH2_1997 values less than (TO_DATE ('1998-01-01 00 SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN'))

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Partition COSTS_XQ1_1998 values less than (TO_DATE ('1998-04-01 00 SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN')

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Storage

(

Initial 8M

Next 1M

Minextents 1

Maxextents unlimited

),

Partition COSTS_XQ2_1998 values less than (TO_DATE ('1998-07-01 00 SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN'))

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Storage

(

Initial 8M

Next 1M

Minextents 1

Maxextents unlimited

),

Partition COSTS_XQ3_1998 values less than (TO_DATE ('1998-10-01 00 SYYYY-MM-DD HH24:MI:SS', NLS_CALENDAR=GREGORIAN'))

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Storage

(

Initial 8M

Next 1M

Minextents 1

Maxextents unlimited

),

Partition COSTS_XQ4_1998 values less than (TO_DATE ('1999-01-01 00 SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN'))

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Storage

(

Initial 8M

Next 1M

Minextents 1

Maxextents unlimited

),

Partition COSTS_XQ1_1999 values less than (TO_DATE ('1999-04-01 00 SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN')

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Storage

(

Initial 8M

Next 1M

Minextents 1

Maxextents unlimited

),

Partition COSTS_XQ2_1999 values less than (TO_DATE ('1999-07-01 00 SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN'))

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Storage

(

Initial 8M

Next 1M

Minextents 1

Maxextents unlimited

),

Partition COSTS_XQ3_1999 values less than (TO_DATE ('1999-10-01 00 SYYYY-MM-DD HH24:MI:SS', NLS_CALENDAR=GREGORIAN'))

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Storage

(

Initial 8M

Next 1M

Minextents 1

Maxextents unlimited

),

Partition COSTS_XQ4_1999 values less than (TO_DATE ('2000-01-01 00 SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN'))

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Storage

(

Initial 8M

Next 1M

Minextents 1

Maxextents unlimited

),

Partition COSTS_XQ1_2000 values less than (TO_DATE ('2000-04-01 00 SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN')

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Storage

(

Initial 8M

Next 1M

Minextents 1

Maxextents unlimited

),

Partition COSTS_XQ2_2000 values less than (TO_DATE ('2000-07-01 00 SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN'))

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Storage

(

Initial 8M

Next 1M

Minextents 1

Maxextents unlimited

),

Partition COSTS_XQ3_2000 values less than (TO_DATE ('2000-10-01 00 SYYYY-MM-DD HH24:MI:SS', NLS_CALENDAR=GREGORIAN'))

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Storage

(

Initial 8M

Next 1M

Minextents 1

Maxextents unlimited

),

Partition COSTS_XQ4_2000 values less than (TO_DATE ('2001-01-01 00 SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN'))

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Storage

(

Initial 8M

Next 1M

Minextents 1

Maxextents unlimited

),

Partition COSTS_XQ1_2001 values less than (TO_DATE ('2001-04-01 00 SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN')

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Storage

(

Initial 8M

Next 1M

Minextents 1

Maxextents unlimited

),

Partition COSTS_XQ2_2001 values less than (TO_DATE ('2001-07-01 00 SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN'))

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Storage

(

Initial 8M

Next 1M

Minextents 1

Maxextents unlimited

),

Partition COSTS_XQ3_2001 values less than (TO_DATE ('2001-10-01 00 SYYYY-MM-DD HH24:MI:SS', NLS_CALENDAR=GREGORIAN'))

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Storage

(

Initial 8M

Next 1M

Minextents 1

Maxextents unlimited

),

Partition COSTS_XQ4_2001 values less than (TO_DATE ('2002-01-01 00 SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN'))

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Storage

(

Initial 8M

Next 1M

Minextents 1

Maxextents unlimited

),

Partition COSTS_XQ1_2002 values less than (TO_DATE ('2002-04-01 00 SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN')

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Partition COSTS_XQ2_2002 values less than (TO_DATE ('2002-07-01 00 SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN'))

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Partition COSTS_XQ3_2002 values less than (TO_DATE ('2002-10-01 00 SYYYY-MM-DD HH24:MI:SS', NLS_CALENDAR=GREGORIAN'))

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Partition COSTS_XQ4_2002 values less than (TO_DATE ('2003-01-01 00 SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN'))

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Partition COSTS_XQ1_2003 values less than (TO_DATE ('2003-04-01 00 SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN')

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Partition COSTS_XQ2_2003 values less than (TO_DATE ('2003-07-01 00 SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN'))

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Partition COSTS_XQ3_2003 values less than (TO_DATE ('2003-10-01 00 SYYYY-MM-DD HH24:MI:SS', NLS_CALENDAR=GREGORIAN'))

Tablespace EXAMPLE

Pctfree 0

Initrans 1

Maxtrans 255

Partition COSTS_XQ4_2003 values less than (TO_DATE ('2004-01-01 00 SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN'))

Tablespace EXAMPLE

Pctfree 5

Initrans 1

Maxtrans 255

);

Start redefine

BEGIN

DBMS_REDEFINITION.START_REDEF_TABLE

(uname= > 'SH'

Orig_table= > 'COSTS'

Int_table= > 'COSTS_XXXX'

Options_flag= > DBMS_REDEFINITION.CONS_USE_ROWID)

End

/

Copy dependent object

DECLARE

Num_errors PLS_INTEGER

BEGIN

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('SH','COSTS','COSTS_XXXX'

DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, num_errors)

END

/

Check for errors

Select object_name,base_table_name,ddl_txt from DBA_REDEFINITION_ERRORS

SELECT INDEX_NAME,INDEX_TYPE,TABLE_NAME,TABLE_TYPE, TABLESPACE_NAME,status,DEGREE from dba_indexes where TABLE_NAME='COST_XXXX' and table_owner='SH'

Select TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE,STATUS,DEFERRED,VALIDATED,INDEX_NAME from DBA_CONSTRAINTS WHERE TABLE_NAME = 'COST_XXXX' and OWNER='SH'

Synchronize

BEGIN

Dbms_redefinition .sync _ interim_table ('SH','COSTS','COSTS_XXXX')

END

End redef

BEGIN

DBMS_REDEFINITION.FINISH_REDEF_TABLE ('SH','COSTS','COSTS_XXXX')

END

/

SELECT INDEX_NAME,INDEX_TYPE,TABLE_NAME,TABLE_TYPE, TABLESPACE_NAME,status,DEGREE from dba_indexes where TABLE_NAME='COSTS' and table_owner='SH'

Select TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE,STATUS,DEFERRED,VALIDATED,INDEX_NAME from DBA_CONSTRAINTS WHERE TABLE_NAME = 'COSTS' and OWNER='SH'

SELECT INDEX_NAME,INDEX_TYPE,TABLE_NAME,TABLE_TYPE, TABLESPACE_NAME,status,DEGREE from dba_indexes where TABLE_NAME='COSTS_XXXX' and table_owner='SH'

Select TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE,STATUS,DEFERRED,VALIDATED,INDEX_NAME from DBA_CONSTRAINTS WHERE TABLE_NAME = 'COSTS_XXXX' and OWNER='SH'

Collect statistics

BEGIN

DBMS_STATS.GATHER_TABLE_STATS (ownname = > 'SH'

Tabname = > 'COSTS'

Estimate_percent = > 30

Method_opt = > 'for all columns size repeat'

Degree = > 7

Cascade= > TRUE

);

END

/

Delete the int table

Drop table SH.COSTS_XXXX

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.