Online DBA

<<Back to DB Administration Main Page How to Enable Oracle  Client Side and  Server side Tracing to investigate Connection Issues Enabling Oracle Net Client Side Tracing To enable oracle SQL *Net client tracing enter following lines in sqlnet.ora file. TRACE_LEVEL_CLIENT = SUPPORT TRACE_UNIQUE_CLIENT = on TRACE_LEVEL_SERVER = SUPPORT TRACE_DIRECTORY_CLIENT = C:\TEMP TRACE_FILE_CLIENT = CLIENT TRACE_DIRECTORY_SERVER = C:\TEMP TRACE_FILE_SERVER = SERVER TRACE_TIMESTAMP_CLIENT = ON DIAG_ADR_ENABLED= OFF NOTE: If TNS_ADMIN variable is set in your environment then, enter the above lines in sqlnet.ora file available under TNS_ADMIN location Enabling Oracle Net Server Side Tracing To enable oracle Net server side tracing enter following lines in sqlnet.ora file. TRACE_LEVEL_CLIENT = SUPPORT TRACE_UNIQUE_CLIENT = on TRACE_LEVEL_SERVER = SUPPORT TRACE_DIRECTORY_CLIENT = C:\TEMP TRACE_FILE_CLIENT = CLIENT TRACE_DIRECTORY_SERVER = C:\TEMP TRACE_FILE_SERVER = SERVER TR

<<Back to DB Administration Main Page If you don't want to enable auditing but still interested to find the last DDL and/or DML statement executed against any table, you can find it using below command Finding latest DML time on a table SQL>select max(ora_rowscn), scn_to_timestamp(max(ora_rowscn)) from SCOTT.EMP Finding latest DDL time on a table SQL> select object_name,object_type,to_char(created,'DD-MM-YY HH24:MI:SS')created,to_char(last_ddl_time,'DD-MM-YY HH24:MI:SS')last_ddl from user_objects where object_name='TEST'

<<Back to DB Administration Main Page Managing TNS_ADMIN  Variable in env.ora S tarting from 12cR2 you can manage TNS_ADMIN variable in $ORACLE_HOME/env.ora file   ls -lrt $ORACLE_HOME |grep env.ora -rw-r--r--  1 oracle dba   852 Aug 18  2015 env.ora By default, the $ORACLE_HOME/env.ora is empty. If you leave this file untouched and nothing else is set, the default $ORACLE_HOME/network/admin location is read. $cat env.ora # Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved. # # NAME #   env.ora # # FUNCTION #   Oracle ENVironment variables persistency file template # # NOTES #   This is a generic file for defining environment variables. #   It facilitates the requirement to maintain persistency of environment #   variables across multiple instances in lieu of setting the variables in #   the environment. # #   Note that setting a variable in the environment takes precedence over its #   definition in this file. # # SYNTAX #   <ENVIRONMENT VARIAB