Oracle : Key Tables

This lists some of the tables that are of good use when looking at data at the session level.
You may have to login as the system user to access some of the tables

1. v$session_longops: Has data regarding operations that are running on the database.
2. v$session: Has data about the session.
3. dba_jobs: Has information about the jobs in the database.
4. dict: Has information about the system tables.
5. dba_source : Has the source for all objects. Useful for finding out which line of your procedure/function is causing errors. The line # in the error message is not the same as in your source code.
6. dba_tablespaces : Has information about the tablespaces in the Database.
7. DBA_TAB_PRIVS: Has information about the grants given to all the objects in the database. Can use this to find out grants given to a user for any object in the database.

 

Running javadoc

A quick handy reference to run javadocs from the command prompt:

% javadoc -d DOC_OUTPUT_DIR -sourcepath SOURCE_PATH -subpackages LIST_OF_PACKAGES separated by space

further ref: http://java.sun.com/j2se/1.4.2/docs/tooldocs/windows/javadoc.html

 

Vi: shortcut to replace word

Go to the relevant word. To replace it type cw . Then enter the word. To replace the line from the cursor onwards type c$ and type the replacement.

To do the same change on multiple lines: Go to the position on the line where the change needs to be done. Then type . This will do the same replacement on the other line too

 

Catchback: Oracle Reg Expressions

This is useful in cases where there is a need to replace only a part of a text and the before and after parts have to be retained.

Eg: http://mywebsite/page/1.htm has to be changed to http://yoururl/page/1.htm

select regexp_replace(URL, 'http://.*mywebsite(.*)','http://yoururl\1')
from table


Using the (.*) to put the /page/1.htm is called catchback

 

Getting sql plans

One of our sql procedure executions took a lot of time. But it was not being monitored when it was executing. So we need a way to figure out the execution plan for the query so we can pinpoint the problem. That is where the oracle history tables helped us

select * from dba_hist_sqltext
where sql_text like '%TABLE_NAME%'

We knew the table name which was being accessed and so got the sql_id for the execution.

Next step was to get the execution plan:


select * from DBA_HIST_SQL_PLAN
where sql_id = 'ABCDEF';


This showed us that the plan was using NESTED loops for execution.

The respective tables for run time monitoring are gv$sqltext and gv$sql_plan

 

Adding partitions to table

Recently, we had performance issues in our SQL code and one of the ways we thought was to increase the number of partitions.

The way to do it is to give one alter table query for each partition. And also the best practice is to have the number of partitions as a power of 2. So we increased it from 8 to 16

ALTER TABLE table_name ADD PARTITION

This will add the partition and also recalculate the rows that need to be moved to the new partition.

Note: If there are Local indexes in the partitions, they are invalidated due to the new partitions added and they need to be made usable

ALTER TABLE list_customers MODIFY PARTITION asia
REBUILD UNUSABLE LOCAL INDEXES;

 

Finding rows in table partition

There are cases when we would need to know how many rows are present in each partition. This would help us in figuring out if the partition strategy is working fine or not


SELECT object_name, subobject_name, DBMS_MView.PMarker(dta.rowid), count(1)
from TABLE_NAME dta, all_objects ao
where ao.object_id = DBMS_MView.PMarker(dta.rowid)
group by object_name, subobject_name, DBMS_MView.PMarker(dta.rowid)
order by 1,2 ;


 

Scheduler job monitoring

Use the following query to take a look at oracle jobs that have run in a scheduler

SELECT *
FROM dba_scheduler_job_log
WHERE TRUNC(log_date) = TRUNC(SYSDATE)
AND job_name = ''


The following query helps to monitor currently running job status:


SELECT *
FROM dba_scheduler_jobs
WHERE job_name = '';


 

Oracle: locked objects

The following query is helpful in figuring out if any objects have been locked during script execution:


select o.object_name, o.subobject_name, o.object_type, l.*
from gv$locked_object l, dba_objects o
where l.object_id = o.object_id;


Another query that can be used to figure out if there is a “deadlock” kind scenario in the database, i.e two or more processes vying for the same resources..


select 'SID ' || l1.sid ||' is blocking ' || l2.sid blocking
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2


 

Oracle: Query Rewrite settings

Query rewrite is a very useful concept provided by Oracle so that queries on a table can be redirected to a Materialized view. I am not too familiar with this, so that will be the topic of another post.

This is a quick post about the settings on the database to enable query rewrite.

The following query helps in knowing the settings:

show parameter rewrite


In the results, the query_rewrite_enabled property must be true.

If it is false, query rewrite will not happen

To set it to true, use the following:

alter system set query_rewrite_enabled = TRUE ;


 

Table sizes in oracle

To find out how much physical space a table occupies in Oracle Database, use the following query :

SELECT
owner, table_name, segment_type,TRUNC(sum(bytes)/1024/1024) MEG
FROM
(SELECT segment_name table_name, owner, bytes, segment_type
FROM dba_segments
)
WHERE owner in UPPER(SCHEMA_NAME)
GROUP BY table_name,segment_type, owner
HAVING SUM(bytes)/1024/1024 >= 10
ORDER BY SUM(bytes) desc;


This will return all the tables in the schema that occupy more than 10 MB of space.