Deallocating Unused Space

When you deallocate unused space, the database frees the unused space at the unused (high water mark) end of the database segment and makes the space available for other segments in the tablespace.

Prior to deallocation, you can run the UNUSED_SPACE procedure of the DBMS_SPACE package, which returns information about the position of the high water mark and the amount of unused space in a segment. For segments in locally managed tablespaces with automatic segment space management, use theSPACE_USAGE procedure for more accurate information on unused space.

The following statements deallocate unused space in a segment (table, index or cluster):

ALTER TABLE table DEALLOCATE UNUSED KEEP integer;

ALTER INDEX index DEALLOCATE UNUSED KEEP integer;

ALTER CLUSTER cluster DEALLOCATE UNUSED KEEP integer;

The KEEP clause is optional and lets you specify the amount of space retained in the segment. You can verify that the deallocated space is freed by examining the DBA_FREE_SPACE view.

How to check free space in ASM

 1. Connect to asm instance:

select name, state, total_mb, free_mb from v$asm_diskgroup;

NAME       STATE     TOTAL_MB FREE_MB

—————————— ———– ———- ———-

ORADATA MOUNTED 65536      53439

SELECT name, type, ceil (total_mb/1024) TOTAL_GB , ceil (free_mb/1024) FREE_GB, required_mirror_free_mb,

ceil ((usable_file_mb)/1024) FROM V$ASM_DISKGROUP;

NAME TYPE TOTAL_GB FREE_GB REQUIRED_MIRROR_FREE_MB CEIL((USABLE_FILE_MB)/1024)

ORACLE_DATA1  EXTERN         466 236           0            236

ORACLE_FLASH1 EXTERN         131  124           0            124

ORACLE_FLASH2 EXTERN         10 9 0 9

2.Using ASMCMD tool

Make sure to setup ORACLE_SID and ORACLE_HOME environment variables to ASM instance.

-bash-3.2$ asmcmd -p

ASMCMD [+]>; du

Used_MB Mirror_used_MB

12011 12011

ASMCMD [+] >;

or run

lsdg command

State Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name

MOUNTED EXTERN N N 1024 4096 1048576 40960 37628 0 37628 0 ORADATA/

MOUNTED EXTERN N N 1024 4096 1048576 32768 32675 0 32675 0 ORADATA1/

3. Using Grid Control

Go to asm host and select asm instance. Go Administration tab and input asm administration password:

ORA-00980: synonym translation is no longer valid

This error occurs when you have a public synonym defined and the object referencing or owning the synonym has been dropped or deleted. You can check for the invalid synonyms using below query.

select * from dba_synonyms s

where table_owner not in(‘SYSTEM’,'SYS’)

and db_link is null

and not exists

(select 1

from dba_objects o

where s.table_owner=o.owner

and s.table_name=o.object_name);



Solution:

Replace the synonym with the name of the object it references or re- create the synonym so that it refers to a valid table, view, or synonym.
The Invalid synonyms must be dropped and recreated with the right owner. I normally do this using below query.(In this example the owner of the synonym was dropped and I am altering the synonym to reference the new owner)

DROP PUBLIC SYNONYM SYNONYM_NAME;
CREATE PUBLIC SYNONYM SYNONYM_NAME FOR NEW_OWNER.OBJECT_NAME;