Follow below procedure to capture following information : 1) Tables row count. 2) Table Actual Size. 3) DBA_Tab_Modifications information. Pre requisite: This Procedure needs to be implemented if we are gathering stats daily. #############On any Dev server : (Create DB link to Standby DB from… Read More
Role of APPLSYS & APPS schema in Oracle Applications: Applsys schema contains all the tables required for administarative purpose and is same as other oracle users like AP, AR, GL etc which hold their set of tables, views etc. In the same way APPLSYS Account… Read More
PARTITIONING PARTITIONS : Dividing the large tables into small tables known as partitions Partition of single table can go in two different tablespaces Sorting can be done by partitioning thus we not require temporary tablespace Partition can be independently managed Backup and recovery… Read More
spool DEPT_NonPart_to_Part.logset timing onset time onset echo on conn SCOTT/PW –> Very Importantshow user –> Create DEPT_PART table (with partitions) EXEC DBMS_STATS.gather_table_stats(user, ‘DEPT’, cascade => TRUE);SELECT num_rows FROM user_tables WHERE table_name = ‘DEPT’; EXEC DBMS_STATS.gather_table_stats(user, ‘DEPT_PART’, cascade => TRUE);SELECT num_rows FROM user_tables WHERE table_name =… Read More
Please check my upcoming articles (Tomorrow and Day after Tomorrow) for detailed steps with examples.
********************************************************************* How to Purge CURSORS related to a single SQL_ID from library cache ********************************************************************* SQL> select ADDRESS, HASH_VALUE from V$SQL where SQL_ID like ‘8zdchyshg4d3g’; select ADDRESS, HASH_VALUE,plan_hash_value from V$SQL where SQL_ID like ‘8zdchyshg4d3g’; SQL> exec DBMS_SHARED_POOL.PURGE (‘000000180DC67320,552744047′,’C’); PL/SQL procedure successfully completed. SQL> select ADDRESS, HASH_VALUE… Read More
1) Bkup Tnsnames: $ cp tnsnames.ora tnsnames.ora.date +”%Y%m%d%H%M” -rw-r—–. 1 oracle oinstall 564 Oct 20 11:57 tnsnames.ora -rw-r—–. 1 oracle oinstall 564 Nov 28 22:25 tnsnames.ora.201611282225 2) Crontab Backup : $ crontab -l > crontab_orig_date ‘+%m%d_%H%M’ $ ls -ltr crontab* -rw-r–r–. 1 oracle oinstall 2175… Read More
************************************************************ List all the hidden parameter and their values ************************************************************ SET PAUSE ON SET PAUSE ‘Press Return to Continue’ SET PAGESIZE 60 SET LINESIZE 300 COLUMN ksppinm FORMAT A50 COLUMN ksppstvl FORMAT A50 SELECT ksppinm, ksppstvl FROM x$ksppi a, x$ksppsv b WHERE a.indx=b.indx AND substr(ksppinm,1,1)… Read More
AUDITING Auditing is for verification purpose Types of auditing: Privilege level Statement level Object level To enable the auditing audit_trail=true or db or os To disable the auditing audit_trail=false or none If the value is true it will enable the auditing and audit files… Read More
Oracle : HOW TO TRACE SESSIONS ========================================================== For TRACING your own session , use the following query to find details on your own session: ========================================================== set linesize 132 set pagesize 1000 set echo on col rbs format a6 col oracle_user format a12 heading “Oracle|Username” col… Read More
