Tablespace Management 1

Tablespace Management

Managing tablespaces in Oracle databases is an essential task for database administrators.

Let’s dive into some detailed scripts for creating, resizing, and managing tablespaces: Monitoring Tablespace: 1. SELECT df.tablespace_name "Tablespace", round(sum(fs.bytes)/(1024*1024),2) "Free Space (MB)", round(sum(df.bytes)/(1024*1024),2) "Total Space (MB)", round((sum(fs.bytes)/sum(df.bytes))*100,2) "Percent Free" FROM dba_free_space fs, (SELECT tablespace_name, sum(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) df WHERE fs.tablespace_name(+) = df.tablespace_name GROUP BY df.tablespace_name; 2. BREAK ON tablespace_name SKIP 2 COMPUTE SUM OF allocated_bytes, free_bytes ON tablespace_name COLUMN allocated_bytes FORMAT 9,999,999,999 COLUMN free_bytes FORMAT 9,999,999,999 SELECT a.tablespace_name, a.file_name, a.bytes allocated_bytes, b.free_bytes FROM dba_data_files a, (SELECT file_id, SUM(bytes) free_bytes FROM dba_free_space b GROUP BY file_id) b WHERE a.file_id=b.file_id ORDER BY a.tablespace_name; 3. col "Tablespace" for a22 col "Used MB" for 9,999,999.99 col "Free MB" for 9,999,999.99 col "Total MB" for 9,999,999.99 col extendable_free_space for 9,999,999.99 col "Pct. Free" for 999,999.99 col "maxspace" for 999,999.99 set linesize 112 select df.tablespace_name "Tablespace", totalusedspace "Used MB", (df.totalspace - tu.totalusedspace) "Free MB", df.totalspace "Total MB", round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace),2) "Pct. Free" from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df, (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu where df.tablespace_name = tu.tablespace_name; 4. select df.tablespace_name "Tablespace", nvl(totalusedspace,0) "Used MB", (df.totalspace - nvl(tu.totalusedspace,0)) "Free MB", df.totalspace "Total MB", round(100 * ( (df.totalspace - nvl(tu.totalusedspace,0))/ df.totalspace),2) "Pct. Free", nvl(fs.free_space,0) extendable_free_space , round(maxspace,2) maxspace from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace , sum(maxbytes)/1024/1024 maxspace from dba_data_files group by tablespace_name) df, (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu , ( select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space from dba_free_space group by tablespace_name ) fs where df.tablespace_name = tu.tablespace_name(+) AND df.tablespace_name = fs.tablespace_name(+) ORDER BY "Pct. Free"; DISPLAYS TOP 10 LARGEST SEGMENTS IN A GIVEN TABLEPACE col owner format a15 col segment_name format a35 select * from ( select owner, segment_Type, segment_name, bytes/1024 kbytes from dba_segments where tablespace_name = '&ts_name' order by bytes/1024 desc ) where rownum < 10 / Datafile creation time: select to_char(CREATION_TIME,'DD-MON-YYYY HH24:MI:SS'),NAME from v$datafile order by creation_time;

Datafile name and size of given tablespace:

select file_name,bytes/1024/1024 "size in mb" from dba_data_files where tablespace_name='&tblname'; Report for Given Tablespace: set pages 100 set line 300 select f.tbs "Tablespace Name",df.tot" TotalSpace(MB)",ds.used "TotalSpaceUsed" ,f.free "TotalSpaceFree", to_char((ds.used/df.tot)*100,'00.99ercentage_Used from (select tablespace_name tbs,sum(bytes)/1024/1024 free from dba_free_space group by tablespace_name) f, (select tablespace_name tbs,sum(bytes)/1024/1024 tot from dba_data_files group by tablespace_name ) df, (select tablespace_name tbs,sum(bytes)/1024/1024 used from dba_segments group by tablespace_name ) ds where df.tbs=f.tbs and f.tbs=ds.tbs and ds.tbs='&tblspc_name order by f.tbs; Creating a New Tablespace: CREATE TABLESPACE DATA DATAFILE '/u01/dbaclass/oradata/data01.dbf' SIZE 5G AUTOEXTEND ON NEXT 500M; CREATE TABLESPACE data_tbs DATAFILE '/u01/app/oracle/oradata/dbname/data_tbs01.dbf' SIZE 50M AUTOEXTEND ON NEXT 10M MAXSIZE 2G; If you’re using ASM (Automatic Storage Management), you can create a tablespace on an ASM disk group like this: CREATE TABLESPACE DATA DATAFILE '+DATAG' SIZE 5G AUTOEXTEND ON NEXT 500M; Alternatively, you can specify the complete path for the datafile: CREATE TABLESPACE DATA DATAFILE '+DATAG/oradata/datafile/data01.dbf' SIZE 5G AUTOEXTEND ON NEXT 500M; Adding a Datafile to an Existing Tablespace: ALTER TABLESPACE DATA ADD DATAFILE '/u01/dbaclass/oradata/data02.dbf' SIZE 2G; Resizing a Datafile: ALTER DATABASE DATAFILE '/u01/dbaclass/oradata/data02.dbf' RESIZE 3G; Creating a Bigfile Tablespace: CREATE BIGFILE TABLESPACE BIGTS DATAFILE '/u01/dbaclass/oradata/bigts01.dbf' SIZE 100G AUTOEXTEND ON NEXT 1G; Check BigFile tablespace and Small File tablespace: select TABLESPACE_NAME, BIGFILE from DBA_TABLESPACES; Script to check temp tablespace utilization: select file#, name, round(bytes/(1024*1024),2) "Temp file SIZE in MB's" from v$tempfile; Script to add tempfile space in temp tablespace: alter tablespace TEMP add tempfile '/home/oracle/app/oracle/oradata/asdb/temp02.dbf' size 10M; Specifying Nonstandard Block Sizes for Tablespaces: CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K BLOCKSIZE 8K; In order for the BLOCKSIZE clause to succeed, you must have already set the DB_CACHE_SIZE and

at least one DB_nK_CACHE_SIZE initialization parameter. Further, and the integer you specify

in the BLOCKSIZE clause must correspond with the setting of one DB_nK_CACHE_SIZE parameter

setting. Although redundant, specifying a BLOCKSIZE equal to the standard block size, as

specified by the DB_BLOCK_SIZE initialization parameter, is allowed. Taking Tablespaces Offline: ALTER TABLESPACE users OFFLINE NORMAL;

You may want to take a tablespace offline for any of the following reasons: • To make a portion of the database unavailable while allowing normal access to the remainder of the database • To perform an offline tablespace backup (even though a tablespace can be backed up while online and in use) • To make an application and its group of tables temporarily unavailable while updating or maintaining the application • To rename or relocate tablespace data files A tablespace can be taken offline normally if no error conditions exist for any of the data

files of the tablespace. No data file in the tablespace can be currently offline as the result of a write error.

When you specify OFFLINE NORMAL, the database takes a checkpoint for all data files of the tablespace as it takes them offline. NORMAL is the default.A tablespace can be taken offline temporarily, even if there are error conditions for one or more files of the tablespace. When you specify OFFLINE TEMPORARY,

the database takes offline the data files that are not already offline, checkpointing them as it does so.If no files are offline, but you use the temporary clause, media recovery is not required to bring the tablespace back online. However,

if one or more files of the tablespace are offline because of write errors, and you take the tablespace offline temporarily, the tablespace requires recovery before you can bring it back online.

A tablespace can be taken offline immediately, without the database taking a checkpoint on any of the data files. When you specify OFFLINE IMMEDIATE,

media recovery for the tablespace is required before the tablespace can be brought online. You cannot take a tablespace offline immediately if the database is running in NOARCHIVELOG mode. Bringing Tablespaces Online: ALTER TABLESPACE users ONLINE; Making a Tablespace Read-Only: ALTER TABLESPACE flights READ ONLY; You must have the ALTER TABLESPACE or MANAGE TABLESPACE system privilege. Before you can make a tablespace read-only, the following conditions must be met. • The tablespace must be online. This is necessary to ensure that there is no undo information that must be applied to the tablespace. • The tablespace cannot be the active undo tablespace or SYSTEM tablespace. • The tablespace must not currently be involved in an online backup, because the end of a backup updates the header file of all data files in the tablespace. • The tablespace cannot be a temporary tablespace. For better performance while accessing data in a read-only tablespace, you can issue a query that accesses all of the blocks of the tables in the tablespace just before making it read-only.

A simple query, such as SELECT COUNT (*), executed against each table ensures that the data blocks in the tablespace can be subsequently accessed most efficiently. This eliminates the need for the database

to check the status of the transactions that most recently modified the blocks. If you find it is taking a long time for the ALTER TABLESPACE statement to complete, then you can identify the transactions that are preventing the read-only state from taking effect. The following example identifies the transaction entry for the ALTER TABLESPACE...READ ONLY statement and displays its session address (saddr): SELECT SQL_TEXT, SADDR FROM V$SQLAREA,V$SESSION WHERE V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS AND SQL_TEXT LIKE 'alter tablespace%'; SQL_TEXT SADDR ---------------------------------------- -------- alter tablespace tbs1 read only 80034AF0 The start SCN of each active transaction is stored in the V$TRANSACTION view. SELECT SES_ADDR, START_SCNB FROM V$TRANSACTION ORDER BY START_SCNB; SES_ADDR START_SCNB -------- ---------- 800352A0 3621 --> waiting on this txn 80035A50 3623 --> waiting on this txn 80034AF0 3628 --> this is the ALTER TABLESPACE statement 80037910 3629 --> don't care about this txn You can now find the owners of the blocking transactions SELECT T.SES_ADDR, S.USERNAME, S.MACHINE FROM V$SESSION S, V$TRANSACTION T WHERE T.SES_ADDR = S.SADDR ORDER BY T.SES_ADDR SES_ADDR USERNAME MACHINE -------- -------------------- -------------------- 800352A0 DAVIDB DAVIDBLAP --> Contact this user 80035A50 MIKEL LAB61 --> Contact this user 80034AF0 DBA01 STEVEFLAP 80037910 NICKD NICKDLAP Making a Read-Only Tablespace Writable: ALTER TABLESPACE flights READ WRITE; You must have the ALTER TABLESPACE or MANAGE TABLESPACE system privilege. Drop Tablespace: drop tablespace test_tbs including contents and datafiles; Tablespace Coalesce: ALTER TABLESPACE USERS COALESCE;

Tablespace Coalesce combines all contiguous free extents into larger contiguous extents inside all datafiles. It takes any free extents that are right next to some other free extent and make one bigger free extent. SMON will perform this coalescing in the background but if you need it to happen right now, coalesce will do it.