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 want to Validate all Objects in a schema which are Invalid, after some initial investigation I found a package UTL_RECOMP,If any remote Object is changed, then the local dependent object is not Invalidated at that moment, so will this Package UTL_RECOMP recompile that local object as well.
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.
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
As a part of requirement need to clean up some of database objects from database. Before cleaning up need to check for dependencies with in database and also on remote database.
Is there any data dictionary in oracle which proivides information about remote dependencies.
Any other way to get a list of remote dependencies other then manual checking.
A developer calls me and tells me there are locks on 3 tables, and the locks are not released. So I run this to see which objects are locked by which session
When I run the query later, I sometimes get another session, but the lock stays. It seems here, that I am catching the same SID on new sessions, but it is probably a different serial # than the original session locking the table rows.
I get nothing from select * from dba_waiters; select * from dba_blockers;
Now why are those 3 tables row locked on some rows and I can't find the session responsible? The developer tells me his application crashed and the rows are locked since. So far ( keep in mind I am not a locking expert) the only way I found to release the locks is a DB bounce, its a test DB so no biggy.
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.
Say i have a Procedure or package or any other stored subprogram, I need a script say to which i can pass this Procedure/Package/View etc name and then the code should give me the data results as which all other stored subprograms are used within the particular stored program name which we mentioned in our WHERE or Predicate clause
I have 3 schemas (master, baby and web)- master has two pakages , PkgA which has user access and package PkgB with definer access. It also has a table called Tab1. Both pakages are inserting data into table Tab1. Baby schema also has table Tab1. When we call pakage PkgA where data will insert (mater or baby table) and when we call pakage PkgB where data will insert (mater or baby table). What will happen when we call pakages from web and how can we make sure data is inserting baby Tab1 table?
I have 2 object tables. Location and a department. Department references a location Object. But this wont insert. I get "0 rows created".
CREATE OR REPLACE TYPE location_objtyp AS OBJECT ( locationID NUMBER, name VARCHAR2(48) ); / CREATE OR REPLACE TYPE dept_objtyp AS OBJECT ( deptID NUMBER, name VARCHAR2(48), locationID_ref REF location_objtyp ); [code]...
This does not insert. I get no error. Only - "0 rows created".
COMP_IDSTATUS APSOPTION OFF SDOOPTION OFF ODMOPTION OFF AMDOPTION OFF XOQOPTION OFF OWMVALID CATALOGVALID CATPROCVALID JAVAVMVALID XMLVALID CATJAVAVALID RULVALID EXFVALID CONTEXTVALID XDBVALID EMVALID ORDIMVALID
In our company we have a requirement to export data out of a live dbs and import that into a test db for issue replication,debugging & fixing from time to time.
As a junior DBA am resposible to import a dump file provided to me into a test DB. The export is taken off by customer dbas or consultants for a specified list of tables/schemas etc.
I have readonly access to source database and the issue I am facing is after the import there are certain objects that get missed out or do not get imported properly.
I would like to know if there was a script I can use to generate a report on the source DB and then run the same on the target DB after the import and compare to find out if any objects got missed out.
Is there any data dictionary table to get the object grants for total objects in the database?
Using DBA_TAB_PRIVS, i get object grants for tables only. But i'm looking for remaining objects also. The list of remaining objects is below.
view, or materialized view Sequence Procedure, function, or package User-defined type Synonym for any of the preceding items Directory, library, operator, or indextype Java source, class, or resource
provide me the other data dictionary tables for querying.
create or replace type testobj as object(col1 number); create or replace type tabtest as table of testobj; create or replace procedure proc(a out tabtest) is cursor c is
During import , tables were imported but index was not imported due to ORACLE home was full.after import i was trying to create the the same index as it was not imported i am getting below error.
The "error ORA-00955: name is already used by an existing object"
My index name is GL01PK . I am trying to verify if the index with above name is present or not , using following query.
select OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID ,OBJECT_TYPE from dba_objects where object_name like '%GL01%'
below is the output which shows that GL01PK index is not present.
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.
I have the metadata of an object in one of my database table as xml. I failed to recreate the object in the other schema using metadata api. I developed a stored function to do the above job. My function doesn't throw any error as well as it doesn't create the object.
My code snippet is
CREATE OR REPLACE PROCEDURE DDI.move_table( table_name in VARCHAR2, from_schema in VARCHAR2, to_schema in VARCHAR2 ) AUTHID CURRENT_USER
[code]....
The schema where i create and execute my function have dba privilege also.
I have created one type as Object and I am trying to display the values available in object type for debugging purpose. To display the contents of object type.
I keep getting an ora-04020: deadlock detected while trying to lock object XDB. SDNRB..The statement I'm trying to issue is:
REVOKE execute on abc."descript_T" FROM PUBLIC;
I am not able to find a solution for this besides the fact of trying the operation again at a later time.I did, but get the same error every single time.
I am facing a problem in my database that whenever I load any kind of database objects in a particular tablespace then it gives:-
ORA-08103: object no longer exists
If I load in any other tablespace, everything works fine. I am getting error while export/import, create a table etc. How can I resolve it. I was wondering that may be it is facing problem while writing to a particular datafile.
I have 8 databases in multi master replication. I need to replicate only one rep object in to master sites. But I have 8 rep objects in the master definition site. is it possible to replicate the required rep object only without bringing the other rep objects in to replication.
how to display REFS from within a REF. I've not used my exact code here as its quite a big file so i've made a similar scenario to get me point across. Here is the code first:
1 CREATE OR REPLACE TYPE object1 AS OBJECT ( 2 object_id NUMBER(5), 3 object_name varchar2(10), 4 object_added DATE); 5 / 6 CREATE TABLE object1_tab OF object1 (object_id PRIMARY KEY); 7 / 8 INSERT INTO object1_tab 9 VALUES (1,'Daniel',sysdate); 10 / 11 show errors; 12 / 13 CREATE OR REPLACE TYPE object2 AS OBJECT ( 14 object_id NUMBER(5), 15 object_job varchar2(10), 16 object1_ref REF object1 ); 17 / 18 CREATE TABLE object2_tab OF object2(object_id PRIMARY KEY); 19 / 20 INSERT INTO object2_tab 21 VALUES (1,'Developer',(SELECT REF(p) FROM object1_tab P 22 WHERE VALUE(p).object_id = &object_id)); 23 / 24 select DEREF(object1_ref) 25 FROM object2_tab; 26 / 27 CREATE OR REPLACE TYPE object3 AS OBJECT ( 28 object_id NUMBER(5), 29 object_location VARCHAR2(20), 30 object2_ref REF object2); 31 / 32 CREATE TABLE object3_tab OF object3 (object_id PRIMARY KEY); 33 / 34 INSERT INTO object3_tab 35 VALUES (1,'New York',(SELECT REF(p) FROM object2_tab P 36 WHERE VALUE(p).object_id = &object_id)); 37 / 38 show errors; 39 / 40 select object_id,object_location,DEREF(object2_ref) 41 FROM object3_tab; 42 /
As yot can see in the code each object refernces from the previous. When I view the DEREF in the third table (object3_tab) i get the following output:
OBJECT_ID OBJECT_LOCATION DEREF(OBJECT2_REF) --------- -------------------- ---------------------------------- 1 New York [SANTA.OBJECT2]
I am trying to select the owner of a certain object, only knowing the name of the object and the user calling it.
Problem is this object might exist in more than one schema with the same name, and I only need the one that is called by the active user, which himself/herself might have access to other schemas containing their own copy of the object.
Example:
SELECT OWNER INTO v_schema_name FROM ALL_OBJECTS WHERE OBJECT_NAME = p_object_name;
This works fine, until the object exists in more than one schema to which the current user has access.