Feeds:
Posts
Comments

Posts Tagged ‘Tablespace’

Used managed backup means you take backup without any oracle feature. Suppose if you take backup by Operating system then it is called user managed backup. And the term hot backup means taking your backup whenever your database is at open state.

To take full database backup follow the following steps.

1)Before proceed remember you can take online/hot backup whenever your database is in Archivelog mode. If your database run on noarchivelog mode then you must take consistent backup that is after cleanly shutdown. In order to determine the archival mode, issue the query,

SQL> SELECT LOG_MODE FROM V$DATABASE;
LOG_MODE
————
ARCHIVELOG

If you see ARCHIVELOG then you can proceed further. In order to take backup while you are in noarhivelog mode follow other post on my blog.

2)Determine the files that you need to take backup.

Whenever you decide to take your database backup then take backup of data files , online redo log files ,control files, spfile.

In order to decide which files you need to backup issue the following query.

SQL>SELECT NAME “File Need Backup” FROM V$DATAFILE
UNION ALL
SELECT MEMBER FROM V$LOGFILE
UNION ALL
SELECT NAME FROM V$CONTROLFILE
UNION ALL
SELECT VALUE FROM V$PARAMETER WHERE NAME=’spfile’;

File Need Backup
——————————————————————————–
/oradata2/data1/dbase/system01.dbf
/oradata2/data1/dbase/undotbs01.dbf
/oradata2/data1/dbase/sysaux01.dbf
/oradata2/data1/dbase/users01.dbf
/oradata2/data.dbf
/oradata2/data1/data02.dbf
/oradata2/6.dbf
/oradata2/DBASE/datafile/o1_mf_permanen_42l31vg0_.dbf
/oradata2/data_test.dbf
/oradata2/data1/dbase/redo03.log
/oradata2/data1/dbase/redo02.log
/oradata2/data1/dbase/redo01.log
/oracle/app/oracle/product/10.2.0/db_1/dbs/cntrldupbase.dbf
/oracle/app/oracle/product/10.2.0/db_1/dbs/spfiledupbase.ora
13 rows selected.

So after running the above query I can say I need to backup 13 files.

3)Take the tablespace in backup mode rather than offline and read-only tablespace. In case of offline and read only tablespace you do not have to place the tablespace in backup mode because the database is not permitting changes to the datafiles.

You can check the status, tablespace_name and it’s associated data file name with the following query,

SELECT t.STATUS,t.TABLESPACE_NAME “Tablespace”, f.FILE_NAME “Datafile”
FROM DBA_TABLESPACES t, DBA_DATA_FILES f
WHERE t.TABLESPACE_NAME = f.TABLESPACE_NAME;
ORDER BY t.NAME;

Take the tablespace in backup mode rather than offline and read-only tablespace.

You can easily make a script of taking the online tablespace in backup mode by following query.

SQL>SELECT ‘ALTER TABLESPACE ‘ ||TABLESPACE_NAME ||’ BEGIN BACKUP;’ “Script” FROM DBA_TABLESPACES WHERE STATUS NOT IN (‘READ ONLY’,’OFFLINE’);

Script
————————————————————-
ALTER TABLESPACE SYSTEM BEGIN BACKUP;
ALTER TABLESPACE UNDOTBS1 BEGIN BACKUP;
ALTER TABLESPACE SYSAUX BEGIN BACKUP;
ALTER TABLESPACE TEMP BEGIN BACKUP;
ALTER TABLESPACE USERS BEGIN BACKUP;
ALTER TABLESPACE TEMP_T BEGIN BACKUP;

6 rows selected.

Alternatively, you can issue

SQL>ALTER DATABASE BEGIN BACKUP;

4)Copy the datafile to backup location.

After making a tablespace in backup mode take backup/copy of the associated datafiles. Here you can also make a script in order to copy datafiles to another location.

For online tablespace you must at first take it backup mode. You can check whether backup mode now active or not by issuing following query,

SQL>SELECT t.name AS “TB_NAME”, d.file# as “DF#”, d.name AS “DF_NAME”, b.status
FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS=’ACTIVE’;

SQL> SELECT ‘host scp ‘|| FILE_NAME || ‘ &backup_location ‘ “Backup Command” FROM DBA_DATA_FILES;
Enter value for backup_location: /backup
old 1: SELECT ‘host scp ‘|| FILE_NAME || ‘ &backup_location ‘ “Backup Command” FROM DBA_DATA_FILES
new 1: SELECT ‘host scp ‘|| FILE_NAME || ‘ /backup ‘ “Backup Command” FROM DBA_DATA_FILES

Backup Command
——————————————————————————————
host scp /oradata2/data1/dbase/system01.dbf /backup
host scp /oradata2/data1/dbase/undotbs01.dbf /backup
host scp /oradata2/data1/dbase/sysaux01.dbf /backup
host scp /oradata2/data1/dbase/users01.dbf /backup
host scp /oradata2/data.dbf /backup
host scp /oradata2/data1/data02.dbf /backup
host scp /oradata2/6.dbf /backup
host scp /oradata2/DBASE/datafile/o1_mf_permanen_42l31vg0_.dbf /backup
host scp /oradata2/data_test.dbf /backup

9 rows selected.

Also you can backup network files. Do a recursive search for *.ora starting in your Oracle home directory and under it.

In order to make script for to copy data files for those tablespace which are only in backup mode then issue,

SQL>SELECT ‘host scp ‘|| d.name ||’ &backup_location’ FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS=’ACTIVE’;

Run the script that you genereted.

On windows or other operating system you can use graphical browser to copy or other associated copy command.

5)Whenever you copy is finished make the tablespace out of backup mode. You can issue BEGIN BACKUP .. SCP serially (Take one tablespace in begin backup mode and then copy the associated datafiles and make the tablespace out of backup mode) or you can do it parallely(Take all tablespaces in begin backup mode and then copy the associated datafiles of all tabelspaces and then make the tablespace out of backup mode).

You here also make a script like,

SQL>SELECT ‘ALTER TABLESPACE ‘ ||t.name ||’ END BACKUP;’ “End Backup Script”
FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS=’ACTIVE’;
End Backup Script
———————————————————-
ALTER TABLESPACE SYSTEM END BACKUP;

You if you have taken Database in backup mode then issue

SQL>ALTER DATABASE END BACKUP;

Read Full Post »

In previuos post we disscused how to find tablespace frgmantation.Here we will disscus how to avoide table fragmentation in oracle.

Once we identify tablespace fragmentation , what do you do about it?  Honeycomb fragmentation is easy to fix.  All that needs to be done is to combine adjacent free segments into one by issuing a coalesce  statement:

alter tablespace USERS coalesce;

 Bubble fragmentation  is more difficult to handle.  Of course, the best course of action is to prevent it in the first place.  The best weapon for this is to use locally-managed tablespaces.If you are using Oracle 8.1.6 or higher you can convert any current dictionary-managed  tablespaces to locally-managed tablespaces.

sys.dbms_space_admin .tablespace_migrate_to_local(‘USERS’)

If you are afraid of how long this procedure might take on large tablespaces, do not be.  It actually runs very fast.  If, for some reason, you would like to take a tablespace that is locally-managed back to dictionary management, you can issue this command:

sys.dbms_space_admin .tablespace_migrate_from_local(‘USERS’)

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

Oracle strongly recommended to use the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
provided with the DBMS_MGMT package in order to move audit trail table out of SYSTEM tablespace. But the procedure SET_AUDIT_TRAIL_LOCATION by default is not available until 11g. It is available in 11g.

However with manual script you can move SYS.AUD$ table out of SYSTEM tablespace. But you need to remember moving AUD$ out of SYSTEM tablespace is not a
supported procedure. Oracle does not support changing ownership of AUD$, or any
triggers on it.

Below is the scripts that you can do as your own risk,
Step 01: Connect to database as SYS user.
conn / as sysdba

Step 02: Create tablespace where audit file will reside.
create tablespace AUDIT_TBS
datafile ‘/oracle/TST/datafile/aud01.dbf’ size 500M;

Step 03: Create audit table inside AUDIT_TBS
create table aud_aux tablespace AUDIT_TBS as select * from aud$ where 1 = 2;
Note that no rows will be created in this state.

SQL> select count(*) from aud$; 

  COUNT(*)
———-
         32706

Step 04: Rename the original Audit table.
rename AUD$ to AUD$$;

SQL> select count(*) from aud$$; 

  COUNT(*)
———-
         32706
Step 05: Rename the aud_aux to AUD$
rename aud_aux to aud$;

SQL> select count(*) from aud$; 

  COUNT(*)
———-
         0
Step 06: Create Index on the AUD$ table.
create index aud_i
on aud$(sessionid, ses$tid)
tablespace AUDIT_TBS;

Read Full Post »

One day I was at client side where need to increase the speed of insert statement.I sneed to insert over 500K record to database as quickly as possible. We were inserting at a rate of 10 records/sec. Well, I was thinking the following approaches to gain speed-

1. Use a large blocksize – By defining large (i.e. 16k or 32k) blocksizes for the target tables, we can reduce I/O because more rows fit onto a block before a “block full” condition (as set by PCTFREE) unlinks the block from the freelist.

>DROP TABLESPACE web_key_tbs INCLUDING CONTENTS AND DATAFILES;
>CREATE TABLESPACE web_key_tbs DATAFILE ‘/mnt/extra/test1.dbf’ SIZE 1024M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED blocksize 16K;

2. Increase the size of UNDO tablespace –

>CREATE UNDO TABLESPACE UNDOTBS DATAFILE ‘/mnt/extra/test_undo1.dbf’ SIZE 200M BLOCKSIZE 16K;
>alter system set undo_tablespace=’UNDOTBS‘ scope=both;
>DROP TABLESPACE UNDOTBSP1 INCLUDING CONTENTS AND DATAFILES;

3. APPEND into tables – By using the APPEND hint, it ensures that Oracle always grabs fresh data blocks by raising the high-water-mark for the table.

>insert /*+ append */ into customer values (’hello’,’;there’);

4. Table into NOLOGGING mode – Putting the table into NOLOGGING mode, which will allow Oracle to avoid almost all redo logging.

>SELECT logging FROM user_tables WHERE table_name = ‘LOGIN’;
>ALTER TABLE login NOLOGGING;

Again, to enable logging –
>ALTER TABLE login LOGGING;

5. Disable/drop indexes – It’s far faster to rebuild indexes after the data load, all at-once. Also indexes will rebuild cleaner, and with less I/O if they reside in a tablespace with a large block size.(As I dissused in my previous post of Database design approach )

6. Parallelize the load – We can invoke parallel DML (i.e. using the PARALLEL and APPEND hint) to have multiple inserts into the same table. For this INSERT optimization, make sure to define multiple freelists and use the SQL “APPEND” option.

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

* Create an index if you frequently want to retrieve less than 15% of the rows in a large table.
* To improve performance on joins of multiple tables, index columns used for joins.

* Small tables do not require indexes.

Some columns are strong candidates for indexing. Columns with one or more of the following characteristics are candidates for indexing:

* Values are relatively unique in the column.
* There is a wide range of values (good for regular indexes).
* There is a small range of values (good for bitmap indexes).
* The column contains many nulls, but queries often select all rows having a value. In this case, use the following phrase:

WHERE COL_X > -9.99 * power(10,125)

Using the preceding phrase is preferable to:

WHERE COL_X IS NOT NULL

This is because the first uses an index on COL_X (assuming that COL_X is a numeric column).

Columns with the following characteristics are less suitable for indexing:

* There are many nulls in the column and you do not search on the not null values.

The size of a single index entry cannot exceed roughly one-half (minus some overhead) of the available space in the data block.

Other Considerations:

1. The order of columns in the CREATE INDEX statement can affect query performance. In general, specify the most frequently used columns first.If you create a single index across columns to speed up queries that access, for example, col1, col2, and col3; then queries that access just col1, or that access just col1 and col2, are also speeded up. But a query that accessed just col2, just col3, or just col2 and col3 does not use the index.

2. There is a trade-off between the speed of retrieving data from a table and the speed of updating the table. For example, if a table is primarily read-only, having more indexes can be useful; but if a table is heavily updated, having fewer indexes could be preferable.

3. Drop Index that are no longer required.

4. Using different tablespaces (on different disks) for a table and its index produces better performance than storing the table and index in the same tablespace. Disk contention is reduced.

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

Step 1: First of all your must mount point out your database using ORACLE_SID in windows ORAENV in linux.
C:>set oracle_sid=samdb 
C:>sqlplus “/as sysdba”
SQL*Plus: Release 10.2.0.1.0 – Production on Thu Jan 6 19:51:53 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production With the Partitioning, OLAP and Data Mining options
SQL> select log_mode from v$database; LOG_MODE ———— ARCHIVELOG

Step2 : Create Rman Tablespace
SQL> CREATE TABLESPACE RMAN01 DATAFILE ‘F:oracleproduct10.2.0oradatasamdbRMAN01.DBF’ SIZE 100M REUSE AUTOEXTEND ON;          Tablespace created.

Step 3: Create RMAN user and password. Here i have been using rman01 and rman01 and assign default tablespace RMAN01 and temporary tablespace TEMP.
SQL> CREATE USER RMAN01 IDENTIFIED BY RMAN01 DEFAULT TABLESPACE RMAN01 TEMPORARY TABLESPACE TEMP;
User created.

Step 4: Grant appropriate permission to rman user (the above user)
SQL> GRANT CONNECT, RESOURCE, RECOVERY_CATALOG_OWNER TO RMAN01;
Grant succeeded.

Step 5: Lanch rman tool.
C:>RMAN
Recovery Manager: Release 10.2.0.1.0 – Production on Thu Jan 6 20:51:53 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved.

Step 6: Connect catalog database using the above rman user.
RMAN> CONNECT CATALOG RMAN01/RMAN01@samdb                                   connected to recovery catalog database

Step 7: Once you connect catalog database immediately run the following query using the given tablespace. RMAN> CREATE CATALOG TABLESPACE RMAN01;                recovery catalog created The recovery catalog will store information about target database. 

Step 8: Once connect target databse immediately Register your database. this is called incornation database (reference for the catalog database). Once incarnation database register there are some steps configre targer databaes. The target database that will store information about bacup’s and recovery.

C:> RMAN TARGET / CATALOG RMAN01/RMAN01@ADAPTIME Recovery Manager: Release 10.2.0.1.0 – Production on Thu Jan 6 22:51:53 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: KEYSTONE (DBID=3390662877) connected to recovery catalog database

RMAN> REGISTER DATABASE;                                                                                      database registered in recovery catalog starting full resync of recovery catalog full resync complete

RMAN> SHOW ALL;                                                                                                              RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default                                                                                            CONFIGURE BACKUP OPTIMIZATION OFF; # default                                    CONFIGURE DEFAULT DEVICE TYPE TO DISK;#default                                                                                                                                                                                     CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default                                     CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default                                                                                                                          CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default                                                                                                                              CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default                                            CONFIGURE ENCRYPTION FOR DATABASE OFF; # default                                               CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default                                 CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default                       CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘F:ORACLEPRODUCT10.2.0DB_1DATABASES NCFKEYSTONE.ORA’; # default                                                       RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;                                                 new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete

Expert are always welcome for their valuable comment or suggestion for the above post.

Related Post:

https://samadhandba.wordpress.com/2010/12/26/rman-login-syntax%e2%80%a6/

https://samadhandba.wordpress.com/2011/02/17/oracle-backup-and-recovery-solutions/

https://samadhandba.wordpress.com/2011/02/10/what-happens-during-oracle-database-hot-backup/

https://samadhandba.wordpress.com/2011/02/10/user-managed-hot-backup-of-oracle-database/

https://samadhandba.wordpress.com/2011/01/08/backing-up-the-db-in-noarchivelog-mode/

https://samadhandba.wordpress.com/2010/12/26/rman-%e2%80%93-recovery-manager-commands%e2%80%a6/

https://samadhandba.wordpress.com/2010/12/26/run-a-command-file-from-the-rman-prompt/

https://samadhandba.wordpress.com/2010/12/26/starting-up-and-shutting-down-the-database-with-rman/

https://samadhandba.wordpress.com/2010/12/26/making-a-full-backup-with-rman/

Read Full Post »

set feedback off
set termout on
set pagesize 56
set linesize 800
ttitle off
spool out.log

set heading off
set verify off
column today NEW_VALUE p_currdate noprint
select TO_CHAR(SYSDATE,’fmMonth ddth, yyyy’) today from dual;
clear breaks
clear computes
clear columns
set heading off
column name heading ” justify center format a74
select ‘Database Name (SID): ‘ || name “name” from v$database;
prompt
prompt
prompt Version/SGA Information:
set heading off
select * from v$version;
column sgatot justify left format 9,999,999,999
select ‘Total System Global Area as of &p_currdate.:’ hd1, sum(value) sgatot, ‘bytes’ hd2 from v$sga;

— ************* TABLESPACES/DATAFILES **************

prompt
prompt
prompt Tablespaces and Datafiles:
clear breaks
clear computes
clear columns
set heading on
column tablespace_name heading ‘Tablespace’ justify left format a15
column file_id heading ‘File|ID’ justify center format 9999
column file_name heading ‘Datafile’ justify center format a60 word_wrapped
column size Heading ‘Size|in MB.’ justify center format 9,99990.99
break on tablespace_name skip 1 on report skip 2
compute sum label ‘TS SIZE:’ of size on tablespace_name
compute sum label ‘DB SIZE:’ of size on report
select tablespace_name,file_id,file_name,bytes/1024/1024 “size” from dba_data_files order by tablespace_name, file_id, file_name;

— ************* SPACE USAGE **************

prompt Space usage (as of &p_currdate.):
prompt
clear breaks
clear computes
clear columns
set heading on
column tspace heading ‘Tablespace’ justify left format a20 truncated
column tot_ts_size heading ‘Size|in MB.’ justify left format 9,99999999990
column free_ts_size heading ‘Used|in MB.’ justify right format 9,9999999990
column tbusedpct heading ” justify left format a6
column tbfreepct heading ” justify left format a6
break on report
compute sum label ‘Totals:’ of tot_ts_size free_ts_size on report
col tspace form a25 Heading “Tablespace”
col tot_ts_size form 99999999999999 Heading “Size (Mb)”
col free_ts_size form 99999999999999 Heading “Free (Mb)”
col ts_pct form 999 Heading “% Free”
col ts_pct1 form 999 Heading “% Used”

SELECT df.tablespace_name tspace,df.bytes/(1024*1024) tot_ts_size,
sum(fs.bytes)/(1024*1024) free_ts_size,round(sum(fs.bytes)*100/df.bytes) ts_pct,
round((df.bytes-sum(fs.bytes))*100/df.bytes) ts_pct1
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, df.bytes;

— ************* REDO LOG FILES **************

prompt
prompt
prompt Online Redo Logfiles:
clear breaks
clear computes
clear columns
column member heading ‘Logfile’ justify center format a60 word_wrapped
column group heading ‘Group|Number’ justify center format 9999
column size heading ‘Size|in MB.’ justify center format 999990.99
select f.member “member”,f.group# “group”,l.bytes/1024/1024 “size” from v$logfile f, v$log l where f.group#=l.group# order by f.group#,f.member;

— ************* CONTROL FILES ************** —
prompt
prompt
prompt Control files:
clear breaks
clear computes
clear columns
column name heading ‘File Name’ format a60 word_wrapped
select name from v$controlfile;

— ************* ROLLBACK SEGMENTS ************** —
prompt
prompt
prompt Rollback Segments (sizes as of &p_currdate.):
clear breaks
clear computes
clear columns
set heading on
column tablespace_name heading ‘Tablespace’ justify left format a15 truncated
column segment_name heading ‘Seg|Name’ justify center format a7
column status heading ‘Status’ justify center format a8
column initial_extent heading ‘Initial|(in M)’ justify center format 99990.9
column next_extent heading ‘Next|(in M)’ justify center format 99990.9
column min_extents heading ‘Min|Ext’ justify center format 99990
column max_extents heading ‘Max|Ext’ justify center format 999999999990
column pct_increase heading ‘Pct|Inc’ justify center format 99990
column rbsize heading ‘Curr Size|(in M)’ justify left format 9,99990
break on tablespace_name skip 1 on report skip 2
select r.tablespace_name,r.segment_name,r.status,
r.initial_extent/1024/1024 “initial_extent”,
r.next_extent/1024/1024 “next_extent”,
r.min_extents,r.max_extents,
r.pct_increase,
sum(e.bytes)/1024/1024 “rbsize”
from dba_rollback_segs r, dba_extents e
where e.segment_name = r.segment_name
group by r.tablespace_name, r.segment_name, r.status,
r.initial_extent/1024, r.next_extent/1024,
r.min_extents, r.max_extents, r.pct_increase;


— ************* PARAMETERS **************

prompt
prompt Parameters (non-defaults):
clear breaks
clear computes
clear columns
column name heading ‘Name’ format a35 word_wrapped
column pvalue heading ‘Value’ format a50 word_wrapped
select name, rtrim(value) “pvalue” from v$parameter order by name;


— ************* USER DETAILS **************

prompt
prompt user details :
clear breaks
clear computes
clear columns
select username,profile,default_tablespace,temporary_tablespace from dba_users;
select * from nls_database_parameters;
select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;
select owner,object_type,count(1) from dba_objects group by owner,object_type order by owner;
SELECT file_name,tablespace_name,autoextensible,maxbytes/1048576 FROM dba_data_files;

Read Full Post »

Tablespace Fragmentation And Defragmentation
Oracle Tablespace Fragmentation
Honeycomb fragmentation
Bubble fragmentationis
convert DMT to LMT:
convert LMT to DMT:
Detecting Oracle Tablespace Fragmentation
Oracle Tablespace Fragmentation
Honeycomb fragmentation
Which is easy to fix. All that needs to be done is to combine adjacent free segments into one by issuing a coalesce statement:

alter tablespace USERS coalesce;

Bubble fragmentationis
Which is more difficult to handle. Of course, the best course of action is to prevent it in the first place. And as discussed earlier, the best weapon for this is to use locally-managed tablespaces. It may sound too simple, but in reality, implementing these storage structures in your database can just about remove the need to perform full tablespace reorganizations.

However, what do you do if you are in a situation where you have many databases that were set up with dictionary-managed tablespaces? You have two options, at least if you are running Oracle 8i (the version that gave birth to locally-managed tablespaces). First, you can create new locally-managed tablespaces in your database and then perform one last, full tablespace reorganization of all the database objects into the new tablespaces.

Needless to say, this can be a difficult task if you have large databases and no third-party reorganization tool. However, it will be worth the effort as you will likely never have to reorganize your tablespaces again, and your objects should never again encounter a maximum extent limit.

If you are using Oracle 8.1.6 or higher, you can convert any current dictionary-managed tablespaces to locally-managed tablespaces. Buried in the Oracle documentation is a procedure for converting a tablespace’s extent management from dictionary to local or vice-versa.

convert DMT to LMT:
sys.dbms_space_admin .tablespace_migrate_to_local(‘USERS’)

If you are afraid of how long this procedure might take on large tablespaces, do not be. It actually runs very fast.

convert LMT to DMT:
sys.dbms_space_admin .tablespace_migrate_from_local(‘USERS’)

Detecting Oracle Tablespace Fragmentation
How can you tell if your tablespaces are suffering from fragmentation problems and then identify the type of fragmentation? The detection and diagnosis is not hard to make at all. To determine if your tablespaces are having a problem with fragmentation, you can use the tsfrag.sql script:

select

tablespace_name, count(*) free_chunks, decode( round((max(bytes) / 1024000),2), null,0, round((max(bytes) / 1024000),2)) largest_chunk, nvl(round(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)) )),2), 0) fragmentation_index

from

sys.dba_free_space

group by

tablespace_name

order by 2 desc, 1;

Output would be like following.

TABLESPACE_NAME FREE_CHUNKS LARGEST_CHUNK FRAGMENTATION_INDEX
—————————— ———– ————- ——————-
C00_DATA 27811 6.85 .17
C00_INDEX 6933 1.02 .2
UNDOTBS1 330 165.82 6.5
SYSAUX 144 1483.58 28.43
C00_SECURITY 34 97.54 40.97
C00_MEDIA 2 2113.47 69.66
C00_REPORT 2 99.26 83.74
SYSTEM 2 505.79 84.06
USERS 2 4.61 83.51

When you examine the script output, you need to hone in on a couple of columns in particular. First, notice the fragmentation index column. This will give your tablespace an overall ranking with respect to how badly it is actually fragmented. A 100% score indicates no fragmentation at all. Lesser scores verify the presence of fragmentation.

The free chunks count column will tell you how many segments of free space are scattered throughout the tablespace. One thing to keep in mind is that tablespaces with multiple datafiles will always show a free chunk count greater than one because each datafile will likely have at least one pocket of free space.

Read Full Post »

« Newer Posts - Older Posts »