SQL & PL/SQL :: Particular Column Of A Table Has Been Used In What All Database Objects

Jan 11, 2011

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

View 15 Replies


ADVERTISEMENT

SQL & PL/SQL :: Access Attributes Of Objects Of Nested Table In Member Function Of Oracle Database

Nov 17, 2011

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

View 4 Replies View Related

Objects :: Deleting A Row With Object Column

Jul 4, 2012

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.

View 4 Replies View Related

PL/SQL :: Find ALL Objects That Point To One Column

Oct 15, 2012

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.

View 5 Replies View Related

SQL & PL/SQL :: How To List Database Objects

Jul 21, 2010

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.

View 3 Replies View Related

SQL & PL/SQL :: Dependencies Between Objects In Database?

Dec 3, 2012

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.

View 9 Replies View Related

SQL & PL/SQL :: Find Out Column Value In All Table In Whole Database

May 19, 2010

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.

View 14 Replies View Related

How To Move Database Objects From One Schema To Another

Sep 6, 2011

I wanted to move all db objects from one schema to another schema.

View 4 Replies View Related

SQL & PL/SQL :: Script To Drop Database Objects?

May 13, 2011

I 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.

View 11 Replies View Related

PL/SQL :: Logs Of Modified Database Objects

Jul 25, 2012

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 Related

Server Utilities :: Move Objects From One Database To Another?

Jun 2, 2011

I want to move 350 database objects from one database to another database schema.

View 16 Replies View Related

Enterprise Manager :: Copy Objects From One Database To Another?

Mar 24, 2011

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 ..?

View 22 Replies View Related

Server Administration :: How To Delete All Objects From Database

Jul 30, 2012

How can I delete all the objects from my database?

View 18 Replies View Related

SQL & PL/SQL :: Create Two Objects In Different Schemas In A Single Database?

Jul 25, 2010

Is 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.

View 4 Replies View Related

Auto Compile Invalid Database Objects

Nov 11, 2013

If i modify any procedure then auto compile should happen dependended objects.

Oracle Version: 10g

View 1 Replies View Related

Import Excel To Database Table By Column Mapping

May 24, 2013

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.

View 1 Replies View Related

SQL & PL/SQL :: Predefined Function Names As Column Name In Database Table

Apr 9, 2012

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))

View 3 Replies View Related

Networking And Gateways :: Objects Referring To Database Links

Jul 21, 2011

how to find out what are all the objects referring to particular database links in database?

View 6 Replies View Related

JVM :: How To Extract Java Class Objects From Oracle Database

Jun 26, 2013

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. 

View 1 Replies View Related

Query To Identify Invalid Objects In Database Per Schema

Sep 29, 2012

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 Related

Server Utilities :: Copy Table With NCLOB Column Into Another Database (10g To 11g)?

Dec 4, 2012

I 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 Related

Returning A Table Without Creating Objects?

May 4, 2010

I'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?

View 1 Replies View Related

SQL & PL/SQL :: Strange Objects Of Type Table

Oct 9, 2010

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?

View 2 Replies View Related

Server Administration :: IPPS Has Use Up This Amount Of Space In Database From All Objects?

Oct 3, 2011

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?

View 7 Replies View Related

Server Utilities :: Find All Objects Of Schema HR In Imported Database

Jul 25, 2010

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]....

View 1 Replies View Related

Client Tools :: Compare Schemas And Database Objects Using TOAD And SQL Developer

Apr 17, 2013

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.

View 2 Replies View Related

Server Administration :: Validate OLAPSYS Schema Objects After Transferring Database

Apr 21, 2011

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.

View 11 Replies View Related

ORA-01747 - Oracle Reserved Words - Query Table Matching Database With NUMBER Column?

Oct 1, 2012

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.

View 12 Replies View Related

Reports & Discoverer :: Value In Table Column Based On Some Existing Column Value Automatically Without User Intervention

May 15, 2011

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.

View 3 Replies View Related

Performance Tuning :: Update Currency Column Of One Table Using Other Table Currency Column?

May 11, 2012

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) ?

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved