Estimate Tablespace growth

Some time it is very helpful to plan disk space/ tablespace management if you estimate the growth of your tablespace. Here is a select query which can be helpful :

SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;


Another One: This query gives average increase per day 

SELECT b.tsname tablespace_name
, MAX(b.used_size_mb) cur_used_size_mb
, round(AVG(inc_used_size_mb),2)avg_increas_mb
FROM (
  SELECT a.days, a.tsname, used_size_mb
  , used_size_mb - LAG (used_size_mb,1)  OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
  FROM (
      SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days
       ,ts.tsname
       ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
      FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts
       ,DBA_HIST_SNAPSHOT sp, DBA_TABLESPACES dt
      WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
       AND ts.tsname = dt.tablespace_name  AND sp.begin_interval_time > sysdate-7
      GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
      ORDER BY ts.tsname, days
  ) A
) b GROUP BY b.tsname ORDER BY b.tsname;

Add Unique key in a table that contains duplicate row

Requirement : A table contains some duplicate data. Now we want to add a unique constraint that skip existing duplicate values but check newly inserted duplicate values.

SQL> create table t2 (id number(10), t varchar2(20));

Table created.


SQL> insert into t2 values (1,'A');

1 row created.

SQL> insert into t2 values (1,'A');

1 row created.

SQL> insert into t2 values (1,'A');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t2 add constraint uk_t2 unique(id) ENABLE NOVALIDATE;
alter table t2 add constraint uk_t2 unique(id) ENABLE NOVALIDATE
*
ERROR at line 1:
ORA-02299: cannot validate (HASAN.UK_T2) - duplicate keys found


SQL> select * from t2;

ID T
---------- ------------------------------------------------------------
1 A
1 A
1 A

So normal method does not work !



Case 1: New Table That means initially the table does not have any data

SQL> create table t3 (id number(10), t varchar2(20));

Table created.

SQL> alter table t3 add constraint gpu unique (id) deferrable initially deferred;

Table altered.

SQL> alter table t3 disable constraint gpu;

Table altered.


SQL> insert into t3 values(1,'A');

1 row created.

SQL> insert into t3 values(1,'A');

1 row created.

SQL> insert into t3 values(1,'A');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL>
SQL> select * from t3;

ID T
---------- ------------------------------------------------------------
1 A
1 A
1 A



SQL> alter table t3 enable novalidate constraint gpu;

Table altered.

SQL> insert into t3 values(2,'A');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t3 values(2,'A');

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (HASAN.GPU) violated

SQL> alter table t3 modify constraint gpu INITIALLY IMMEDIATE;

Table altered.

SQL> insert into t3 values(1,'A');
insert into t3 values(1,'A')
*
ERROR at line 1:
ORA-00001: unique constraint (HASAN.GPU) violated



Case 2: Existing Table that contains duplicate data


SQL> create table t2 (id number(1),a varchar2(10));

Table created.


SQL> insert into t2 values(1,'A');

1 row created.

SQL> insert into t2 values(1,'A');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t2 add constraint uk_t2 unique(id) DEFERRABLE INITIALLY DEFERRED disable;

Table altered.

SQL> alter table t2 enable novalidate constraint uk_t2;

Table altered.

SQL> insert into t2 values(1,'A');

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (HASAN.UK_T2) violated


SQL> alter table t2 modify constraint uk_t2 INITIALLY IMMEDIATE;

Table altered.

SQL> insert into t2 values(1,'A');
insert into t2 values(1,'A')
*
ERROR at line 1:
ORA-00001: unique constraint (HASAN.UK_T2) violated


OR


SQL> alter table t2 add constraint uk_t2 unique(id) disable;

Table altered.


SQL> alter table t2 enable novalidate constraint uk_t2;

Table altered.

SQL> insert into t2 values(1,'A');
insert into t2 values(1,'A')
*
ERROR at line 1:
ORA-00001: unique constraint (HASAN.UK_T2) violated

Who use Temporary Tablespace ?

Temporary Tablespace is used for sorting purpose it also temporarily data like temporary tables.

Here is a query that give you, which sql statement use who much on Temporary Tablespace (I considered TEMP is a 8k block size tablespace)

select su.username
, dbms_lob.substr(sq.sql_fulltext, 4000, 1) sql_text
, su.blocks
, su.extents
, su.segtype
, (su.blocks*8)/1024 size_MB
, sq.last_active_time
, sq.last_load_time
from v$sort_usage su
join v$sqlarea sq on (su.sql_id = sq.sql_id)
left join v$session s on (s.sql_id=su.sql_id)

where su.tablespace='TEMP';

ORA-10631

Message: ORA-10631: SHRINK clause should not be specified for this object

Cause: shrink clause use inappropriately.

Solution: Write the syntax correctly. If a table has function based index or Text index, it can not be shrinked

Moving Oracle Text Index

Moving an index from one tablespace to another tablespace is very easy task. It can be accomplished by using rebuild option:

ALTER INDEX index_name REBUILD TABLESPACE NEW_TABLESPACE;

You can even do it online for most indexes:

ALTER INDEX index_name REBUILD TABLESPACE NEW_TABLESPACE ONLINE;

But trying to move a domain index (such as Oracle Text Index) is not so simple. If you follow thw above way it will cause error:

ALTER INDEX my_text_index REBUILD TABLESPACE NEW_TABLESPACE;
ORA-29871: invalid alter option for a domain index

You may ask What is the reson behind that error and how to resolve it? In fact Domain index is a set of other objects. Oracle Text CONTEXT index is set of tables:

* DR$[index_name]$I
* DR$[index_name]$K
* DR$[index_name]$N
* DR$[index_name]$R

Unfortunately to move context index you have to drop and recreate that text index. But first you need to specify storage parameters:

begin
ctx_ddl.create_preference('TEXT_INDEX_STORE', 'BASIC_STORAGE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'I_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'K_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'R_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'N_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'I_INDEX_CLAUSE',
'tablespace NEW_TABLESPACE COMPRESS 2');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'P_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
end;
/

and then just drop the previously created index and recreate that index with changed parameters

create index MY_TEXT_I on MY_TAB(text_column)
indextype is ctxsys.context parameters('storage TEXT_INDEX_STORE');

TEXT Index Size

Oracle Text Index, an interMedia Index, is powerful for searching user specified text within a column or columns. It is widely used in web applications such as search engines, content management systems. As a DBA, accurately identify sizes of text indexes in the database and monitor their growth is important. But the problem is that text index is a domain index, and every text index internally consists of several tables with names prefixed with 'DR$'. Some of these tables have their own indexes and some of them are Index Organized Tables (IOTs). If you search in dba_segments, there is no segment for text indexes. SO when you try to calculate the size of an text index, you should consider all of these DR$ tables and their indexes. The script provided here gives a fast report of the sizes of all text indexes in the schema:

COL table_name format A30;
COL ind_nm format A30;
COL KB 9999999999;

select table_name, x.index_name, sum(KB) KB from
(select substr(table_name, 4, instr(table_name, '$', -1)-4) index_name,
sum(bytes)/1024 KB
from user_tables t, user_segments s
where t.table_name = s.segment_name and t.table_name like 'DR$%$%'
group by substr(table_name, 4, instr(table_name, '$', -1)-4)
union
select substr(table_name, 4, instr(table_name, '$', -1)-4) index_name,
sum(bytes)/1024 KB from user_indexes i, user_segments s
where i.index_name = s.segment_name and i.table_name like 'DR$%$%'
group by substr(table_name, 4, instr(table_name, '$', -1)-4)
) x, user_indexes ind
where x.index_name = ind.index_name
group by table_Name, x.index_name
order by table_name, x.index_name;

Related Topics:

1. Introduction of Oracle Text
2. Oracle Text Installation
   

Features of Oracle Flashback

1. Flashback query

Introduced with Oracle9i, Flashback Query provides the ability to view the data as it existed in the past. By default, operations on the database use the most recent committed data available. If you want to query the database as it was at some time in the past, you can do so with the Flashback Query feature.

SQL> select dbms_flashback.get_system_change_number from dual;

SQL>select id from hasan.test where id in (100,101);
SQL>delete from hasan.test where id in (100,101);
SQL>commit;

SQL>select id from hasan.test as of timestamp systimestamp - interval '20' minute where id not in (select id from hasan.test);

SQL>insert into hasan.test
select * from hasan.test as of timestamp systimestamp - interval '10' minute
where id not in (select id from hasan.test);

SQL>commit;


2. Flashback Table

When a human or application error occurs, you want to be able to restore the state of one or more tables to a point in time before the problem occurred. Flashback Table provides the DBA the ability to recover a table or a set of tables to a specified point in time quickly, easily, and online. Flashback Table restores the tables while automatically maintaining its associated attributes such as - the current indexes, triggers and constraints

SQL> select dbms_flashback.get_system_change_number from dual;

SQL>select count(*) from hasan.test;
SQL>delete from hasan.test;
SQL>commit;


SQL>alter table hasan.test enable row movement;
SQL>Flashback table hasan.test to SCN ;
SQL>Flashback table hasan.test to timestamp systimestamp - interval '5' minute ;

SQL>select count(*) from hasan.test;


3. Flashback Version Query

Flashback Versions Query provides a way to audit the rows of a table and retrieve information about the transactions that changed the rows. It retrieves all committed versions of the rows that exist or ever existed between the time the query was issued and a point in time in the past. It accomplishes this by utilizing Automatic Undo Management.

SQL> select dbms_flashback.get_system_change_number from dual;

SQL> select password from hasan.test where id in (100,101);

SQL> update hasan.test set password='Bang'where id in (101);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;

SQL> update hasan.test set password='ladesh'where id in (100);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;

SQL> delete from hasan.test where id in (100,101);
SQL> select dbms_flashback.get_system_change_number from dual;

SQL> insert into hasan.test
select * from hasan.test as of timestamp systimestamp - interval '20' minute
where id not in (select id from hasan.test);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;

SQL> SELECT versions_startscn start_scn
, versions_endscn end_scn
, versions_xid transection_id
, DECODE(versions_operation,'I','Insert','U','Update','D','Delete',NULL) oper
, id, password
FROM hasan.test versions between scn 85174629 AND 85186130
WHERE id in (100,101) Order by start_scn;


4. Flashback Transaction Query

You may discover that somehow data in a table has been inappropriately changed. To research this change, you can use multiple flashback queries to view row data at specific points in time. More efficiently, you can use Flashback Versions Query feature to view all changes to a row over a period of time and the associated transaction id's. This feature allows you to append VERSIONS BETWEEN clause to a SELECT statement that specifies an SCN or timestamp range between which you want to view changes to row values. Once you identify an erroneous transaction, you can then use the Flashback Transaction Query feature to identify other changes that were done by the transaction, and to request the undo SQL to reverse those changes.

SQL> sqlplus / as sysdba

SQL> SELECT * FROM flashback_transaction_query x
where x.table_name='LOGIN' AND x.table_owner='HASAN'
AND x.start_timestamp >(systimestamp - interval '10' minute);


5. Flashback Drop

Dropping of objects by accident has always been a problem for users and DBAs alike. Users soon realize their mistake but then it's too late and historically there is no easy way to recover those dropped tables, indexes, constraints, triggers, etc. Flashback Drop provides a safety net when dropping objects in Oracle Database 10g. When a user drops a table, Oracle automatically places it into the Recycle Bin.

SQL> create table table1 (a number(1));
SQL> INSERT INTO table1 values(1);
SQL> INSERT INTO table1 values(2);
SQL> INSERT INTO table1 values(3);
SQL> INSERT INTO table1 values(4);
SQL> commit;

SQL> select dbms_flashback.get_system_change_number from dual;

SQL> DROP TABLE hasan.table1;

SQL> SELECT ur.base_object, ur.object_name,ur.original_name FROM user_recyclebin ur;

SQL> FASHBACK TABLE table1 TO BEFORE DROP;


6.Flashback Database

Flashback Database quickly rewinds an Oracle database to a previous time, to correct any problems caused by logical data corruptions or user errors. Flashback Database is like a 'rewind button' for your database. It provides database point in time recovery without requiring a backup of the database to first be restored. When you eliminate the time it takes to restore a database backup from tape, database point in time recovery is fast

Related Topics:

1. Flashback Part #1 Basics
2. Flashback Part #2 Setup and Maintenance
   

Uninstall Oracle 10gR2 On Solaris 5.10

To uninstall Oracle 10gR2 from Solaris 5.10, please do the followings

1. Remove all database, by running dbca.
2. Stop aall oracle running process : A. Database Control : $ORACLE_HOME/bin/emctl stop dbconsole B.Oracle Net listener : $ORACLE_HOME/bin/lsnrctl stop C. iSQL*Plus : $ORACLE_HOME/bin/isqlplusctl stop D.Ultra Search : $ORACLE_HOME/bin/searchctl stop
3. Start Oracle Universal installer locating at $ORACLE_HOME/oui/bin/runInstaller.
4. In the Welcome window, click Deinstall Products.
5. In the Inventory screen, select the Oracle home and the products that you want to remove, then click Remove.

Multiplexing Control File

Oracle consist of three major physical files, they are :

• Controlfiles
• Datafiles
• Online Redo log files
  

Among them control files are the most impotent one. Controlfile contains Database name, database creation date, Tablespace names, Physical location of datafiles and Recovery information.

With default installation, Oracle has 3 control files placed in same physical location. According to database availability, It is safe to place the 3 controlfiles in disk.


To see the current physical location of control file

SQL> select name from v$controlfile;

NAME
---------------------------------------------------------
/ua1/control01.ctl
/ua1/control02.ctl
/ua1/control03.ctl

Suppose we have two HD and those two are mount as /ua1 and /ua2. So we need to move at list one controlfile in /ua1 and we move the 3rd contronlife. there are several way to do the Control File Multiplexing:

1. Using SPFILE:

The steps to multiplex control files using an SPFILE are describe bellow:

Login as SYSDBA

1. Alter the SPFILE: Using the ALTER SYSTEM SET command, alter the SPFILE to include a list of all control files to be used.

SQL> ALTER SYSTEM SET control_files='/ua1/control01.ctl'
,'/ua1/control01.ctl', '/ua1/control01.ctl' scope=spfile;


2. Shut down the database: Shut down the database in order to create the additional/ relocate control files on the operating system.

SQL> SHUTDOWN IMMEDIATE;

3. Create additional control files: Using the operating system copy command, create/move the additional control files as required and verify that the files have been created in the appropriate directories.

mv /ua1/control01.ct /ua2

4. Start the database: When the database is started the SPFILE will be read and the Oracle server will maintain all the control files listed in the CONTROL_FILES parameter.

SQL>STARTUP;


To see the changed physical location of control file

SQL> select name from v$controlfile;

NAME
---------------------------------------------------------
/ua1/control01.ctl
/ua1/control02.ctl
/ua2/control03.ctl

Find Top N resource absorbing SQL Queries

At times database performance problems arise that require your diagnosis and correction. Sometimes problems are brought to your attention by users who complain about slow performance. Other times you might notice performance spikes in the Host CPU chart on the home page. Suppose user reported slow performance and this occurred in night or in holiday. Now You need to see which sql queries were run at that specific time along with their resources usages details.

With default settings, in every hour a snapshots taken by Automatic Workload Repository (AWR) and those snapshots are available for next 7 days. Here is a SQL query that give the desired top N resource sensitive queries :

SELECT * FROM
(SELECT X.SNAP_ID, X.SQL_ID,SQL_TEXT
,ROUND(X.ELAPSED_TIME/1000000/X.EXECUTIONS_DELTA,3) AVG_ELAPSED_TIME_SEC
,ROUND(X.CPU_TIME /1000000/X.EXECUTIONS_DELTA,3) AVG_CPU_TIME_SEC
,X.ELAPSED_TIME TOTAL_ELAPSED_TIME_MIC_SEC
,X.CPU_TIME TOTAL_CPU_TIME_MIC_SEC
,X.EXECUTIONS_DELTA EXECUTIONS
FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SNAP_ID,DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME, SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME
, SUM(DHSS.EXECUTIONS_DELTA) EXECUTIONS_DELTA
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE
BEGIN_INTERVAL_TIME >= TO_DATE('18-feb-2009 18:00', 'dd-mon-yyyy hh24:mi')
AND END_INTERVAL_TIME <= TO_DATE('18-feb-2009 18:40', 'dd-mon-yyyy hh24:mi'))
AND PARSING_SCHEMA_NAME='SCOTT'
GROUP BY DHSS.SQL_ID,DHSS.SNAP_ID) X
WHERE X.SQL_ID=DHST.SQL_ID
ORDER BY AVG_ELAPSED_TIME_SEC DESC
) WHERE rownum < =200;

DRIVING_SITE SQL Hint

We all are know about Oracle Data Base Link and I called it dblink in short throughout this article. Usisg dblink you can easily access remote Database Object. Now Please think the following situation:

Situation:

Suppose you have 2 tables: 1. table1 in local DB 2. table2 in remote DB.The table1 size is 10 MB and table2 size is 100 MB. We need to join those two tables and we access only in local DB.

If you now think about performance, it is important to where the sql query executed and the join perform. For above situation, it is better to bring table1 from local DB to Remote DB and execute the sql in remote server and finally bring back the result in local DB.

Lets do the job

The DRIVING_SITE hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization. The syntax of this hint is:

/*+DRIVING_SITE(table)*/

where table is the name or alias for the table at which site the execution should take place.

Example:

SELECT /*+DRIVING_SITE(table2)*/ * FROM tabl1, table2@remote_db
WHERE table1.DEPTNO = table2.DEPTNO;

If this query is executed without the hint, rows from table2 will be sent to the local site and the join will be executed there. With the hint, the rows from table1 will be sent to the remote site and the query will be executed there, returning the result to the local DB.

create Foreign key On two diffrent schema table

Some times you may face easy questions which can put into difficulties. Today I got that kind of a question and i am happy so that I get the answer though it take some time find it.

Here is the question which i got from a forum member :

" how to link a table from scott user to the hr user using a foreign key. For an example hr.emp to scott.dept using a foreign key "


In my answer, I assume hr.emp table has a colunm name scott_dept_id . So I create a foreign key on hr.emp (scott_dept_id) by referencing scott.dept (id).

sql> connect scott / pass;
sql> GRANT select,update, delete, references on dept to hr;

Though only references privilege required to create the foreign key

sql> connect hr/password;
sql> alter table emp add constraint fk_test foreign key (scott_dept_id) references scott.dept (id);


So simple ! but hard enough to bother you, if you not know exactly . Thanks to that forum user who lead me to learn this thing .

Reverse Key Index

I think we are familiar with Oracle Index, specially Bitmap Index. Indexes are used to help oracle retrieve data faster. But there is a drawback of Index, if an index create on a large table which under go massive insert/ update, the index may raise contention issue. To get ride from this Oracle provides couple of solutions and Reverse key Index is one of them.

Real world case scenario

Case 1: Suppose table 'track_user' contains login related information of a system. The primary key column 'ID' of the table populated by an increasing sequence. So every new entries (for the index) come to the same blocks when a row inserted into the table. This is the way contention may increase!

As all we know that the primary key constraint is impose on column by creating an unique index on that column. So if we use reverse key indexes in this case then the index entries will go to different blocks and contention will be reduced.

Case 2: If you have a table with column which is populated by an increasing sequence and some times it go under some delete operation for old records. A index was created on That column and this index is on face range scan when you issue a select on that table. But this index has contention issues on index blocks.

To avoide the contention issue you can use Reverse key index as a soluation.

What is Reverse key index ?

Reverse key index was first introduce in Oracle 8. A reverse key index reverses the bytes of each column indexed (except for the ROWID) while keeping the column order same as normal index.

Uses

1. Reverse key index can help avoid performance degradation in indexes in an Oracle Parallel server environment where modifications to the index are concentrated on a small set of leaf blocks. For example, if you insert rows with keys 101, 102 and 103 into a table with a regular index, the rows are likely to be inserted into the same leaf block. When users connected to different instances of the same database insert into the same block a pingoccurs.(When a block is written to disk by one instance so that another instance can read it, it is called a ping.) Excessive pinging will severely degrade performance, so you want to reduce it. In a Reverse Key Index the keys in our example become 101, 201 and 301, and the rows are inserted into disparate parts of the index segment. This type of index is therefore useful in a parallel server environment because it reduces pinging.
2. Reverse Key Indexes can also be useful in situations where users insert ascending values and delete lower values from a table. A regular index would become skewed, but a Reverse Key Index would not.
3. 
   

Disadvantages

With a Reverse Key Index you cannot run an index range scanning query. This is because lexically adjacent keys are not stored next to each other in a Reverse Key Index. You can only perform fetch-by-key value or full-index scans . Of course, you can avoid the index and perform full table scans or use the parallel query option.

Create And Manage Reverse Key Index

You create a Reverse Key Index with the key word REVERSE:

Create Index index_name on table_name (a,b,c) Reverse;

You can rebuild a Reverse Key Index into a regular index with the keyword NOREVERSE

Alter Index index_name Rebuild Noreverse;

If you rebuild a Reverse Key Index without the keyword NOREVERSE, it will rebuilt the Reverse Key Index.

Alter Index index_name Rebuild;

You cannot rebuild a normal index into a reverse key index. You must drop the normal index and create the reverse index.

Find Bind variable value

The usages of bind variable increase SQL query performance. Suppose you have a SQL query that execute several times and only change are in literal values, IN such case professionals are suggested to use bind variable rather than hard cote value. The impact is significant on query performance if you use bind variable.

But the problem is rice when you try to collect full query text from database, the value of the bind variable is not present in the query text. But you may need those values when you are working on that SQL query specially when you try to tune that query.

Here are two query that help you to find the valu of bind variable. The only thing you needed is the SQL ID

1. If your query was run within 30 minutes:

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING
FROM v$sql_bind_capture WHERE sql_id='';


2. If your query was run more than 30 minutes ago. For this you also need a snapshot Id. By default oracle store snapshots of last 7 days:

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING
FROM DBA_HIST_SQLBIND WHERE SQL_ID='' and SNAP_ID='';


Note: v$sql_bind_capture and DBA_HIST_SQLBIND stors only those bind values
which are given in HWERE clause and they do not store timstamp,lob,clob type value



Related Topics:

1. Bind Variable

January 2009

January 07: We can use a single character wild card search In LIKE. For an example - where a.text LIKE '%_L_NK%'. '_' is replace by any character.

January 15: In parent child situation , you can delete records from parent if you delete child records first. In this situation you can truncate child table but not parent table. To truncate parent table you must disable all foreign key constraint of child tables. Interesting :)

ORA-14086

ORA-14086: a partitioned index may not be rebuilt as a whole

Cause: User attempted to rebuild a partitioned index using ALTER INDEX REBUILD statement, which is illegal.

Action: Rebuild the index, a partition at a time or drop and recreate the entire index.


SQL> ALTER INDEX TEST_ID_IDX REBUILD ONLINE COMPUTE STATISTICS;
ORA-14086: a partitioned index may not be rebuilt as a whole

Rebuilding a partitioned index is slightly different then rebuilding a normal (non-partitioned) index. So you should do this in the following way

SQL> ALTER INDEX TEST_ID_IDX REBUILD PARTITION TEST_2005 ONLINE COMPUTE STATISTICS;

Related Topics:

1. Rebuilding a partitioned index.
   

Rebuilding a Partitioned Index

Rebuilding a partitioned index is slightly different then rebuilding a normal (non-partitioned) index.

Let see what happen when we try to rebuild a partitioned index in usual way

SQL> ALTER INDEX TEST_ID_IDX REBUILD ONLINE COMPUTE STATISTICS;
ORA-14086: a partitioned index may not be rebuilt as a whole

Cause: User attempted to rebuild a partitioned index using ALTER INDEX REBUILD statement, which is illegal.

Action: Rebuild the index, a partition at a time or drop and recreate the entire index


If you choose to rebuild that partition then run the following SQL

SQL> ALTER INDEX TEST_ID_IDX REBUILD PARTITION TEST_2005 ONLINE COMPUTE STATISTICS;

Here is a SQL for finding/ rechecking partitioned index

SELECT uip.index_name
, utp.table_name
, uip.partition_name
, ui.index_type
, ui.global_stats
, uip.last_analyzed
FROM USER_IND_PARTITIONS uip
, USER_TAB_PARTITIONS utp
, USER_INDEXES ui
WHERE uip.partition_name= utp.partition_name
AND ui.index_name=uip.index_name
AND ui.table_name = utp.table_name
AND uip.index_name= 'TEST_ID_IDX'
AND utp.table_name= 'TEST';

SET DEFINE

'SET DEFINE' is useful when you are working with sql*plus. There are three combinations of 'SET DEFINE' and those are:

1. set define on.
2. set define off.
3. set define x.
   

1. set define x: set define x specifies the prefix-character for substitution variables. The default is the ampersand (&).

set define &;

select * from dba_objects where object_name like '%&object_name%';


2. set define on: Turns on substitution variables.

set define on;

select '&hello' from dual;

If define is set to on and SQL*Plus finds the current substitution prefix, it asks for a string to be entered. In the following example, I entered: hasan

Enter value for hello: this string was entered

old 1: select '&hello' from dual
new 1: select 'this string was entered' from dual

'HASAN'
---------------
hasan

It might be annoying to see the following lines printed by SQL*Plus:

old 1: select '&hello' from dual
new 1: select 'this string was entered' from dual

This behavior can be turned off by setting verify off.


3. set define off


Turns off substitution variables.

set define off;
select '&hello' from dual;

'&HELL
----------------
&hello