Oracle SQL*Loader Tips and Tricks

# Oracle SqlLoader from Command Line

sqlldr system/pump5k1n control=LOV_LOAD.ctl data=LOV_Value.txt LOG=LOV_Value.log BAD=LOV_Value.bad

sqlldr system/pump5k1n control=LOV_TYPE_LOAD.ctl data=New_LOV_Type.txt LOG=New_LOV_Type.log BAD=New_LOV_Type.bad

DBA_OBJECTS and Calculate the Size of Oracle Objects and Database Files

# How to calculate the Size of Database Datafiles, Tablespace and Temp-Tablespace

select "Reserved_Space(MB)", "Reserved_Space(MB)" - "Free_Space(MB)" "Used_Space(MB)","Free_Space(MB)" from(select(select sum(bytes/(1014*1024)) from dba_data_files) "Reserved_Space(MB)", (select sum(bytes/(1024*1024)) from dba_free_space) "Free_Space(MB)" from dual);

select sum(bytes/1024/1024/1024) from dba_data_files;

select ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) + ( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) + ( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) + ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB" from dual;

select sum(bytes)/1024/1024/1024 temp_size from dba_temp_files;

select TABLESPACE_NAME, sum(bytes)/1024/1024 "DB Size in MB" from dba_data_files group by tablespace_name;

select sum(bytes / (1024*1024)) "DB Size in MB" from dba_data_files where tablespace_name='RECONAREA';

select sum(bytes / (1024*1024)) "Temp Size in MB" from dba_temp_files;

# How to calculate the size of tables

select owner,segment_name,sum(bytes)/1024/1024/1024 as GB from dba_segments where owner='SCOTT' and segment_name='EMP_TABLE' group by owner,segment_name;

select sum(bytes)/1024/1024/1024 as size_in_gig,segment_name from dba_segments where segment_name in ('SS7DATA','MSCDATA') and owner='RECON' group by segment_name;

select owner,segment_name,sum(bytes)/1024/1024/1024 as GB from dba_segments where owner='TRACK_OWNER' and segment_name='PR_ADDTL_DATA' group by owner,segment_name;

# DBA_OBJECTS Queries

select OWNER, object_type, count(object_name) from dba_objects where OWNER in ('USER1','USER2','USER3') group by object_type, owner order by owner;

select count(*) from dba_objects where owner in ('USER1','USER2','USER3') and object_type not in ('TABLE','DATABASE LINK');

select count(*) from dba_objects where owner in ('USER1','USER2','USER3') and object_type in ('MATERIALIZED VIEW');

select object_type, count(object_name) as Count from dba_objects group by object_type order by cc;

select object_type,status,count(*) from dba_objects where owner='REPOSITORY1' group by object_type, status order by object_type, status;

select owner,object_name,status from dba_objects where status= 'VALID' and owner in ('REPOSITORY1','EMBARC_ETL','EMBARC_SAS');

select owner,object_name,object_type, status from dba_objects where owner ='GPAS_STAGING' and object_name like '%GM_GPAS_REFRESH_ALL_MV%';

# Calculate the Size of TableSpaces

SELECT /* + RULE */  df.tablespace_name "Tablespace",

       df.bytes / (1024 * 1024) "Size (MB)",

       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",

       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",

       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"

  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

UNION ALL

SELECT /* + RULE */ df.tablespace_name tspace,

       fs.bytes / (1024 * 1024),

       SUM(df.bytes_free) / (1024 * 1024),

       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),

       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)

  FROM dba_temp_files fs,

       (SELECT tablespace_name,bytes_free,bytes_used

          FROM v$temp_space_header

         GROUP BY tablespace_name,bytes_free,bytes_used) df

 WHERE fs.tablespace_name (+)  = df.tablespace_name

 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used

 ORDER BY 4 DESC;

SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,

fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,

(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,

fs.free_space_mb FREE_SPACE_MB,

ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE

FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,

   ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB

   FROM dba_data_files

   GROUP BY tablespace_name) df,

  (SELECT tablespace_name, SUM(bytes) FREE_SPACE,

    ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB

    FROM dba_free_space

    GROUP BY tablespace_name) fs

WHERE df.tablespace_name = fs.tablespace_name(+)

ORDER BY fs.tablespace_name;

Manage Oracle Tablespace, Datafile and Tempfile.

Use different views for viewing information about temp files and Datafiles with V$TEMPFILE and DBA_TEMP_FILES views are analogous to the V$DATAFILE and DBA_DATA_FILES views.

Note: Remember we can extend one Datafile up it Max limit around 32GB only, It depends on a block size of oracle.
Default Block Size: 8192(Byte) Means 8Kb, please run this query to cross verify:

SQL> Select (maxblocks*8192)/1024/1024/1024 Size_GB from dba_data_files where rownum=1;


# Tablespace Creation and Resizing Datafile
SQL> col TABLESPACE_NAME format a20;
SQL> col FILE_NAME format a50;
SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='USERS';

CREATE TABLESPACE tb_name DATAFILE '/mount1/directory1/directory1/my_data_01.dbf' SIZE 20G;
ALTER TABLESPACE tb_name ADD DATAFILE '/mount1/directory1/directory1/my_data_01.dbf' SIZE 10G;
ALTER DATABASE DATAFILE '/mount1/directory1/directory1/my_data_01.dbf' AUTOEXTEND ON;
ALTER DATABASE DATAFILE '/mount1/directory1/directory1/my_data_01.dbf' RESIZE 24G;


# Temp Tablespace Creation and Managing its Datafile
By default, a single temporary tablespace named TEMP is created for each new Oracle Database installation. You can create additional temporary tablespaces with the CREATE TABLESPACE statement.

SQL> col TABLESPACE_NAME format a20;
SQL> col FILE_NAME format a50;
SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_TEMP_FILES WHERE TABLESPACE_NAME='TEMP';
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '/mount2/directory1/directory1/lmtemp01.dbf' SIZE 15G AUTOEXTEND ON;
ALTER TABLESPACE lmtemp ADD TEMPFILE '/mount2/directory1/directory1/lmtemp02.dbf' SIZE 10G REUSE;
ALTER DATABASE TEMPFILE '/mount2/directory1/directory1/lmtemp02.dbf' RESIZE 26G;
ALTER TABLESPACE TEMP ADD TEMPFILE '/mount2/directory1/directory1/lmtemp03.dbf' SIZE 512M AUTOEXTEND ON NEXT 250M MAXSIZE UNLIMITED;

# Drop Tablespace Datafile along with data.
DROP TABLESPACE users INCLUDING CONTENTS;
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;
ALTER TABLESPACE lmtemp DROP TEMPFILE '/mount2/directory1/directory1/lmtemp02.dbf';
ALTER TABLESPACE tb_name DROP DATAFILE '/mount1/directory1/directory1/my_data_01.dbf';