Oracle Database Listener.log Analize

Hello, We are creating a new listener log file from listener.log file. ErmanTESTDB:/oracle/grid/diag/tnslsnr/ErmanTESTDB/listener/trace#cp listener.log a_listener.log   1. create a new directory for listener.log file. create directory erman_log_dir as ‘/oracle/grid/diag/tnslsnr/alioth/listener/trace’; 2. create a new table for listener.log file create table erman_log ( log_date date, connect_string varchar2(300), protocol_info varchar2(300), action varchar2(15), service_name varchar2(15), return_code number(10) ) organization […]

Oracle Database Listener.log File Location

Hello, I am just getting my feet wet with 11gR1. When I executed lsnrctl status, I get the following: Connect to terminal with Oracle user than run this command: {ermanTestDB:oracle}/home/oracle/>lsnrctl status LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.4.0 – Production on 08-JAN-2018 10:59:28 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of […]

Oracle Database Export

Hello,Step by step Oracle database export Schema, Tables, Procedure, Full Database etc. For the examples to work we must first unlock the SYS account and create a directory object it can access. The directory object is only a pointer to a physical directory, creating it does not actually create the physical directory on the file […]

Check undo tablespace size oracle 11g or 12c

SELECT SUM(a.bytes) as UNDO_SIZE  FROM v$datafile a, v$tablespace b, dba_tablespaces c  WHERE c.contents = ‘UNDO’   AND c.status = ‘ONLINE’    AND b.name = c.tablespace_name   AND a.ts# = b.ts#; Output: UNDO_SIZE 3.7770E+10 find the number of undo block per second; SELECT TO_NUMBER(value) as DB_BLOCK_SIZE FROM v$parameter WHERE name = ‘db_block_size’; Output: DB_BLOCK_SIZE ————- 8192 1 row […]

How can we check if a partition of a table exists?

Hello, Partitions : Maintenance of large tables and indexes can become very time and resource consuming. At the same time, data access performance can reduce drastically for these objects. Partitioning of tables and indexes can benefit the performance and maintenance in several ways. Partition independance means backup and recovery operations can be performed on individual […]

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 […]

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 […]

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 […]

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 […]