find out the list of referenced objects which are dependent on INVALID objects.
I wrote the below query to get the referenced objects which depends on invalid objects.
select owner,name,type, referenced_owner,referenced_name, referenced_type, referenced_link_name , dependency_type from dba_dependencies where type not in ('JAVA CLASS') AND referenced_type NOT IN('JAVA CLASS') AND (NAME, TYPE) IN ( select object_name, OBJECT_TYPE from dba_objects WHERE STATUS='INVALID')
And the output is something like below -
Sr. No OWNERNAMETYPEREFERENCED_OWNERREFERENCED_NAMEREFERENCED_TYPEREFERENCED_LINK_NAMEDEPENDENCY_TYPE 1PUBLICVEHICLE_INSPECTION_REQUESTSYNONYMINSBIN$Uu99fysmRj6Ppn2QppCTWg==$0TABLEHARD 2PUBLICPRODUCT_HIERARCHY_MAP_TEMPSYNONYMCONFSYSBIN$ndGddLcKSDWRwsn5g91Rcg==$0TABLEHARD 3PUBLICACPKG_SUB_RECEIPTINGSYNONYMINSACPKG_SUB_RECEIPTINGPACKAGEHARD
[code].....
Our requirement is to drop all the invalid objects. But we need to know whether it effecting to any other valid objects?Do any valid objects gets INVALID after dropping of the INVALID objects. We need to know the hierarchy of it.
While i'm doing delete obsolete backup its Throwing error.
RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status RMAN-06210: List of Mismatched objects RMAN-06211: ========================== RMAN-06212: Object Type Filename/Handle RMAN-06213: --------------- --------------------------------------------------- RMAN-06214: Datafile Copy /u01/app/oracle/oradata/demo.ctl
Dependencies between objects in the database? I want to see the PL/SQL code which is an appeal to a particular procedure or function or package and where and how does a table use in any PL/SQL code.
Often do you have a need to get answers to these questions? I'm developing such tool now and I want to know, if everybody interested to one.
i have a sequence for one of my table that this sequence's current value was 3000 yesterday but today when i checked current value of it, i surprised because the value changed to 50, can i check who changed my sequence? is exists any data dictionary that shows logs of modified database objects.
I need to copy all the database objects from one database to another database.I have 32 bit windows environment I tried export and put it in a dump file but dump file size exceeds the limit, my database size is about 300 GB.
What would be efficient way to replicate all the database objects move to another box ..?
How to Pick / Extract the java class files from the database.? We have not maintained the latest codes in the oracle application server where java class code is residing.
All the Java Classes are available only in database. So we need to pick the latest java class code from production environment. In TOAD we tried but all class objects are listing at the left side but we are unable to take the code. So how can we take the latest codes(java classes) from the Production Database as a backup.
I need a job to get executed for every 1hour.Like i need a query to identify invalid objects in database per schema ,invalid type and this is a job to run every hour.How to schedule it.I only know that dba_jobs have the info.
I imported a schema HR from export DUMP ....i can find all the objects of schema HR in the imported database... but i got an error for a plan_table which is assigned to USERS tablespace in the source database.. ...
HERE COMES THE ERROR I GOT DURING IMPORT:
[oracle@localhost mom]$ imp file=exp_schema_ref.dmp log=imp.ref.log fromuser=hr touser=hr commit=y Import: Release 10.2.0.1.0 - Production on Mon Jul 26 00:03:57 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. Username: sys/sys as sysdba
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via direct path import done in US7ASCII character set and AL16UTF16 NCHAR character set . importing HR's objects into HR . . importing table "COUNTRIES" 25 rows imported . . importing table "DEPARTMENTS" 27 rows imported . . importing table "EMPLOYEES" 107 rows imported [code]....
My primary objective was to compare objects in schemas in two different databases and find out the differences, Execute DDL's in the database where objects are missing and syn schemas in two different databases. So I need to compare schemas in databases. make a comparison of database objects existing in schemas in two different databases. I'd like to see if I can get a list of pro and cons between Toad and SQL Developer for comparing schemas pros and cons. navigation in SQL Developer to compare schemas.
Connect to Source Connect to Target Compare schemas with different object types Find out differences Generate DDL's for the missing objects or for the objects in difference report Run them in missing instace(Source/Target) Make sure both are in sync.
We transferred our Oracle database 11.1.0.7 from windows 2003 enterprise edition 32 bit to windows 2008 enterprise edition server 64 bit.Database is working fine but we have 53 uncompiled objects which are related to OLAPSYS and public as follows
I have created two types and a list of the first type:
create type type1; / create type type1_list as table of ref type1; / create type type2; /
I have now just created the two types as follows:
create type type1 as object( id# number ); / create type type2 as object( attribute1 type1_list, MEMBER FUNCTION function1 RETURN NUMBER ); /
Ok, I've created the tables (I don't know if it's necessary to point out my problem)
CREATE TABLE type1_table OF type1; / CREATE TABLE type2_table OF type2 NESTED TABLE attribute1 STORE AS nested_type1_list_table; /
And what I wanted to do now is to implement the member function1 and check something of the attributes of type1 in the list of attribute1... And that's where my question occurs, how does it work, I can't figure it out. I tried something like this:
Quote: create or replace TYPE BODY type2 AS MEMBER FUNCTION function1 RETURN NUMBER AS
The database system they have has around 5000 tables. The majority of these are not used (it is an off the shelf package). What I am trying to do is get a list of all tables in the database BUT only those that contain records (not the empty tables) and then copy this to an excel spreadsheet. We use a tool called aquadata to run sql enquiry statements on.
Currently, the only way I can find of doing so is to try to disable an instance, and if it is already disabled, it warns you that its disabled. This is not exatly an ideal solution! How do I simply run a query or issue a crs command to list all disabled database instances.
I remember a method in which I used a property in the property pallete to specify the query in which i wanted the list linked with, it gave me the option to input a static list or as mentioned a dynamic list which was dependent on a query entry, but I cant for the life of me remember where this property was.
I have a form which contains two canvas.In the first canvas, i have a data block which contains two columns(database item of both are no). In second canvas i have a list item.
My problem is how to pass the value(s) of this data block to the item list which is present in second canvas.
The requirement user has given is such that the user will enter different categories, and in the second canvas where the main datablock is present, will contain a list item in data block which should show the data entered by the user.
I am having a little trouble retrieving info from my mock database.
here are my definitions: response.setHeader("pragma", "no-cache"); response.setHeader("Cache-control", "no-cache, no-store, must-revalidate"); response.setHeader("Expires", "01 Apr 1995 01:10:10 GMT"); response.addHeader("Cache-Control", "post-check=0, pre-check=0");
[code]...
and here is my select statement. ds = (DataSource) context.lookup(dSource); conn = ds.getConnection(); stmt = conn.prepareStatement("select add_date, campus, name, leader from TABLENAME where id = ? order by xbbo_add_date"); stmt.setString(1, colleagueID); result = stmt.executeQuery();
***basically I am trying to retrieve this info from my db, and list it out in a HTML table. I am writing this on a JSP page. Also, where i want the retrieved data listed, I am using for ex: <%=name%>
I have 3 tables in the Oracle database( emp, employee, emp1) which has following record values in it.
empidenamejob 7369, 'SMITH', 'CLERK'
I would like to list these 3 tables thru SQL/PLSQL, having the above record values combination. Also, the name of the columns could be different in all the tables i.e. name could be 'ename' in Emp table , and 'name' in Employee table. Is there way to do this in SQL or PLSQL ?
based on a provided list of key values, joining the collected list against a source table to retrieve additional information related to the key. In this simple example, the procedure accepts a list of employee numbers. The goal is to print a list of names associated with those numbers.
The method is to materialize the list of employee numbers as rows and join those rows to a source table to get the names. I have used BULK COLLECT. Why we cannot cast PLSQL tables using a type defined in the procedure's specification (why the type needs to exist as an object before we can cast it, like this:
SELECT * FROM TABLE ( CAST ( SOME_FUNCTION(&some parameter) AS SOME_TYPE ) );
here is my demo SQL, which you should be able to execute against the SCOTT schema without any change
declare type employee_numbers is table of emp.empno%type index by binary_integer; type employee_names is table of emp.ename%type index by binary_integer; type employees_record is record (empno employee_numbers, person_name employee_names); records employees_record;