Data file queries

Data file queries 

Adding Datafile to a Tablespace:
-----------------------------------------------------
Check whether mount point has sufficient space

 df -k mount_point

Check the available space in the existing tablespace

select sum(bytes)/1024/1024/1024 "GB_Allot",tablespace_name  from dba_data_files 
where tablespace_name='PIAS_ACTIVITY_IS' 
group by tablespace_name;

Adding datafile to the tablespace

alter tablespace PIAS_ACTIVITY_IS  add datafile
'/dev/oasvg_ops_40/rpoas_activity_is_20' size 1999M;

-------------------------------------------------------

Manually Resizing a Datafile:
--------------------------------------------------------


ALTER DATABASE DATAFILE '/oracle/data/icmn/mna1/vol10/icmnmna1.tools.a001.dbf'
RESIZE 1000M;

-------------------------------------------------------

How to Rename(change path) of a Non-System datafile:
--------------------------------------------------------

   1. Login to SQLPlus.

   2. Connect as SYS DBA with CONNECT / AS SYSDBA command.
   "/ as sysdba"

   3. Make offline the affected tablespace with ALTER TABLESPACE  OFFLINE; command.
   " ALTER TABLESPACE tablespace_name OFFLINE;"
 
   4. Rename or/and move the datafiles at operating system level.
   " cp datafile_path/filename to datafile_path/filename"

   5. Modify the name or location of datafiles in Oracle data dictionary using following command syntax:
                        "ALTER DATABASE RENAME FILE ‘Old Path/filename’ TO ‘New Path/filename’;"

   6. Bring the tablespace online again with ALTER TABLESPACE alter tablespace  ONLINE; command.
   " ALTER TABLESPACE tablespace_name ONLINE;"

------------------------------------------------------


How to Rename(change path) of a System or Tools datafile:
------------------------------------------------------



   1. Login to SQLPlus.

   2. Connect as SYS DBA with CONNECT / AS SYSDBA command.
   "/ as sysdba"

   3. Shutdown the database instance with SHUTDOWN command.
   " Shutdown immediate"
 
   4. Rename or/and move the datafiles at operating system level.
   " cp datafile_path/filename to datafile_path/filename"

   5. Start Oracle database in mount state with STARTUP MOUNT command.
   " Startup mount" 

   6. Modify the name or location of datafiles in Oracle data dictionary using following command syntax:
                        "ALTER DATABASE RENAME FILE ‘Old Path/filename’ TO ‘New Path/filename’;"

   7. Open Oracle database instance completely with ALTER DATABASE OPEN command.
   " Alter database open;"
----------------------------------------------------------

Free Space in Datafile:
----------------------------------------------------------

SET PAGES 2000
col rank form 99 
col file_id form 9999 heading "F ID"
col tspace form a20 Heading "Tablespace"
col tot_ts_size form 99999999999999 Heading "Size (Mb)"
col free_ts_size form 99999999999999 Heading "Free (Mb)"
col used_ts_size form 99999999999999 Heading "Used (Mb)"
compute sum of tot_ts_size on report
compute sum of free_ts_size on report

select rownum as rank, a.*
from (
   select df.tablespace_name tspace, 
       df.file_id,
       df.bytes/(1024*1024) tot_ts_size,
       sum(fs.bytes)/(1024*1024) free_ts_size,
       (df.bytes-sum(fs.bytes))/(1024*1024) used_ts_size
   from dba_free_space fs, (select tablespace_name,file_id, sum(bytes) bytes
                                 from dba_data_files
                                 group by tablespace_name,file_id ) df          
   where fs.tablespace_name = df.tablespace_name
   and fs.file_id = df.file_id
   and df.tablespace_name like UPPER('%&tablespace_name%')
   group by df.tablespace_name, df.file_id, df.bytes
   ORDER BY free_ts_size DESC) a
where rownum < 11
/

-----------------------------------------------------------

Shrink Datafile:
-----------------------------------------------------------

select file_name,
 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
 ceil( blocks*&&blksize/1024/1024) currsize,
 ceil( blocks*&&blksize/1024/1024) -
 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
 from dba_data_files a,
 ( select file_id, max(block_id+blocks-1) hwm
 from dba_extents
 group by file_id ) b
 where a.file_id = b.file_id(+)
 and a.tablespace_name ='&tabs';

-----------------------------------------------------------