Perform the Installing Oracle Database 10g on Windows tutorial.

Download and unzip the space.zip into your working directory (c:\wkdir)

Open your browser and enter the following URL (Replace <hostname> with your own host name or IP address):

http://<hostname>:1158/em

Enter sys as User Name, oracle as Password, SYSDBA in the Connect As field, and click Login.

Click the Administration tab.

Click Tables in the Schema section.

Click Create.

Select Standard, Heap Organized and click Continue.

Enter EMPLOYEES1 in the Name field. Specify SYSTEM as the schema and EXAMPLE as the tablespace. Click the Define Using drop-down list and select SQL. Enter select * from hr.employees in the CREATE TABLE AS field, and then click the Options tab.

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

Your table has been created. Click the Database Instance locator link.

To start a SQL*Plus session, select Start > Programs > Oracle - OraDb10g_home1 > Application Development > SQL Plus. Enter system as the User Name, oracle as Password and click OK.

Execute the following commands 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 following SQL script in your SQL*Plus session:

@c:\wkdir\genactivity02

The genactivity02.sql file includes the following commands:

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 following SQL script in your SQL*Plus session:

@c:\wkdir\genactivity03

The genactivity03 file includes the following commands:

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 following SQL script in your SQL*Plus session:

@c:\wkdir\genactivity04

The genactivity04.sql file includes the following commands:

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;

Switch back to Enterprise Manager. Now, you can take a look at the table segment statistics. From the Administration tab, click the Tables link.

To locate the SYSTEM.EMPLOYEES1 table, enter SYSTEM in the Schema field and emp in the Object Name field, and then click Go.

Click Edit.

Click the Segments tab.

Note the percentage of wasted space in the EMPLOYEES1 table. (Your actual values may differ from the ones in this screenshot.) You can use the Segment Advisor to reclaim the unused space in this table. Click the Database Instance locator link.

Scroll down to the bottom of the page. Click the Advisor Central link in the Related Links section.

Note: In Oracle Database 10g Release 2, the Segment Advisor automatically runs at various intervals. As a result, you see the number of Segment Advisor Recommendations represented on the Home page after a period of time.

Click Segment Advisor.

Select Schema Objects and click Next.

Click Add.

Select Table for Type, enter system as Schema and employees1 as Object, then click Search.

In the Results section, select the SYSTEM.EMPLOYEES1 table and click OK.

Click Next.

In the Schedule window, ensure that the Schedule Type is Standard and Start is Immediately. Click Next.

Click Submit.

Click Refresh.

Select SEGMENTADV_<number>, the result for the request you have just submitted and click View Result.

Click Recommendation Details.

Select the EMPLOYEES1 segment and click Shrink in the Recommendation column. (Your values may differ slightly from the ones on the screenshot).

Click Implement.

Click Submit.

Now you can check how much space was actually freed from the table. Click the Database Instance locator link.

Click the Tables on the Administration page.

Enter SYSTEM as Schema and EMP as Object Name and click Go.

Select EMPLOYEES1 and click Edit.

Click the Segments tab.

The wasted space has been reduced significantly. (Your actual values may be different from the ones displayed in this screenshot).

Return to your SQL*Plus session and execute the following SQL script :

@c:\wkdir\cleanup

The cleanup.sql file includes the following commands:

drop table system.employees1 purge;

exit