With Oracle Database 10g, a tablespace’s disk space utilization
is proactively managed by the database. Proactive tablespace management is possible
in the following ways:
Through the use of database alerts, you are informed
when a tablespace runs low on available disk space. You can then provide the
tablespace with more disk space, or reclaim space from the tablespace. That
way, you can avoid out-of-space conditions. Information gathered is stored in
the Automatic Workload Repository (AWR) and used to do growth trend analysis
and capacity planning of the database.
Tablespace thresholds can be defined in terms of the fullness
of the tablespace. Critical and warning thresholds are the two thresholds that
apply to a tablespace. The DBMS_SERVER_ALERTS package contains procedures to
set and get the threshold values. You can also use the EM interface to define
the thresholds. When the tablespace fullness crosses either of these two limits,
an appropriate alert is raised. The threshold is expressed in terms of a percentage
of the tablespace size or in bytes.
First, you need to create a new tablespace with a 20MB data
file. This tablespace needs to be locally managed, and uses Automatic Segment
Space Management (ASSM). Finally, a table needs to be created in this new tablespace.
This table will have its Enable Row Movement option set to yes. Having this
option turned on allows for space reclamation on this table. Perform the following:
1.
Open your browser and enter the following URL:
http://<hostname>:5500/em
Enter sys/<password> as SYSDBA and
click Login.
2.
Click the Administration tab.
3.
Click the
Tablespaces link.
4.
Click the Create button.
5.
Enter the tablespace name TBSALERT then click
Add to define a datafile for the tablespace.
6.
Enter the datafile name tbsalert.dbf and the
size 20MB then click Continue.
7.
Click on Thresholds tab to specify the space
used warning and critical threshold levels.
8.
Click Specify Thresholds, by percent used. Set
the database wide default threshold values for space usage. Enter 60
for the Warning%, and 68 for the Critical%. Click OK.
9.
Click the Database breadcrumb to go back to the
Administration tab.
10.
Click the Tables link.
11.
Click the Create button.
12.
Click Continue.
13.
Enter employees1 in the Name field. Specify SYSTEM
as the schema and TBLALERT as the tablespace. Click on the
Define Using drop-down list and select SQL. Type select
* from hr.employees in the Create Table As field, then click
on the Options tab.
14.
Select Yes for Enable Row Movement and click
OK to complete creation of the table.
15.
Your table has been created. Click the Database
breadcrumb.
You will now perform some updates on the table to trigger
a space utilization alert. Perform the following:
1.
Open a Terminal window and execute the following command:
sqlplus system/<password>
2.
Copy and paste the following SQL commands into your
SQL*Plus session to
simulate user activity on the EMPLOYEES1
table:
begin
for i in 1..1000 loop
insert into employees1
select * from hr.employees;
commit;
end loop;
end;
/
3.
Go to the Enterprise Manager window. Click on
the Database breadcrumb to return to the Administration
page then click on the Tablespaces link.
4.
Notice that the TBSALERT tablespace space used percentage
has increased.
5.
Switch back to the SQL*Plus window and copy and
paste the following commands into your SQL*Plus sessionto simulate more user activity on the EMPLOYEES1
table:
delete employees1 where department_id = 50;
begin
for i in 1..500 loop
insert into employees1
select * from hr.employees;
commit;
end loop;
end;
/
6.
Go to the Enterprise Manager window. Refresh
your browser (for Linux Mozilla, select View from the menubar then
select Reload). Notice that the TBSALERT tablespace space usage
percentage has increased further.
7.
Switch back to the SQL*Plus window and copy and
paste the following commands into your SQL*Plus sessionto simulate more user activity on the EMPLOYEES1
table:
begin
for i in 1..500 loop
insert into employees1
select * from hr.employees;
commit;
end loop;
end;
/
7.
Copy and paste the following SQL commands into your
SQL*Plus session to
simulate user activity on the EMPLOYEES1
table:
delete employees1 where department_id = 30;
commit;
delete employees1 where department_id = 100;
commit;
delete employees1 where department_id = 50;
commit;
delete employees1 where department_id = 80;
commit;
exit
8.
Go to the Enterprise Manager window. Refresh
your browser (for Linux Mozilla, select View from the menubar then
select Reload). Notice that the TBSALERT tablespace space usage
percentage has now exceeded the critical threshold level of 68%.
9.
While you are waiting for the space usage alert to be
displayed on the Enterprise Manager home page, take a look at the table
segment statistics. Click on the Database breadcrumb then click
on the Tables link.
10.
To locate the SYSTEM.EMPLOYEES1 table, enter system
in the Schema field and emp in the Object Name field then click
Go.
11.
Click on the EMPLOYEES1 link.
12.
Click the Segments tab.
13.
Notice the percentage of wasted space in the EMPLOYEES1table. You can very likely resolve the tablespace space usage alert
by reclaiming the unused space in this table.
On this same screen, you can project the EMPLOYEES1
table's future space usage by specifying a date range for Space Usage
Trend then click the Refresh button. Because there has not
been a long enough history of activity on the EMPLOYEES1 table, you will
not see very meaningful data in the space usage analysis graph. Click
on the Database breadcrumb then click on the Home page tab.
15.
Click on the Refresh button a few times until
you see a red x and the number 1 next to Problem Tablespaces.
Scroll down to the Alerts table.
16.
You should see a Tablespaces Full alert. Click
on the Tablespace [TBSALERT] is [70 percent] full link.
17.
Click the Back button in your browser.
18.
The Automatic Database Diagnostic Monitor (ADDM) advises
to add more space. In the next section, you will resolve the tablespace
full alert by reclaiming wasted space in the tablespace. Click the Advisor
Central link in the Related Links section on your home page.
Because you do not want to add space to your tablespace, you
want to determine which segments can be shrunk in order to restitute space to
the tablespace. When space used by an object at steady state is much less than
the amount of space allocated to it, it becomes necessary to reclaim the unused
space..With previous releases of the Oracle server, space once allocated below
the segment’s HWM could only be freed by moving or redefining the segment..
In Oracle Database 10g you can now shrink segments. When a segment is
shrunk, its data is compacted, its HWM is pushed down, and unused space is released
back to the tablespace containing the segment. A shrink operation is an online
and in-place operation, and does not use extra database space to be executed.
In-place means the shrink operation does not create a separate object but rather
the shrink is done on the same object. Perform the following:
1.
Click Segment Advisor.
2.
Make sure that Tablespaces is selected and click
Continue.
3.
Click Add.
4.
Select the tablespace TBSALERT and click OK.
5.
Click Next.
6.
At the Options window, accept the defaults and click
Next.
7.
At the Schedule window, select the Schedule Type Standard
and make sure Immediately is selected and click Next.
8.
Click Submit.
9.
Click the Refresh button until the task is completed.
10.
Once the task is complete, click View Results.
11.
You can see that the EMPLOYEES1 table is a good candidate
for a shrink operation. Scrolling down to the bottom of the result page,
you can choose which kind of shrink operation to do. Make sure that the
first option is selected because it is the one that reclaims space. Then
click Show SQL to see what statements will be executed.
12.
Click OK.
13.
Click Schedule Implementation.
11.
Click Submit.
12.
Click the Run History tab.
13.
The job ended successfully.
14.
Check to see how much space was actually freed from
the tablespace. Click the Database breadcrumb then click on the
Tablespaces link.
15.
Notice that the TBSALERT tablespace's space usage level
has been greatly reduced.
16.
Within about 10 minutes, the tablespace full alert should
be removed from the Alerts table on the Home page. To return to the Home
page, click on the Database breadcrumb then click the Home tab.
Click the Refresh button a few times until the Tablespace Full
alert is removed.
Place the cursor on this icon to hide all screenshots.
Bookmark
