Managing Datafiles and Tempfiles

Managing Datafiles and Tempfiles

Creating Datafiles
CREATE TABLESPACE
CREATE TEMPORARY TABLESPACE ge 8-9
ALTER TABLESPACE ... ADD DATAFILE
ALTER TABLESPACE ... ADD TEMPFILE ge 8-9
CREATE DATABASE
ALTER DATABASE ... CREATE DATAFILE
Enabling and Disabling Automatic Extension for a Datafile
ALTER TABLESPACE users
ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;
Manually Resizing a Datafile
-- if there is space in the datafile
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' RESIZE 100M;
Bringing Datafiles Online or Taking Offline in ARCHIVELOG Mode
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;
Bringing Datafiles Online or Taking Offline in ARCHIVELOG Mode
ALTER DATABASE DATAFILE ... {ONLINE|OFFLINE}
-- all datafiles will be affected in the following code
-- this is different from ALTER TABLESPACE...ONLINE|OFFLINE which controls
tablespace availability
ALTER TABLESPACE ... DATAFILE {ONLINE|OFFLINE}
ALTER TABLESPACE ... TEMPFILE {ONLINE|OFFLINE}
Taking Datafiles Offline in NOARCHIVELOG Mode
 Use it when you want to drop the datafile.

-- datafile cannot be brought ONLINE again
ALTER DATABASE DATAFILE ... OFFLINE FOR DROP;
-- the datafile MUST then be dropped
-- for dictionary managed tablespace
ALTER TABLESPACE ... DROP DATAFILE
DROP TABLESPACE ... INCLUDING CONTENTS AND DATAFILES
Renaming and Relocating Datafiles in a Single Tablespace
ALTER TABLESPACE users OFFLINE NORMAL;
Copy the datafiles to their new locations and rename them using the operating
system.
ALTER TABLESPACE users RENAME DATAFILE
'/u02/oracle/rbdb1/user1.dbf',
'/u02/oracle/rbdb1/user2.dbf'
TO
'/u02/oracle/rbdb1/users01.dbf',
'/u02/oracle/rbdb1/users02.dbf';
-- for system, default temporary, or undo
-- ALTER TABLESPACE cannot be used because you cannot take them OFFLINE
mount the database
ALTER DATABASE RENAME FILE '/u02/oracle/rbdb1/sort01.dbf',
'/u02/oracle/rbdb1/user3.dbf'
TO '/u02/oracle/rbdb1/temp01.dbf', '/u02/oracle/rbdb1/users03.dbf;
Back up the database.
Dropping Datafiles
 The following restrictions apply:
o The database must be open.
o The datafile must be empty, otherwise use drop the tablespace.
o You cannot drop datafiles in a read-only tablespace.
o You cannot drop datafiles in the SYSTEM tablespace.
o If a datafile in a locally managed tablespace is offline, it cannot be dropped.
ALTER TABLESPACE example DROP DATAFILE ...
ALTER TABLESPACE lmtemp DROP TEMPFILE
ALTER DATABASE TEMPFILE .. DROP INCLUDING DATAFILES
Copying a File on a Local File System
 The copied file must meet the following requirements:
o The size must be a multiple of 512 bytes.
o The size must be less than or equal to two terabytes.
 Be aware not to coy a file that is being used by a process.
 If you are copying a database datafile, make it READ ONLY before you start to copy.
CREATE DIRECTORY SOURCE_DIR AS '/usr/admin/source';
CREATE DIRECTORY DEST_DIR AS '/usr/admin/destination';
GRANT READ ON DIRECTORY source_dir TO strmadmin;
GRANT WRITE ON DIRECTORY dest_dir TO strmadmin;
CONNECT strmadmin/strmadminpw
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => 'SOURCE_DIR',
source_file_name => 'db1.dat',

destination_directory_object => 'DEST_DIR',
destination_file_name => 'db1_copy.dat');
END;
Transferring a File to a Different Database
 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, make it READ ONLY before you start to copy.
 You can monitor copying progress using V$SESSION_LONGOPS view.
CREATE DATABASE LINK ODB
CONNECT TO system IDENTIFIED BY system_passwd USING 'prod1';
BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
SOURCE_DIRECTORY_OBJECT => 'SOURCE_DIR',
SOURCE_FILE_NAME => 'mydata1.dbf',
DESTINATION_DIRECTORY_OBJECT => 'DEST_DIR',
DESTINATION_FILE_NAME => 'mydata2.dbf'
DESTINATION_DATABASE => 'ODB.ACME.COM');
END;
/
BEGIN
DBMS_FILE_TRANSFER.GET_FILE(
SOURCE_DIRECTORY_OBJECT => 'SOURCE_DIR',
SOURCE_FILE_NAME => 'TEST01.DBF',
SOURCE_DATABASE => 'ODB.ACME.COM',
DESTINATION_DIRECTORY_OBJECT => 'DEST_DIR',
DESTINATION_FILE_NAME => 'TEST01.DBF');
END;
/
Dumping a Data Block
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 3281;
-- To dump a number of consecutive blocks::
ALTER SYSTEM DUMP DATAFILE 5 BLOCK MIN 42 BLOCK MAX 50;
select value from v$parameter where name='user_dump_dest';
/u01/app/oracle/admin/pasu/udump/pasu_ora_29673.trc
/* to dump index blocks */
-- get object id of the index:
SELECT object_id FROM dba_objects WHERE object_name = 'MYINDEX';
-- do a treedump of the index:
ALTER SESSION SET EVENTS 'immediate trace name treedump level 106315';
index height
distinct index blocks in the lower level |
RBA block at position zero | |
| | | |
branch: 0×1c3588a 29579402 (0: nrow: 222, level: 1)
number of entries
lead block number (starts from -1) | non-deleted entries
| | |

leaf: 0×1c3588b 29579403 (-1: nrow: 485 rrow: 485)
leaf: 0×1c3588c 29579404 (0: nrow: 479 rrow: 479)
leaf: 0×1c3588d 29579405 (1: nrow: 479 rrow: 479)
leaf: 0×1c3588e 29579406 (2: nrow: 479 rrow: 479)
-- define the RBA of the block to dump then
-- get its file# and block#:
SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(223456765),
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(223456765)
FROM dual;
-- dump the header block
ALTER SYSTEM DUMP DATAFILE 7 BLOCK 328745;
-- also, you can find the root block ( it is the block following the header bloc):
SELECT header_file, header_block+1 FROM dba_segments WHERE segment_name='MYINDEX';