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.
Oracle sys object are Invalid,But database working fine. How to fix these problem, I don't have oracle support,
Database Dba_registry contain are All Valid.But sys object getting Invalid.
Oracle 10g 10.2.0.5 Standard
OWNERCOUNT(*)
MDSYS359
DMSYS24
PUBLIC147
CTXSYS3
OLAPSYS32
XDB 5
SYS 141
Dba_registry
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
Note : BABU is a empty schema with no tables
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...
View 5 Replies View RelatedI 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 passed the values like this in proc execution.
T_T_A_I_V := typeObj3('asdasd',NULL,NULL),
typeObj3('String654',NULL,NULL),
typeObj3('abcdef',NULL,NULL));
T_T_A_I_I := typeObj2('CampusCode',T_T_A_I_V),
typeObj2('PlanNumber',T_T_A_I_V);
What i have done is removed typeObj3 from typeObj2, variables defined in typeObj3 are added in typeObj2 then i got ride of above error. is it correct
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 Relatedtwo 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?
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.
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 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".
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 Relatedfind 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]....
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.
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 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.
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 RelatedI 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 Relatedhow to get command history in sql*plus under windows
View 2 Replies View RelatedMy 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 ?
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
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 RelatedI 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.
View 1 Replies View Related