SQL & PL/SQL :: Useful Analysis From History Tables?

Mar 8, 2010

I have 2 tables that I am going to use. These are AUDIT_LOG, and AUDIT_PROPERTIES. AUDIT_LOG tells me that who made the change and at what time. AUDIT_PROPERTIES basically tells me that what the old value was and what the new value is.

These tables operate at the field level. For example, I have a module called BUG. Now a BUG (as a module entity or record) has several fields. There are around 25 fields. But I am interested in only 3 of these fields- these are BG_STATUS (Status), Priority (BG_PRIORITY) and Severity (BG_SEVERITY). This is my requirement:

Between a given period of time (let us say the month of January), give me the count of bugs that, 1) had an "Open" Status, 2) Were of "Medium" Severity, 3) And had a "Low" priority....each of these 3 conditions must be true simultaneously, means, that to be a "qualified" bug, the bug must be Open, Medium severity and low priority at the same given time within the month.

Below are the query and an image of the data looks like.

View 11 Replies


ADVERTISEMENT

Search History For All Tables Created By A User

Mar 28, 2012

I want to find the history of manipulation the database by filtering the user who created the last tables, what tables are, when he created it etc ..

I'm using oracle XE and the client is toad.

View 3 Replies View Related

SQL & PL/SQL :: Top N Analysis

Oct 6, 2013

Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production
CORE Version 4.0.5.0.0 - Production
TNS for 32-bit Windows: Version 8.0.5.0.0 - Production
NLSRTL Version 3.3.2.0.0 - Production

With reference to above oracle version. I am trying to retrieve top 300 donors from the table, below is my query.

Select * FROM
(select
distinct(d.contact_code),
c.contact_title||' '||c.contact_name,
c.contact_address,
c.contact_pcode,
ci.city_name,
cy.country_name,

[Code]...

ERROR at line 50: ORA-00907: missing right parenthesis

what is incorrect with this query. If I remove the main query then this query gives 76223 rows is correct, but I only want top 300 donors. How do I resolve this

View 17 Replies View Related

How To Capture SQL Statement For Analysis

Aug 27, 2012

we have 1 server oracle and many clients. when client sends sql statement to server.How to Capture sql statement to analysis before it sent to server and execution.

View 18 Replies View Related

Performance Analysis And Optimization Tools

Jan 27, 2011

I am looking for some tools for Performance analysis and optimization for Oracle. For now I looked over Spotlight, Ignite and Embarcadero DB Optimizer.

View 1 Replies View Related

PL/SQL Timer Wait Event Analysis

Jun 22, 2013

One of our Job running long than usual time. I checked the wtait event for which its waiting. its PL/SQL Timer wait. But i noticed the total waits is 179 and timed out is also 179 for PL/SQL Timer. I checked that job that particular procedure calls dbms_lock.sleep in it. I want to know why this wait event is getting timed out ?

View 1 Replies View Related

SQL & PL/SQL :: ORA-06552 / Compilation Unit Analysis Terminated

Apr 28, 2011

The following error has occurred:

ORA-06550: line 26, column 30:
PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 7, column 6:
PL/SQL: SQL Statement ignored

I am getting the error when i run the below anonymous block. Historic_report_data has got plenty of columns and using only the columns that are in tb_Statement in the query. It is working fine if i use a table with same number of columns as tb_statement instead of historic_report_data.

DECLARE
v_cnt NUMBER:=0;
v_data_load_error EXCEPTION;
BEGIN
dbms_output.put_line('Reconciliation scripts');
dbms_output.put_line('Reconcile tb_bank_Account_month and report_hist_data');

[code]....

View 16 Replies View Related

Performance Tuning :: Explain Plan Analysis - Execution Ordering

Jul 21, 2010

I have two Oracle instances that are setup identically.When I run a query on one of them, it takes around 3 seconds, on the other it takes around 200 seconds.

I have looked at the explain plans, and it has shown me what I think is the problem. On one instance, it does a join on two tables, then runs the other filter/access predicates. On the other instance it runs the filter/access predicated first, then does the expensice join. The one that does the join first is the one that takes around 200 seconds. How to tell Oracle to make this join after runnning the other predicates?

View 15 Replies View Related

Server Administration :: Graphical Analysis Of Dynamic Performance Views (V$ Views)?

Nov 5, 2012

is there some open source or free tool which can graphical display V$ Views. Can TOAD do that in a good maner?

in UNIX there is the "sar" command, but a Java tool "ksar" for displaying the statistics in user friendly fashion.

View 2 Replies View Related

How To Find SQL Text History

May 27, 2011

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 Related

SQL & PL/SQL :: History Table Join

Aug 16, 2010

two history tables with each record having effective date and end date needs to join (date is in dd/mm/yyyy)

table one

effdate enddate ID Name
01/08/2010 04/08/2010 01 devendra
04/08/2010 06/08/2010 01 deven

table two

effdate enddate ID Family
01/08/2010 02/08/2010 01 X
02/08/2010 03/08/2010 01 Y
03/08/2010 05/08/2010 01 Z
05/08/2010 06/08/2010 01 W

Expected output

effdate enddate ID Name Family
01/08/2010 02/08/2010 01 devendra X
02/08/2010 03/08/2010 01 devendra Y
03/08/2010 04/08/2010 01 devendra Z
04/08/2010 05/08/2010 01 deven Z
05/08/2010 06/08/2010 01 deven W

what can be optimum sql for this?

View 3 Replies View Related

SQL & PL/SQL :: Invalid Object History?

Jun 10, 2010

I can get a list of invalid objects, know how to recompile them but I was wondering if there was a way to query the date/time of when an object went invalid.

I'm working on a 10.2.0.4.0 (Enterprise Edition) database running on SLES10.

View 1 Replies View Related

History Of Frequency Of Execution Of A Particular Sql

Jun 18, 2012

I am having a particular SQL, i want to know the frequency/count of execution of this particular sql in my prod DB. how can i get this.

I get the sql in v$sql but didn't get the same record in dba_hist_sqlstat view.

View 3 Replies View Related

Select From Main If There Is No Record In History

Aug 9, 2010

I have 3 main tables as projects, tasks, clients. Then I have a history table for each. I created a trigger on update/delete that takes the old records and inserts into the history.

Now, I need to retrieve the history but I have no clue how to do so. I need to join these 3 history tables with:

1. select from projects_history with projectID = 1
2. if no records, then go back to projects and get projectID = 1

3. select from task_history with taskID = 1
4. if no records, then go back to tasks with taskID = 1

5. select from client_history with clientID = 1
6. if no records, then go back to client with clientID = 1

where client.taskID = task.taskId and task.projectID = project.projectID.

View 1 Replies View Related

SQL & PL/SQL :: Updating Fields Of History Table

Aug 25, 2011

pgit_policy is transaction table having producer code field.

pgith_policy is history table, on that table if any endorsement passed new records created with same polh_sys_id and increment on POLH_END_NO_IDX.

I am trying to update all records of the history table but its updating only higest POLH_END_NO_IDX only. i need to update all producer code.

update pgith_policy a
set a.polh_producer_code= (select b.pol_producer_code
from pgit_policy b
where b.pol_no=a.polh_no
--and b.POL_END_NO_IDX= a.POLH_END_NO_IDX and b.POL_END_SR_NO = a.POLH_END_SR_NO
and b.pol_producer_code is NOT NULL
and b.pol_class_code='10')
where a.polh_class_code='10'
and a.polh_producer_code is null
and a.polh_appr_dt between to_date('01-06-2011', 'dd-mm-yyyy') and to_date('30-06-2011', 'dd-mm-yyyy')

View 4 Replies View Related

SQL & PL/SQL :: Trigger To Move Records To History?

Mar 23, 2010

I have a table CISCOWORKS that contains dumps from Ciscoworks. Now I want to make a history table CISCOWORKS_HISTORY that contains all information I no longer need actively (entries that have the same CISCOWORKS_MAC as newer entries).

The two tables are like this, exactly the same.

DROP TABLE CISCOWORKS IF EXISTS;
CREATE TABLE CISCOWORKS
(
CISCOWORKS_IDNUMBER(9,0),
CISCOWORKS_MACVARCHAR2(20 BYTE),
CISCOWORKS_SWITCHVARCHAR2(10 BYTE),
CISCOWORKS_PORTVARCHAR2(10 BYTE),

[code].....

I guess I will have to check the INSERTED table and check every entry to see if there is a entry in the CISCOWORKS table with the same CISCOWORKS_MAC. If so, insert an entry in CISCOWORKS_HISTORY with the attributes of the CISCOWORKS entry, and then delete the CISCOWORKS entry. how would I check every entry separately?

View 4 Replies View Related

SQL & PL/SQL :: How To Return Last 2 Rows In History Table

Nov 23, 2011

I have a history table which has a new record written to it for every time static data is updated. What I want to do is return any record that has been changed on a daily basis returning both the new record and the previous record so you can see the 2 records one below the other and compare.I have written the below but it returns all records per account:

SELECT H.Timestamp,H.AccountNo FROM History H
where exists (select H2.AccountNo,count(*) from History H2
where H2.AccountNo = H.AccountNo
group by H2.AccountNo having count(*) > 1 )
Where H.Timestamp > '20111101'
order by H.AccountNo,H.Timestamp

View 18 Replies View Related

Forms :: Record History Is Not Enabling?

May 1, 2012

I developed a custom form upon which I developed a query find block to query the data on the main form. I have WHO columns in the table and referencing them in the main form. But Some reason when I query the data by pressing the find button on the query block it is retreiving the data properly, but record history is not enabling.

In the query find block I have 3 text fields, 1 check box and 2 date fields.

View 1 Replies View Related

Server Administration :: LOG History In Standby DB?

Mar 6, 2013

I want to know the DATE, LOG SEQUENCE NUMBER which was applied in the Standby (DR)one year before.

I am not able to get through "v$log_history".

View 5 Replies View Related

Security :: History Of Privileges Changes Done For A DB User

Jan 1, 2013

As how do I query, as when was the last time or rather list of privileges changes done for a db user and what was the changes made?

View 1 Replies View Related

SQL & PL/SQL :: Query To Find History In Oracle 11?

Jul 29, 2011

find the recent SQL history from Oracle 11. could the following SQL retrieve all SQL history happened in last 5 mins?

SELECT h.user_id,
s2.username,
h.sample_time,
s1.sql_text
FROM v$active_session_history h,
v$sqlarea s1,

[code]....

View 3 Replies View Related

Catbundle.sql Not Updating Dba-registry-history?

Jan 17, 2013

I just applied the Jan 2013 SPU and the patch succeeded with no errors; however, when I query DBA_REGISTRY_HISTORY after running the catbundle.sql, the table is not reflecting the new patch. I checked through the catbundle log files and did not see any errors. What could be wrong?

I am running 11.2.0.3 single instance.

View 3 Replies View Related

PL/SQL :: Update / Insert History Table?

Jun 15, 2012

I've a one history table in which I'm putting approval history data.

For any transaction id you may have more than one record with approval status APPROVED,REJECTED,ERRORED,OVERLIMIT etc

Another program selects records from this table by passing transaction_id. For transaction id, it needs to check the most recent approved record exist or not ? If it's there then updating the record by adding comments to comments field of the same record and then delete all other records for the same transaction id.

If it does not exist then delete all other records and create one with approved status?

What's the simple and best approach to do this (sql or pl/sql)?

View 2 Replies View Related

PL/SQL :: Date Format In History Table

Mar 1, 2013

Issue with Date format. I am having data in date column in History table like

'2012/02/22 11:05:20 AM'
'2012/08/15 17:00:00'

I am doing extract from this table to txt file. i want date fomrat in "YYYYMMDD HHMMSS".

i tried below query:

select to_char(to_date('1998/05/31 11:00:00 AM','yyyy/mm/dd hh24:mi:ss'),'YYYYMMDD HH24MMSS') from dual;But it is giving error like "ORA-01830: date format picture ends before converting entire input string".

I am using ORACE 11g verion.
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE     11.2.0.3.0     Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

View 8 Replies View Related

SQL & PL/SQL :: Trace Privileges Granted And Revoked History?

Nov 20, 2012

I have written a windows service which grants and revokes based on request. How to trace in oracle data dictionary if those privilages were granted and at what time to whom and by whom. I mean is there any table which can be queried for past privilages granted to users.

View 2 Replies View Related

SQL & PL/SQL :: Data Transfer From Fact To History Table?

Aug 9, 2010

I need to transfer 6 million records from fact tables to history table .. What is the better and fast process to do that.

View 3 Replies View Related

Client Tools :: How To Get Command History In Sql*plus Under Windows

Jan 28, 2011

how to get command history in sql*plus under windows

View 2 Replies View Related

Performance Tuning :: TEMP Usage History

Jul 19, 2010

My stats jobs failed last night with "ORA-01652 :Unable to extend TEMP" error.

Is there any way to check this history data, what other session was using TEMP tablespace extensively ?

View 6 Replies View Related

PL/SQL :: Get Not Null Recent Values From History Data?

Jul 5, 2012

Below given is the sample data

SELECT *
FROM
(
SELECT 1 ORDR_ID, TO_DATE('01-JAN-2012', 'DD-MON-YYYY') INWD_DATE, 5 SIZE_, 'APD' Colr_CD FROM DUAL UNION ALL
SELECT 1 ORDR_ID, TO_DATE('15-JAN-2012', 'DD-MON-YYYY') INWD_DATE, NULL SIZE_, 'KPD' Colr_CD FROM DUAL UNION ALL
SELECT 1 ORDR_ID, TO_DATE('16-JAN-2012', 'DD-MON-YYYY') INWD_DATE, NULL SIZE_, 'ALD' Colr_CD FROM DUAL UNION ALL
SELECT 2 ORDR_ID, TO_DATE('02-JAN-2012', 'DD-MON-YYYY') INWD_DATE, 9 SIZE_, 'APD' Colr_CD FROM DUAL UNION ALL
SELECT 2 ORDR_ID, TO_DATE('05-JAN-2012', 'DD-MON-YYYY') INWD_DATE, 10 SIZE_, '' Colr_CD FROM DUAL UNION ALL

[code]....

Where every first row is the initial entry for an order and following rows are the changes done to the order with modification date

And the expected output is

ORDR_ID     Start_date     End_date     SIZE_     COLR_CD
1     01-01-12     01/15/2012     5     APD
1     01/15/2012     01/16/2012     5     KPD
1     01/16/2012     00-01-00     5     ALD
2     01-02-12     01-05-12     9     APD
2     01-05-12     01-06-12     10     APD

[code]....

I cam use a select statement within select, but wanted to write an efficient SQL...

I'm using

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0

View 2 Replies View Related

Active Session History And Null Events

Sep 13, 2013

11.2.0.3 EVENTS: Null(blank) or called a 'null event' See link. Checked the docs. I don't see anythingI think a null (blank) is for an idle wait since per the V$ACTIVE_SESSION_HISTORY spec these are not record. Am I correct?What is a 'null event'. I see a paper from Oracle magician dating to 9i that says this is an 'oracle goof'.  Descriptions of Wait Events

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved