Appendix 1 Script to create environment to test Table_Partitioning procedure The following scripts can be used to create a test environment to check how Table_Partitioning procedures are working within your environment.

Drop Table "NON_PARTITIONED" Cascade Constraints Purge ;
CREATE TABLE "NON_PARTITIONED" 
   (	"TEST" NUMBER, 
	"CREATED_DATE" DATE DEFAULT sysdate, 
	"NAME" VARCHAR2(10) DEFAULT 'TEST' NOT NULL ENABLE, 
	 CONSTRAINT "PK" PRIMARY KEY ("TEST")
  USING Index, 
	 CONSTRAINT "UK1" UNIQUE ("CREATED_DATE")
  USING INDEX 
   ) ;

Drop Table "REFERENCED_TABLE" Cascade Constraints Purge ;
CREATE TABLE "REFERENCED_TABLE" 
   (	"TEST" NUMBER, 
	"TEST2" NUMBER, 
	 CONSTRAINT "FK" FOREIGN KEY ("TEST")
	  REFERENCES "NON_PARTITIONED" ("TEST") ENABLE
   ) ;

CREATE OR REPLACE FORCE VIEW "REFERENCED_VIEW" ("TEST") AS 
  select test
    from non_partitioned
;

CREATE OR REPLACE SYNONYM "NON_PARTITIONED_SYN" FOR "NON_PARTITIONED";
CREATE OR REPLACE SYNONYM "REFERENCED_VIEW_SYN" FOR "REFERENCED_VIEW";

 
CREATE INDEX "N1" ON "NON_PARTITIONED" ("NAME") ;
 
COMMENT ON COLUMN "NON_PARTITIONED"."TEST" IS 'column for testing script';
 
COMMENT ON TABLE "NON_PARTITIONED"  IS 'table for testing script';

Drop Role New;
create role NEW;
 
GRANT SELECT ON "NON_PARTITIONED" TO "NEW";
  
GRANT SELECT ON "REFERENCED_TABLE" TO "NEW";
  
CREATE OR REPLACE FUNCTION "REFERENCED_FUNCTION1" Return Number Is
	Result Number;
Begin
	Select Test Into Result From Referenced_View;
	Return(Result);
End;
/

CREATE OR REPLACE FUNCTION "REFERENCED_FUNCTION2" Return Number Is
	Result Number;
Begin
	Select Test Into Result From Referenced_View_Syn;
	Return(Result);
End;
/


create or replace trigger trggier1
  before insert on non_partitioned  
  for each row
Declare
	-- local variables here
Begin
	Null;
End Trggier1;
/

b) the next scripts can be used to clean-up the test environment

Drop Table NON_PARTITIONED Cascade Constraints Purge ;
Drop Table REFERENCED_TABLE Cascade Constraints Purge ;
Drop Table non_partitioned_old Cascade Constraints Purge ; 
Drop VIEW REFERENCED_VIEW;
Drop SYNONYM NON_PARTITIONED_SYN;
Drop SYNONYM REFERENCED_VIEW_SYN;
Drop Role New;
Drop FUNCTION REFERENCED_FUNCTION1;
Drop FUNCTION REFERENCED_FUNCTION2;

Appendix 2 Table_Partitioning procedure

Create Or Replace Procedure Table_Partitioning(p_Owner               Varchar2,
						   p_Table               Varchar2,
						   p_Data_Tablespace     Varchar2,
						   p_Index_Tablespace    Varchar2,
						   p_Partitioning_Clause Varchar2) Authid Current_User Is
	v_Sql       Varchar2(30000);
	v_Ddl       Varchar2(30000);
	v_Owner     Varchar2(30);
	v_Table     Varchar2(30);
	v_Table_Old Varchar2(30);

	Function Get_Name(p_Possible_Name Varchar2) Return Varchar2 Is
		v_Resulting_Name Varchar2(30);
		v_Prefix         Varchar2(4) Default 'GEN_';
	Begin
		If Length(p_Possible_Name) > 30 Then
			v_Resulting_Name := Substr(To_Char(Rawtohex(Sys_Guid())), 1, 26);
			v_Resulting_Name := v_Prefix || v_Resulting_Name;
		Else
			v_Resulting_Name := p_Possible_Name;
		End If;
		Return v_Resulting_Name;
	End;

Begin
	-- 0. setup transformation to reduce appearance of storage,constraints in ddl 
	--    coming out from get_ddl
	Dbms_Metadata.Set_Transform_Param(Dbms_Metadata.Session_Transform, 'STORAGE', False);
	Dbms_Metadata.Set_Transform_Param(Dbms_Metadata.Session_Transform, 'CONSTRAINTS', False);
	Dbms_Metadata.Set_Transform_Param(Dbms_Metadata.Session_Transform, 'SEGMENT_ATTRIBUTES', False);
	v_Table     := Upper(p_Table);
	v_Owner     := Upper(p_Owner);
	v_Table_Old := Get_Name(v_Table || '_OLD');

	Dbms_Output.Put_Line('--1. rename non-partitioned table:');
	-- 1. rename non-partitioned table:
	v_Sql := 'Alter Table ' || v_Owner || '.' || v_Table || ' Rename To ' || v_Table_Old;

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

	Dbms_Output.Put_Line('-- 2. geting ddl to reproduce table:');
	Dbms_Output.Put_Line('-- create new partitioned table based on ddl statement:');
	-- 2. geting ddl to reproduce table:
	-- create new partitioned table based on ddl statement:
	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('');

	Dbms_Output.Put_Line('-- 2. copy all existing records from one table to another');
	-- 3. copy all existing records from one table to another
	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('');

	Dbms_Output.Put_Line('-- 4. applying table comments to the new partitioned table:');
	-- 4. applying table comments to the new partitioned table:
	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;

	Dbms_Output.Put_Line('-- 5. applying comments to the columns of new partitioned table:');
	-- 5. applying comments to the columns of new partitioned table:
	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;

	Dbms_Output.Put_Line('-- 6. renaming constraints on the previous (old) table and');
	Dbms_Output.Put_Line('--adding contsraints to new partitioned table');
	-- 6. renaming constraints on the previous (old) table and 
	--adding contsraints to new partitioned table
	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;

	Dbms_Output.Put_Line('-- 7. reproduce privileges given on the table:');
	-- 7. reproduce privileges given on the table:
	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;

	Dbms_Output.Put_Line('-- 8. reproducing trigger for the partitioned table:');
	-- 8. reproducing trigger for the partitioned table:
	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;

	Dbms_Output.Put_Line('-- 9. create indexes for the partitioned table:');
	-- 9. create indexes for the partitioned table:
	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;

	Dbms_Output.Put_Line('-- 10. relink dependent foreign keys to partition table:');
	-- 10. relink dependent foreign keys to partition table:
	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
			  Where Con.r_Constraint_Name In (Select Constraint_Name
										 From Dba_Constraints
										Where Owner = v_Owner
										  And Table_Name = v_Table)
				 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;

	Dbms_Output.Put_Line('-- 11. recompile dependent objects (views, synonyms stored packages,');
	Dbms_Output.Put_Line('-- procedures and functions):');
	-- 11. recompile dependent objects (views, synonyms stored packages, procedures and functions):
	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;

	Dbms_Output.Put_Line('-- 12. bring transformation to previous state:');
	-- 12. bring transformation to previous state:
	Dbms_Metadata.Set_Transform_Param(Dbms_Metadata.Session_Transform, 'STORAGE', True);
	Dbms_Metadata.Set_Transform_Param(Dbms_Metadata.Session_Transform, 'CONSTRAINTS', True);
	Dbms_Metadata.Set_Transform_Param(Dbms_Metadata.Session_Transform, 'SEGMENT_ATTRIBUTES', True);

	--Dbms_Output.Put_Line('-- 13. Optional: enable row movement in the partitioned table');
	-- 13. Optional: enable row movement in the partitioned table
	--v_Sql := 'ALTER table ' || v_Owner || '.' || v_Table || ' ENABLE ROW MOVEMENT;';
	--Dbms_Output.Put_Line(v_Sql);
End Table_Partitioning;

Appendix 3 Resulting script after Table_Partitioning procedure is applied:

a) call to generate script:

declare 
begin
  -- Test statements here
    Table_Partitioning('TEST_SCHEMA', 'non_partitioned','TABLESPACE_DATA','TABLESPACE_INDEX',
' PARTITION BY RANGE (CREATED_DATE) (PARTITION p_max VALUES LESS THAN(Maxvalue)) '); 
end;

b) resulting script:

--1. rename non-partitioned table:
Alter Table TEST_SCHEMA.NON_PARTITIONED Rename To NON_PARTITIONED_OLD;

-- 2. geting ddl to reproduce table:
-- create new partitioned table based on ddl statement:

  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 all existing records from one table to another
Insert /*+ APPEND*/ Into TEST_SCHEMA.NON_PARTITIONED (Select * From TEST_SCHEMA.NON_PARTITIONED_OLD);

-- 4. applying table comments to the new partitioned table:
Comment On Table  TEST_SCHEMA.NON_PARTITIONED Is 'table for testing script';

-- 5. applying comments to the columns of new partitioned table:
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. renaming constraints on the previous (old) table and
--adding contsraints to new partitioned table

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. reproduce privileges given on the table:
grant SELECT on TEST_SCHEMA.NON_PARTITIONED to NEW;
-- 8. reproducing trigger for the partitioned table:
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. create indexes for the partitioned table:
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. relink dependent foreign keys to partition table:
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. recompile dependent objects (views, synonyms stored packages,
-- procedures and functions):
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;