Is it possible that we can know that a particular column of a table has been used in what all data base objects?
i.e. like emp.empno is used in which all procedures/packages/triggers etc.
I would like to know the data dictionary view
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
[Code]....
But I don't get the right way, it doesn't work
I have a table, a superclass and several derived classes.
the table definition looks like this:
/*-------------------------------------------------------------------------------------*/
/* table to store ObjectData for the current session/transaction */
/*-------------------------------------------------------------------------------------*/
create table SessionObjectRegistry(
oid number,
transactid varchar2(200),
sessionid number,
hash number,
[Code]....
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkbnftn2], [], [], [], [], [], [], [], [], [], [], []The db is
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsThe error occurs on 11.2.0.3.0 - 64bit as well.
As a workaround i'm not deleting the rows, but setting the stored objects to null. At the end of the routine i can truncate the table with the objects since truncating works. I'm not the administrator of the database, therefore i may not change any settings.
Unfortunately i wanted to be able to share Objects between Sessions, which is possible with this workaround (simply don't truncate at the end), but leaves a lot of crap lying around.
Oracle Database 11g Enterprise Edition 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 Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
I have multiple schemas, all which have tons of objects that point to one column of one table. I tried to look at v$sqltext, and v$sqlarea, but it doesn't seem to show as expected.
Is there a view that I can look at that will show me all the objects that relate to one column?
my situation. Had to change the data structure of this one column. Changed the default value from a Y to an L. I have packages, functions, triggers...etc... that deal with this one column. I need to ensure that I go through EACH one and edit them to reflect the change to the table column. And again, this spans multiple schemas that point back to it.
Is there a SQL that gives the list database objects (tables/views/packages/functions etc.) where a particular column in a table is used?
Example:
CREATE TABLE XYZ (ID NUMBER);
I want to know all the objects that used the column ID in table XYZ either in select statement or in a condition.
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 need the column name and table name which is having the particular value in the whole database.
E.g. :We have 'Scott' value in emp table emp_name column. and we have lot of tables. here we need emp_name and emp as emp table is having scott value in emP_name column.
I wanted to move all db objects from one schema to another schema.
View 4 Replies View RelatedI am looking for sql script which would drop all database objects like
JOB','TYPE',
'QUEUE','LOB','queue_table,TABLE,PROCEDURE,functions,PACKAGE,VIEW,
SEQUENCE,DATABASE LINK except TRIGGER,INDEX,PACKAGE BODY,SYNONYM )
The script should fetch the drop commands first and then execute drop objects.
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.
View 3 Replies View RelatedI want to move 350 database objects from one database to another database schema.
View 16 Replies View RelatedI 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 can I delete all the objects from my database?
View 18 Replies View RelatedIs it possible to create two objects in different schemas in a single database.
For example in Schema A, table "T1" is there, is to possible to create a table with the same name "T1" in Schema B.
If i modify any procedure then auto compile should happen dependended objects.
Oracle Version: 10g
Using oracle forms 6i i want to carray out following task.
suppose i've table with following fields namely
loc_id, del_date, del_time, del_id
and i've excel sheet with following details
dateiddtimedid
---------------------------------------------------------------------
20-03-13 loc120:30:45RDT-01
25-03-13 loc215:25:30RTD-02
26-03-13 loc310:15:20RTD-03
want to import this excel data to my oracle table but l_id in my table is in first place but in excel sheet it is in second place, del_date in my table is in second place but in excel sheet it is in first place.
next time i may get excel sheet which will be like this
did lid date dtime
--------------------------------------------------------------------------------
DEL-01loc1 20-03-1320:30:45
DEL-03loc2 25-03-1315:25:30
This excel sheet also have different oreder than my database table and i want to import it to my database table using forms 6i. yes, i suppose to use ole2 package but my problem is how to map the columns.
I've already design a form i required i can attach it or provide screen shot of the same.
Why Oracle allowing the Built-in function names as column name in data base table
Ex :
create table to_char
(to_number number(8) , to_char char(20) , to_date date ,substr varchar2(20))
how to find out what are all the objects referring to particular database links in database?
View 6 Replies View RelatedHow 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.
View 3 Replies View RelatedI need to copy one table which is having NCLOB column into our new DB(11g) from old DB (10g). As this table having huge records (1 crore) it is difficult to use direct insert statement (INSERT INTO ... SELECT * FROM @remotedb). I don't have DBA privilege to use loader.
View 3 Replies View RelatedI'm creating a stored procedure where i get to return (OUT parameter) a cursor that points to a custom table. If I create an object, I could just do something like:
Quote: CREATE OR REPLACE TYPE TmpObjType AS OBJECT
(...);
CREATE OR REPLACE TYPE TmpObjTblType AS TABLE OF TmpObjType;
PROCEDURE tmp_proc (...,
out_param_resultset OUT g_cursor_type )
....
OPEN out_param_resultset FOR
SELECT * FROM TABLE(CAST(tmpObjTbl AS TmpObjTblType));
....
How do I return the table (referenced by a cursor) without creating objects?
When as scott, I run the following command select * from tab, I get the output that has been attached as pdf. This output contain some strange objects like-
BIN$Pjh/Uul1QJyWtCiF1oTUHQ==$0
BIN$fr3Ks/3LSdSHOFHhjdXCFQ==$0
BIN$ral0nTtJTG2f7dRgYXmCtQ==$0
and as per the attachment these are all of type table. When I try to delete them-
delete table BIN$ral0nTtJTG2f7dRgYXmCtQ==$0
I get the following error-
Error starting at line 1 in command:
delete table BIN$ral0nTtJTG2f7dRgYXmCtQ==$0
Error at Command Line:1 Column:7
Error report:
SQL Error: ORA-00903: invalid table name
00903. 00000 - "invalid table name"
*Cause:
*Action:
whether something is happening to the database?
select sum(bytes/1024/1024) from dba_segments where owner='IPPS';
Does the above finding means that IPPS has use up 'this amount' of space in the database from all his objects?
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
OLAPSYSALL$OLAP2_AW_CUBE_AGG_LVLVIEW
OLAPSYSALL$OLAP2_AW_CUBE_AGG_MEASVIEW
OLAPSYSMRAC_OLAP2_AW_DIMENSIONS_VVIEW
OLAPSYSALL$OLAP2_AW_CUBE_AGG_OPVIEW
OLAPSYSALL$OLAP2_AW_CUBE_AGG_SPECSVIEW
[code]....
Our business objec is working fine and all other schema does not have any uncompiled objects.How can we validate the OLAPSYS and PUBLIC schema.
I am having issue with Oracle reserved words, one of the application is using table which has NUMBER as column. I am not able to query that table matching database with NUMBER column.
HERE
select a.*
from DOC a , FOLDER B
where a.NUMBER= B.INCIDENT_ID
and b.open = 'Closed'; I tried double quotes (“”) and sigle quotes too, none of them worked.
i have two questions.
(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'
(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.
I am trying to update currency column of one table using the currency column of other table using the following sql code.
update ODS.SO_ITEM OSI
set CURRENCY__CODE=(select currency__code from sa_sales.SO_ITEM SSI where SSI.ID=OSI.ID)
This update is taking taking a lot of time and is never ending.
should i create index on source table (SA_SALES.SO_ITEM) or on target table (ODS.SO_ITEM) ?