SQL & PL/SQL :: How To Get Full Query Based On SQL_ID
Jun 27, 2013
I want to get the SQL queries based on SQL_ID.I have tried the following query,but I am not getting full query.
SET linesize 132 pagesize 999
column sql_fulltext format a60 word_wrap
break on sql_text skip 1
SELECT REPLACE (TRANSLATE (sql_text, '0123456789', '999999999'), '9', ''),sql_id
FROM dba_hist_sqltext s
WHERE s.sql_id = '7tvurftg8zryb';
One of my friend said use grid to get full query text. how to use grid ,else any other method to get the full query based on SQL_ID.
I�m Using Oracle 11.I have a table with 16 million rows and an index (let's call it the employee table with an index on department). I need to select all the employees whose departments are located in the uk. I achieve this by selecting all the department numbers from departments where location = 'UK' in a sub select then plug this into the main query as follows:
SELECT * FROM employees WHERE department IN (SELECT department from departments where location = 'UK');
It takes ages, 25 seconds or more, the explain plan shows its doing a full table scan on emplyees. I need it to use the index. The sub query is instant and returns only 5 rows. If I explicitly put the 5 numbers in the IN clause the query uses the index and executes in 0.04 seconds. See below:
SELECT * FROM employees WHERE department IN (1,2,3,4,5);
I need it to use the subquery once and then use the index on the main table.
Below query is degrading the performance of database. As we know that, without where clause, query do full table scan.Now, it is written to generate the sequence no.
SQL> explain plan for 2 SELECT NVL(MAX(P.NUM_SERIAL_NO), 0) + 1 FROM CNFGTR_IRDA_ENVELOPE_DTLS P 3 / Explained. SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Plan hash value: 3345343365 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------
I am getting below ORA-01555 error in alert log everyday.
ORA-01555 caused by SQL statement below (SCN: 0x09ad.86a4562a): Sat Jan 21 08:39:45 2012 SELECT (NVL(MAX(BLOCK_ID + BLOCKS ),0) * :b1 ) / 1024 FROM DBA_EXTENTS WHERE TABLESPACE_NAME = :b2 AND FILE_ID = :b3
I cannot able to find the sql_id here. So how can I find from which process or session this query is firing? before increasing the undo size, I need to analyze as why it is occuring?
When I run a query form the the Query Window in Visuial Studios 2012 all the date fields truncated to 'mm/dd/yyyy', but i need the full date returned. I am able to get full date from TO_char(MyDateField, 'yyyy-mm-dd hh24:mi:ss'), but if I do TO_DATE(MyDateField, 'yyyy-mm-dd hh24:mi:ss') it only returns 'mm/dd/yyyy'. I'm sure this is a simple setting in Visual studios but I cant find it to save my life. Is there there a way to have the full date returned by default?
In the below Table Structure, TB_Vehicles is the Master table and TB_Cars, TB_Bikes, TB_Cars are Satellite tables which hold more info about respective vehicles.
I want users to search using the Name. So, when users enter Name as 'Access', my query should give all information about that vehicle, including that from Satellite Tables (using both TB_Vehicles and TB_Scooters).
SELECT * FROM TB_Vehicles WHERE V_NAME = 'Access';
Similarly, if user enters 'Linea', it should give info from TB_Vehicles and TB_Cars tables.
So, with V_NAME as input, I'll find V_TYPE from TB_Vehicles table, and using that, I need to identify which satellite table, I need to join to retrieve more info.
One of our query is not using function based index, the required priv is granted to the user executing the query and also tables stats are gathered? what could be the reason for the query to not to pick the FBIndx? the table is a huge one having million of records, is it that CBO thinks that not picking FB indx is the best execution plan? let me know how can we make the query use the FB indx, also there is a restriction that we cannot force it using hints.
The above 8 are the possible ways to key in the data using a form.The logic is like based on a particular condition the message should display in the report.If they specify a particular USER and RESP_NAME and FUNC_NAME then it should display AAAAAAAAAAA (This should take priority first). The priority is in order the default should be the last one.
I want to get top two rows based on ACCT_UNIT & order by status_date, if there is only one row on acct_unit, get one row. IF more than two rows available, want to get the top two rows based on status_date.
I am using Apex 4.1 and have a requirement to create an interactive report based on a dynamic query. This option is available in Classic report but in Interactive this feature is not there. Tried using collections or just a view (thought of changing the view definition during "On load", but structure of the report is already defined based on the definition of the view at design time).
a tabular form with two "select list (query based LOV)" fields. The first one must affect elements in the second one. And so the second query based LOV field should has a "where attr1 = first_list_selected_value" in the query.
Id Country city 1 US 2 US Boston 3 Boston 4 US Newyork 5 London 6 Japan Tokyo
Im looking for a query which returns results based on both city and country passed.
If i pass country US and city Boston it should return row2 with US and Boston row If i pass country null and city Boston it should return row3 If i pass country UK and city Boston it should return row3 If i pass country UK and city London it should return row5
i.e. If country/city combination exists in DB return that row Else city row should be returned.
While making a full db export i have got this error even though my export was completed with this warning. What should i need to do regarding to this error. My oracle version is 10.1.0.2 and Server is windows 2003.
I am trying to use Full Outer Join, here is the DB version and test case
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options create table tab1 (The_Date DATE, x_ID varchar2(4), x_CODE varchar2(10), r_qty number(8)) create table tab2 (The_Date DATE, x_ID varchar2(4), x_CODE varchar2(10), o_qty number(8))
tab1 can have multiple records for key fields The_Date + x_ID + x_CODE insert into tab1 values (to_date(sysdate)-2, '001', 'ABC', 100) insert into tab1
[code]...
tab2 should have unique record for key fields The_Date + x_ID + x_CODE
insert into tab2 values (to_date(sysdate)-2, '001', 'ABC', 1000) insert into tab2 values (to_date(sysdate)-2, '001', 'XYZ', 1500)
select (SELECT MIC.MICR_BANK_NAME FROM M_ECS_MICR_MST MIC WHERE MIC.PK_MICR_ID = BANK.ECS_MICR_CODE) AS BANKNAME, , (SELECT PAY.PAYMENTCODE FROM M_ECS_PAYMENT_MODE PAY WHERE TO_CHAR(PAY.ID) = BANK.ECS_FACTORING_HOUSE) AS [code].....
i have used composite index below column which used in the tbl_bank_statement table.like column name tbl_bank_ statement (policy_ no,ecs_ micr_code,ecs_factoring_house,ecs_mandate_status)
I have a couple of questions to ask. see below that I have provided a script to re-create my problem.
Question 1.
I have the following query that will not run when I include columns (see 1.2) in the select but will run when I use the asterix symbol (see 1.1)
*1.1 Runs with no problems* select * from ora full outer join txt on ora.ora_id_y = txt.txt_id_y;*1.2 Returns error.* select txt.txt_id_y , txt.txt_n
[code]...
Is it possible to use one query with self joins and using multiple full outer joins to accomplish what I have below which is 6 view to get to the query that I have in Question 1.
CREATE SCRIPT drop table master; create table master ( id varchar2 (10 char) , txt varchar2 (1 char)
We are having 5 schemas in one database. Now we need to move Oracle server from one machine to another. I can take schema level back up using Export and Import in new server. I am not a DBA as we don't have a DBA I need to do this myself.
I want to know Is there an option to take full database back up including tablespaces and all the schemas in one shot. I read that RMAN is one option. But any option using EXP/IMP for same?
My plan is
-Take list of Tablespaces and create them in new server -Take Exports of schemas from original server -Create schemas and import the data in new server.
I am trying to update full table with row by row by using pl/sql blocks but I am getting "INVALID ROW ID" error. FYI. following is the screen shot from sql*plus.
SQL> declare 2 cursor csr is 3 select upc_code from pos.tbk_pos_fact_newslink_bk FOR UPDATE OF upc_code nowait; 4 5 begin 6 For row in csr [code]....
