Reclaiming Unused Space

At the time of table creation there is no extend allocate for this table, when table data are increasing/inserting new extend are allocating for this table and extend are not leave until table are :

1. drop
2. Truncate
3. Move table to same/another tablespace.

After deleting data from table space are not release, because when we delete data from the table block will go onto the freelist for that table only, for that another table/extend are not able to use this free blocks and space are not usable by another table/extends.

Now question is how we can Reclaim this free space ?

1. Move Tablespace.
2. Export and import table by using data pump.
3. Redefine table using DBMS_REDEFINITION
4. Create new table, drop existing table, rename new table [Must create existing index in new table].
5. Drop table [If there is no data and table are not required in future ].
6. Truncate table [if Table data are not required].

SQL> CREATE TABLESPACE TBS_DATA DATAFILE
  2    '/u01/app/oracle/oradata/orcln1/tbs_data01.dbf' SIZE 500M AUTOEXTEND OFF;

Tablespace created.

SQL> SELECT A.TABLESPACE_NAME,
  2         (SELECT NVL(SUM (BYTES) / 1024,0)
  3            FROM DBA_FREE_SPACE
  4           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  5            FREE_SPACE,
  6         (SELECT NVL(SUM (BYTES) / 1024,0)
  7            FROM DBA_EXTENTS
  8           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  9            USED_SPACE,
 10         (SELECT COUNT (*)
 11            FROM DBA_EXTENTS
 12           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
 13            TOTAL_EXTENTS
 14    FROM DBA_TABLESPACES A
 15   WHERE TABLESPACE_NAME = 'TBS_DATA';

TABLESPACE_NAME                FREE_SPACE USED_SPACE TOTAL_EXTENTS
------------------------------ ---------- ---------- -------------
TBS_DATA                           510976          0             0

SQL>
--- There is no extends in this tablespace, so total space of this tablespace are free.
SQL> CREATE TABLE TEST_RECLAIM_SPACE
  2  (SERIAL_NO NUMBER,
  3   DATA1     VARCHAR2(4000),
  4   DATA2     VARCHAR2(4000),
  5   DATA3     VARCHAR2(4000)
  6  ) TABLESPACE TBS_DATA;

Table created.

SQL> INSERT /*+append*/ INTO TEST_RECLAIM_SPACE
  2  SELECT SERIAL_NO, DATA||DATA||DATA||DATA||DATA||DATA DATA1, DATA||DATA||DATA||DATA||DATA||DATA DATA2,
  3   DATA||DATA||DATA||DATA||DATA||DATA DATA3
  4  FROM(
  5  SELECT LEVEL SERIAL_NO, 'CREATE TABLE TEST_RECLAIM_SPACE
  6  (SERIAL_NO NUMBER,
  7   DATA1     VARCHAR2(4000),
  8   DATA2     VARCHAR2(4000),
  9   DATA3     VARCHAR2(4000)
 10  ) TABLESPACE TBS_DATA
 11  ' DATA
 12  FROM DUAL
 13  CONNECT BY LEVEL <= 100000);

100000 rows created.

SQL>  SELECT A.TABLESPACE_NAME,
  2          (SELECT NVL(SUM (BYTES) / 1024,0)
  3             FROM DBA_FREE_SPACE
  4            WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  5             FREE_SPACE,
  6          (SELECT NVL(SUM (BYTES) / 1024,0)
  7             FROM DBA_EXTENTS
  8            WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  9             USED_SPACE,
 10          (SELECT COUNT (*)
 11             FROM DBA_EXTENTS
 12            WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
 13             TOTAL_EXTENTS
 14     FROM DBA_TABLESPACES A
 15    WHERE TABLESPACE_NAME = 'TBS_DATA';

TABLESPACE_NAME                FREE_SPACE USED_SPACE TOTAL_EXTENTS
------------------------------ ---------- ---------- -------------
TBS_DATA                           101376     409600           121

SQL> DELETE FROM TEST_RECLAIM_SPACE;

100000 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM TEST_RECLAIM_SPACE;

  COUNT(*)
----------
         0

SQL>

SQL> SELECT A.TABLESPACE_NAME,
  2         (SELECT NVL(SUM (BYTES) / 1024,0)
  3            FROM DBA_FREE_SPACE
  4           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  5            FREE_SPACE,
  6         (SELECT NVL(SUM (BYTES) / 1024,0)
  7            FROM DBA_EXTENTS
  8           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  9            USED_SPACE,
 10         (SELECT COUNT (*)
 11            FROM DBA_EXTENTS
 12           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
 13            TOTAL_EXTENTS
 14    FROM DBA_TABLESPACES A
 15   WHERE TABLESPACE_NAME = 'TBS_DATA';

TABLESPACE_NAME                FREE_SPACE USED_SPACE TOTAL_EXTENTS
------------------------------ ---------- ---------- -------------
TBS_DATA                           101376     409600           121

SQL> ALTER TABLE TEST_RECLAIM_SPACE MOVE;

Table altered.

SQL> SELECT A.TABLESPACE_NAME,
  2         (SELECT NVL(SUM (BYTES) / 1024,0)
  3            FROM DBA_FREE_SPACE
  4           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  5            FREE_SPACE,
  6         (SELECT NVL(SUM (BYTES) / 1024,0)
  7            FROM DBA_EXTENTS
  8           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  9            USED_SPACE,
 10         (SELECT COUNT (*)
 11            FROM DBA_EXTENTS
 12           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
 13            TOTAL_EXTENTS
 14    FROM DBA_TABLESPACES A
 15   WHERE TABLESPACE_NAME = 'TBS_DATA';

TABLESPACE_NAME                FREE_SPACE USED_SPACE TOTAL_EXTENTS
------------------------------ ---------- ---------- -------------
TBS_DATA                           510912         64             1

SQL>

Schema Password Lifetime

In oracle database schema password lifetime managed by the user profile, before short time the expiry of limit user will get ORA-28002: the password will expire within 7 days alter.

SQL> SELECT U.PROFILE, RESOURCE_NAME, LIMIT
  2  FROM DBA_USERS U, DBA_PROFILES P
  3  WHERE U.PROFILE=P.PROFILE
  4  AND USERNAME = 'SCOTT'
  5  AND RESOURCE_NAME='PASSWORD_LIFE_TIME';

PROFILE                        RESOURCE_NAME                    LIMIT
------------------------------ -------------------------------- ----------------------------------------
DEFAULT                        PASSWORD_LIFE_TIME               60

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 180;

Profile altered.

SQL> SELECT U.PROFILE, RESOURCE_NAME, LIMIT
  2  FROM DBA_USERS U, DBA_PROFILES P
  3  WHERE U.PROFILE=P.PROFILE
  4  AND USERNAME = 'SCOTT'
  5  AND RESOURCE_NAME='PASSWORD_LIFE_TIME';

PROFILE                        RESOURCE_NAME                    LIMIT
------------------------------ -------------------------------- ----------------------------------------
DEFAULT                        PASSWORD_LIFE_TIME               180

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

SQL> SELECT U.PROFILE, RESOURCE_NAME, LIMIT
  2  FROM DBA_USERS U, DBA_PROFILES P
  3  WHERE U.PROFILE=P.PROFILE
  4  AND USERNAME = 'SCOTT'
  5  AND RESOURCE_NAME='PASSWORD_LIFE_TIME';

PROFILE                        RESOURCE_NAME                    LIMIT
------------------------------ -------------------------------- ----------------------------------------
DEFAULT                        PASSWORD_LIFE_TIME               UNLIMITED

SQL>

Archive log delete policy.

Delete of archive log without proper policy can make an disaster. Archive log can be require at the time of complete or point in time recovery for that we need to care about archive log deleting policy. Normally we need to delete archive log to get back some space from archive log location specified by log_archive_dest_n parameter.

Show current configuration using sqlplus.

SQL>
COL NAME FORMAT A50
COL VALUE FORMAT A20

SELECT NAME, VALUE
  FROM V$RMAN_CONFIGURATION
WHERE NAME LIKE '%ARCHIVELOG DELETION POLICY%';SQL> SQL> SQL> SQL>   2    3

NAME                                               VALUE
-------------------------------------------------- --------------------
ARCHIVELOG DELETION POLICY                         TO NONE

SQL>

Archive log delete policy.
By default archive log deletion are none (There is no policy for deletion of archive log.)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCLN1 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 COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcln1.f'; # default

Configuring archive log deletion policy after taken 2 successful backup.

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;

new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;
new RMAN configuration parameters are successfully stored

RMAN>

We can also configure archive log deletion policy if there is data guard setup.

In this case RMAN will delete the archived logs after they have been applied to the mandatory standby locations. 

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
new RMAN configuration parameters are successfully stored

In this case RMAN will delete the archived logs after they have been applied to the all standby locations.

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters are successfully stored

RMAN>

Cancel Delete policy and return to default configuration.

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
new RMAN configuration parameters are successfully stored

Case Sensitive Password Issue

From 11g version Oracle introduce case sensitive password.  This can avoid by setting sec_case_sensitive_logon parameter false.

SQL> grant  connect, resource to TEST identified by TEST;

Grant succeeded.

SQL> conn test/test;
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn /as sysdba
Connected.
SQL> show parameter sec_case_sen

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL>  alter system set sec_case_sensitive_logon=false scope=both;

System altered.

SQL>  conn test/test;
Connected.
SQL>

Find the mapping of ASM Disk to Physical Device.

1. Disk list of oracleasm

[root@OEL564ASMN1 ~]# oracleasm listdisks
DATAVOL1
FRAVOL1
RMAN_BKP

2. Physical Device for ASM Disks.

[root@OEL564ASMN1 ~]# /etc/init.d/oracleasm querydisk -p DATAVOL1
Disk "DATAVOL1" is a valid ASM disk
/dev/sdb1: LABEL="DATAVOL1" TYPE="oracleasm"
[root@OEL564ASMN1 ~]# /etc/init.d/oracleasm querydisk -p FRAVOL1
Disk "FRAVOL1" is a valid ASM disk
/dev/sdc1: LABEL="FRAVOL1" TYPE="oracleasm"

Or

[root@OEL564ASMN1 ~]# /etc/init.d/oracleasm querydisk -d DATAVOL1
Disk "DATAVOL1" is a valid ASM disk on device /dev/sdb1[8,17]

3. ASM Disks Name for Physical Device.

[root@OEL564ASMN1 ~]# /etc/init.d/oracleasm querydisk /dev/sdb1
Device "/dev/sdb1" is marked an ASM disk with the label "DATAVOL1"

Convert BLOB/CLOB Data to VARCHAR2

Convert BLOB Column to VARCHAR2 ...


SELECT TO_CHAR(DBMS_LOB.SUBSTR(QUERY_FILE, 4000, 1 )) QUERY_TEXT
FROM SQL_TEXT

WHERE UNIQUE_ID=:P2_UNIQUE_ID;

Convert CLOB Column to VARCHAR2 ...

SELECT UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(QUERY_FILE, 2000, 1 ) ) QUERY_TEXT
FROM SQL_TEXT

WHERE UNIQUE_ID=:P2_UNIQUE_ID;

Find and recompile invalid object for particular table/objects.

SELECT DISTINCT NAME, TYPE, STATUS, 'ALTER '||REPLACE (TYPE, 'PACKAGE BODY', 'PACKAGE')||' '||NAME||' COMPILE;' RECOMPILE_SCRIPTS
  FROM USER_SOURCE U, USER_OBJECTS O
 WHERE     O.OBJECT_NAME = U.NAME
       AND UPPER (TEXT) LIKE '%TABLE_NAME%'
       AND UPPER(STATUS)<>'VALID'

Find the session and SQL query which was previously locked.

This Query will help you to find lock history.

SQL> SELECT  DISTINCT A.INST_ID, A.SQL_ID ,A.INST_ID,A.BLOCKING_SESSION,A.BLOCKING_SESSION_SERIAL#,A.USER_ID,S.SQL_TEXT,A.MODULE
  2  FROM  GV$ACTIVE_SESSION_HISTORY A  ,GV$SQL S
  3  WHERE A.SQL_ID=S.SQL_ID
  4  AND BLOCKING_SESSION IS NOT NULL
  5  AND A.USER_ID <> 0;

   INST_ID SQL_ID           INST_ID BLOCKING_SESSION BLOCKING_SESSION_SERIAL#    USER_ID SQL_TEXT
---------- ------------- ---------- ---------------- ------------------------ ---------- -------------------------------------------------------
         1 0jgf8xdjf2h8w          1               74                        9         84  UPDATE REGIONS  SET REGION_NAME='TEST'

SQL>

Note : It will show data if your current log is enough.

ORA-14048: a partition maintenance operation may not be combined with other operations

Cause : When I am trying to make an index as UNUSABLE from Toad I got this error.

Solution : Try from sqlplus.

SQL> ALTER INDEX IND_ID UNUSABLE;

Index altered.

SQL>