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: