Do you have a query to find all the executions and the elapsed time of a particular sql_id?I have a query, but I don't find it very practical
set pages 1000 lines 4000
col sql_profile for a32
select executions_total,
trunc(decode(executions_total, 0, 0, rows_processed_total/executions_total)) rows_avg,
trunc(decode(executions_total, 0, 0, fetches_total/executions_total)) fetches_avg,
[code]...
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?
undo_management string MANUAL
undo_retention integer 900
undo_suppress_errors boolean FALSE
undo_tablespace string
I am trying to investigate my execution plans against my DB; and was informed i can check current status of SQL scripts using:
Performance tab > SQL Monitoring > Monitored SQL Executions
I click on SQL ID i want to investigate and comes back with blank screen instead of the overview tab.
See Oracle URL below for screenshot
Figure 6-8 Monitored SQL Execution Details Page
[URL]
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.
multiple execution plns for the sql_id from AWR,i have following questions
1.which plan is opimizer tcurrently using ?
2.how to make sure optimizer to pick right plans
SQL> select * from table(dbms_xplan.display_awr('fb0p0xv370vmb')) ;
PLAN_TABLE_OUTPUT
SQL_ID fb0p0xv370vmb
[code]....
i am trying to analyze a query i have and noticed that it does not show the sql_id in v$session.
preparing a test case:
create table t1(a number, b varchar(10));
insert into t1 values(123 , 'value1');
when i execute
select count(*) from dual;
select * from dual;
select count(*) from t1;
i can see the sql_id by running
select
sql_id sql_id_,
sql_child_number sql_child_num,
module module_,
action action_,
logon_time lgtime,
[code]....
however, when i'm running
select * from t1
sql_id and sql_child_id in v$session appears to be null, and i can't analyze it.
why those columns are NULL?
I have a table with 50 columns, lets call it Table_A . There is a identical table called Table_B. The data in Table_B gets flushed every night and data from Table_A is moved to Table_B. A new set of data is inserted into Table_A. Now I need to find out which field value is changed and what is changed . A minus command can give the changed records, but how to get the what was prior value for each changed field . Here is what I am looking for
WITH TABLE_A AS ( SELECT 1 ID, 'JOHN' NAME, 'SALES' DIV FROM DUAL
UNION
SELECT 2 ID, 'MARRY' NAME, 'ACCOUNTS' DIV FROM DUAL
UNION
SELECT 3 ID, 'KIM' NAME, 'SERVICE' DIV FROM DUAL),
[code]...
what i want is something that will spit out this (only one record, because there is only one diff record found in the above query.)
ID, OLD_ID, NAME, OLD_NAME , DIV , OLD_DIV
-- ------ ---- -------- --- -------
3 3 KIM KIM SERVICE SALESThe above one is for sample purpose, The actual table has 50 fields, and except the ID field, all other field values might change.
i need more important to know about this query,below query in where clause i have one condition like 'pk1_value =New_Deliveries.DELIVERY_ID" but in my New_Deliveries table i don't have pk1_value column,how to find that column.
select MAX(DECODE(fndcatusg.format,'H', st.short_text,NULL,st.short_text, NULL))
FROM fnd_attachment_functions fndattfn,
fnd_doc_category_usages fndcatusg,
fnd_documents_vl fnddoc,
fnd_attached_documents fndattdoc,
fnd_documents_short_text st,
fnd_document_categories_tl fl
[code]....
i m create a table emp . all user of database have privileged to read and write the emp table. now How can identify that which user have insert row in emp file ?
View 4 Replies View RelatedI am facing a problem to find the last day of the month which is stored in a variable. i tried the following query which gave me an appropriate answer
select to_char(last_day(sysdate),'DD-MON-YY')from dual
ans : 30-SEP-2012
But I am having only the month in my hand through which I want to find its last day. I cant use sysdate for my query.
I have to load 50 Million records in a table. I also need to ensure that no duplicate records enter. Business demand is that It may send 2 Million records today, 1 Million records tomorrow, 3 million records on the next day and so on...
I have loaded 6 Million records into the table, but onwards speed of loading records (with duplicate check) is decreasing.
I need to find the maxmimum value in a row, which consists 4 fields.
Columns: field_1, field_2, field_3, field_4
values : 2 , 4, 5, 1
the output should be field_3 =5
I want to find out the name of objects like procedures,functions which are using a specific column of table.
for example i have a table 'Orafaq' which has column 'Iss_Ide', i want to know which objects like procedures,functions,views are using column "Iss_Ide" of table orafaq
How to find out the no.of records in a cursor for loop without using counter variable. can i use %rowcount inside for loop?
declare
cursor dept_cur
is
select deptno,dname,loc
from dept;
[code]...
on 11g R2 on Win 2008
How to find ddl for any indexes built on MY_VW ?
how can I find SessionID of Oracle Job? In Oracle 9i Jobs were running under Sessions with SID = 0. This seems to be not the case under Oracle 11g.
View 10 Replies View RelatedAs titled.
View 15 Replies View RelatedA query to find the table fragmentation.
View 1 Replies View Relatedi have table like TWRCHAIN_WS_SUM. it is regularly updated by another object like procedure, function. how can i find which object is updating the particular table across the database.
View 4 Replies View Related From database server, I need to monitor the details about the sql statements which are being currenlt running in client machines.
I tried with V_$SQLTEXT view where I can only see the SQL statements, hash value,address,SQL_id. but I'm not able to get the user name,name the client machine .
find out these details?.Which Data Dictionary i need to use ?.
why am i getting this?
SQL> SELECT TABLE_NAME,owner from dba_TABLES where TABLE_NAME in ('BSNL_ACCOUNT_DATA','BSNL_SERVICE_DATA','BSNL_PKG_DETAILS');
TABLE_NAME OWNER
------------------------------ ------------------------------
BSNL_ACCOUNT_DATA BPREAD
BSNL_SERVICE_DATA BPREAD
but the table exists
SQL> select count(*) from BPREAD.BSNL_PKG_DETAILS;
COUNT(*)
----------
3704
SQL> select TABLE_NAME,owner from dba_TABLES where TABLE_NAME='BSNL_PKG_DETAILS';
no rows selected
why is it showing no rows even if the table exists
I have written a script to get count of each tables of a schema.
DECLARE CURSOR c1 IS
select distinct table_name from all_tab_columns where OWNER='PLAY';
countrows NUMBER;
[Code].....
this is my table
create table sequence ( id int not null primary key);
insert into sequence(id) values
(1), (2), (3), (4), (6), (7), (8), (9),
(10), (15), (16), (17), (18), (19), (20),(22);
i need the answer to group the sequence like this
start_number | end_number | count
1 | 4 | 4
6 | 10 | 5
15 | 20 | 6
22 | 22 | 1
what should i do?
I have a table as:
ID VALUE
---------- ----------
1 2
1 14
1 41
1 42
1 42
1 48
1 56
1 65
1 86
1 93
2 15
2 36
2 42
2 48
2 62
2 75
2 75
2 85
2 85
2 99
There are millions of distinct ID and more than 5000 VALUE's(in ascending order) for each ID.
I need to find the VALUE stored at [int((95 / 100) * COUNT(ID)) - 1] column for each ID, where COUNT(ID) is the no of rows for each ID.
The result will be as
ID VALUE
---------- ----------
1 86
2 85
How does the following underlined query works to find the nth highest salary
SELECT DISTINCT(A.SALARY) FROM EMPLOYEE A
WHERE &SALARY = (SELECT COUNT(DISTINCT(B.SALARY)) FROM EMPLOYEE B
WHERE A.SALARY<=B.SALARY);
I have SQL ID , sql with bind variables ("SYS_B_005")from AWR report which is taken last week.I need find the full exact sql text with bind variable value (sql ran 1 week before).
View 4 Replies View RelatedI'm connecting from SQL Server to ORACLE (ORACLE is linkedserver) and I'm trying to find out when ORACLE started and how long it has been running. Is there query or command in ORACLE that I can use to check this? Is there more than one option to find this out and is it same for all ORACLE versions or diffferent from version to version?
View 1 Replies View RelatedSQL> select count(*) from tab;
COUNT(*)
----------
63
I need to find out how many rows in each table.I've tried with cursor.
I am having to pull out all the dependencies for a column as below.
When I select a column in a table, it should list
1. All Views that use this column and table
2. All Stored procs that use this column and table
3. All Functions that use this column and table
4. All Pl/SQL scripts that use this column and table.
Is there a easy way of getting this information from the Oracle dba tables?
this is my table
create table sequence (
id int not null primary key
);
insert into sequence(id) values
(1), (2), (3), (4), (6), (7), (8), (9),
(10), (15), (16), (17), (18), (19), (20),(22);
i need the answer to group the sequence like this
start_number | end_number | count
1 | 4 | 4
6 | 10 | 5
15 | 20 | 6
22 | 22 | 1
what should i do?