shivenracdba

To find out information about package , function , procedure etc :- SQL> set long 100000 SQL> select TEXT from  dba_source where name = '<package_name>'; SQL> SELECT REFERENCED_NAME,REFERENCED_OWNER,REFERENCED_TYPE,DEPENDENCY_TYPE FROM DBA_DEPENDENCIES WHERE NAME= '<package_name>'; SQL> SELECT REFERENCED_NAME,REFERENCED_OWNER,REFERENCED_TYPE,DEPENDENCY_TYPE FROM DBA_DEPENDENCIES WHERE NAME= '<package_name>' AND REFERENCED_TYPE IN ('TABLE','SYNONYM');

This single script provides the overall picture of the database in terms of Waits events, Active/Inactive killed sessions, Top Processes (physical I/O, logical I/O, memory and CPU processes), Top CPU usage by users,etc.Database Version :- set serveroutput on declare  cursor c1 is select version from v$instance; cursor c2 is     select           host_name        ,  instance_name        ,  to_char(sysdate, 'HH24:MI:SS DD-MON-YY') currtime        ,  to_char(startup_time, 'HH24:MI:SS DD-MON-YY') starttime      from v$instance; cursor c4 is select * from (SELECT count(*) cnt, substr(event,1,50) event FROM v$session_wait WHERE wait_time = 0 AND event NOT IN ('smon timer','pipe get','wakeup time manager','pmon timer','rdbms ipc message', 'SQL*Net message from client') GROUP BY event ORDER BY 1 DESC) where rownum <6; cursor c5 is select round(sum(value)/1048576) as sgasize from v$s

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 475 ORA-29283: invalid file operation Solution : You have to manually create directory  and give writes to specified users on specified path then after you try your operation. SQL> create directory expbkp as '/opt/oracle/expbkp'; SQL> grant read, write on directory expbkp to <user_name>; SQL>select * from dba_directories where directory_name = 'EXPBKP'; Otherwise you can use DATA_PUMP_DIR which is defalut in Oracle 10g and Oracle 11g.

A query to show you current sessions generating redo:- select s.sid, n.name, s.value, sn.username, sn.program, sn.type, sn.module from v$sesstat s   join v$statname n on n.statistic# = s.statistic#   join v$session sn on sn.sid = s.sid where name like '%redo entries%' order by value desc; Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values indicate a  session generating lots of redo. SQL> SELECT s.sid, s.serial#, s.username, s.program,      i.block_changes      FROM v$session s, v$sess_io i      WHERE s.sid = i.sid      ORDER BY 5 desc, 1, 2, 3, 4; Query V$TRANSACTION. This view contains information about the amount of undo blocks and undo records accessed by the transaction (as found in the  USED_UBLK and USED_UREC columns). SQL> SELECT s.sid, s.serial#, s.username, s.program,       t.used_ublk, t.used_urec      FROM v$session s, v$tr

count  the number of the files in that directory and its sub directories is: ls -laR | grep -v ^[.dlt] | grep -v ^$ | wc -l OR run either of  the below codes  :- for t in files links directories; do echo `find . -maxdepth 1 -type ${t:0:1} | wc -l` $t; done 2> /dev/null 50 files 0 links 74 directories for t in files links directories; do echo `find . -type ${t:0:1} | wc -l` $t; done 2>/dev/null 6781 files 0 links 814 directories

( Note All these steps are just indicative , run these on your test environment , before production ) - shutdown all instance except one - restart your one instance in mount state which you have left in read - write mode on which you are going to   perform this - alter system set cluster_database=false scope=spfile sid='*'; - shutdown immediate - startup mount;   - alter database noarchivelog; - alter system set log_archive_dest='' scope=spfile sid='*';   - alter system set cluster_database=true scope=spfile sid='*' ; - Startup all instances

select user_name, audit_option, success, failure         from sys.dba_stmt_audit_opts; select sessionid, to_char(ntimestamp#,'DD-MON-YY:HH24:MI:SS') login, userid, to_char(logoff$time,'DD-MON-YY:HH24:MI:SS') logoff from sys.aud$ where userid='<schema_name>'; select username, priv_used, ses_actions from dba_audit_object; select action, action_name, username from dba_audit_trail ; select * from stmt_audit_option_map;

select * from  DBA_HIST_ACTIVE_SESS_HISTORY; select * from  DBA_HIST_BASELINE; select * from  DBA_HIST_BG_EVENT_SUMMARY; select * from  DBA_HIST_BUFFERED_QUEUES; select * from  DBA_HIST_BUFFERED_SUBSCRIBERS; select * from  DBA_HIST_BUFFER_POOL_STAT; select * from  DBA_HIST_COMP_IOSTAT; select * from  DBA_HIST_CR_BLOCK_SERVER; select * from  DBA_HIST_CURRENT_BLOCK_SERVER; select * from  DBA_HIST_DATABASE_INSTANCE; select * from  DBA_HIST_DATAFILE; select * from  DBA_HIST_DB_CACHE_ADVICE; select * from  DBA_HIST_DLM_MISC; select * from  DBA_HIST_ENQUEUE_STAT; select * from  DBA_HIST_EVENT_NAME; select * from  DBA_HIST_FILEMETRIC_HISTORY; select * from  DBA_HIST_FILESTATXS; select * from  DBA_HIST_INSTANCE_RECOVERY; select * from  DBA_HIST_INST_CACHE_TRANSFER; select * from  DBA_HIST_JAVA_POOL_ADVICE; select * from  DBA_HIST_LATCH; select * from  DBA_HIST_LATCH_CHILDREN; select * from  DBA_HIST_LATCH_MISSES_SUMMARY; select * from  DBA_HIST_LATCH_NAME; select * fr

 If you get this  error by deleting log files in any directory :- /bin/rm: Argument list too long then run the following command :-  find . -name '*' | xargs rm  find . -name 'spam-*' | xargs rm   find . -name '*.trc' | xargs rm find . -name 'core*' | xargs rm -rf   find /rpt01/dbs/log/ -name '*.log' -mtime +4 -exec rm {} \; (This will delete 4 days old log files)

SQL> select to_char(completion_time,'DD/MM/YYYY') HOUR,count(*),round(sum(blocks*block_size)/1024/1024/1024,2) size_g from v$archived_log where completion_time>sysdate-30 group by to_char(completion_time,'DD/MM/YYYY') order by to_char(completion_time,'DD/MM/YYYY') asc; SQL> show parameter db_recovery_file_dest NAME                                 TYPE                             VALUE ------------------------------------ -------------------------------- ------------------------------ db_recovery_file_dest                string                           /ora/db001/dbfra001/<db_name> db_recovery_file_dest_size           big integer                      500G SQL>!df -h | grep /ora/db001/dbfra001           2.5T  348G  2.1T  15% /ora/db001/dbfra001 redo logs are located on "/ora/db001/redo001/FPD1AP/" and "/ora/db001/redo002/<db_name>/" ,total 15 redo logfiles with total size 92GB.  SQL>

To verify structure of a table, index, materialized view, use the ANALYZE statement with the VALIDATE STRUCTURE option. If the structure is valid, no error is returned. However, if the structure is corrupt, you receive an error message. For example, in rare cases such as hardware or other system failures, an index can become corrupted and not perform correctly. If index is corrupt, you can drop and re-create it. If a table, index is corrupt, you should drop it and re-create it. If a materialized view is corrupt, perform a complete refresh and ensure that you have remedied the problem. If the problem is not corrected, drop and re-create the materialized view. The following statement analyzes the table: ANALYZE TABLE <table_name> VALIDATE STRUCTURE; You can validate an object and all dependent objects (for example, indexes) by including the CASCADE option. The following statement validates the table and all associated indexes: ANALYZE TABLE <table_name>

Sometimes user getting locked again and again after running some procedure or function , then in that case either disable  password_verify_function or alter profile or unlock the user by providing password manually . select * from dba_profiles; select * from dba_users where USERNAME like '<USERNAME>'; ALTER PROFILE DEFAULT limit PASSWORD_GRACE_TIME unlimited; to disable password_verify_function:- SQL> ALTER PROFILE DEFAULT limit password_verify_function null; SQL> select profile from dba_users where username='<user_name>'; SQL> select RESOURCE_NAME,RESOURCE_TYPE,LIMIT FROM DBA_PROFILES WHERE PROFILE='<PROFILE_NAME>' AND RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION'; SQL> ALTER PROFILE <PROFILE_NAME> LIMIT PASSWORD_VERIFY_FUNCTION NULL; Profile altered. SQL> alter user <username> identified by <password> account unlock; User altered.

alter table <schema_name>.<table_name>   add constraint <contraint_name> primary key (<column_name>, <column_name>)   using index   tablespace <tablespce_name>   pctfree 10   initrans 2   maxtrans 255   storage   (     initial 1M     next 1M     minextents 1     maxextents unlimited     pctincrease 0   )LOCAL;

SQL> select count(*),event from v$active_session_history where session_id=331 group by event order by count(*) desc;   COUNT(*) EVENT ---------- ----------------------------------------------------------------        323 log file sync        220 db file sequential read        132 log file switch (checkpoint incomplete)        110 buffer busy waits 2.            Today, Log File related wait events are not there. Below are the wait events: SQL> select EVENT,TOTAL_WAITS,TIME_WAITED from v$session_event where sid=331 order by time_waited desc;                  EVENT                                                            TOTAL_WAITS TIME_WAITED ---------------------------------------------------------------- ----------- ----------- SQL*Net message from dblink                                            32615     1785828 local write wait                                                        2048      199947 enq: RO - fast object reuse           

[oracle@cint ~]$ orapwd file=$ORACLE_HOME/dbs/orapwevt01 password=sys entries=5 [oracle@cint ~]$ sqlplus sys/sys as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 19 10:41:00 2012 Copyright (c) 1982, 2005, Oracle.  All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> Select username from dba_users where username='SYSMAN'; USERNAME ------------------------------ SYSMAN SQL> alter user sysman identified by sys account unlock; User altered. SQL> alter user dbsnmp identified by sys account unlock; User altered. SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options [oracle@cint ~]$ emca -repos create (or run the command ./emca -config dbcontrol db -repos create) STARTED EMCA at Mar 19, 2012 10:44:12 AM EM Configuration