Oracle Index Information

Some queries to help find out infomration about specific indexes.

This will show you the indexes for a specified table

select	i.index_name
,	i.tablespace_name
,	ceil(s.bytes / 1048576) "Size MB"
from 	dba_indexes i
,	dba_segments s
where 	i.index_name = s.segment_name
and 	table_name like '&table'
order 	by 2, 1

This will show you the columns of a specific index

select 	column_name
from 	dba_ind_columns
where 	index_name = '&index'
order 	by column_position 

This will give you some extra details about the index

select index_name, num_rows, last_analyzed from dba_ind_statistics where table_name = 'Table Name';

Recreate Oracle Controlfile

Use the following example if you need to create a controlfile script. The script can be used to recreate a controlfile.

 

alter database backup controlfile to trace;

Once you run this head over to your trace files directory and locate the script. Find the following line in the control file script:

CREATE CONTROLFILE REUSE DATABASE "SID" RESETLOGS ARCHIVELOG

From here you can edit the path of the datafiles and redologfiles if necessary. Startup the database in nomount mode and run the script you created as sysdba.

Check on the status of the instance.

 

select status from v$instance;

Query to see the control files.

select name from v$controlfile;

It is a good idea to generate this trace file from time to time. If/when you lose a control file you can use this to rebuild it from scratch.

EXPDP using QUERY option

Took me a while to get this command escaped correctly. The query has a varchar column so you need to make sure and escape the command correctly. Also make sure you set your environment variables using ORAENV before you execute this. I left the credentials off this command so you will be prompted for credentials if you run this as is.

expdp directory=data_pump_dir dumpfile=SY00500T.dmp logfile=SY00500T.log 
tables=schema.tablename query=schema.tablename:"WHERE ENTITY_NAME='AR00200T' 
AND SEQ > 150000000"

Oracle DB Link to SQL Server using Windows

Found several dozen posts on this but it still took me a while to figure out. Wanted to share my quest here. The following instructions are for Windows. I will try to figure out a Linux install next.

1. Create an ODBC connection (using 32bit ODBC) to your SQL Server instance

2. Create a SQL Server account on the SQL Server database you want to connect to.

3. Add an entry to your TNSNAMES.ORA file for the new SID

dg4odbc  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST = localhost)(PORT=1521))
    (CONNECT_DATA=(SID=dg4odbc))
    (HS=OK)
  ) 

4. Modify your listener.ORA file. Add the Following SID DESC to your SID LIST

(SID_DESC=
         (SID_NAME=dg4odbc)
         (ORACLE_HOME=C:oracleappproduct11.2.0dbhome_1)
         (PROGRAM=dg4odbc)

      )

5. Create a initSID.ora file in ORACLE_HOME/hs/admin and set the following parameters:

HS_FDS_CONNECT_INFO = <SQL SERVER DSN NAME>
HS_FDS_TRACE_LEVEL = 0
HS_OPEN_CURSORS = 300

You can use HD_FDS_TRACE_LEVEL = ODBC if while you are troubleshooting. 

6. Create your database link

CREATE PUBLIC DATABASE LINK "Link NAME" CONNECT TO "SQL USER" IDENTIFIED BY VALUES 'SQL USER PASSWORD' USING 'DG4ODBC'

7. Execute remote sql statement

 select * from <sql server table name>@<db link name from step 6>;

Crack open a Celebratory Mt. Dew and bask in the glory of this small victory.

Setting optimal UNDO retention

Run this query to find the optimal

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';

Then run this to set the optimal

ALTER SYSTEM SET UNDO_RETENTION=5;

Disable Oracle Expire passwords

In order to prevent passwords from expiring on your DB users you can create a separate profile (or modify the exisisting DEFAULT profile) to disable the expire time. The DEFAULT profile was set to 180 in my environment. The accounts in question were not user accounts but were application accounts. Make sure you check with your security folks before throwing this down.

Create the New Profile.

CREATE PROFILE my_profile LIMIT
       PASSWORD_LIFE_TIME UNLIMITED;

Change your user to the new profile

ALTER USER scott PROFILE my_profile;

You can set other options i your new profile if you want. The cool thing is that the options you do not set will automatically revert to the DEFAULT profile. Thus its a good idea to leave your DEFAULT PROFILE alone and create custom profiles.

Some other options you can change.

• FAILED_LOGIN_ATTEMPTS - failed login attempts before the account is locked


• PASSWORD_LIFE_TIME - limits the number of days the same password can be used for authentication


• PASSWORD_REUSE_TIME - number of days before a password can be reused


• PASSWORD_REUSE_MAX - number of password changes required before the current password can be reused


• PASSWORD_LOCK_TIME - number of days an account will be locked after maximum failed login attempts


• PASSWORD_GRACE_TIME - number of days after the grace period begins during which a warning is issued and login is allowed


• PASSWORD_VERIFY_FUNCTION - password complexity verification script

Current Running Queries Oracle

Several different ways to get this data. The first one is a query to get the current running quries.

select sesion.sid,
      sess.username,
      optimizer_mode,
      hash_value,
      address,
      cpu_time,
      elapsed_time,
      sql_text
 from v$sqlarea sqlarea, v$session sess
where sess.sql_hash_value = sqlarea.hash_value
  and sess.sql_address    = sqlarea.address
  and sess.username is not null

The next one will display the SQL of the PIDs using the Most CPU. You get the PID number by running the top command

select proc.spid, sess.username, s.sql_text
  from v$process proc, v$session sess, v$sqlarea s
 where proc.addr = sess.paddr
   and sess.sql_hash_value = s.hash_value
   and proc.spid in (22725,553)

Oracle Slow Session Check

The following queries will allow you to find the sessions currently logged into the database that have accumulated the most time on CPU or for certain wait events. Use them to identify potential sessions to trace using 10046. These queries are filtering the sessions based on logon times less than 4 hours and the last call occurring within 30 minutes. This is to find more currently relevant sessions instead of long running ones that accumulate a lot of time but aren’t having a performance problem. You may need to adjust these values to suit your environment.

Find Sessions with the Highest CPU Consumption

-- sessions with highest CPU consumption
SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;

Find Sessions with Highest Waits for a Certain Type

-- sessions with the highest time for a certain wait
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = '&event_name' 
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;

10g or higher: Find Sessions with the Highest DB Time

-- sessions with highest DB Time usage
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time (sec)"
, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"
FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p
WHERE sn.name = 'DB time' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND  sncpu.name = 'CPU used by this session' -- CPU
AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND st.value > 0;

Tablespace out of space

So your tablespaces will eventually run out of space.

Query to show space usage on tablespaces

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;

Add some data files:

alter tablespace tablespaceName
add datafile 'file location'
size 2G
autoextend on
next 100M
maxsize 4G;

Tracking Down Oracle Deadlocks

The Dreaded ORA-00060 error. DEADLOCK DETECTED Fortunately this is not an Oracle problem, but is an application problem. Now the fun part of tracking it down.

1. Locate the Trace file so you can gather some details.

2. Find the resource names in the Trace file…

Resource Name        
TM-00014ef4-00000000 
TM-00014ef4-00000000 

Hopefully this will give you some relavant object information. This will allow you to query the all_objects table looking for the objects.

3. Query the all_objects table

select object_name, object_type from all_objects
where object_id in ( to_number( '14ef4', 'xxxxx' ), to_number( '14ef4', 'xxxxx' ) );

From here you get to do some more digging. The trace file will contain a lot of information, mostly cryptic but hopefully it can help point you in the right direction.

Un-expire Oracle User account

There is no real un-expire option for an Oracle user account. Say you have an account that goes into expire status…you have to alter the user using the identified by clause to set the password. Problem is if you do not know the original password you have to change it…this could cause big problems.

However here is a cool workaround to remedy the problem:

First find the user int he sys.user$ table using this query:

select password, spare4 from sys.user$ where name='USERNAME';

You should see some results similar to below:

PASSWORD-1FDF39535EDB8EDD
SPARE4-S:5BEFDD3B650E6910F42EAB51EBDFF612205E92620B56166AF480551F41B6

Now take the results and run the ALTER statement on the user: 

alter user USERNAME identified by values 
'S:5BEFDD3B650E6910F42EAB51EBDFF612205E92620B56166AF480551F41B6;1FDF39535EDB8EDD'; 

You concatenate the password field and the spare4 field with a semicolon in between.