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.

 

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.

 

Maintain timestamp zone in Database

We have a requirement where we get timestamp data for any time zone. But when we store it in the database it must be in GMT.

So the following change has been made :

For all the columns that need to be maintained in GMT format
we create it as the following:

CREATE table temp
(
TEST TIMESTAMP WITH LOCAL TIME ZONE
);


Also the database has the DBTIMEZONE as 00:00. This means that the Database Time zone is GMT and any time stamp data with the timezone information will be stored in GMT timezone.

Update:

Some more observations as I working on this today as well:

1. After you insert the data, if you use SQL Plus to read back the data, it will display it in your local timezone. Because that is how the data will be made available to it. However if you login directly to the
database machine, you will see the data in the timezone you wanted.

2. This is more weird than (1). If you change the timezone on your machine to the required timezone, and then insert the data, exit and start a new session, and then view the data, it retains the timezone!! . It does not convert it to the timezone on your machine.

3. Writing a procedure does not help as it takes the timezone of the client machine when inserting data.

Will keep updating this post as and when I find something

 

Editing an Oracle Job

After you create an oracle job, you may need to edit it.

Oracle provides a procedures for the same.

Eg: to change the next time a job is to be run,

dbms_job.next_date(
job IN BINARY_INTEGER,
next_date IN DATE);

Log in as the user who owns the job and execute a commit after the operation.

ref: http://www.psoug.org/reference/dbms_job.html

 

Tablespace info using Command Prompt

In the absence of any UI based Oracle Database access tools, one has to access the same using SQL PLUS.

The following tables are helpful in getting information about tablespaces

Referred from http://www.cs.uvm.edu/oracle9doc/server.901/a90117/tspaces.htm#570

I have used the following tables:

V$TABLESPACE : Name and number of all tablespaces from the control file.

V$DATAFILE : Information about all datafiles, including tablespace number of owning tablespace.

DBA_DATA_FILES : Shows files (datafiles) belonging to tablespaces.