I am having to pull out all the dependencies for a column as below.
When I select a column in a table, it should list
1. All Views that use this column and table
2. All Stored procs that use this column and table
3. All Functions that use this column and table
4. All Pl/SQL scripts that use this column and table.
Is there a easy way of getting this information from the Oracle dba tables?
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.
I need to identify the dependencies of all the Tables on Packages at column level.
E.g. : XYZ is a package that uses ABC Table having E,F,G has a column, PQR - Table and its columns - R,S,T
The resultant query / code should return like this
PackageName TableName ColumnName
XYZ ABC E
XYZ ABC F
XYZ ABC G
XYZ PQR R
XYZ PQR S
XYZ PQR T
Identify the dependencies at column level.
How to add ROWDEPENDENCIES in an existing table. I mean How to Alter any table to add ROWDEPENDENCIES..
View 2 Replies View RelatedI have a hierarchical data structure where a child can have many parents, and a parent can have many childs.See the attached file hierarchy_iliustration.jpg. This example has 4 hierarchy levels, in real problem there can be unlimited number of levels.I want to write a SQL query that lists all indirectly dependent child nodes for a given parent node.
Test structure for example attached:
CREATE TABLE TEST.NODE_T
(
ID NUMBER PRIMARY KEY,
TEXTAS VARCHAR2(254)
);
[code]....
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.
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.
In a document from the oracle website. "If a single-table view selects only a subset of columns in a table, only those columns are involved in the dependency".
View 4 Replies View RelatedI have been asked to take the export of a schema and then asked to remove all dependencies on tablespace. Provide me the syntax.
View 1 Replies View RelatedTo find out column in which table it is located.
View 1 Replies View RelatedWith Data in a table, is it possible to find out the table name and column name? Means i know the account number (data) of the table and with this data i want to find out column name and table name.
View 3 Replies View RelatedI 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 need an sql query or procedure to find out table name based on the column name..
View 4 Replies View RelatedI have a table with thirty column for attendance like Day_A01,Day_A02......Day_A31.
I need a query that should return the column which is empty but its next and previous column is not empty.
How to find out the required column from user tables using query ?
View 6 Replies View RelatedI have a table as follows:
customers
------------------------------------------------------------------------------------------
custid credit amt month
--------------------------------------------------------------------------------------------
001 C 2000 Jan-2012
001 D 5000 Feb-2012
001 C 3000 Mar-2012
001 C 3000 Apr-2012
001 D 7000 May-2012
I Have to write a single query to calculate the sum of credit and sum of debit value separately.
I google to find the Table Name and Column Name by having a value(Number/String). And my where clauses are
where owner NOT IN ('SYS','SYSTEM') and data_type IN ('CHAR','VARCHAR2','NUMBER')
My query as follows
select a.owner, c.column_name, c.data_type, c.owner, c.table_namefrom dba_objects a, all_tab_cols c where a.owner NOT IN ('SYS','SYSTEM') and where c.owner NOT IN ('SYS','SYSTEM') and where c.data_type IN ('CHAR','VARCHAR2')order by a.owner
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.
I have two tables tab1 and tab2 with below data Now requirement is to find difference between two table with respect to column "serial_no" which exist only in table tab1 so as per below at serial_no 2 CC4 in tab1 but CC2 in tab2 so this is different how to dump out this data
TAB1
SQL> select * from tab1;
PARENT CHILD ROW_ID SERIAL_NO
-------------------- -------------------- ---------- ----------
P1 CC1 101 1
P1 CC4 104 2
P1 CC5 105 3
P1 CC2 102 4
P1 CC3 103 5
TAB2
SQL> select * from tab2;
PARENT CHILD PREVIOUS_ROWID ROW_ID NEXT_ROWID
-------------------- -------------------- -------------- ---------- ----------
P1 CC1 0 101 102
P1 CC2 101 102 103
P1 CC3 102 103 104
P1 CC4 103 104 105
P1 CC5 104 105 0
create table tab1(parent varchar2(20), child varchar2(20),row_id integer,serial_no integer);
insert into tab1 values('P1','CC1',101,1);
insert into tab1 values('P1','CC4',104,2);
insert into tab1 values('P1','CC5',105,3);
insert into tab1 values('P1','CC2',102,4);
insert into tab1 values('P1','CC3',103,5);
create table tab2(parent varchar2(20), child varchar2(20),previous_rowid integer,row_id integer,next_rowid integer);
insert into tab2 values('P1','CC1',0,101,102);
insert into tab2 values('P1','CC2',101,102,103);
insert into tab2 values('P1','CC3',102,103,104);
insert into tab2 values('P1','CC4',103,104,105);
insert into tab2 values('P1','CC5',104,105,0);
Need a query to find the count of presence of delimiter "," in column VAL for below mentioned table.
Table:
CREATE TABLE N_B_S_1
(
NUM NUMBER,
VAL VARCHAR2(20)
)
Data:
INSERT INTO N_B_S_1 VALUES(1,'A,B,C');
INSERT INTO N_B_S_1 VALUES(2,'D');
INSERT INTO N_B_S_1 VALUES(3,'ER,NF,G,H,XK');
INSERT INTO N_B_S_1 VALUES(4,'LQW,MBV');
INSERT INTO N_B_S_1 VALUES(5,'KS,YJ,WE,RQ,PM');
COMMIT;
Required output:
num, count of delimiters
===== ===========
1 2
2 1
3 4
4 2
5 4
How to find date and time of a column in table ?
say for example there is a column called 'date_txn' in a table .When i select that particular column it display output as 'June 2013'.But i want output "with date and time".
I'm using Oracle 11g enhanced ADD COLUMN Functionality. Adding new columns with DEFAULT values and NOT NULL constraint no longer requires the default value to be stored in all existing records.
Sometimes we change DB columns from NOT NULL with DEFAULT to NULL with DEFAULT. This operation "materialize" column default. Is there an easy way (Dictionary view) how to find, that COLUMN default value is stored as metadata or is "materialized" ?
Oracle RDBMS version : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
My requirment is to find the top 20 most occurred words in the perticular text column accross the full table. i WILL have desc column which is plain text where user wites the essay. i have below 2 data example where i have 2 find which word occur most of the time. i have more than 1 million data where i have to perfor this calculation
E.g
INCIDENT_NOINITIAL_SD_FAULT_DESC 70000030 late entering service on the outer rail causing a 4 minute delay. After speaking to the Hainault DDM and the Hainault DMT it has been established that 70000031E105 on entering into platform by one cars lenght applied emergency brakes to stop the train due to a person under his train. Train stopped with two cars still outside the platform. I received a call from Line Controllor at 0823 hrs that a person has jumped in front of train 105 at Greenford. I informed Ian Williamson Train Operations Manager. We traveled on a special taxi with a spare Train Operator at 0839hrs and reached Greenford Station at 0859hrs. Emergency Services ( Police, London Ambulance, London Fire Brigade and ERU)
CREATE TABLE tbl_incd(
INCIDENT_NO NUMBER ,
SD_DESC VARCHAR2(4000 BYTE)
)
data for the above table
Insert into tbl_incd ( INCIDENT_NO, SD_DESC)
Values
(70000031,
'E105 on entering into platform by one cars lenght applied emergency brakes to stop the train due to a person under his train. Train stopped with two cars still outside the platform. I received a call from Line Controllor at 0823 hrs that a person has jumped in front of train 105 at Greenford. I informed Ian Williamson Train Operations Manager. We traveled on a special taxi with a spare Train Operator at 0839hrs and reached Greenford Station at 0859hrs. Emergency Services ( Police, London Ambulance, London Fire Brigade and ERU) were already on site. When spoken to Line Controlllor stated he received a normal radio call from E105 to inform him that he has got a person under his train. At 0826 Line Controllor had traction current switched off between Northolt and Greenford. Train service was suspended from North Acton to West Ruislip in both directions (east and west).
I could not speak to the Train Operator as Police was in the process of interviewing him and he was too traumatised to speak. Trauma Support was arranged from Loughton as we had no one available at West Ruislip and White City. Trauma Support spoke to T/OP over the telephone and Train Operator decided to travel in a special taxi which was booked for him on his own, although a Station Staff was was arranged to travel with him. During the process a spare T/OP stayed with this T/OP. When spoken to Mr. Clack Operations Security Manager stated he was traveling on this train from West Ruislip and made a telephone call to Network Operations Centre at 0826hrs to inform them of the situation. Mr. Clack travelled to the leading cab and confirmed that although the T/OP was badly shaken but he carried out his duties in a professinal manner by placing Short Circuiting Devise in front of the train and second SCD was placed on the rear of the track by Station Supervisor after traction current was discharged. Passengers were detrained by operation butterfly cock. Les Blaxwall Operations Standards Manager was also travelling on this train and offered his to T/OP. Mr. Blaxwall assisted in the wrong direction move and stayed till the end of the operation to assist. Wrong Direction move was carried out by T/OP Martin to assist emergency services for the track search. The deceased was an elderly of Asian origin approximately of 65yrs of age wearing white shirt, beige trauser and white trainers. When CCTV was viewed he was seen pacing up and down on the east bound platform near the waiting room. On approach of T105 he jumped in front of the train at 0824hrs and train stopped with two cars outside the station. The body was removed from platform at 0940hrs by ERU and taken in a body bag to the secure room at Greenford Station. Track was accessed by BTP to carry out track search for any trace of identification at 0932hrs and gave all clear at 0942hrs.
TOM Williamson assisted in closing the train doors by operating the butterfly cock to close position. Traction Current was switched on at 0948hrs and T105 was worked empty to North Acton and reversed to Ruislip Depot. All clear was given at 0951hrs and after station search, Greenford Station was re opened at 0955hrs. E142 was the first train to run in service on the east bound. Body was removed at 1013hrs from Greenford Station and taken to Uxbridge by the police. Trauma Support was offered to Station supervisor by his DSM, but he declined the offer. Service Manager and Line Controllor were kept informed by myself.');
Insert into tbl_incd ( INCIDENT_NO,SD_DESC)
Values
( 70000032'PART OF TRAIN WITHDRAWAL Train 17 had a 6 min delayed departure from White City due to the train having a defective speedometer. Train was changed over and dud train was sent to Ruislip Depot for the attention of the DDMs staff. Ruislip DDM confirmed that the speedometer was defective. This was traced to a defective ATP controller.');
Insert into tbl_incd ( INCIDENT_NO, SD_DESC)
Values
( 70000033,' had a 1 min delayed arrival at Ealing Broadway due to the train having problems at signal EAB2058. The T/op stated that the train was in ATO and was stopped at the red signal (2058). The signal cleared and the train moved forward, the signal went straight back to red and the train stopped. This happened once again. The train did not pass the signal. T/op contacted the Line controller via radio and the signal was cleared, with the train proceeding into the platform at Ealing Broadway. The train was taken to Hainault for the attention of the DDM and a download. Train download and Tracker net confirm the T/op`s account of events. Due to the serious implications of events, an investigation is being undertaken by the Duty Engineer at Wood Lane. The T/op carried out his duties in a professional and correct manner and informed the passengers of the situation as and when required.');
COMMIT;
Given a table with some columns and data associated with that. Need to find out a column or a combination of some columns, so that the values or combination of values will be unique in the table.The table and number of columns and the columns will be dynamic.
View 10 Replies View RelatedQuote: I have a table(table name is names) with column as name(varchar) . I have the following data for name column.
Miss
Mississ
Mississipp
I would like to find a nearest match for Mississippi, that means sql should return row that contains Mississipp( Row #3)
If I try to find nearest match for Mississirr then sql should return row that has column value Mississ (Row#2)
Is this possible ? Here is the code for table creation and data.
create table names (name varchar2(20));
insert into names values('Miss');
insert into names values('Mississ');
insert into names values('Mississipp');
commit;
I have a table with structure as:
CREATE TABLE XML_TABLE_1
(
ID NUMBER NOT NULL,
SOURCE VARCHAR2(255 CHAR) NOT NULL,
XML_TEXT SYS.XMLTYPE,
CREATION_DATE TIMESTAMP(6) NOT NULL
[code].....
- So HOW do I find the total size occupied by this table. Does BINARY storage work as LOB storage. i.e. I need to consider USER_LOBS as well for this. OR foll. will work
select segment_name as tablename, sum(bytes/ (1024 * 1024 * 1024 )) as tablesize_in_GB
From dba_segments
where segment_name = 'XML_TABLE_1'
and OWNER = 'SCHEMANAME'
group by segment_name ;
- Also if I am copying it to another table of same structure as:
Insert /*+ append */ into XML_TABLE_2 Select * from XML_TABLE_1.
Then how much space in ROllbackSegment do I need. Is it equal to the size of the table XML_TABLE_1?
I was looking for application column name corresponding to "Draft Invoice Number" ra_interface_lines_all table.
I tried the below.
/* Get descriptive_flexfield_name for the application table name*/
SELECT *
FROM FND_DESCRIPTIVE_FLEXS_VL
WHERE application_id = 222
AND APPLICATION_TABLE_NAME=upper('ra_interface_lines_all');
/* Get the application column name and end user column name*/
SELECT *
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE application_id = 222
AND descriptive_flexfield_name = 'RA_INTERFACE_LINES';
There are many DESCRIPTIVE_FLEX_CONTEXT_CODEs obtained. I could finally trace out that draft invoice number corresponds to INTERFACE_LINE_ATTRIBUTE2. How can I know what DESCRIPTIVE_FLEX_CONTEXT_CODE should I look for?
I want to build a single query to fetch the application column name and flex field name for a specific table .
I have 20 tables. In all 20 tables, some of column names are same and some are different. I need to find all column names in all 20 tables that have same names.
create table t1 (
col1 varchar2(10),
col2 varchar2(10));
create table t2 (
col1 varchar2(10),
col3 varchar2(10));
create table t3 (
col1 varchar2(10));
I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically
ORG_UNITCOST_CENTERORG_UNIT_NAMEPARENT_ORG_UNITLLSYS_CONNECT_BY_PATH(ORG_UNIT,'/')
500171960000022000Managing Director - LUL500169965/00000001/50000001/50017588/50016996/50017196
500018370000021241FSO500171966/00000001/50000001/50017588/50016996/50017196/50001837
502894940000021241Knowledge Management500018377/00000001/50000001/50017588/50016996/50017196/50001837/50289494
508014980000021241Finance500018377/00000001/50000001/50017588/50016996/50017196/50001837/50801498
On my APEX page i have region which has sql query as source and it displays as HTML table the query result to the user.
I want to display addinonal column with a hyperlink inside, and that hyperlink would have CGI/URL-parameters which contains the other values of the HTML row.
So, let's say my APEX region queryes columns as "select c1, c2, c3, c4 ..." and displays out values "V1, V2, V3, V4" then i want to have addional output column with such hyperlink:
a href="f?p=100:7:13467554876288::NO::c1,c2,c3,c4:v1,v2,v3,v4">My link column with CGI-parameters</aHow can i create such hyperlink?
The overall idea is that the link would forward to a page which loads those values "v1,v2,v3,v4" into form fields and user can proceed from there.
---
"Application Express 4.2.1.00.08"