SQL & PL/SQL :: Find Non-relation Table And Relation Table?

Feb 7, 2012

Suppose i have to two tables.. emp and dept. Deptno column Foriegn for the both table. how to find the non relation table and relation table.

View 8 Replies


ADVERTISEMENT

Server Administration :: Data File / Table Relation

Feb 25, 2011

I have 2 schemas in my database with over 500 tables in each.i am trying to know which tables actually belong to which datafile in which tablespace.

View 2 Replies View Related

SQL & PL/SQL :: Relation Between Two Tables?

Dec 28, 2010

oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

I have a main table : CLAIMS_MAIN, in which claim_id is a primary key I have another table : CLAIMS_TRANS , in which claim_id is not a unique key, I mean, there can be more than one records with the same claim_id.. but the column transaction_id is unique, but transaction_id column doesnt exist in the main claims table.

It is required to join both these tables on claim_id...

View 1 Replies View Related

SQL & PL/SQL :: Foreign Key - Primary Key Relation

May 2, 2012

I am trying to establish PK-FK relationship between 2 tables. Table 1 has VACC_ID as a primary key. Table 2 has VACC_ID and SCH_ID as the primary keys. I am trying to add VACC_ID from Table 2 as a FK to Table 1. This is the code I used

ALTER TABLE Table_1
ADD CONSTRAINT FK_VACC_ID
FOREIGN KEY (VACC_ID)
REFERENCES Table_2(VACC_ID)

I also tried to add it without writing the query and editing the Table 1. By default, I am getting 2 columns(both VACC_ID) in the local column being referenced to the 2 primary keys(VACC_ID and SCH_ID) of table 2. I also uploaded the screeshot of the error, when I run the query.

View 4 Replies View Related

SQL & PL/SQL :: Update Hierarchical Relation

Nov 13, 2011

I have a table which contains the connectivity details. It is telecom data. The table contains the relation between the point features(Ex: Terminal) and linear features(Ex: Cable)

Below is some sample data

drop table connectivity ;
create table connectivity
(
ftr_no number(5),
ftr_id number(5),

[Code]...

-- 2nd linear ftr

insert into connectivity values (30,10359,33, 9655, 2,NULL,NULL);
insert into connectivity values (30,10359,33, 9656, 1,NULL,NULL);
insert into connectivity values (30,10359,30, 10359,3,NULL,NULL);

[Code]...

In the connectivity table PARENT_FTR_NO and PARENT_FTR_ID needs to be updated based on these conditions.

1. Correspponding to each feature there are 3 entries in this table.

2. Each feature(Point orLinear) has 2 sides INcoming and OUTgoing

3. The possible values for the RELATION column are 1, 2 and 3 where

-> 1 indicates feature conncted at the OUTgoing end
->2 indicates features connected at the INcoming end
->3 indicates self connected

4. Always a linear feature is the parent of a point feature which are connected to the OUTgoing end of the linear feature (i.e where RELATION = 1)

There is another table which contains FEATURE_NO and FEATURE_TYPE.

create table ftr_catgry
(
ftr_no number(5),
ftr_type char(1)
);

[Code]...

FTR_NO F
---------- -
33 P
31 L
30 L

Where
'L' represents linear feature
'P' represents point feature

The relation should be updated as

Step 1. Start with a linear feature, for example FTR_NO = 31

SQL> select * from connectivity where ftr_no = 31 and ftr_id = 10354 and relation = 1;

FTR_NO FTR_ID CONNECTED_FTR_NO CONNECTED_FTR_ID RELATION PARENT_FTR_NO PARENT_FTR_ID
---------- ---------- ---------------- ---------------- ---------- ------------- -------------
31 10354 33 9651 1

The feature connected to the OUTend of the 10354 i 9651 with FTR_NO = 33 and it is a point feature so the parent for 9651 is 10354

Step 2.

SQL> select * from connectivity where ftr_no = 33 and ftr_id = 9651 and relation = 1;

FTR_NO FTR_ID CONNECTED_FTR_NO CONNECTED_FTR_ID RELATION PARENT_FTR_NO PARENT_FTR_ID
---------- ---------- ---------------- ---------------- ---------- ------------- -------------
33 9651 33 9652 1

9652 is connected to 9651 at the OUTend, since 9651 is point feature so the parent of 9651 which is a linear feature is also the parent for 9652

Likewise the all relations have to be updated

Expected result after update

SQL> select * from connectivity;

FTR_NO FTR_ID CONNECTED_FTR_NO CONNECTED_FTR_ID RELATION PARENT_FTR_NO PARENT_FTR_ID
---------- ---------- ---------------- ---------------- ---------- ------------- -------------
31 10354 33 9650 2
31 10354 33 9651 13110354
31 10354 33 10354 3
33 9651 31 10354 2
33 9651 33 9652 13110354
33 9651 33 9651 3
33 9652 33 9651 2

[Code]...

24 rows selected.

View 2 Replies View Related

SQL & PL/SQL :: Parent Child Relation

Sep 17, 2013

I have one requirment like below

Table1 have records like below

parent childflag
null.....A......Y
A........B......Y
B........C
C........D
D........E......Y

I want it should be like

parent child flag
null......A.....Y
A.........B.....Y
B.........E.....Y

I want to replace 'D' (parent of 'E') with any of the upper level which has Flag 'Y' (first level parent which satisfy falg 'Y')

View 7 Replies View Related

Forms :: Master Child Relation For Non Control Blocks

Jul 8, 2011

I am working on form builder 6i. I have a header block A(Multi record block), child block B. Both are non control blocks. My requirement is, if i put the cursor in any header record, i want to auto populate corresponding child record like in master child relation.

which trigger i need to write logic to auto populate the child record.

View 4 Replies View Related

Forms :: Master / Detail Relation / Why Populate-details Trigger Not Being Fired

Mar 14, 2013

On a form I have 2 blocks, shown on different tabs. Relation created by forms wizard. The relation works great when navigating through the blocks, the details for the master are shown fine.

Example for this form is master block is a type of food. Detail block is list of ingredients.When creating a new type of food, if it's = CAKE we insert records based on a select on another table into the ingredients table.Else, the user has to manually create the ingredients.

Upon commit of the form on master block (must be saved to enable detail block tab), the ingredients are not displayed but are there. If I execute query by hitting F8, the records are present.

Why is the populate-details trigger not being fired or what am I missing?

View 4 Replies View Related

Server Administration :: Any Relation Of Tablespace Disk Space And Actual Data Present In Tables

Aug 16, 2012

We are using Oracle 10g and have 10 tablespaces defined for our Database which have 108 tables. Size of 108 tables is around 251 MB as seen during importing the dump. While creating these 10 tablespaces I used below parameters for allocation of space

SIZE 1M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 1M;

which set the initial space for 10 tablespaces to around 1032Kb each. Now my Question is after importing the dump , how the disk space for 10 tablespaces increases to 398 MB in total ?

Is there any relation of Tablespace disk space and Actual Data present in the tables ?

View 18 Replies View Related

Forms :: How To Create Relation Of Two Forms On 6i

Aug 9, 2010

when i create the buttons on the master form. if i click the button then open the second form

write us the code on 6i form and this form send to then i see him ok .

View 3 Replies View Related

How To Find Table Fragmentation

Jul 22, 2011

A query to find the table fragmentation.

View 1 Replies View Related

How To Find Table For Script

Aug 19, 2010

i have table like TWRCHAIN_WS_SUM. it is regularly updated by another object like procedure, function. how can i find which object is updating the particular table across the database.

View 4 Replies View Related

Unable To Find A Table?

Mar 22, 2011

why am i getting this?

SQL> SELECT TABLE_NAME,owner from dba_TABLES where TABLE_NAME in ('BSNL_ACCOUNT_DATA','BSNL_SERVICE_DATA','BSNL_PKG_DETAILS');

TABLE_NAME OWNER
------------------------------ ------------------------------
BSNL_ACCOUNT_DATA BPREAD
BSNL_SERVICE_DATA BPREAD

but the table exists

SQL> select count(*) from BPREAD.BSNL_PKG_DETAILS;

COUNT(*)
----------
3704

SQL> select TABLE_NAME,owner from dba_TABLES where TABLE_NAME='BSNL_PKG_DETAILS';

no rows selected

why is it showing no rows even if the table exists

View 3 Replies View Related

Find Out Row Count From Each Table?

May 6, 2009

I have written a script to get count of each tables of a schema.

DECLARE CURSOR c1 IS
select distinct table_name from all_tab_columns where OWNER='PLAY';
countrows NUMBER;

[Code].....

View 1 Replies View Related

Find Out Row Count From Each Table

Mar 31, 2009

SQL> select count(*) from tab;
COUNT(*)
----------
63

I need to find out how many rows in each table.I've tried with cursor.

View 9 Replies View Related

SQL & PL/SQL :: How To Find Table That Contains Certain Column

Nov 9, 2011

To find out column in which table it is located.

View 1 Replies View Related

SQL & PL/SQL :: How To Find Table And Column Name

May 14, 2013

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

SQL & PL/SQL :: Find Constraint Name Of A Table

Dec 24, 2010

I want to know constraint name of a particular table if it exists.Here are the queries that i tried to find the constraint name of a particular table but the results were 'no rows selected'.

Queries:

select constraint_name from user_cons_columns where table_name='x';
select constraint_name from user_constraints where table_name='x';

View 5 Replies View Related

SQL & PL/SQL :: How To Find Which Table Is Last Updated

Apr 11, 2012

I want to know that How to find which table got last updated and how to find last DDL and DML operation obtained in which table? here I know the table name

SQL> SELECT LAST_DDL_TIME FROM DBA_OBJECTS WHERE OBJECT_NAME='PREM';

LAST_DDL_
---------
20-MAR-12

SQL> TRUNCATE TABLE PREM;

Table truncated.

SQL> SELECT LAST_DDL_TIME FROM DBA_OBJECTS WHERE OBJECT_NAME='PREM';

LAST_DDL_
---------
10-APR-12

Note: With out enable the auditing I want to know that .

View 6 Replies View Related

SQL & PL/SQL :: Find Out Duplicate Name From Table?

Oct 28, 2010

i have a employee table .i want to find out duplicate name from this table.so i write following query

select empname from employee
having count(empname) >1
group by empname

i find the result but problem is how i can find out the same name if it have any space like ramkumar ram kumar both are same but above query did not.give this data. how i can solve it

View 16 Replies View Related

SQL & PL/SQL :: How To Find Out Wrong Value From Table

Jan 13, 2011

I've written the code (see below) and after run I get an error:

ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind

I don't know how to find out the wrong value from table. How to find it out?

DECLARE
TYPE rowids IS TABLE OF ROWID;
r1 rowids;
type t_varchar is table of varchar2(50);
n1 t_varchar ;

cursor c1 is select e.rowid rid,msisdn_displayed
from the_table
where contract_id is not null;
BEGIN
OPEN c1;
[code].........

View 3 Replies View Related

PL/SQL :: How To Find Max Date From A Table

Mar 2, 2013

I have two table in the database. I want to find the maximum routine date for a case_id when the lock_date is between 20 jan to 29 jan 2012 .

But I am not getting actual output (Output routine_date should be "28-jan-2012" but I m getting "31-jan-2012")

Master
case_id lock_date
101 23-jan-2012
101 24-jan-2012
102 27-jan-2012
102 29-jan-2012
101 30-jan-2012
101 29-jan-2012

Routine (for routine work)
case_id routine_date
101 23-jan-2012
103 28-jan-2012
102 21-jan-2012
102 29-jan-2012
101 21-jan-2012
101 28-jan-2012
101 31-jan-2012

select m.case_id, r.routine_date from master m, routine r,
(select case_id, max(routine_date) from routine group by case_id) rr

where m.case_id=r.case_id
and m.case_id=rr.case_id
and r.routine_date=rr.routine_date

View 7 Replies View Related

Find Special DML On A Table

Apr 12, 2013

i'm just trying to find out DML Statements, that where executed on a special table at a defined time. By googeling i just found a statement to find out the last DML, that was executed on a table or something else. But i want to look into the past. How can i do this?

View 16 Replies View Related

PL/SQL :: How To Find Sequence For A Table

May 14, 2013

I've got plenty of sequences. How can I identify that what are the sequences acting on a table?

View 8 Replies View Related

How To Find Last Inserted Record In A Table

Oct 5, 2010

I have a table where there is no sequence maintained and there is no time stamp column to track the inserted record.How do I find the latest inserted record in the table.

View 5 Replies View Related

Find DDL Statement Executed On A Table?

May 19, 2011

I want to know the DDL statements executed on a table. Example:

i have a table test1 with structure as below: test1 (aa number, bb varchar2(10))

After some period of time, one DDL statement to alter the column is issued.

alter table test1 modify (bb varchar2(30));

so new table structure is: test1(aa number, bb varchar2(30))

Basically i would like to find these 2 DDL statements executed on this table - test1, so that i compare which column get modified and what modification done.

Is this information stored in any data dictionary table?

note - audit, flashback option not enabled in this database.

View 3 Replies View Related

What SQL Command To Find Index Of A Table

May 14, 2007

1) how to find a primary key of a table in oracle, for example if I want to drop a index in table , how do I find what is the primary key in a table so that I can issue that command.

P/s: I don't have OEM installed so I must use SQL command

Example of dropping the index
ALTER TABLE table_name
drop CONSTRAINT constraint_name;

For example:

ALTER TABLE supplier
drop CONSTRAINT supplier_pk;

View 2 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

SQL & PL/SQL :: Find Out Base Table Of Synonym?

Jun 10, 2011

How to find out the base table of a synonym?

View 3 Replies View Related

SQL & PL/SQL :: Find Out Table Name Based On Column Name

Jul 28, 2011

i need an sql query or procedure to find out table name based on the column name..

View 4 Replies View Related







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