The simplest way to rebuild and index is:

Alter index rebuild tablespace ;

This also has the advantage of not preventing the index from being used whilst it is being rebuilt. The tablespace must be specified otherwise the index will be rebuilt in the current users default tablespace.

Rebuilding the index also allows new storage parameters to be assigned to the index.

If the index is corrupt, it must be dropped and recreated.

1. Firstly, identify the original creation parameters:

   SELECT COLUMN_NAME FROM USER_IND_COLUMNS
   WHERE INDEX_NAME = 'index_name';

   SELECT TABLE_NAME, UNIQUENESS, TABLESPACE_NAME,
   INITIAL_EXENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS,
   PCT_INCREASE, PCT_FREE
   FROM USER_INDEXES WHERE INDEX_NAME = 'index_name';

   The above will give you the columns on which the index is based.

2. And then drop the index and recreate it:

   Drop index ;

   Create [UNIQUE] index 
   On
   (  [ ,  ] )
   tablespace
   PCTFREE
   
   STORAGE (
   NEXT K
   INITIAL K
   PCTINCREASE
   
   MIN_EXTENTS 
   MAX_EXTENTS  );

Today, a battle is raging between the “academics” who do not believe that indexes should be rebuilt without expensive studies and the “pragmatists” who rebuild indexes on a schedule because their end-users report faster response times.

I highly recommend Reading up on the differences between these two different approaches.

-Thanks for reading and come back again.

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.

The DBA should assign a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace.

In Here we focus on the resize operation rather than Creation:

To resize TEMP, you must drop and recreate it. You cannot drop the temporary temp tablespace, so these commands create a temporary temp tablespace, then drop and recreate temp with the new size.

CREATE TEMPORARY TABLESPACE temp1 TEMPFILE
‘/u08/oracledbf/zwebt/temp101.dbf’ SIZE 800M
EXTENT MANAGEMENT LOCAL
uniform size 1M;

alter database default temporary tablesp@ace temp1;

drop tablespace temp including contents and datafiles;

! ls /u*/oracledbf/zwebt/temp0*.dbf

! rm /u*/oracledbf/zwebt/temp0*.dbf

CREATE TEMPORARY TABLESPACE temp TEMPFILE

‘/u08/oracledbf/zwebt/temp01.dbf’ SIZE 2048M

EXTENT MANAGEMENT LOCAL

uniform size 1M;

alter database default temporary tablespace temp;

drop tablespace temp1 including contents and datafiles;

! ls /u*/oracledbf/zwebt/temp10*.dbf

! rm /u*/oracledbf/zwebt/temp10*.dbf

HERE IS TO REVERT THESE CHANGES:

CREATE TEMPORARY TABLESPACE temp1 TEMPFILE
‘/u08/oracledbf/zwebt/temp101.dbf’ SIZE 800M
EXTENT MANAGEMENT LOCAL
uniform size 1M;

alter database default temporary tablespace temp1;

drop tablespace temp including contents and datafiles;

! ls /u*/oracledbf/zwebt/temp0*.db

! rm /u*/oracledbf/zwebt/temp0*.dbf

CREATE TEMPORARY TABLESPACE temp TEMPFILE
‘/u08/oracledbf/zwebt/temp01.dbf’ SIZE 800M
EXTENT MANAGEMENT LOCAL
uniform size 1M;

alter database default temporary tablespace temp;

drop tablespace temp1 including contents and datafiles;

! ls /u*/oracledbf/zwebt/temp10*.dbf

! rm /u*/oracledbf/zwebt/temp10*.dbf

Starting in Oracle9i, rollback segments are re-named undo logs. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued, at which point it was made available for overlaying.

It is worth to tune the following important parameters

1. The size of the UNDO tablespace
2. The UNDO_RETENTION parameter

Actual Undo Size

SELECT SUM(a.bytes) “UNDO_SIZE”
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = ‘UNDO’
AND c.status = ‘ONLINE’
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;

UNDO_SIZE
———-
209715200

Undo Blocks per Second

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
“UNDO_BLOCK_PER_SEC”
FROM v$undostat;

UNDO_BLOCK_PER_SEC
——————
3.12166667

DB Block Size

SELECT TO_NUMBER(value) “DB_BLOCK_SIZE [KByte]”
FROM v$parameter
WHERE name = ‘db_block_size’;

DB_BLOCK_SIZE [Byte]
——————–
4096

Optimal Undo Retention

209‘715‘200 / (3.12166667 * 4′096) = 16′401 [Sec]

Using Inline Views, you can do all in one query!

SELECT d.undo_size/(1024*1024) “ACTUAL UNDO SIZE [MByte]”,
SUBSTR(e.value,1,25) “UNDO RETENTION [Sec]”,
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) “OPTIMAL UNDO RETENTION [Sec]”
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = ‘UNDO’
AND c.status = ‘ONLINE’
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = ‘undo_retention’
AND f.name = ‘db_block_size’
/

ACTUAL UNDO SIZE [MByte]
————————
200

UNDO RETENTION [Sec]
——————–
10800

OPTIMAL UNDO RETENTION [Sec]
—————————-
16401

The following steps explain what needs to take place in order to recover from a crash where a DB control file gets corrupted:

The likely error thrown in this scenario is as follow:

Fri Mar  7 22:00:46 2008
Hex dump of (file 0, block 1) in trace file /u01/app/oracle/admin/teng/bdump/teng_j001_9438.trc
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Bad check value found during control file header read
Data in bad block:
type: 21 format: 2 rdba: 0x00000001
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00001501
check value in block header: 0xf8de
computed block checksum: 0x1501

Problem Description:
====================

The OS crashed and at the time two instances were running.

After correcting the OS issue then you try to start the instance(s)
and upon mounting the database you receive the following errors:

ORA-00227: corrupt block detected in controlfile: (block %s, # blocks %s)
Cause: A block header corruption or checksum error was detected on
reading the controlfile.
Action: Use the CREATE CONTROLFILE or RECOVER DATABASE USING BACKUP
CONTROLFILE command.

ORA-00202: control file: 
Cause: This message reports the name of the file involved in other
messages.
Action: See the associated messages for a description of the problem.

Problem Explanation:
====================

Control file is corrupt.

Solution Description:
=====================

Try to use one of the mirrored copies of the control file. You should have
mirrored images of your control file. Oracle recommends at least 2 mirrored
copies. Their locations should be listed in your “init.ora” file.

Try using one of these mirrored control files to enable you to bring up the
database by copying one of them over the bad one.

Or

Comment out the bad control file from the “control_files” parameter in your
“init.ora” file and try bringing up the database with one of your mirrored
control files.

If the above doesn’t work, then all control files are corrupt and you must
create a new control file.

Take a backup of the control files from the last backup and follow steps
below to recreate a good control file.

===========
OPTION ONE
===========

1. The alert log should identify which control file it is trying to use.

See error ora-202.
>  Note this (control1).

2. Make a backup of control files as they are now.

cp controlfiles controlfile.bk

3. Take one of the other control files and copy over the bad control file
noted above.

If this does not work, then proceed to the next step.

==============
OPTION TWO
==============

Comment the bad control file from the control_files parameter.

In the init.ora:

control_file = comment out the one listed in alert log from ora-202.

Try starting the instance.

If you receive the same errors as previously with the name of the control file
changed to controlfile2, then go to the next option.

=============
OPTION THREE
=============

Issue:
1. Shutdown immediate.

2. Startup nomount pfile= .

3. Alter database mount;

4. Alter database backup controlfile to trace;

5. Shutdown.

6. Check for trace file:

Either in user_dump_dest = trace file with latest time stamp; nothing
or background_dump_dest.

The file name should be:  ora
.trc.

This should contain a script to recreate the control file.

7. Save file as control.sql.

8. Edit file to start with:  startup nomount.

9. Check Oracle_sid : should be set to correct sid.

10. Svrmgrl>  connect internal

11. @control.sql

You will reeceive a message: Stmt processed.

waiting on svrmgrl prompt

12. Svrmgrl media recovery complete

svrmgrl> select sysdate from dual; june 4, 1999

Database is up and open.

13. Select on main application table:

sqlplus :  connected ok.

Solution Explanation:
=====================

If all the control files are corrupt, then you will need to create a new control
file to be able to bring up your database.

Once again Thanks for reading   !

You have a tablespace that is growing out of control and you need to re org.

this is a very lengthy subject but below are the steps you ll need to take in order to rename your datafiles, you can then trun off autoextend, and run a monitoring script to either:

1) Resize a current datafile and adding extra KB
2) Addin a new datafile with a given # of KB.

Here is an example: (remember you can only do this if you are sys)
sqlplus “/ as sysdba”
shutdown immediate;
!mv /p03/oracledbf/pan/AWPDR.dbf /p03/oracledbf/pan/AWPDR01.dbf
!mv /p03/oracledbf/pan/AWMBeanDB.dbf /p03/oracledbf/pan/AWMBeanDB01.dbf
!mv /p03/oracledbf/pan/AWMSUI.dbf /p03/oracledbf/pan/AWMSUI01.dbf
startup mount;

alter database rename file ‘/p03/oracledbf/pan/AWPDR.dbf’ TO ‘/p03/oracledbf/pan/AWPDR01.dbf’;
alter database rename file ‘/p03/oracledbf/pan/AWMBeanDB.dbf’ TO ‘/p03/oracledbf/pan/AWMBeanDB01.dbf’;
alter database rename file ‘/p03/oracledbf/pan/AWMSUI.dbf’ TO ‘/p03/oracledbf/pan/AWMSUI01.dbf’;

alter database datafile ‘/p03/oracledbf/pan/AWPDR01.dbf’ autoextend off;
alter database datafile ‘/p03/oracledbf/pan/AWMBeanDB01.dbf’ autoextend off;
alter database datafile ‘/p03/oracledbf/pan/AWMSUI01.dbf’ autoextend off;

alter database open;

To Verify:
col file_name format a50;
select file_name, autoextensible from dba_data_files;
clear columns;
eof

As always Thanks for reading

Does oracle really expect you DBAs to know every vdollar table by heart ?

The answer is no. I have run into the issue so many times. specially if you are oncall and that big poster that has every table name etc… is in you cube and nowhere close to the terminal u are sitting in . Here is a quick solution to this problem be SYS before you try this:

15:05:26 SQL> !cat /home/fayoubi/dba/scripts/vdollar.sql
select table_name, column_name
from dba_tab_columns
where table_name like ‘V_%’
and column_name like UPPER(’%&column_name%’)
order by table_name
/

15:05:43 SQL>

Enter value for column_name: INDEX
old   4: and column_name like UPPER(’%&column_name%’)
new   4: and column_name like UPPER(’%INDEX%’)

TABLE_NAME                     COLUMN_NAME
—————————— ——————————
V$OBJECT_USAGE                 INDEX_NAME
VTABLE$                        VINDEX
V_$ASM_ALIAS                   REFERENCE_INDEX
V_$ASM_ALIAS                   ALIAS_INDEX
V_$ASM_ALIAS                   PARENT_INDEX
V_$ASM_DISK                    COMPOUND_INDEX
V_$ASM_DISK_STAT               COMPOUND_INDEX
V_$ASM_FILE                    COMPOUND_INDEX
V_$CONTROLFILE_RECORD_SECTION  LAST_INDEX
V_$CONTROLFILE_RECORD_SECTION  FIRST_INDEX
V_$INDEXED_FIXED_COLUMN        INDEX_NUMBER

TABLE_NAME                     COLUMN_NAME
—————————— ——————————
V_$LOADISTAT                   INDEXNAME

12 rows selected.

Quickly you get back which tables (v$ ones) have the column you are looking for. you can now select the one you want to query and know what you ll get back.

Hope you found this very helpful.

-Thanks for reading.

What: Pinning an Object to the shared pool area is the process of tellin oracle to leave an object in the shared pool at all time - unless the database is shutdown.

Why: Pinning Oracle objects prevents oracle from doing moving objects in and out of the shared pool which can degrade performance (specifically if the objects in question are core libraries (large core packages that always get called by your applications - i.e. Web application )

When: Pinning should happen when a database is started, while calling your custom statup.sh script, u can call a pin_objs.sh which can in turn call a sql file, that determines what objects to pin and then pins them to the shared pool.

How: Let s get to the core of this

insert into OBJECTS_TO_PIN (object_name, object_type_flag,
owner, date_entered)
SELECT name, decode(type,’PACKAGE’, ‘P’,'PACKAGE BODY’,'P’,
‘TRIGGER’,'R’,'PROCEDURE’,'P’), owner, sysdate
FROM v$db_object_cache
WHERE type in (’TRIGGER’,'PROCEDURE’,'PACKAGE BODY’)
AND executions > 0
AND kept = ‘NO’
AND (loads > 1
OR sharable_mem >=40000)
ORDER BY
sharable_mem desc,
loads,
executions desc;

commit;

——————————————————————————————————

******Once all these objects are identified and inserted into your table OBJECTS_TO_PIN******

——————————————————————————————————
declare
own varchar2(100);
nam varchar2(100);
flagx varchar2(1);

cursor objs is
select owner, object_name, object_type_flag
from objects_to_pin;

begin
open objs;
loop
fetch objs into own, nam, flagx;
exit when objs%notfound;
dbms_shared_pool.keep(own||’.'||nam, flagx);
end loop;

end;
/

USE Pinning it will have a great advantage on performance of your DBs.

I hope you enjoyed reading

From a command prompt:

ftp updates.oracle.com (metalink username and password)
ftp> bin
## For 9i R2(9.2.x) and 10GR1(10.1.x)
ftp> cd /2617419
ftp> get p2617419_10102_GENERIC.zip
ftp> quit
## For 10GR2(10.2.x) ftp> cd /4898608
ftp> get p4898608_10202_GENERIC.zip
ftp> quit

2)

## For 9i R2(9.2.x) and 10GR1(10.1.x)

Then upload or move the p2617419_10102_GENERIC.zip to ORACLE_HOME and uncompress it:

Example:
==================================================================

% mv p2617419_10102_GENERIC.zip $ORACLE_HOME
% cd $ORACLE_HOME
% unzip p2617419_10102_GENERIC.zip

Archive:p2617419_10102_GENERIC.zip
inflating: OPatch/docs/README.txt
inflating: OPatch/docs/bt1.txt
inflating: OPatch/docs/bt2.txt
inflating: OPatch/docs/FAQ
inflating: OPatch/docs/Users_Guide.txt
inflating: OPatch/jlib/opatch.jar
extracting: OPatch/opatch.ini
inflating: OPatch/opatch
inflating: OPatch/opatch.pl
inflating: OPatch/perl_modules/Query.pm
inflating: OPatch/perl_modules/Version.pm
inflating: OPatch/perl_modules/RollBack.pm
inflating: OPatch/perl_modules/XML.pm
inflating: OPatch/perl_modules/compile.csh
inflating: OPatch/perl_modules/opatchIO.pm
inflating: OPatch/perl_modules/LsInventory.pm
inflating: OPatch/perl_modules/AttachHome.pm
inflating: OPatch/perl_modules/Command.pm
inflating: OPatch/perl_modules/Apply.pm
inflating: OPatch/opatch.bat
inflating: README.txt

% cd OPatch
% opatch version
PERL5LIB=/oracle/V920/Apache/perl/lib/5.00503:./perl_modules; export PERL5LIB
/oracle/V920/Apache/perl/bin/perl ./opatch.pl version
./opatch.pl version: 1.0.0.0.49

## For 10GR2(10.2.x)

Then upload or move the p4898608_10202_GENERIC.zip to ORACLE_HOME and uncompress it:
Example
=======
% mv p4898608_10202_GENERIC.zip $ORACLE_HOME
% cd $ORACLE_HOME
% unzip p4898608_10202_GENERIC.zip
Archive: p4898608_10202_GENERIC.zip
creating: OPatch/
creating: OPatch/docs/
inflating: OPatch/docs/FAQ
inflating: OPatch/docs/Users_Guide.txt
inflating: OPatch/docs/Prereq_Users_Guide.txt
creating: OPatch/jlib/
inflating: OPatch/jlib/opatch.jar
inflating: OPatch/jlib/opatchutil.jar
inflating: OPatch/jlib/opatchprereq.jar
creating: OPatch/opatchprereqs/
creating: OPatch/opatchprereqs/opatch/
inflating: OPatch/opatchprereqs/opatch/opatch_prereq.xml
inflating: OPatch/opatchprereqs/opatch/rulemap.xml
inflating: OPatch/opatchprereqs/opatch/runtime_prereq.xml
creating: OPatch/opatchprereqs/oui/
inflating: OPatch/opatchprereqs/oui/knowledgesrc.xml
extracting: OPatch/opatchprereqs/opatch_prereq.sh
inflating: OPatch/opatchprereqs/prerequisite.properties
inflating: OPatch/opatch
inflating: OPatch/opatch.bat
inflating: OPatch/opatch.pl
inflating: OPatch/opatch.ini
inflating: OPatch/emdpatch.pl
inflating: OPatch/README.txt
% cd OPatch
% opatch version
Invoking OPatch 10.2.0.2.1

OPatch Version: 10.2.0.2.1

==================================================================

3) Now you can set the Opatch directory in your PATH variable so you can execute the opatchcommand from anywhere.

Example:
==================================================================

For Korn / Bourne shell
% export PATH=$PATH:$ORACLE_HOME/Opatch

And that should do it !

As Always thanks for reading

This article is building up on the older post, so if you understand the previous article this will make lots of sense.

Suppose you found what the sessions SIDs that are accessing your objects, now u got to do something about them…

KILL THEM ALL… (Metallica album

Here is how you acheive that in the most efficient way: - This will prompt you for the Object you are trying to release
DECLARE
CURSOR rec IS
SELECT s.SID, s.serial#, s.username, s.osuser
FROM v$access a, v$session s
WHERE a.SID = s.SID
AND (object LIKE ‘&object_to_release’);
BEGIN
FOR i IN rec LOOP
EXECUTE IMMEDIATE ‘ALTER SYSTEM KILL SESSION ‘'’ || i.sid || ‘,’|| i.serial# || ‘'’ IMMEDIATE’;
COMMIT;
END LOOP;
EXCEPTION WHEN OTHERS THEN
RAISE;
END;
/

There is one caveat here. if you have a session that is accssing that object (for example you are compiling the same object) the Block above will kill your session too ! -you can avoid that by adding a clause the query and doing something like where SID <> ‘your_sid’

As Always.. Thanks for reading, keep coming back for more

You are running into locing issues, or not sure what is accessing a simple object and pissbly locking it.

The query below simplifies the process and returns to you sessions that are holding the object.

column username format a10
column osuser format a10
column machine format a15
column sid format 9999
column serial# format 9999999
column db_object format a25

select s.username, s.osuser, s.machine, a.sid, s.serial#, a.object db_object
from v$access a, v$session s
where a.sid = s.sid
and a.object like upper(’&name_of_object’);

clear columns

Again Thanks for reading.