Pro Oracle Database 11g Administration (31 page)

SQL> show recyclebin;

Here is some sample output:

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

---------------- ------------------------------ ------------ -------------------

PURCHASES BIN$YzqK0hN3Fh/gQHdAPLFgMA==$0 TABLE 2009-02-18:17:23:15

Next, use the FLASHBACK TABLE...TO BEFORE DROP statement to recover the dropped table: SQL> flashback table purchases to before drop;

■
Note
You can’t FLASHBACK TABLE...TO BEFORE DROP for a table created in the SYSTEM tablespace.

In Oracle Database 10
g
and higher, when you issue a DROP TABLE statement, the table is renamed (to a name that starts with BIN$) and placed in the recycle bin. The recycle bin is a mechanism that allows you to view some of the metadata associated with a dropped object. You can view complete metadata regarding renamed objects by querying DBA_SEGMENTS:

select

owner

,segment_name

,segment_type

,tablespace_name

from dba_segments

where segment_name like 'BIN$%';

Here is some sample output:

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME

--------------- ------------------------------ --------------- ---------------

INV BIN$kptXkzzMdFrgQAB/AQBsFw==$0 TABLE USERS

INV BIN$kptXkzzNdFrgQAB/AQBsFw==$0 TABLE USERS

The FLASHBACK TABLE statement simply renames the table back to its original name. By default, the RECYCLEBIN feature is enabled in Oracle Database 10
g
and higher. You can change the default by setting the RECYCLEBIN initialization parameter to OFF.

I recommend that you not disable the RECYCLEBIN feature. It’s safer to leave this feature enabled and purge the RECYCLEBIN to remove objects that you want permanently deleted. This means the space associated with a dropped table isn’t released until you purge your RECYCLEBIN. If you want to purge the entire contents of the currently connected user’s recycle bin, use the PURGE RECYCLEBIN statement: SQL> purge recyclebin;

If you want to purge the recycle bin for all users in the database, then do the following as a DBA privileged user:

SQL> purge dba_recyclebin;

152

CHAPTER 7 ■ TABLES AND CONSTRAINTS

If you want to bypass the RECYCLEBIN feature and permanently drop a table, use the PURGE option of the DROP TABLE statement:

SQL> drop table dept purge;

You can’t use the FLASHBACK TABLE statement to retrieve a table dropped with the PURGE option. All space used by the table is released, and any associated indexes and triggers are also dropped.

Removing Data from a Table

You can use either the DELETE statement or the TRUNCATE statement to remove records from a table. You need to be aware of some important differences between these two approaches. Table 7–3 summarizes the attributes of the DELETE and TRUNCATE statements.

Table 7–3.
Features of DELETE and TRUNCATE

DELETE

TRUNCATE

Choice of COMMIT or ROLLBACK YES

NO

Generates undo

YES

NO

Resets the high-water mark to zero

NO

YES

Affected by referenced and

NO YES

enabled foreign-key constraints

Performs well with large amounts

NO YES

of data

Using DELETE

One big difference is that the DELETE statement can be either committed or rolled back. Committing a DELETE statement makes the changes permanent:

SQL> delete from inv;

SQL> commit;

If you issue a ROLLBACK statement instead of COMMIT, the table contains data as it was before the DELETE was issued.

Using TRUNCATE

TRUNCATE is a DDL statement. This means Oracle automatically commits the statement (and the current transaction) after it runs, so there is no way to roll back a TRUNCATE statement. If you need the option of choosing to roll back (instead of committing) when removing data, then you should use the DELETE

statement. However, the DELETE statement has the disadvantage that it generates a great deal of undo and redo information. Thus for large tables, a TRUNCATE statement is usually the most efficient way to remove data.

153

CHAPTER 7 ■ TABLES AND CONSTRAINTS

This example uses a TRUNCATE statement to remove all data from the COMPUTER_SYSTEMS table: SQL> truncate table computer_systems;

By default, Oracle deallocates all space used for the table except the space defined by the MINEXTENTS

table-storage parameter. If you don’t want the TRUNCATE statement to deallocate the extents, use the REUSE STORAGE parameter:

SQL> truncate table computer_systems reuse storage;

The TRUNCATE statement sets the high-water mark of a table back to zero. When you use a DELETE

statement to remove data from a table, the high-water mark doesn’t change. One advantage of using a TRUNCATE statement and resetting the high-water mark is that full-table scans only search for rows in blocks below the high-water mark. This can have significant performance implications.

You can’t truncate a table that has a primary key defined that is referenced by an enabled foreign-key constraint in a child table—even if the child table contains zero rows. Oracle prevents you from doing this because in a multiuser system, there is a possibility that another session can populate the child table with rows in between the time you truncate the child table and the time you subsequently truncate the parent table. In this scenario, you must temporarily disable the referenced foreign-key constraints, issue the TRUNCATE statement, and then re-enable the constraints.

Because a TRUNCATE statement is DDL, you can’t truncate two separate tables as one transaction.

Compare this TRUNCATE behavior to that of DELETE. Oracle does allow you to use the DELETE statement to remove rows from a parent table while the constraints are enabled that reference a child table. This is because DELETE generates undo, is read-consistent, and can be rolled back.

■
Note
Another way to remove data from a table is to drop and re-create the table. However, this means you also have to re-create any indexes, constraints, grants, and triggers that belong to the table. Additionally, when you drop a table, it’s temporarily unavailable until you re-create it and reissue any required grants. Usually, dropping and re-creating a table is acceptable only in a development or test environment.

Viewing and Adjusting the High-Water Mark

Oracle defines the
high-water mark
of a table as the boundary between used and unused space in a segment. When you create a table, Oracle allocates a number of extents to the table defined by the MINEXTENTS table-storage parameter. Each extent contains a number of blocks. Before data is inserted into the table, none of the blocks have been used, and the high-water mark is zero.

As data is inserted into a table, the high-water mark boundary is raised as extents are allocated. A DELETE statement doesn’t reset the high-water mark.

You need to be aware of a couple of performance-related issues regarding the high-water mark:

• SQL query full-table scans

• Direct-path load-space usage

Oracle sometimes needs to scan every block of a table (under the high-water mark) when performing a query. This is known as a
full-table scan
. If a significant amount of data has been deleted from a table, a full-table scan can take a long time to complete, even for a table with zero rows.

154

CHAPTER 7 ■ TABLES AND CONSTRAINTS

Also, when doing direct-path loads, Oracle inserts data above the high-water mark line. Potentially, you can end up with a large amount of unused space in a table that is regularly deleted from and also is loaded via a direct-path mechanism.

You can use a couple of methods to detect space below the high-water mark:

• Autotrace tool

• DBMS_SPACE package

The autotrace tool provides a simple method for detecting high-water-mark issues. Autotrace is advantageous because it’s easy to use and the output is easy to interpret.

You can use the DBMS_SPACE package to determine the high-water mark of objects created in tablespaces that use auto-space segment management. The DBMS_SPACE package allows you to programmatically check for high-water mark problems. The downside to this approach is that the output is somewhat cryptic and sometimes difficult to derive concrete answers from.

Tracing to Detect Space Below the High-Water Mark

You can run this simple test to detect whether you have an issue with unused space below the high-water mark:

1. SQL> set autotrace trace statistics

2. Run the query that performs the full-table scan.

3. Compare the number of rows processed to the number of logical I/Os (memory and disk accesses).

If the number of rows processed is low but the number of logical I/Os is high, you may have an issue with the number of free blocks below the high-water mark. Here’s a simple example to illustrate this technique:

SQL> set autotrace trace statistics

The next query generates a full-table scan on the INV table:

SQL> select * from inv;

Here’s a partial snippet of the output from AUTOTRACE:

no rows selected

Statistics

----------------------------------------------------------

1405 consistent gets

0 physical reads

The number of rows returned is zero, yet the consistent gets (memory accesses) is 1405. This indicates that there is free space beneath the high-water mark.

Next, truncate the table and run the query again:

SQL> truncate table inv;

SQL> select * from inv;

Here’s a partial listing from the output of AUTOTRACE:

155

CHAPTER 7 ■ TABLES AND CONSTRAINTS

no rows selected

Statistics

----------------------------------------------------------

3 consistent gets

0 physical reads

Notice that the number of memory accesses has been reduced to 3.

Using DBMS_SPACE to Detect Space Below the High-Water Mark

You can use the DBMS_SPACE package to detect free blocks beneath the high-water mark. Here’s an anonymous block of PL/SQL that you can call from SQL*Plus:

set serverout on size 1000000

declare

p_fs1_bytes number;

p_fs2_bytes number;

p_fs3_bytes number;

p_fs4_bytes number;

p_fs1_blocks number;

p_fs2_blocks number;

p_fs3_blocks number;

p_fs4_blocks number;

p_full_bytes number;

p_full_blocks number;

p_unformatted_bytes number;

p_unformatted_blocks number;

begin

dbms_space.space_usage(

segment_owner => user,

segment_name => 'INV',

segment_type => 'TABLE',

fs1_bytes => p_fs1_bytes,

fs1_blocks => p_fs1_blocks,

fs2_bytes => p_fs2_bytes,

fs2_blocks => p_fs2_blocks,

fs3_bytes => p_fs3_bytes,

fs3_blocks => p_fs3_blocks,

fs4_bytes => p_fs4_bytes,

fs4_blocks => p_fs4_blocks,

full_bytes => p_full_bytes,

full_blocks => p_full_blocks,

unformatted_blocks => p_unformatted_blocks,

unformatted_bytes => p_unformatted_bytes

);

dbms_output.put_line('FS1: blocks = '||p_fs1_blocks);

dbms_output.put_line('FS2: blocks = '||p_fs2_blocks);

dbms_output.put_line('FS3: blocks = '||p_fs3_blocks);

dbms_output.put_line('FS4: blocks = '||p_fs4_blocks);

dbms_output.put_line('Full blocks = '||p_full_blocks);

end;

/

156

CHAPTER 7 ■ TABLES AND CONSTRAINTS

In this scenario, you want to check the INV table for free space below the high-water mark. Here’s the output of the previous PL/SQL:

FS1: blocks = 0

FS2: blocks = 0

FS3: blocks = 0

FS4: blocks = 1394

Full blocks = 0

In the prior output, the FS1 parameter shows that 0 blocks have 0% to 25% free space. The FS2

parameter shows that 0 blocks have 25% to 50% free space. The FS3 parameter shows that 0 blocks have 50% to 75% free space. The FS4 parameter shows there are 1394 blocks that with 75% to 100% free space.

Finally, there are 0 full blocks. Because there are no full blocks and a large number of blocks are mostly empty, this is an indication that free space exists below the high-water mark.

How can you reduce a table’s high-water mark? You can use several techniques to reset the high-water mark back to zero:

• Use a TRUNCATE statement

• Use ALTER TABLE ... SHRINK SPACE

• Use ALTER TABLE ... MOVE

Using the TRUNCATE statement was discussed earlier in this chapter. Shrinking a table and moving a table are discussed in the following subsections.

Shrinking a Table

To readjust the high-water mark, you must enable row movement for the table and then use the ALTER

TABLE...SHRINK SPACE statement. The tablespace in which the table is created must have been built with automatic segment-space management enabled. You can determine the tablespace space-segment management type via this query:

SQL> select tablespace_name, segment_space_management from dba_tablespaces; The SEGMENT_SPACE_MANAGEMENT value must be AUTO for the tablespace in which the table is created.

Next, you need to enable row movement for the table to be shrunk. This example enables row movement for the INV table:

SQL> alter table inv enable row movement;

Now you can shrink the space used by the table:

SQL> alter table inv shrink space;

You can also shrink the space associated with any index segments via the CASCADE clause: SQL> alter table inv shrink space cascade;

If for some reason you don’t want to move the high-water mark when you shrink the table, then use the COMPACT clause: