Perform the Installing Oracle Database 10g on Windows tutorial.

Download and unzip the onlredef.zip file into your working directory (i.e. c:\wkdir).

Open a browser and launch Enterprise Manager as follows:

http://<your host name>:1158/em

Login as system/oracle and click Login.

Note: You may receive the license agreement page. If so, scroll down to the bottom of the page and click I agree.

Select the Administration tab.

Scroll down to the Schema section.

Click on the Tables link.

Enter HR in the Schema field and EMP in the Object Name field. Then click Go.

Select the EMPLOYEES table link.

Notice that the EMPLOYEES table is not partitioned and it does not have a column named LOCATION. Click OK.

In order to redefine your table, you need to verify that the table is a candidate for online redefinition and specify that redefinition is to be performed using a primary key. Log in to SQL*Plus as system/oracle and execute the redef_emp_cand script as follows:

@c:\wkdir\redef_emp_cand

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('HR','employees',
dbms_redefinition.cons_use_pk);
END;
/

You now create two tablespaces and an interim table. The interim table shows what the table will look like after the reorganization, including the LOCATION_ID column and the partitioning scheme. In your SQL*Plus session, execute the redef_crobj script as follows:

@c:\wkdir\redef_crobj

connect system/oracle
CREATE TABLESPACE EMP_TBS1;
CREATE TABLESPACE EMP_TBS2;
CREATE TABLE hr.int_employees
(employee_id      NUMBER(6) ,
first_name           VARCHAR2(20) ,
last_name            VARCHAR2(25) ,
email                   VARCHAR2(25) ,
phone_number    VARCHAR2(20) ,
location                NUMBER(4),
hire_date              DATE DEFAULT (sysdate),
job_id                  VARCHAR2(10),
salary                   NUMBER(8,2),
commission_pct  NUMBER (2) DEFAULT(10),
manager_id          NUMBER(6),
department_id      NUMBER(4) )
PARTITION BY RANGE(employee_id)
(PARTITION emp1000 VALUES LESS THAN (1000) TABLESPACE EMP_TBS1,
PARTITION emp2000 VALUES LESS THAN (2000) TABLESPACE EMP_TBS2); 

Now you can start the redefinition process and define the column mappings for the old and new columns as well as increase the commission by 1%. In your SQL*Plus session, execute the redef_startscript as follows:

@c:\wkdir\redef_start

connect system/oracle
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('hr', 'employees','int_employees',
'employee_id employee_id, first_name first_name, last_name last_name,
 email email, phone_number phone_number, 0 location, hire_date hire_date,
 job_id job_id, salary salary, commission_pct+1 commission_pct, 
 manager_id manager_id, department_id department_id' ,
 dbms_redefinition.cons_use_pk);
END;
/

Start another SQL*Plus session where you can make changes to the EMPLOYEES table. Connect as system/oracle and execute the redef_testscript as follows:

@c:\wkdir\redef_test

connect hr/hr
UPDATE  employees set salary = 25000 where employee_id=200;

Note: Do not commit this transaction at this time.

Next you automatically create any triggers, indexes and constraints on the interim table. Switch back to the first SQL*Plus session and execute the redef_crdep_obj script:

@c:\wkdir\redef_crdep_obj

connect system/oracle
set serveroutput on 
DECLARE
 no_errors int;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
 ('hr', 'employees','int_employees',1, TRUE, TRUE, TRUE, FALSE, no_errors);
DBMS_OUTPUT.put_line ('No of errors during copy of dependents ' || no_errors );
END;
/

Return to your second SQL*Plus session (where you executed the redef_test script) and commit the salary change. Exit from this SQL*Plus session.

commit;
exit

Now you synchronize the interim table so that it includes the salary change that you made. Return to your first SQL*Plus session and execute the redef_sync script:

@c:\wkdir\redef_sync

connect system/oracle
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('hr', 'employees', 'int_employees');
END;
/

Now you finish the reorganization and the tables will be transparently switched. In your SQL*Plus session, execute the redef_finish_drop script:

@c:\wkdir\redef_finish_drop

connect system/oracle
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('hr', 'employees', 'int_employees');
END;
/
DROP TABLE hr.int_employees CASCADE CONSTRAINTS PURGE;

Now you view the reorganized table in Enterprise Manager. Switch back to your Enterprise Manager session. Click the Refresh button in your browser and click EMPLOYEES again.

Notice the new LOCATION column. Scroll down to the bottom.

The table is also now partitioned. Click OK.

On the Tables page in Enterprise Manager, click the Create button.

Accept the default of "Standard, Heap Organized" and click Continue.

Enter EMPLOYEES1 in the Name field. Specify SYSTEM as the schema and EXAMPLE as the tablespace. Select SQL in the Define Using drop-down list. Enter select * from hr.employees in the Create Table As field. Click the Options tab.

Select Yes in the Enable Row Movement list and click OK to complete the creation of the table.

Your table has been created.

Now you generate some activity. Log in to SQL*Plus as system/oracle. Execute the genactivity01 script to simulate user activity on the EMPLOYEES1 table.

@c:\wkdir\genactivity01

The genactivity01.sql file includes the following:

begin
  for i in 1..1000 loop
    insert into system.employees1
    select * from hr.employees;
    commit;
  end loop;
end;
/
          

To simulate more user activity on the EMPLOYEES1 table, execute the genactivity02 script in your SQL*Plus session:

@c:\wkdir\genactivity02

The genactivity02.sql file includes the following:

delete system.employees1 where department_id = 50;

begin
  for i in 1..500 loop
    insert into system.employees1
    select * from hr.employees;
    commit;
  end loop;
end;
/
           

To simulate even more user activity on the EMPLOYEES1 table, execute the genactivity03.sql script in your SQL*Plus session:

@c:\wkdir\genactivity03.sql

The genactivity03.sql file includes the following:

begin
  for i in 1..500 loop
     insert into system.employees1
     select * from hr.employees;
     commit;
  end loop;
end;
/

To simulate more user activity on the EMPLOYEES1 table, execute the genactivity04 script in your SQL*Plus session:

@c:\wkdirgenactivity04

The genactivity04.sql file includes the following:

delete system.employees1 where department_id = 30;
commit;
delete system.employees1 where department_id = 100;
commit;
delete system.employees1 where department_id = 50;
commit;
delete system.employees1 where department_id = 80;
commit;

Return to your Enterprise Manager Tables page. Select the EMPLOYEES1 table and click Edit.

Click the Segments tab.

Note the percentage of wasted space in the EMPLOYEES1 table. You can use the Segment Advisor to reclaim the unused space in this table. Select Shrink Segment from the Actions list and click Go.

Click Continue.

On the Schedule page, enter Segment_Shrink_Employees1 for the Job Name. Ensure that Immediately is selected and click Submit.

Your job has been submitted. Click the All tab.

Your segment shrink job was successful. Now you review the statistics. Click your Database Instance breadcrumb.

Scroll down the Administration page. Click the Tables link in the Database Objects section.

Enter SYSTEM for Schema and EMP for Object Name. Click Go.

Make sure the EMPLOYEES1 table is selected and click Edit.

Click the Segments tab.

Notice that the Wasted Space(%) has been drastically reduced. Select the Database Instance breadcrumb.

Log in to SQL*Plus as system/oracle and execute the rebuild_crtblsp script to create a new tablespace:

@c:\wkdir\rebuild_crtblsp

connect system/oracle
CREATE TABLESPACE indexes; 

Return to your Enterprise Manager window. Select Indexes in the Database Objects section of the Administration page.

Select Index Name in the Search By list. Enter HR in the Schema field and JHIST% in the Object Name field. Click Go.

Note that the JHIST_JOB_IX index is currently stored in the EXAMPLE tablespace.

In your SQL*Plus session, issue the following command to move the index to the INDEXES tablespace you just created:

ALTER INDEX hr.jhist_job_ix REBUILD ONLINE TABLESPACE indexes; 

Return to your Enterprise Manager session and refresh the page. You see that the index is now assigned to the INDEXES tablespace. Click the Database breadcrumb to return to the Administration page.

Scroll down the Administration page.

Under Database Objects, select Reorganize Objects.

Make sure Schema Objects is selected and click Next.

Click Add.

Select Tables in the Type list and enter HR for the Schema. Then click Search.

In the Available Objects: Tables section, select HR.DEPARTMENTS and click OK.

Click Set Attributes.

Select Relocate object to another tablespace and enter EMP_TBS2 for the tablespace name. Click the Storage tab.

Enter 2MB for the Initial Size of the Extents. Then click OK.

Click Next.

In the Method section, select Availability (online). Click Next.

The script was generated and an impact report produced with no errors. Click Next.

Enter your host credential username and password. Click Next.

The script to be executed is displayed. Click Submit Job .

The job was created. Click the job link to view the results.

Click the Status link.

Click the Reorganize link.

The output is shown. The reorganize operation completed successfully. Scroll to the bottom of the page to review the entire output log. Click Database to return to the Database home page.