Oracle Explain Plan: Use 'explain plan' to mesure cost of query

Execute query as given below :

explain plan for select * from wireless_nport_message  where requestnumber = '6017809144100804' and correlationid = '5846000245497600000000000000000000000000000000000000000000000821' AND DIRECTION='Out' AND MESSAGETYPE in ('MultiPortRequest','MultiPortResponse','ModifyPortRequest','StoreStatus') ORDER BY MESSAGEKEY;

Execute below query to display plan:

SELECT * FROM   TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT:

Plan hash value: 2718731310

 

-------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                        |     1 |   280 |     3  (34)| 00:00:01 |

|   1 |  SORT ORDER BY               |                        |     1 |   280 |     3  (34)| 00:00:01 |

|*  2 |   TABLE ACCESS BY INDEX ROWID| WIRELESS_NPORT_MESSAGE |     1 |   280 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | WNM_REQNO_CORRID_IDX   |     1 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("DIRECTION"='Out' AND ("MESSAGETYPE"='ModifyPortRequest' OR 

              "MESSAGETYPE"='MultiPortRequest' OR "MESSAGETYPE"='MultiPortResponse' OR 

              "MESSAGETYPE"='StoreStatus'))

   3 - access("REQUESTNUMBER"='6017809144100804' AND 

              "CORRELATIONID"='5846000245497600000000000000000000000000000000000000000000000821')

Query to get timezonoe of oracle Client(like Tode) and Server (Oracle)

select systimestamp,sessiontimezone,dbtimezone from dual;

Data Base SystemTime in UTC TimeZone (Oracle)

We have developed a DB function to change date from one time zone to another time zone (With the help of Google J). Here is the function.

CREATE OR REPLACE FUNCTION CONVERT_TIME ( datetime IN TIMESTAMP, tz1 IN VARCHAR2, tz2 IN VARCHAR2 )

return TIMESTAMP WITH TIME ZONE

AS

retval TIMESTAMP WITH TIME ZONE;

BEGIN

retval := from_tz(datetime, tz1) at time zone tz2;

return retval;

END;

If we want to convert Date in the DB time zone to some other time zone then use following query.

SELECT CONVERT_TIME(SYSTIMESTAMP, SESSIONTIMEZONE ,'GMT') FROM DUAL;

If we want to convert date in known time zone to some other time zone then pass appropriate value in tz1 parameter of the function.

We will deploy this db function in ICP 2.1 release. Anyone can use this function for their development. Please let me know if you need more information.

Examples:

--------- 

INSERT INTO JOSSO_ROLE

(name, datetime) 

values ('verma', CONVERT_TIME(SYSTIMESTAMP, SESSIONTIMEZONE ,'GMT'));

Oracle table Space Issue solution

 Oracle ORA-01658 Unable to Create INITIAL Extent for Segment in Tablespace Error

When inserting records into Oracle database by SQL statements, creating new tables, importing backup dump into Oracle database or when manipulating tables or data in the Oracle database, the following error may occurs:

ORA-01658: unable to create INITIAL extent for segment in tablespace tablespace_name

The problem is caused by the Oracle unable or fails to find sufficient contiguous space to allocate INITIAL extent for segment being created, due to the data files for a tablespace specified in tablespace_name has become full, or there is not enough space in the datafile.

You can check if the datafiles available and used by Oracle’s tablespaces is autoextensible, and if there is any free space in the datafile by using the following query.

select a.file_id,b.file_name,b.autoextensible,b.bytes/1024/1024,sum(a.bytes)/1024/1024
from dba_extents a , dba_data_files b
where a.file_id=b.file_id
group by a.file_id,b.file_name,autoextensible,b.bytes/1024/1024

The solutions or workarounds for the ORA-01658 Oracle error include:

Add a new datafile into the tablespace to increase additional space by using SQL query as shown:

ALTER TABLESPACE ADD DATAFILE

Retry the transaction or process with a smaller value for INITIAL.

Set AUTOEXTEND on for the data file of the tablespace.

Increase the size of the existing datafile by using the following SQL command:

ALTER DATABASE DATAFILE RESIZE newsize;

 

JMS Queue Creation (Oracke Script)

BEGIN

            dbms_aqadm.create_queue_table(         queue_table=>'nverma.ICP_SEND_Q',

                                                            queue_payload_type=> 'SYS.AQ$_JMS_TEXT_MESSAGE',

                                                            sort_list=> 'ENQ_TIME',

                                                            comment=> 'This statement creates the queue table.',

                                                            multiple_consumers=> FALSE,

                                                            message_grouping=>DBMS_AQADM.NONE,

                                                            compatible=>'8.1',

                                                            storage_clause=> 'TABLESPACE USERS'

                                                );

END;

/

BEGIN

            dbms_aqadm.create_queue(queue_name=>'ICP_SEND_Q',

                                                queue_table=> 'nverma.ICP_SEND_Q',

                                                queue_type=> DBMS_AQADM.NORMAL_QUEUE,

                                                max_retries=> '3',

                                                retry_delay=> '30',

                                                retention_time=> 30*24*60*60,

                                                comment=> 'This statement creates a queue.'

                                    );

END;

/

BEGIN

            dbms_aqadm.start_queue (queue_name => 'ICP_SEND_Q');

END;

/