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   !

because when you own it, you own the world. You can literally do anything you want (given you have that privilege of course)…

1. Quick, what does the following Unix/Linux command do?

ls -R | grep “:$” | sed -e ’s/:$//’ -e ’s/[^-][^\/]*\//–/g’ -e ’s/^/ /’ -e ’s/-/|/’

If you said, “Well, that’s obvious; it shows a graphical representation of the current sub-directories.“, you’d be correct.

2. You can use the command to create a program called tree that would work something like the following:

[ fayoubi@mars - Saturday, February 09, 2008 - 12:11:04 PM]
[ /home/fayoubi ] dba/scripts/tree.sh

/home/fayoubi
.
|-dba
|—cfg_refresh
|—junk
|—onetime
|—scripts
|—snaps
|—–2006_07
|——-nic
|——-nie
|——-nij
|—–2006_11
|——-nic
|——-nie
|——-nij
|—–2007_03
|——-nic
|——-nie
|—–2007_08
|—–2007_09
|—–2007_q1
|—–2007_q2
|——-nic
|——-nie
|—–2007_q4
|——-nic
|——-nie
|—–2008_q1
|——-nic
|——-nie
|—Training
|-example
|—yourdir
|—–mydir
|-junk
|-nipw
|-tempdir
|-tmp
|-Vignette
|-web

[ fayoubi@mars - Saturday, February 09, 2008 - 12:11:07 PM]
[ /home/fayoubi ]

3. Here is the code ready to go in a shell script:

#!/bin/sh
#######################################################
#  UNIX TREE                                          #
#                                                     #
#  Displays Structure of Directory Hierarchy          #
#  -------------------------------------------------  #
#  This tiny script uses "ls", "grep", and "sed"      #
#  in a single command to show the nesting of         #
#  sub-directories.                                   #
#                                                     #
#  Setup:                                             #
#     % cd ~/apps/tree                                #
#     % chmod u+x tree.sh                             #
#     % ln -s ~/apps/tree/tree.sh ~/bin/tree          #
#                                                     #
#  Usage:                                             #
#     % tree [directory]                              #
#                                                     #
#  Examples:                                          #
#     % tree                                          #
#     % tree /etc/opt                                 #
#     % tree ..                                       #
#                                                     #
#######################################################

echo
if [ "$1" != "" ]  #if parameter exists, use as base folder
then cd "$1"
fi
pwd
ls -R | grep ":$" |
sed -e 's/:$//' -e 's/[^-][^/]*//--/g' -e 's/^/   /' -e 's/-/|/'
# 1st sed: remove colons
# 2nd sed: replace higher level folder names with dashes
# 3rd sed: indent graph three spaces
# 4th sed: replace first dash with a vertical bar
if [ `ls -F -1 | grep "/" | wc -l` = 0 ]   # check if no folders
then echo "   -> no sub-directories"
fi
echo
exit

That’s it.

Again y’all, 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

you run a top on your unix box only to find out that there is a process running at 25% CPU usage. Here is an example:

11703 oracle     1  59    0 2874M 2856M sleep    1:01  25.05% oracle

You are not sure what is really happening, but you know it is an oracle process and it is something definetly running against your database and churning cpu. Here is a script that u can run against your database to determine what SQL statement your database is currently running:
select  distinct s.username,
s.osuser,
s.machine,
substr(s.program,1,4) prog,
s.sid,
s.serial#,
decode(trunc(logon_time), trunc(sysdate),to_char(logon_time,’HH24:MI’),
to_char(logon_time, ‘DD-MON’) ) logon,
floor(last_call_et/3600) || ‘:’ ||
floor(mod(last_call_et,3600)/60) || ‘:’ ||
mod(mod(last_call_et,3600),60) run_time,
last_call_et,
q.sql_text
from v$session s, v$sql q
where s.sql_address= q.address
and s.status = ‘ACTIVE’
and s.username is not null
and q.sql_text not like ‘%pipe%’
order by last_call_et desc;

Remember that you can only run the statement above if you have sys or sysdba access privilges.

-As always thanks for Reading.

While researching this I came through the following metalink documentation which I thought did a great job describing the problem: (need to have metalink access)
How to Determine Real Space used by a Table (Below the High Water Mark)

However the article leave you in the dark as far as where defragmentation is happening and where action needs to happend in order to see the performance benefits

I wanted to share the following script also on metalink.
declare
cursor c_dbfile is
select tablespace_name
,file_name
,file_id
,bytes
from sys.dba_data_files
where status !=’INVALID’
and tablespace_name = upper(’&tablespace_name’)
order by tablespace_name,file_id;

cursor c_space(v_file_id in number) is
select block_id,blocks
from sys.dba_free_space
where file_id=v_file_id
order by block_id desc;

blocksize binary_integer;
filesize integer;
extsize integer;

begin

/* get the blocksize of the database, needed to calculate the startaddress */

select value
into blocksize
from v$parameter
where name = ‘db_block_size’;

/* retrieve all datafiles */

for c_rec1 in c_dbfile
loop
filesize := c_rec1.bytes;
<>
for c_rec2 in c_space(c_rec1.file_id)
loop
extsize := ((c_rec2.block_id - 1)*blocksize + c_rec2.blocks*blocksize);
if extsize = filesize
then
filesize := (c_rec2.block_id - 1)*blocksize;
else
/* in order to shrink the free space must be uptil end of file */
exit outer;
end if;
end loop outer;
if filesize = c_rec1.bytes
then
dbms_output.put_line(’Tablespace: ‘
||’ ‘||c_rec1.tablespace_name||’ Datafile: ‘||c_rec1.file_name);
dbms_output.put_line(’Can not be resized, no free space at end of file.’)
;
dbms_output.put_line(’.');
else
if filesize < 2*blocksize
then
dbms_output.put_line(’Tablespace: ‘
||’ ‘||c_rec1.tablespace_name||’ Datafile: ‘||c_rec1.file_name);
dbms_output.put_line(’Can be resized uptil: ‘||2*blocksize
||’ Bytes, Actual size: ‘||c_rec1.bytes||’ Bytes’);
dbms_output.put_line(’.');
else
dbms_output.put_line(’Tablespace: ‘
||’ ‘||c_rec1.tablespace_name||’ Datafile: ‘||c_rec1.file_name);
dbms_output.put_line(’Can be resized uptil: ‘||filesize
||’ Bytes, Actual size: ‘||c_rec1.bytes);
dbms_output.put_line(’.');
end if;
end if;
end loop;
end;
/

Thanks for reading

When running an sql script from unix, the unix ps (process status) command has a nasty habit of showing the Oracle userid and password of the sqlplus session.  To get around this problem, you can pipe the password into sqlplus instead of putting it in as part of the sqlplus command line (which is what shows up in the ps listing).  Below are examples of piping in the password (userid system, password systempw) for sqlplus, import, and export.  If you put these in a unix shell command file, such as myfile.shl, be sure to do a “chmod 700 myfile.shl” so that only your userid can see your Oracle passwords in that file!

After doing this, any time anyone executes sqlplus with command line options, those command line options will not be visible from ps.  Be aware, though, if you apply any patches to your Oracle bin directory, you must first do a “mv sqlplus.hide sqlplus” to put back the original sqlplus binary into the Oracle bin directory.  Then, after applying the patches, you can once again run through those steps above to create a link from the hide binary to sqlplus.  (Note: I haven’t tried using hide.c, since I always use the pipe method, but, I’ve seen this at several places on the web and listservs, so, I’m including it here in case you want to try it out.)

-Thanks for reading