Thursday, March 15, 2012

Index Clustering Factor

›
The clustering_factor measures how synchronized an index is with the data in a table. A table with a high clustering factor is out-of-seque...

CTAS create table as select

›
Index Hint is best solution ----------------------------------CTAS with ORDER BY create table transactions14 as select * from transactions;...

Get DDL

›
GET_DEPENDENT_DDL(object_type, base_object_name, base_object_schema, version, model, transform, object_count) GET_GRANTED_DDL(object_typ...

Table Actual Size

›
SELECT owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg FROM (SELECT segment_name table_name, owner, bytes FROM dba_segments WHERE se...

Schedule Job for Exe file

›
BEGIN dbms_scheduler.create_job( job_name => 'del_archive', job_type => 'EXECUTABLE', job_action => ...

Role Recreation

›
set heading off verify off feedback off echo off term off linesize 200 wrap on spool c:\temp\roles_creation.sql SELECT 'Create Role ...

Shrink Datafile Suggestion

›
select bytes/1024/1024 real_size,ceil( (nvl(hwm,1)*16384)/1024/1024 ) shrinked_size, bytes/1024/1024-ceil( (nvl(hwm,1)*16384)/1024/1024 ) re...
Wednesday, March 14, 2012

ORA-03297 file contains used data beyond requested RESIZE value

›
select a.file_name, a.bytes file_size_in_bytes, (c.block_id+(c.blocks-1)) * &_BLOCK_SIZE HWM_BYTES, a.bytes - ((c.block_id+(c.blocks-1))...
Friday, February 17, 2012

ORA-00997: illegal use of LONG datatype (Migration Data LOB column)

›
SQL> CREATE GLOBAL TEMPORARY TABLE TMP_scan ON COMMIT PRESERVE ROWS as select FIRMNUMBER,CODE,PSCAN NEDIMAGE,NFINANCIALYEAR from ldbo....
Thursday, February 16, 2012

Job schedule compile invalid objects

›
REQUIRE SYS PRIVILIGES TO EXECUTE UTL_RECOMP BEGIN DBMS_SCHEDULER.create_job ( job_name => 'compile_invalid', jo...
Tuesday, February 14, 2012

ORA-00600: internal error code, arguments: [4193], [5396], [2242]

›
ORA-00607: Internal error occurred while making a change to a data block ORA-00600: internal error code, arguments: [4193], [5396], [2242], ...
Monday, February 13, 2012

Server Capacity Planning

›
1) Existing server configuration (Processor, No of CPU, RAM, Disk Capacity, … , …) 2) No. of running databases on server 3) Databases fol...

SGA PGA measuring

›
select * from v$sgastat order by 1; select * from v$pgastat order by 1; I noticed that you have some Pools in your SGA which are not used: l...

pga requirement as per high watermark

›
select (select highwater from dba_high_water_mark_statistics where name = ('SESSIONS'))*(2048576+a.value+b.value)/1024/1024 pga_size...
Thursday, February 9, 2012

Invalid Object Why?????????????

›
SELECT owner || '.' || object_name invalid_object,'--- ' || object_type || ' ---' likely_reason FROM dba_objects WHE...
Wednesday, February 8, 2012

Analyze Scheduling using oracle

›
---------------------------------------------------------------------frequency 1 day----------------------------------------- BEGIN DBMS_S...

EXPDP Data Pump Job Scheduling with rename dump and remove old files

›
1) create directory export_auto as 'd:\expdp1213'; create user dba_export_user identified by test123; grant connect, create databas...
1 comment:
Monday, February 6, 2012

ORACLE AUDIT FOR ALTER COMMAND

›
CREATE TABLE DBA_AUDIT_TAB_KSH (USERNAME VARCHAR2(10), SQL_TEXT VARCHAR2(2000),TIMESTAMP DATE); CREATE OR REPLACE TRIGGER DBA_AUDIT_KSH BEFO...
Saturday, February 4, 2012

Performance Tuning Basic Guidelines

›
** Redo Log files – ensure that redo log are allocated on the fast disk, with minimum activities. ** Temporary tablespaces – ensure that tem...
Monday, January 2, 2012

ORA-01114: IO error writing block to file 202 (block # 1473756)

›
Linux-x86_64 Error: 25: Inappropriate ioctl for device ERROR at line 29: ORA-01114: IO error writing block to file 202 (block # 1473756) ORA...