Compiles all invalid views for specified schema

SET PAGESIZE 0 SET FEEDBACK OFF SET VERIFY OFF SPOOL temp.sql SELECT 'ALTER VIEW ' || a.owner || '.' || a.object_name || ' COMPILE;' FROM all_objects a WHERE a.object_type = 'VIEW' AND a.status = 'INVALID' AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1')); SPOOL OFF -- Comment out following line to prevent immediate run @temp.sql SET PAGESIZE … Continue reading Compiles all invalid views for specified schema

Advertisements

Compile all İnvalid Trigger

SET PAGESIZE 0 SET FEEDBACK OFF SET VERIFY OFF SPOOL temp.sql SELECT 'ALTER TRIGGER ' || a.owner || '.' || a.object_name || ' COMPILE;' FROM all_objects a WHERE a.object_type = 'TRIGGER' AND a.status = 'INVALID' AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1')); SPOOL OFF -- Comment out following line to prevent immediate run @temp.sql SET PAGESIZE … Continue reading Compile all İnvalid Trigger

Compile all invalid Procedure

SET PAGESIZE 0 SET FEEDBACK OFF SET VERIFY OFF SPOOL temp.sql SELECT 'ALTER PROCEDURE ' || a.owner || '.' || a.object_name || ' COMPILE;' FROM all_objects a WHERE a.object_type = 'PROCEDURE' AND a.status = 'INVALID' AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1')); SPOOL OFF -- Comment out following line to prevent immediate run @temp.sql SET PAGESIZE … Continue reading Compile all invalid Procedure

How to create a database link in Oracle?

The syntax has been enhanced to remove the need to epscify a TNS service_name: CREATE PUBLIC DATABASE LINK <Link_Name>  CONNECT TO <remote_username> IDENTIFIED BY <PWD> USING ''tns_service_name';    

Oracle Trace file (.trc) or Trace map (.trm) cleanup

To clean up old Oracle trace file (.trc) and trace map (.trm) files, use this command on UNIX based system: Connect sqlplus :   And, find /u01/app/oracle/diag/rdbms/sbuat01/SBUAT01/trace/*.trc -mtime +14 -exec rm {} \; find /oracle/diag/rdbms/ssekdb/ssekdb/trace/*.trm -mtime +14 -exec rm {} \; The above command will delete trace files and trace map files which is older … Continue reading Oracle Trace file (.trc) or Trace map (.trm) cleanup

Flash Recovery Area

Setting up a Flash Recovery Area The recovery area is defined by setting two initialization parameters. These two parameters can be dynamically altered or disabled. The db_recovery_file_dest_size sets the disk limit, expressed in bytes The db_recovery_file_dest sets the location for the recovery area Enabling a Flash Recovery Area This statement sets the disk limit for … Continue reading Flash Recovery Area

Oracle Database Disk Size

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;