Oracle 10g Pre Installation Validation for AIX 5L

Posted by Gavin Soorma
On June 18, 2009
1 Comments

Oracle has provided a utility which we can run before doing an installation on AIX – it will check quite a few things for us and enable us to fix those before proceeding with an installation which could fail at a later stage. There is a tar file validate.tar located in the $HOME directory on […]

Posted by Gavin Soorma
On June 18, 2009
1 Comments

Wrong Update SQL> update customers set credit_limit=100000; Shutdown the database Startup mount the database invoke rman $ rman target / Flashback database to time one hour prior to current time RMAN> FLASHBACK DATABASE TO TIME ‘(SYSDATE-1/24)’; Starting flashback at 27-JUN-05 using target database controlfile instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 devtype=DISK […]

Redo Log File Switches – By hour of the day

Posted by Gavin Soorma
On June 18, 2009
0 Comments

prompt prompt "Morning .........." select to_char(first_time,'DD/MON') day, to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'000')"07", to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'000')"08", to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'000')"09", to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'000')"10", to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'000')"11", to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'000')"12", to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'000')"13", to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'000')"14", to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'000')"15", to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'000')"16", to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'000')"17", to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'000')"18" from v$log_history WHERE TRUNC(FIRST_TIME) > TRUNC(SYSDATE) - 7 group by to_char(first_time,'DD/MON'); prompt prompt Prompt "Evening ........" prompt select to_char(first_time,'DD/MON') day, to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'000')"19", to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'000')"20", to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'000')"21", to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'000')"22", to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'000')"23", to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'000') "00", to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'000')"01", to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'000')"02", to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'000')"03", to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'000')"04", to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'000')"05", to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'000')"06" from v$log_history WHERE […]

Creating a job using Oracle 10g Scheduler

Posted by Gavin Soorma
On June 18, 2009
0 Comments

BEGIN sys.dbms_scheduler.create_job( job_name => ‘”HR”.”ALTER_INDX001″‘, job_type => ‘PLSQL_BLOCK’, job_action => ‘begin EXECUTE IMMEDIATE ”alter index HR.EMP_NAME_IX coalesce”; end;’, start_date => systimestamp at time zone ‘Asia/Muscat’, job_class => ‘DEFAULT_JOB_CLASS’, comments => ‘This job will coalesce index HR.EMP_NAME_IX on the EMPLOYEES table’, auto_drop => FALSE, enabled => FALSE); sys.dbms_scheduler.set_attribute( name => ‘”HR”.”ALTER_INDX001″‘, attribute => ‘restartable’, value => […]

10g Datapump – quick introduction

Posted by Gavin Soorma
On June 18, 2009
1 Comments

In 10g you can continue to use the earlier exp and imp versions to export/import data. However if you export data using exp, then the import has to be with imp utility. You cannot export using exp and import using impdp The 2 new utilites in 10g are EXPDP and IMPDP. EXPORT USING DATAPUMP (EXPDP). […]

Check if block change tracking file is being used

Posted by Gavin Soorma
On June 18, 2009
0 Comments

select file#, avg(datafile_blocks), avg(blocks_read), avg(blocks_read/datafile_blocks) * 100 as "% read for backup" from v$backup_datafile where incremental_level > 0 and used_change_tracking = 'YES' group by file# order by file#;

Check for dependant foreign keys before dropping Primary Key

Posted by Gavin Soorma
On June 18, 2009
0 Comments

SELECT A.owner foreign_owner, a.table_name foreign_table, b.owner primary_owner, b.table_name primary_table from dba_constraints a, dba_constraints b where a.r_constraint_name = b.constraint_name and a.constraint_type = 'R' and b.constraint_type = 'P' and b.table_name = 'DEPT';

Posted by Gavin Soorma
On June 18, 2009
0 Comments

Business requirement User MILLER should only see records in the EMP table that match his name. User ADAMS should only see records in the EMP table that match his name User KING should be able to access all records in the EMP table Solution Overview Oracle Virtual Private Database (VPD) enables you to create security […]

How to kill all similar processes in Unix

Posted by Gavin Soorma
On June 18, 2009
0 Comments

In this example all opmn processes are being killed. This command is useful if we find a lot of processes still present at the OS level even after shutting down in this case the Oracle Application Server ps -ef | grep opmn |grep -v grep | awk ‘{print $2}’ |xargs -i kill -9 {}

Posted by Gavin Soorma
On June 18, 2009
1 Comments

Normal Restore Points If the db_recovery_dest_file_size has been allocated less space then what is required for the flashback transaction logs as dictated by the db_flashback_retention_target parameter, then the flashback logs are deleted to free up space for the additional logs that are being generated. Alert log will show also that flashback logs are being […]

Page 25 of 25«‹23 2425

News