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.
COMP_IDSTATUS APSOPTION OFF SDOOPTION OFF ODMOPTION OFF AMDOPTION OFF XOQOPTION OFF OWMVALID CATALOGVALID CATPROCVALID JAVAVMVALID XMLVALID CATJAVAVALID RULVALID EXFVALID CONTEXTVALID XDBVALID EMVALID ORDIMVALID
I am trying to create Master_repobject , getting following error
BEGIN DBMS_REPCAT.CREATE_MASTER_REPOBJECT ( gname => 'babu_rep', sname => 'BABU', oname => 'EMP', type => 'TABLE', use_existing_object => TRUE ,
[code]....
ERROR at line 1: ORA-23308: object BABU.EMP does not exist or is invalid ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2627 ORA-06512: at "SYS.DBMS_REPCAT", line 562 ORA-06512: at line 3
While practicing with Triggers, the following error was encountered. An Object Type and an object Table are created.
create or replace type typPerson is object (id number, firstname varchar2(30), lastname varchar2(30) [code].........
I executed the below insert statement, and I got the following error.
SQL> insert into person_obj_tab values (10,'Object1','From Trigger'); insert into person_obj_tab values (10,'Object1','From Trigger') * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 4153 Session ID: 136 Serial number: 305 [code]......
SQL> insert into person_obj_tab values (10,'Object','Original');
1 row created.Question:
1) Trigger of version 1 did not report any error during compilation, but during DML execution, hangs for sometime and gives the above error. 2) Whether direct assignment of Objects of greater size is possible inside triggers built on object Tables? 3) Suppose an object contain multiple attributes (say more than 20), then how to assign them inside a trigger?
I have a problem with executing oracleCommand.ExecuteReader() method. Whatever I try it always returns null and it won't create OracleData reader object. I'm using ODAC 1120320_x64 for .net 4.0 and timesten112241.win64. Don't sure what to do. Debugger is showing strange thing in OracleConnection object : ConnectionState = Closed, but output of ttStatus shows connection to TimesTen data store and ExecuteNonQuery() command works just fine with or without (in or out) parameters. But when I try to execute some query with multile output such as select *, I can't get any result.
I also have a strange problem with connection.Open() When I execute Open() i throws AccessViolationException that can be handled with [Handle ProcessCorruptedStateExceptions] attribute, but connection is established after that and my application works fine until I try to instance OracleDataReader object.
Here is the code: OracleCommand select = null; OracleDataReader reader = null;
select = new OracleCommand(selectStmt, connection); select.CommandType = CommandType.Text; try { reader = select.ExecuteReader(); // this line throws NullReferenceException if (reader.HasRows) { [code]....
Just to mention, I tried it with different queries (pl/sql, plane sql, stored procedure) and all of them works fine in SQL Developer, but not in app.
Is there any query to find the dependent object details for any object. Like if mview is built on a table, then i should be able to find the table name with out checking code of the mview. similar way for view and functions or procedures etc...
I have an Type-object typeObj1 that consists another Type-object typeObj2. this def has another Type-object typeObj3. how to access variable declared inside typeObj3. I have syntax below for each Type.
CREATE OR REPLACE TYPE typeObj1 AS OBJECT ( SYSTEM_IDENTIFER VARCHAR2(50), PROCESS_TYPE VARCHAR2(50), abc typeObj2
) /
[Code]...
/I have tried to access the type-object in where clause in following way
FROM TABLE(CAST(I_typeObj1 AS typeObj1)) ITTPRC, ...... Where ....... AND (ADDKEY.ADDTN_INFO_KEY_TYP_NM IN (SELECT ADDTN_INFO_KEY_TYP_NM FROM TABLE(ITTPRC.abc)))
AND (ADTINF.ADDTN_RQST_TYP_VAL_DT IN (SELECT ADDTN_RQST_VAL_DT FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL ) AND (ADTINF.ADDTN_RQST_TYP_VAL_NUM IN (SELECT ADDTN_RQST_VAL_NUM FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL ) AND (ADTINF.ADDTN_RQST_TYP_VALUE IN (SELECT ADDTN_RQST_VALUE FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
In this way i am able to access the variable inside typeObj3. But problem is i am getting error "ORA-01427 single-row subquery returns more than one row" when i pass more that one typeObj2.
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).
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
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.
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.
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')
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?
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
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.
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'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)?
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
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.
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
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
I would like to know 2 days back lock conflicts session information which user held the lock,sql text,when the lock released. any sql query or view in oracle 11g db.