Space and Storage Management Enhancements

 

Proactive Tablespace Management

• In Oracle Database 10g, by default, all tablespaces have built-in alerts that notify you when the free space in the tablespace goes below a certain predetermined threshold level.
• By default, Oracle sends out a warning alert when your tablespace is 85 percent full and a critical alert when the tablespace is 97 percent full. This also applies in the undo tablespace.
• If you are migrating to Oracle Database 10g, Oracle turns off the automatic tablespace alerting mechanism by default.

Tablespace Alerts Limitations

• You can set alerts only for locally managed tablespaces.
• When you take a tablespace offline or make it read-only, you must turn the alerting mechanism off.
• You will get a maximum of only one undo alert during any 24-hour period.

Using the Database Control to Manage Thresholds

Manage Metrics link | click the Edit Thresholds button

Using the DBMS_SERVER_ALERT Package

You can use the procedures: SET_THRESHOLD and GET_THRESHOLD in the DBMS_SERVER_ALERT package to manage database thresholds.

Examples:

To set your own databasewide default threshold values for the Tablespace Space Usage metric:

EXECUTE DBMS_SERVER_ALERT.SET_THRESHOLD(

METRICS_ID=>DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,

WARNING_OPERATOR=>DBMS_SERVER_ALERT.OPERATOR_GE,

WARNING_VALUE=>80,

CRITICAL_OPERATOR=>DBMS_SERVER_ALERT.OPERATOR_GE,

CRITICAL_VALUE=>95,

OBSERVATION_PERIOD=>1,

CONSECUTIVE_OCCURRENCES=>1,

INSTANCE_NAME=>NULL,

OBJECT_TYPE=>DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,

OBJECT_NAME=>NULL)

To set a warning threshold of 80% and a critical threshold of 95% on the EXAMPLE tablespace, use the same previous example except OBJECT_NAME parameter should take value of ‘EXAMPLE’

To turn off the space-usage tracking mechanism for the EXAMPLE tablespace:

EXECUTE DBMS_SERVER_ALERT.SET_THRESHOLD(

METRICS_ID=>dbms_server_alert.tablespace_pct_full,

WARNING_OPERATOR=>dbms_server_alert.operator_do_not_check,

WARNING_VALUE=>’0′,

CRITICAL_OPERATOR=>dbms_server_alert.operator_do_not_check,

CRITICAL_VALUE=>’0′,

OBSERVATION_PERIOD=>1,

CONSECUTIVE_OCCURRENCES=>1,

INSTANCE_NAME=>NULL,

OBJECT_TYPE=>dbms_server_alert.object_type_tablespace,

OBJECT_NAME=>’EXAMPLE’)

 

Reclaiming Unused Space

In Oracle Database 10g, you can use the new segment-shrinking capability to make sparsely populated segments give their space back to their parent tablespace.

Restrictions on Shrinking Segments

• You can only shrink segments that use Automatic Segment Space Management.
• You must enable row movement for heap-organized segments. By default, row movement is disabled at the segment level.

ALTER TABLE test ENABLE ROW MOVEMENT;

• You can’t shrink the following:
  • Tables that are part of a cluster
  • Tables with LONG columns,
  • Certain types of materialized views
  • Certain types of IOTs.
  • Tables with function-based indexes.
• In Oracle 10.2 you can also shrink:
  • LOB Segments
  • Function Based Indexes
  • IOT Overflow Segments

Segment Shrinking Phases

There are two phases in a segment-shrinking operation:

Compaction phase

During this phase, the rows in a table are compacted and moved toward the left side of the segment and you can issue DML statements and queries on a segment while it is being shrunk.

Adjustment of the HWM/releasing space phase

During the second phase, Oracle lowers the HWM and releases the recovered free space under the old HWM to the parent tablespace. Oracle locks the object in an exclusive mode.

Manual Segment Shrinking

Manual Segment Shrinking is done by the statement:

ALTER TABLE test SHRINK SPACE

You can shrink all the dependent segments as well:

ALTER TABLE test SHRINK SPACE CASCADE

To only compact the space in the segment:

ALTER TABLE test SHRINK SPACE COMPACT

To shrinks a LOB segment:

ALTER TABLE employees MODIFY LOB(resume) (SHRINK SPACE)

To shrink an IOT overflow segment belonging to the EMPLOYEES table:

ALTER TABLE employees OVERFLOW SHRINK SPACE

Shrinking Segments Using the Database Control

To enable row movement:

Follow the links: Schema, Tables, Edit Tables, then Options.

To shrink a table segment:

Follow the links: Schema, Tables, select from the Actions field Shrink Segments and click Go.

Using the Segment Advisor

Choosing Candidate Objects for Shrinking

The Segment Advisor, to estimate future segment space needs, uses the growth trend report based on the AWR space-usage data.

Follow the links:

Database Home page, Advisor Central in the Related Links, Segment Advisor.

Automatic Segment Advisor

Automatic Segment Advisor is implemented by the AUTO_SPACE_ADVISOR_JOB job. This job executes the DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC procedure at predefined points in time.

When a Segment Advisor job completes, the job output contains the space problems found and the advisor recommendations for resolving those problems.

You can view all Segment Advisor results by navigating to the Segment Advisor Recommendations page. You access this page from the home page by clicking the Segment Advisor Recommendations link in the Space Summary section.

The following views display information specific to Automatic Segment Advisor:

• DBA_AUTO_SEGADV_SUMMARY: Each row of this view summarizes one Automatic Segment Advisor run. Fields include number of tablespaces and segments processed, and number of recommendations made.
• DBA_AUTO_SEGADV_CTL: This view contains control information that Automatic Segment Advisor uses to select and process segments.

 

Object Size Growth Analysis

You plan to create a table in a tablespace and populate it with data. So, you want to estimate its initial size. This can be achieved using Segment Advisor in the EM or its package DBMS_SPACE.

Estimating Object Size using EM

You can use the Segment Advisor to determine your future segment resource usage.

Follow these steps:

1. From the Database Control home page, click the Administration tab.
2. Under the Storage section, click the Tables link.
3. Click the Create button to create a new table.
4. You’ll now be on the Create Table page. Under the Columns section, specify your column data types. Then click the Estimate Table Size button.
5. On the Estimate Table Size page, specify the estimated number of rows in the new table, under Projected Row Count. Then click the Estimated Table Size button. This will show you the estimated table size.

Estimating Object Size using DBMS_SPACE

For example, if your table has 30,000 rows, its average row size is 3 and the PCTFREE parameter is 20. You can issue the following code:

set serveroutput on

DECLARE

V_USED NUMBER;

V_ALLOC NUMBER;

BEGIN

DBMS_SPACE.CREATE_TABLE_COST (

TABLESPACE_NAME => ‘USERS’,

AVG_ROW_SIZE => 30,

ROW_COUNT => 30000,

PCT_FREE => 5,

USED_BYTES => V_USED,

ALLOC_BYTES => V_ALLOC);

DBMS_OUTPUT.PUT_LINE(‘USED: ‘|| V_USED/1024 || ‘ KB’);

DBMS_OUTPUT.PUT_LINE(‘ALLOCATED: ‘|| V_ALLOC/1024 || ‘ KB’);

END;

The USED_BYTES represent the actual bytes used by the data. The ALLOC_BYTES represent the size of the table when it is created in the tablespace. This takes into account, the size of the extents in the tablespace and tablespace extent management properties.

If you want to make the estimation based on the column definitions (not average row size and PCTFREE):

set serveroutput on

DECLARE

UB NUMBER;

AB NUMBER;

CL SYS.CREATE_TABLE_COST_COLUMNS;

BEGIN

CL := SYS.CREATE_TABLE_COST_COLUMNS(

SYS.CREATE_TABLE_COST_COLINFO(‘NUMBER’,10),

SYS.CREATE_TABLE_COST_COLINFO(‘VARCHAR2′,30),

SYS.CREATE_TABLE_COST_COLINFO(‘VARCHAR2′,30),

SYS.CREATE_TABLE_COST_COLINFO(‘DATE’,NULL));

DBMS_SPACE.CREATE_TABLE_COST(‘USERS’,CL,100000,0,UB,AB);

DBMS_OUTPUT.PUT_LINE(‘USED: ‘|| UB/1024 || ‘ KB’);

DBMS_OUTPUT.PUT_LINE(‘ALLOCATED: ‘|| AB/1024 || ‘ KB’);

END;

 

Using the Undo and Redo Logfile Size Advisors

Undo Advisor

The Undo Advisor helps you perform the following tasks:

• Set the undo retention period
• Set the size of the undo tablespace

To access the Undo Advisor in the Database Control:

Follow the links: Database Control home page, Administration, Undo Management button, the Undo Advisor button in the right corner.

Redo Logfile Size Advisor

The Redo Logfile Size Advisor will make recommendations about the smallest online redo log files you can use.

The Redo Logfile Size Advisor is enabled only if you set the FAST_START_MTTR_TARGET parameter.

Check the column OPTIMAL_LOGFILE_SIZE in V$INSTANCE_RECOVERY view to obtain the optimal size of the redo log file for your FAST_START_MTTR_TARGET setting.

To access the Redo Logfile Size Advisor:

1. Database Control home page, Administration, Under the Storage section, Redo Log Groups.
2. Select any redo log group, and then choose the Sizing Advice option from the Action drop-down list, Click Go

 

Rollback Monitoring

In Oracle Database 10g, when a transaction rolls back, the event is recorded in the view V$SESSION_LONGOPS, if the process takes more than six seconds. This view enables you to estimate when the monitored rollback process will finish.

SELECT TIME_REMAINING,

SOFAR/TOTALWORK*100 PCT

FROM V$SESSION_LONGOPS

WHERE SID = 9

AND OPNAME =’Transaction Rollback’

 

Tablespace Enhancements

Managing the SYSAUX Tablespace

• Some Oracle features use SYSAUX in its operation.
• SYSAUX is mandatory in any database.
• SYSAUX cannot be dropped, renamed or transported.
• Oracle recommends that you create the SYSAUX tablespace with a minimum size of 240MB.

Creating SYSAUX

• DBCA creates it automatically and asks you about its configuration.
• Can be included in the manual database creation:

CREATE DATABASE mydb

USER SYS IDENTIFIED BY mysys

USER SYSTEM IDENTIFIED BY mysystem

..

SYSAUX DATAFILE ‘c:\..\sysaux01.dbf’ SIZE 500M

If you omit the SYSAUX clause, Oracle will create the SYSAUX tablespace automatically with their datafiles in location defined by the following rules:

• If you are using Oracle Managed Files (OMF), the location will be on the OMF.
• If OMF is not configured, default locations will be system-determined.
• If you include the DATAFILE clause for the SYSTEM tablespace, you must use the DATAFILE clause for the SYSAUX tablespace as well, unless you are using OMF.

You can use ALTER TABLESPACE command to add a datafile though.

Relocating SYSAUX Occupants

If there is a severe space pressure on the SYSAUX tablespace, you may decide to move components out of the SYSAUX tablespace to a different tablespace.

• Query the column SPACE_USAGE_KBYTES in the V$SYSAUX_OCCUPANTS to how much of the SYSAUX tablespace’s space each of its occupants is currently using.
• Query the column MOVE_PROCEDURE to obtain the specific procedure you must use in order to move a given occupant out of the SYSAUX tablespace.

SQL> exec dbms_wm.move_proc(‘DRSYS’);

Note: You can’t relocate the following occcupants of the SYSAUX tablespace: STREAMS, STATSPACK, JOB_SCHEDULER, ORDIM, ORDIM/PLUGINS, ORDIM/SQLMM, and SMC.

Renaming Tablespaces

In Oracle Database 10g, you can rename tablespaces:

ALTER TABLESPACE users RENAME TO users_new

Restrictions:

• Your compatibility level must be set to 10.0 or higher.
• You can’t rename the SYSTEM or SYSAUX tablespace, or offline tablespaces.
• If the tablespace is read-only, the datafile headers aren’t updated, although the control file and the data dictionary are.

Renaming Undo Tablespace

• If database started using init.ora file, Oracle retrieves a message that you should set value of UNDO_TABLESPACE parameter.
• If database started using spfile, Oracle will automatically write the new name for the undo tablespace in your spfile.

Specifying the Default Permanent Tablespace

During Database Creation

Use DEFAULT TABLESPACE clause in the CREATE DATABASE command

CREATE DATABASE mydb

…

DEFAULT TABLESPACE deftbs DATAFILE …

If DEFAULT TABLESPACE not specified, SYSTEM tablespace will be used.

Note: The users SYS, SYSTEM, and OUTLN continue to use the SYSTEM tablespace as their default permanent tablespace.

After Database Creation Using SQL

Use ALTER DATABASE command as follows:

ALTER DATABASE DEFAULT TABLESPACE new_tbsp;

Using the Database Control

1. Database Control home page, Administration, Storage Section, Tablespaces.
2. Edit Tablespace page, select the Set As Default Permanent Tablespace option in the Type section. Then click Apply.

Viewing Default Tablespace Information

SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME=’DEFAULT_PERMANENT_TABLESPACE’

Temporary Tablespace Groups

A temporary tablespace group is a list of temporary tablespaces.

It has the following advantages:

• You define more than one default temporary tablespace, and a single SQL operation can use more than one temporary tablespace for sorting. This prevents large tablespace operations from running out of temporary space.
• Enables one particular user to use multiple temporary tablespaces in different sessions at the same time
• Enables the slave processes in a single parallel operation to use multiple temporary tablespaces

Creating a Temporary Tablespace Group

You implicitly create a temporary tablespace group when you specify the TABLESPACE GROUP clause in a CREATE TABLESPACE statement:

CREATE TEMPORARY TABLESPACE temp_old TEMPFILE

‘/u01/oracle/oradata/temp01.dbf’ SIZE 500M

TABLESPACE GROUP group1;

You can also create a temporary tablespace group by:

ALTER TABLESPACE temp_old

TABLESPACE GROUP group1

Note: If you specify the NULL or ” tablespace group, it is equivalent to the normal temporary tablespace creation statement (without any groups).

Setting a Group As the Default Temporary Tablespace for the Database

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE group1

Assigning a Temporary Tablespace Group to Users

CREATE USER sam IDENTIFIED BY sam

DEFAULT TABLESPACE users

TEMPORARY TABLESPACE group1;

ALTER USER SAM TEMPORARY TABLESPACE GROUP2;

Viewing Temporary Tablespace Group Information

Use the following views:

• DBA_TABLESPACE_GROUPS
• DBA_USERS

Bigfile Tablespaces

• A bigfile tablespace (BFT) contains only one very large file (can be as large as 8 to 128 terabytes depending on block size).
• The main benefit is easier management of tablespaces and their datafiles in very large databases (VLDB). All operations that were performed on data files in previous releases can now be performed on BFT tablespaces. For example: ALTER TABLESPACE … RESIZE

Big File Teblespaces Restrictions

• You use bigfile tablespaces along with a Logical Volume Manager (LVM) or the Automatic Storage Management (ASM) feature, which support striping and mirroring.
• Both parallel query execution and RMAN backup parallelization would be adversely impacted, if you used bigfile tablespaces without striping.
• You cannot change tablespace type from smallfile to bigfile or vice versa. However, you can migrate object between tablespace types by using either the ALTER TABLE … MOVE or CREATE TABLE … AS
• To avoid performance implications, use the following table as a guide to the maximum number of extents for a BFT with specific block size. If the expected size requires more extents than specified in the table, you can create the tablespace with UNIFORM option (instead of AUTOALLOCATE) with a large extend size.

Database Block Size	Recommended Maximum Number of Extents
2 KB	100,000
4 KB	200,000
8 KB	400,000
16 KB	800,000

Making Bigfile the Default Tablespace Type

Once you set the default type of your tablespace, all the tablespaces you subsequently create will be by default of the bigfile type:

CREATE DATABASE test

SET DEFAULT BIGFILE TABLESPACE … ;

ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;

You can view the default tablespace type using the following command:

SELECT PROPERTY_VALUE

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME=’DEFAULT_TBS_TYPE’

Creating a Bigfile Tablespace Explicitly

CREATE BIGFILE TABLESPACE bigtbs

DATAFILE ‘/u01/oracle/data/bigtbs_01.dbf’ SIZE 100G …

When you use the BIGFILE clause, Oracle will automatically create a locally managed tablespace with automatic segment-space management (ASSM).

You can use the keyword SMALLFILE in replacement with BIGFILE clause.

Altering a Bigfile Tablespace’s Size

ALTER TABLESPACE bigtbs RESIZE 120G;

ALTER TABLESPACE bigtbs AUTOEXTEND ON NEXT 20G;

Viewing Bigfile Tablespace Information

All the following views have the new YES/NO column BIGFILE:

• DBA_TABLESPACES
• USER_TABLESPACES
• V$TABLESPACE

Bigfile Tablespaces and ROWID Formats

	Bigfile tablespace	Smallfile tablespace
Format	Object# – Block# – Row#	Object# – File# – Block# – Row#
block number size	Can be much larger than smallfile tbs.	Is smaller than bigfile tbs.

For bigfile tablespaces, there is only a single file, with the relative file number always set to 1024.

The only supported way to extract the ROWID components is by using the DBMS_ROWID package.

You can specify the tablespace type by using the new parameter TS_TYPE_IN, which can take the values BIGFILE and SMALLFILE.

SELECT DISTINCT DBMS_ROWID.ROWID_RELATIVE_FNO (rowid,’BIGFILE ‘) FROM test_rowid

Note: The functions DATA_BLOCK_ADDRESS_FILE and DATA_BLOCK_ADDRESS_BLOCK in the package DBMS_UTILITY do not return the expected results with BFTs.

Bigfile Tablespaces and DBVERIFY

You cannot run multiple instances of DBVERIFY utility in parallel against BFT. However, integrity-checking parallelism can be achieved with BFTs by starting multiple instances of DBVERIFY on parts of the single large file. In this case, you have to explicitly specify the starting and ending block addresses for each instance.

dbv FILE=BFile1 START=1 END=10000

dbv FILE=BFile1 START=10001

Viewing Tablespace Contents

You can obtain detailed information about the segments in each tablespace using Enterprise Manager.

On the Tablespaces page, select the tablespace of interest, choose Show Tablespace Contents from the Actions drop-down list, and click Go. The Processing: Show Tablespace Contents page is displayed.

 

Using Sorted Hash Clusters

Sorted hash clusters are new data structures that allow faster retrieval of data for applications where data is consumed in the order in which it was inserted.

In a sorted hash cluster, the table’s rows are already presorted by the sort key column.

Here are some of its main features:

• You can create indexes on sorted hash clusters.
• You must use the cost-based optimizer, with up-to-date statistics on the sorted hash cluster tables.
• You can insert row data into a sorted hash clustered table in any order, but Oracle recommends inserting them in the sort key column order, since it’s much faster.

Creating Sorted Hash Cluster

CREATE CLUSTER call_cluster

(call_number NUMBER,

call_timestamp NUMBER SORT,

call_duration NUMBER SORT)

HASHKEYS 10000

SINGLE TABLE

HASH IS call_number

SIZE 50;

SINGLE TABLE	indicates that the cluster is a type of hash cluster containing only one table.
HASH IS expr	Specifies an expression to be used as the hash function for the hash cluster.
HASHKEYS	this clause creates a hash cluster and specify the number of hash values for the hash cluster.
SIZE	Specify the amount of space in bytes reserved to store all rows with the same cluster key value or the same hash value.

CREATE TABLE calls

(call_number NUMBER,

call_timestamp NUMBER,

call_duration NUMBER,

call_info VARCHAR2(50))

CLUSTER call_cluster

(call_number,call_timestamp,call_duration)

 

Partitioned IOT Enhancements

The following are the newly supported options for partitioned index-organized tables (IOTs):

• List-partitioned IOTs: All operations allowed on list-partitioned tables are now supported for IOTs.
• Global index maintenance: With previous releases of the Oracle database, the global indexes on partitioned IOTs were not maintained when partition maintenance operations were performed. After DROP, TRUNCATE, or EXCHANGE PARTITION, the global indexes became UNUSABLE. Other partition maintenance operations such as MOVE, SPLIT, or MERGE PARTITION did not make the global indexes UNUSABLE, but the performance of global index–based access was degraded because the guess–database block addresses stored in the index rows were invalidated. Global index maintenance prevents these issues from happening, keeps the index usable, and also maintains the guess–data block addresses.
• Local partitioned bitmap indexes: The concept of a mapping table is extended to support a mapping table that is equi-partitioned with respect to the base table. This enables the creation of bitmap indexes on partitioned IOTs.
• LOB columns are now supported in all types of partitioned IOTs.

 

Redefine a Partition Online

The package DBMS_REDEFINITION is known to be used as a tool to change the definition of the objects while keeping them accessible (online). In previous versions, if you use it to move a partitioned table to another tablespace, it will move the entire table. This results in massive amount of undo and redo generation.

In Oracle 10g, you can use the package to move a single partition (instead of the entire table). The following code illustrates the steps you follow.

1. Confirm that you can redefine the table online. Having no output after running the following code means the online redefinition is possible:

BEGIN

DBMS_REDEFINITION.CAN_REDEF_TABLE(

UNAME => ‘HR’,

TNAME => ‘customers’,

OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_ROWID,

PART_NAME => ‘p1′);

END;

1. Create a temporary (interim) table to hold the data for that partition:

CREATE TABLE hr.customers_int

TABLESPACE custdata

AS

SELECT * FROM hr.customers

WHERE 1=2;

Note: If the table customers had some local indexes, you should create those indexes (as non-partitioned, of course) on the table customers_int.

1. Start the redefinition process:

BEGIN

DBMS_REDEFINITION.START_REDEF_TABLE(

UNAME => ‘HR’,

ORIG_TABLE => ‘customers’,

INT_TABLE => ‘customers_int’,

PART_NAME => ‘p1′ ); — partition to move

END;

1. If there were DML operations against the table during the move process, you should synchronize the interim table with the original table:

BEGIN

DBMS_REDEFINITION.SYNC_INTERIM_TABLE (

UNAME => ‘HR’,

ORIG_TABLE => ‘customers’,

INT_TABLE => ‘customers_int’,

COL_MAPPING => NULL,

OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_ROWID,

PART_NAME => ‘p1′ );

END;

1. Finish the redefinition process:

BEGIN

DBMS_REDEFINITION.FINISH_REDEF_TABLE (

UNAME => ‘HR’,

ORIG_TABLE => ‘customers’,

INT_TABLE => ‘customers_int’,

PART_NAME => ‘p1′);

END;

To confirm the partition P1 was moved to the new tablespace:

SELECT PARTITION_NAME, TABLESPACE_NAME, NUM_ROWS

FROM USER_TAB_PARTITIONS

WHERE PARTITION_NAME=’P1′

Note: If there is any global index on the table, they will be marked as UNUSABLE and must be rebuilt.

Note: You cannot change the structure of the table during the definition process.

Note: statistics of object moved with this tool are automatically generated by end of the process.

 

Copying Files Using the Database Server

The DBMS_FILE_TRANSFER package helps you copy binary files to a different location on the same server or transfer files between Oracle databases.

Both the source and destination files should be of the same type, either operating system files or ASM files.

The maximum file size is 2 terabytes, and the file must be in multiples of 512 bytes.

You can monitor the progress of all your file-copy operations using the V$SESSION_LONGOPS view.

Copying Files on a Local System

CREATE DIRECTORY source_dir AS ‘/u01/app/oracle’;

CREATE DIRECTORY dest_dir AS ‘/u01/app/oracle/example’;

BEGIN

DBMS_FILE_TRANSFER.COPY_FILE(

SOURCE_DIRECTORY_OBJECT => ‘SOURCE_DIR’,

SOURCE_FILE_NAME => ‘exm_old.txt’,

DESTINATION_DIRECTORY_OBJECT => ‘DEST_DIR’,

DESTINATION_FILE_NAME => ‘exm_new.txt’);

END;

Transferring a File to a Different Database

BEGIN

DBMS_FILE_TRANSFER.PUT_FILE(

SOURCE_DIRECTORY_OBJECT => ‘SOURCE_DIR’,

SOURCE_FILE_NAME => ‘exm_old.txt’,

DESTINATION_DIRECTORY_OBJECT => ‘DEST_DIR’,

DESTINATION_FILE_NAME => ‘exm_new.txt’

DESTINATION_DATABASE => ‘US.ACME.COM’);

END;

In order to transfer a file the other way around, you must replace the PUT_FILE procedure with the GET_FILE procedure.

If you are copying a database datafile, do not forget to make it READ ONLY before you start to copy.

You can monitor copying progress using V$SESSION_LONGOPS view.

 

Dropping Partitioned Table

In previous versions, if you drop a partitioned table, Oracle removes all the partitions at once. This led to a time and resource consuming process.

In Oracle Database 10g Release 2, when you drop a partitioned table, partitions are dropped one by one. Because each partition is dropped individually, fewer resources are required than when the table is dropped as a whole.

 

Dropping Empty Datafiles

In Oracle 10g release 2, empty datafiles can be dropped

ALTER TABLESPACE test DROP DATAFILE ‘hr1.dbf’;

You cannot drop non-empty datafiles

ORA-03262: the file is non-empty

You cannot drop first file in tablespace

ORA-03263: cannot drop the first file of tablespace HR

 

Renaming Temporary Files

In Oracle 10.2 temporary files can be renamed.

ALTER DATABASE TEMPFILE ‘temp1.dbf’ OFFLINE

$ mv temp1.dbf temp2.dbf

ALTER DATABASE RENAME FILE ‘temp1.dbf’ TO ‘temp2.dbf’

ALTER DATABASE TEMPFILE ‘temp1.dbf’ ONLINE