ORACLE LIVE EXAMPLES

Find segments with corrupted block from alert log with ORA-01578

2009-10-23T23:26:00.000-07:00

1. Get the list of corrupted blocks  grep 'ORA-01578' alert_DB.log | sort -u > tmp.txt

2. Generate SQL script to find segments with corrupted blocks  cat tmp.txt | awk '{ printf("%s %s\n", $8, $11); }' | \ awk -F\, '{ printf ("%s %s\n", $1, $2); }' | \ awk -F\) '{ printf("%s\n", $1); }' | \ awk '{printf ("SELECT SEGMENT_NAME, SEGMENT_TYPE, RELATIVE_FNO FROM DBA_EXTENTS WHERE FILE_ID = %s AND %s BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;\n", $1, $2); }' > find_segs.sql

3. Run SQL to find out which objects have corrupted blocks  SQLPLUS> col SEGMENT_NAME format a30 SQLPLUS> spool corrupted_segments.log SQLPLUS> @find_segs SQLPLUS> spool off

4. Sort the output to remove duplicates  sort -u corrupted_segments.log

How to send email from 10g Oracle Database (UTL_MAIL)

2009-08-26T08:21:00.000-07:00

Heres a simple solution to send out emails from 10g Database sql prompt.
This solution will be really helpful if the OS utility (mailx, sendmail) is restricted for end users.
Steps to enable Mailing from Database
1. sqlplus ‘/ as sysdba’2. @$ORACLE_HOME/rdbms/admin/utlmail.sql3. @$ORACLE_HOME/rdbms/admin/prvtmail.plb4. Set smtp_server information in init.ora or spfile.oraalter system set smtp_out_server = ‘SMTP_SERVER_IP_ADDRESS:SMTP_PORT’ scope=both;25 = Default SMTP Port
If instance had been started with spfile
eg: alter system set smtp_out_server = ‘172.25.90.165:25′ scope=both;
If instance had been started with pfilealter system set smtp_out_server = ‘172.25.90.165:25′;Also make below entry in your initSID.ora
smtp_out_server = ‘172.25.90.165:25′
Thats It, your database is configured to send emails ….
How to send an email
1. sqlplus ‘/ as sysdba’2. exec utl_mail.send((sender => ‘oraclepitstop@wordpress.com’, recipients => ‘oraclepitstop@wordpress.com’, subject => ‘Testing UTL_MAIL Option’, message => ‘blah blah blah’);3. Check the inbox of the email id, to verify the email receipt.
To enable other DB users to use this functionality, grant execute permission on UTL_MAIL package.
eg: grant execute on utl_mail to apps;

Internal view for Oracle to track features used

2009-08-26T01:10:00.000-07:00

In 10g there is a new view called DBA_FEATURE_USAGE_STATISTICS which keeps features have been used.

Here is description of some of the columns:
name – feature name
first_usage_date – first date when the feature was used
last_usage_date – latest date when it was detected the feature was used
last_sample_date – latest date when the sample was taken
last_sample_period – time in seconds between sample taken at last_sample_date and sample before last_sample_date
currently_used – value is TRUE or FALSE, TRUE if the feature was used since it was last checked

SQL> SELECT name, first_usage_date, last_usage_date, last_sample_date, last_sample_period FROM dba_feature_usage_statistics WHERE currently_used = ‘TRUE’;

NAME FIRST_USA LAST_USAG LAST_SAMP
—————————————- ——— ——— ———
Locally Managed Tablespaces (user) 30-SEP-08 08-FEB-09 08-FEB-09
Multiple Block Sizes 25-NOV-08 08-FEB-09 08-FEB-09
Partitioning (system) 30-SEP-08 08-FEB-09 08-FEB-09
Protection Mode – Maximum Performance 30-SEP-08 08-FEB-09 08-FEB-09
Segment Advisor 07-OCT-08 08-FEB-09 08-FEB-09
Streams (system) 30-SEP-08 08-FEB-09 08-FEB-09
Virtual Private Database (VPD) 30-SEP-08 08-FEB-09 08-FEB-09
Change-Aware Incremental Backup 02-DEC-08 08-FEB-09 08-FEB-09
Recovery Area 30-SEP-08 08-FEB-09 08-FEB-09
Streams (user) 30-SEP-08 08-FEB-09 08-FEB-09

Enable and Disable Automatic Snapshots

2009-08-25T23:58:00.000-07:00

exec dbms_scheduler.enable(’GATHER_STATS_JOB’) – (Default)

exec dbms_scheduler.disable(’GATHER_STATS_JOB’)

-----------------------------------------------------------------------------

we can also display list of stats as follows :

SQL> set lines 100 pages 999
SQL> select snap_id, snap_level, to_char(begin_interval_time, ‘dd/mm/yy hh24:mi:ss’) starting
2 from dba_hist_snapshot
3 order by 1;

SNAP_ID SNAP_LEVEL STARTING
———- ———- —————–
11950 1 27/07/09 23:00:24
11951 1 28/07/09 00:00:25
11952 1 28/07/09 01:00:26
11953 1 28/07/09 02:00:27
11954 1 28/07/09 03:00:28
11955 1 28/07/09 04:00:30
11956 1 28/07/09 05:00:31
11957 1 28/07/09 06:00:31
11958 1 28/07/09 07:00:32
11959 1 28/07/09 08:00:33
11960 1 28/07/09 09:00:34
11961 1 28/07/09 10:00:35
11962 1 28/07/09 11:00:36
11963 1 28/07/09 12:00:37
11964 1 28/07/09 13:00:38
11965 1 28/07/09 14:00:39
11966 1 28/07/09 15:00:40
11967 1 28/07/09 16:00:41
11968 1 28/07/09 17:00:43
11969 1 28/07/09 18:00:43
11970 1 28/07/09 19:00:45
11971 1 28/07/09 20:00:46
11972 1 28/07/09 21:00:46
11973 1 28/07/09 22:00:47
11974 1 28/07/09 23:00:49
11975 1 29/07/09 00:00:50

If you want to change the retention police for the snapshot to 20 days:

exec dbms_workload_repository.modify_snapshot_settings (retention => 20*24*60);