Partitioning a Non-Partitioned Oracle system - Page 2

1.  Create alter table statements to rename the non-partitioned tables using table name and owner:

v_Sql := 'Alter Table ' || v_Owner || '.' || v_Table || 
  ' Rename To ' || v_Table_Old;

Dbms_Output.Put_Line(v_Sql || ';');
Dbms_Output.Put_Line('');

Result:

Alter Table TEST_SCHEMA.NON_PARTITIONED Rename To NON_PARTITIONED_OLD;

2.  To create a DDL statement that reproduces the original table name plus a partitioning clause, use the dbms_metadata package to get the DDL definition of the non-partitioned table. Since we have already defined the transformation parameters above, all storage clauses, segment attributes and constraints will be suppressed. You can choose to not suppress storage clauses and segment attributes if you want to keep them as is. Table related constraints should not appear in dbms_metadata.get_ddl output, since all constraints will be reconstructed one-to-one for partitioned tables later in this process:

v_Ddl := Dbms_Metadata.Get_Ddl('TABLE', v_Table, v_Owner);
v_Sql := v_Ddl || p_Partitioning_Clause || ' Tablespace ' || 
  p_Data_Tablespace || ' Parallel 8';

Dbms_Output.Put_Line(v_Sql || ';');
Dbms_Output.Put_Line('');

Result:

CREATE TABLE "TEST_SCHEMA"."NON_PARTITIONED" 
   (	"TEST" NUMBER, 
	"CREATED_DATE" DATE DEFAULT sysdate, 
	"NAME" VARCHAR2(10) DEFAULT 'TEST'
   ) 
PARTITION BY RANGE (CREATED_DATE)  
  (PARTITION p_max VALUES LESS THAN(Maxvalue)) 
Tablespace TABLESPACE_DATA Parallel 8;

3.  Copy records from the original table to the newly partitioned tables. We have used the APPEND hint to improve performance during this step. You also can use the NOLOGGING clause so that it will not write information into the log file. When using the NOLOGGING clause, remember to be careful so you don’t cause problems for your backup procedures.

v_Sql := 'Insert /*+ APPEND*/ Into ' || v_Owner || '.' || 
  v_Table || ' (Select * From ' ||
		 v_Owner || '.' || v_Table_Old || ')';
Dbms_Output.Put_Line(v_Sql || ';');
Dbms_Output.Put_Line('');

Result:

Insert /*+ APPEND*/ Into TEST_SCHEMA.NON_PARTITIONED 
  (Select * From TEST_SCHEMA.NON_PARTITIONED_OLD);

4.  Adding comments to your tables is an important part of database design because it provides additional information about tables and columns to developers and DBAs. If you are following good practices like this, use the script below to reproduce the comments that exist for the table. We have used information stored in dba_tab_comments view to recreate a table comment:

For i In (Select Owner, Table_Name, Comments
			From Dba_Tab_Comments
		   Where Owner = v_Owner
			 And Table_Name = v_Table) Loop
	v_Sql := 'Comment On Table  ' || v_Owner || '.' || 
	   v_Table || ' Is ''' ||
		 Replace(i.Comments, '''', '''''') || '''';

	Dbms_Output.Put_Line(v_Sql || ';');
	Dbms_Output.Put_Line('');
End Loop;

Result:

Comment On Table  TEST_SCHEMA.NON_PARTITIONED Is 'table for testing script';

5.  To get the columns comments, we are using dba_col_comments view. Based on information stored in that view, we can recreate column comments:

For i In (Select Owner, Table_Name, Column_Name, Comments
			From Dba_Col_Comments
		   Where Owner = v_Owner
			 And Table_Name = v_Table) Loop
	v_Sql := 'Comment On Column  ' || v_Owner || '.' || v_Table || '.' || i.Column_Name ||
			 ' Is ''' || Replace(i.Comments, '''', '''''') || '''';

	Dbms_Output.Put_Line(v_Sql || ';');
	Dbms_Output.Put_Line('');
End Loop;

Result:

Comment On Column  TEST_SCHEMA.NON_PARTITIONED.TEST Is 'column for testing script';

Comment On Column  TEST_SCHEMA.NON_PARTITIONED.CREATED_DATE Is '';

Comment On Column  TEST_SCHEMA.NON_PARTITIONED.NAME Is '';

6.  The script below provides the functionality to recreate constraints for the new table, based on database view dba_constraints. This view provides information about all existing constraints for non-partitioned tables. If the constraints were named by the user, then we can rename such constraints for non-partitioned tables and create exactly the same constraint for the partitioned table. If the constraint is generated by the system, as are many of them we are getting for Not Null constraints, then the best way to reproduce them for partitioned tables is to use dbms_metadata.get_ddl.

In the case of a Unique and Primary key constraint, we have a situation where a related index can exist in the database. We can just create a statement to rename it.

For i In (Select Con.Owner,
				 Con.Constraint_Name,
				 Con.Generated,
				 Con.Table_Name,
				 Con.Constraint_Type,
				 Con.Index_Owner,
				 Con.Index_Name
			From Dba_Constraints Con
		   Where Con.Table_Name = v_Table
			 And Con.Owner = v_Owner) Loop
	v_Ddl := Dbms_Metadata.Get_Ddl('CONSTRAINT', i.Constraint_Name, i.Owner);
	If i.Generated = 'USER NAME' Then
		v_Sql := 'Alter Table ' || i.Owner || '.' || v_Table_Old || '	Rename Constraint ' ||
				 i.Constraint_Name || ' To ' || Get_Name(i.Constraint_Name || '_OLD') || ';';
		Dbms_Output.Put_Line(v_Sql);
	
		If (i.Constraint_Type In ('U', 'P')) Then
			If i.Index_Owner Is Not Null And i.Index_Name Is Not Null Then
				v_Sql := 'Alter index ' || i.Index_Owner || '.' || i.Index_Name ||
						 ' Rename to ' || Get_Name(i.Index_Name || '_OLD') || ';';
				Dbms_Output.Put_Line(v_Sql);
			End If;
			v_Sql := Replace(v_Ddl, ') ENABLE', ')') || ' using index tablespace ' ||
					 p_Index_Tablespace || ' nologging compute statistics;';
		Else
			v_Sql := v_Ddl || ';';
		End If;
		Dbms_Output.Put_Line(v_Sql);
	Else
		v_Sql := v_Ddl || ';';
		Dbms_Output.Put_Line(v_Sql);
	End If;
End Loop;

Result:

ALTER TABLE "TEST_SCHEMA"."NON_PARTITIONED" MODIFY ("NAME" NOT NULL ENABLE);
Alter Table TEST_SCHEMA.NON_PARTITIONED_OLD	Rename Constraint PK To PK_OLD;
Alter index TEST_SCHEMA.PK Rename to PK_OLD;
ALTER TABLE "TEST_SCHEMA"."NON_PARTITIONED" ADD CONSTRAINT "PK" PRIMARY KEY ("TEST")
  using index tablespace TABLESPACE_INDEX nologging compute statistics;
Alter Table TEST_SCHEMA.NON_PARTITIONED_OLD	Rename Constraint UK1 To UK1_OLD;
Alter index TEST_SCHEMA.UK1 Rename to UK1_OLD;
ALTER TABLE "TEST_SCHEMA"."NON_PARTITIONED" ADD CONSTRAINT "UK1" UNIQUE ("CREATED_DATE")
  using index tablespace TABLESPACE_INDEX nologging compute statistics;

7.  Because the new partitioned table must be ready for use immediately after creation, we must provide the same privileges on the new tables to all users and roles that are on the existing non-partitioned tables. To reconstruct grants, we are using dba_tab_privs view:

For i In (Select 'grant ' || Tpr.Privilege || ' on ' || Tpr.Owner || '.' || Tpr.Table_Name ||
	 ' to ' || Tpr.Grantee || Decode(Tpr.Grantable, 'NO', '', ' WITH GRANT OPTION') || ';' Sql_Str
			From Dba_Tab_Privs Tpr
		   Where Owner = v_Owner
			 And Table_Name = v_Table) Loop
	v_Sql := i.Sql_Str;
	Dbms_Output.Put_Line(v_Sql);
End Loop;

Result:

grant SELECT on TEST_SCHEMA.NON_PARTITIONED to NEW;

8.  All triggers that are available for the non-partitioned tables are selected from the dba_triggers view. Based on the data coming from dba_triggers view, all non-partitioned table triggers are renamed for the partitioned tables. To get the trigger DDL statement, we are using dbms_metadada.get_ddl function:

For i In (Select Owner, Trigger_Name
			From Dba_Triggers Tr
		   Where Table_Owner = v_Owner
			 And Table_Name = v_Table) Loop
	v_Sql := 'alter trigger ' || i.Owner || '.' || i.Trigger_Name || ' rename to ' ||
			 Get_Name(i.Trigger_Name || '_OLD') || ';';
	Dbms_Output.Put_Line(v_Sql);
	v_Sql := Replace(Replace(Replace(Dbms_Metadata.Get_Ddl('TRIGGER', i.Trigger_Name, i.Owner),
						 'ALTER TRIGGER ',
						 Chr(10) || '/' || Chr(10) || 'ALTER TRIGGER '),
						 'END;' || Chr(10) || Chr(10) || Chr(10) || Chr(10) || Chr(10),
						 'END;'),
					 'ALTER TRIGGER "' || i.Owner || '"."' || i.Trigger_Name || '" ENABLE',
 'ALTER TRIGGER "' || i.Owner || '"."' || i.Trigger_Name || '" ENABLE' ||
					 Chr(10) || '/');
	Dbms_Output.Put_Line(v_Sql);
End Loop;

Result:

alter trigger TEST_SCHEMA.TRGGIER1 rename to TRGGIER1_OLD;

CREATE OR REPLACE TRIGGER "TEST_SCHEMA"."TRGGIER1" 
  before insert on non_partitioned
  for each row
Declare
	-- local variables here
Begin
	Null;
End Trggier1;
/
ALTER TRIGGER "TEST_SCHEMA"."TRGGIER1" ENABLE
/

9.  This step recreates the indexes based on the view dba_indexes. We assume that primary and unique keys are created using indexes. Based on that assumption, the script below reduces all indexes related to constraints. Before we create the duplicate index on the new table, we are renaming the original indexes that belong to non-partitioned tables. Using the procedure below, dbms_metadata.get_ddl, we can return the index clause and then modify it slightly to gather statistics in order to improve performance. While creating the index creation, do not forget to apply the NOLOGGING clause. By default, all non-unique indexes are created as local indexes.

For i In (Select Dbms_Metadata.Get_Ddl('INDEX', Ind.Index_Name, Ind.Owner) Sql_Str,
				 Ind.Index_Name,
				 Ind.Owner,
				 Ind.Uniqueness
			From Dba_Indexes Ind
		   Where Table_Owner = v_Owner
			 And Table_Name = v_Table
			 And (Ind.Owner, Ind.Index_Name) Not In
				 (Select Con.Index_Owner, Con.Index_Name
					From Dba_Constraints Con
				   Where Con.Owner = v_Owner
				 And Con.Table_Name = v_Table
					 And Con.Index_Owner Is Not Null
					 And Con.Index_Name Is Not Null)) Loop
	v_Sql := 'alter index ' || i.Owner || '.' || i.Index_Name || ' rename to ' ||
			 Get_Name(i.Index_Name || '_OLD') || ';';
	Dbms_Output.Put_Line(v_Sql);
	v_Ddl := i.Sql_Str;
	v_Sql := v_Ddl || ' COMPUTE STATISTICS NOLOGGING TABLESPACE ' || p_Index_Tablespace;
	If i.Uniqueness = 'NONUNIQUE' Then
		v_Sql := v_Sql || ' LOCAL ';
	End If;
	v_Sql := v_Sql || Chr(10) || '/' || Chr(10);
	Dbms_Output.Put_Line(v_Sql);
End Loop;

Result:

alter index TEST_SCHEMA.N1 rename to N1_OLD;

CREATE INDEX "TEST_SCHEMA"."N1" ON "TEST_SCHEMA"."NON_PARTITIONED" ("NAME") 
COMPUTE STATISTICS NOLOGGING TABLESPACE TABLESPACE_INDEX LOCAL ;

10.  Since previous non-partitioned tables can be used in relationships with any other tables, we have to provide logic to point referential constraints to the new partitioned table. We have used dba_constraints view to find referential constraints. Before creating relation to partitioned tables, the previous referential constraints are dropped:

For i In (Select Con.Constraint_Name,
			 Con.Owner,
			 Con.Table_Name,
			 Dbms_Metadata.Get_Ddl('REF_CONSTRAINT', Con.Constraint_Name, Con.Owner) Sql_Str
			From Dba_Constraints Con ,
					 (Select Constraint_Name
						From Dba_Constraints
					   Where Owner = v_Owner
						 And Table_Name = v_Table) Ref_Con
			   Where Con.r_Constraint_Name = Ref_Con.Constraint_Name
			 And Status = 'ENABLED') Loop
	-- drop foreing key constraint pointing to non-partitioned table         
	v_Sql := 'ALTER TABLE ' || i.Owner || '.' || i.Table_Name || ' Drop CONSTRAINT ' ||
			 i.Constraint_Name;
	Dbms_Output.Put_Line(v_Sql || ';');
	Dbms_Output.Put_Line('');
	-- create foreing key constraint pointing to partitioned table
	v_Sql := i.Sql_Str;
	Dbms_Output.Put_Line(v_Sql || ';');
	Dbms_Output.Put_Line('');
End Loop;

Result:

ALTER TABLE TEST_SCHEMA.REFERENCED_TABLE Drop CONSTRAINT FK;
ALTER TABLE "TEST_SCHEMA"."REFERENCED_TABLE" ADD CONSTRAINT "FK" FOREIGN KEY ("TEST")
  REFERENCES "TEST_SCHEMA"."NON_PARTITIONED" ("TEST") ENABLE;

11.  During the process of renaming the non-partitioned table, a set of objects using the declaration of this table will invalidate themselves. We consider it to be a good practice to recompile affected dependent objects (views, synonyms stored packages, procedures and functions). To find these objects, use dba_dependencies views to help. Using the views, we will create an alter statement that compiles all dependent objects.

For i In (Select 'alter ' || Type || ' ' || Owner || '.' || Name || ' compile' Sql_Str
			From Dba_Dependencies
		   Start With Referenced_Owner = v_Owner
				  And Referenced_Name = v_Table
		  Connect By Prior Name = Referenced_Name
				 And Prior Owner = Referenced_Owner
		   Order By Level) Loop
	v_Sql := i.Sql_Str;
	Dbms_Output.Put_Line(v_Sql || ';');
	Dbms_Output.Put_Line('');
End Loop;

Result:

alter TRIGGER TEST_SCHEMA.TRGGIER1 compile;

alter VIEW TEST_SCHEMA.REFERENCED_VIEW compile;

alter SYNONYM TEST_SCHEMA.NON_PARTITIONED_SYN compile;

alter SYNONYM TEST_SCHEMA.REFERENCED_VIEW_SYN compile;

alter FUNCTION TEST_SCHEMA.REFERENCED_FUNCTION1 compile;

alter FUNCTION TEST_SCHEMA.REFERENCED_FUNCTION2 compile;

The scripts above can also be used to create duplicates of the original table if Partitioning_Clause is defined as Null value.

Conclusion:

The purpose of this article was to show how you can safely and easily transform non-partitioned tables into partitioned tables. This article shows a general approach to help you get a handle on performance and manageability. The reason for this approach was because the tables in the database were not partitioned when they were created. With a little help from the steps and scripting shown in this article, you can easily make partitioning possible at anytime.