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';

Create, Modify, Remove Oracle Scheduler Jobs and Queues.

# Some useful Queries to check the status of Jobs and Queues.
SQL> select job, schema_user, last_date, next_date, interval,failures,broken from dba_jobs where job=613;
SQL> SELECT r.sid, r.job, r.this_date, r.this_sec FROM dba_jobs_running r,dba_jobs j WHERE r.job = j.job;
SQL> select job,LOG_USER,SCHEMA_USER,NEXT_DATE,NEXT_SEC,INTERVAL,FAILURES,BROKEN from dba_jobs where job in (243,244);

SQL> select ACTUAL_START_DATE from ALL_SCHEDULER_JOB_RUN_DETAILS order by ACTUAL_START_DATE desc;
SQL> select OWNER,JOB_NAME, STATUS from ALL_SCHEDULER_JOB_RUN_DETAILS where owner != 'SYS';

SQL> select OWNER,JOB_NAME from DBA_SCHEDULER_JOBS where owner != 'SYS';
SQL> select * from DBA_QUEUE_SCHEDULES where qname='SBL_PTCL_IN_Q';
SQL> select * from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name='SBL_PTCL_IN_Q';


# Create, run and drop of Scheduler Job with example
DECLARE
  Y NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => Y
   ,what      => 'begin
 ctms_operation.update_site_score;
 CTMS_OPERATION.generate_xml;
 end;'
   ,next_date => SYSDATE
   ,interval  => 'NEXT_DAY(TRUNC(SYSDATE),''SATURDAY'')+ 5/24'
   ,no_parse  => FALSE
  );
  DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(Y));
 COMMIT;
END;
/
SQL> select to_char(NEXT_DAY(TRUNC(SYSDATE),'SATURDAY')+ 5/24,'DD-MM-YYYY:HH:MI:SS') from dual;


# Run, Remove, Broken Operation on Job
SQL> exec dbms_job.run(613);
SQL> exec dbms_job.remove(243);
SQL> exec dbms_job.broken(314,FALSE);
SQL> exec dbms_ijob.broken(27,true);

SQL> DBMS_SCHEDULER.DROP_JOB (job_name => 'STATISTICS_COLUMNS_JOB');
SQL> begin dbms_aqadm.start_queue (queue_name => 'SBL_CONTACT_Q', enqueue => TRUE , dequeue => FALSE); end;
SQL> begin dbms_aqadm.start_queue (queue_name => 'SBL_CONTACT_Q', enqueue => FALSE, dequeue => TRUE); end;

# Please visit Below link to know more with examples.

Link1:  http://www.idevelopment.info/data/Oracle/DBA_tips/SQL/SQL_4.shtml
Link2:  http://www.mandsconsulting.com/oracle-dbms_scheduler-vs-dbms_job-create-run-monitor-remove