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 Repliesif a table contains two columns and both are part of the primary key of that table (Kind of obvoius).
should i opt for a index organized tbale in this case ?Or should i opt for another running sequential colum which would serve as a primary key of this table and define the actual two columns of the system as unique keys.
there is a drawback if a most of the tables of a database contain composite primary keys?
To 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 RelatedHow to find out the required column from user tables using query ?
View 6 Replies View RelatedI 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
trying to update a column in a table which has 3 columns of 16million rows from column in another table which has 1million rows, there is no relationship between the 2 tables.
Table A has 3 columns of 16million rows, the first two columns have 16million ID numbers, the 3rd colunm is currently NULL.
Table B has 1million Numbers, i need to somehow update column 3 in table A using the numbers in table B, it doesnt how many times each of the 1 million numbers are used but i dont want it to just update every row to the same value.
How can I delete the duplicate combination of records from the below table.
CREATE TABLE test
(
gidNUMBER(10),
pidNUMBER(10)
);
INSERT INTO test VALUES (10,20);
INSERT INTO test VALUES (20,10);
INSERT INTO test VALUES (25,46);
[code]....
The condition is if GID = PID and PID = GID then only one combination of these records should be retained. For example Out of 10-20 and 20-10 only one record should be retained.
Expected result after deletion
GID PID
---------- ----------
10 20
25 46
89 64
15 16
19 26
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);
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 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 .
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
[Code]...
How to find in the oracle10g database if there is any column which is not being used at all.If the data is there in the column,how to check when was it last populated.
View 3 Replies View RelatedI need a query to find list all tables in a schema which does not have 'ADDRESS', 'CITY', 'STATE' columns.
View 8 Replies View RelatedI have a table with two columns Column1 and column2
Like such
create table testTable (column1 number(15), column2 number(15));
insert into testTable values (1,5);I need to find the numbers between column 1 and column 2 including the column 1's number and column's 2 number.
so my answer set should be
1,2,3,4,5
Suppose i have two columns ID and Status (or any number of columns ) belongs to table customer. Now I want to create below index
Ex. create index test1 on customer (ID , Status )
But before creating this index i want to check that whether there is already index on these 2 columns ? May be by other name ?
I have a two question.
Question 1:How to select all columns from table except those columns which i type in query
Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data
I have three tables,let's say
table stores
sid | store_name
1 | one
2 | two
3 | three
table products
pid | sid | p_name
1 | 2 | pone
2 | 2 | ptwo
3 | 3 | pthree
table sales
said | sid | pid
1 | 2 | 1
2 | 3 | 1
3 | 2 | 2
4 | 1 | 3
5 | 2 | 2
6 | 3 | 2
7 | 3 | 2
and i want display the product that sells best in every store. I try to group by multiple columns counting how many times each product was sold in every store, but don't know how to select the one which was best sold (maximal number of times)
is there a way to find out which unused columns in oracle?
View 8 Replies View Relatedquery to find out common data from 2 columns in two different tables??
View 6 Replies View RelatedI need query to find the missing dates between two columns in given month.
CREATE TABLE emp_shift (
empno NUMBER(4),
fr_date DATE,
TO_DATE DATE,
shift VARCHAR2(1));
CREATE TABLE emp (
empno NUMBER(4)
);
[code].....
Required output is
MISSING_DATES EMPNO
---------------------- ----------
09-SEP-12 TO 11-SEP-12 7499
23-SEP-12 TO 26-SEP-12 7499
01-sep-12 TO 30-SEP-12 7521
01-sep-12 TO 30-SEP-12 7788
How to find non-indexed queries and columns that require indexes?
View 3 Replies View RelatedI just ponder how to write SQL or PLSQ if I'd like to get more columns into the one column.
For example:
I have table T1 where are 10 records. One columns TEXT is VARCHAR2. And I need to get by SQL/PLSQL one record with the new column VARCHAR2 where will be written 3 records as I wrote.
select TEXT from T1:
result: aa
bb
cc
And the desire result should lool like:
aa bb cc
how to quickly do it?
I have a requirement in this format.
These are the scripts for the 2 input tables
CREATE TABLE TMP_split
AS
SELECT 1000 AS grp_id, 'abc' AS ATTRIB, 60 AS PCT FROM DUAL
UNION ALL
SELECT 1000 AS grp_id, 'pqr' AS ATTRIB, 40 AS PCT FROM DUAL;
[Code].....
The output needs to be in this format
key | val | grp_id | pct
------------------------------------------
6~7~8 |DIM_11~DIM21~DIM31 | 1000 | 60
6~7~8 |DIM_11~DIM21~DIM31 | 1000 | 40
6~7~8 |DIM_11~DIM22~DIM31 | 1000 | 60
6~7~8 |DIM_11~DIM22~DIM31 | 1000 | 40
6~7~8 |DIM_11~DIM23~DIM31 | 1000 | 60
6~7~8 |DIM_11~DIM23~DIM31 | 1000 | 40
Is there a good way to dynamically obtain this output either by PL/SQL or by query?
This is a case where combination needs to be used. However, I am not able to arrive at the right algorithm for this
I am able to get a list of all the duplicate location_code values.
select count(*), location_code
from ZZPN_PARKING_PORTFOLIO_801_V
group by location_code
having count(*) > 1
But how can I select all the other columns from ZZPN_PARKING_PORTFOLIO_801_V where the location_codes are duplicated.
select * from apps.ZZPN_PARKING_PORTFOLIO_801_V
where location_code in
(
select count(*), location_code
from apps.ZZPN_PARKING_PORTFOLIO_801_V
group by location_code
having count(*) > 1
)
order by location_code
my need is to perform merge - update when id column is matched, but one of others columns not.When id column is not matched then I perform insert.
It works fine for matched or not matched id column.
Commented code is my try to perform check for others columns, The code should not update when all columns match. It should update only when on of columns doesn't match (except id column of course, because it's key column).
begin
merge into copy.table1 rr
using
(
select
ID ,
DEALID ,
ESTIMATIONDATE ,
BOUNDOVERESTIMATDATE ,
ESTIMATIONTYPEID ,
MARKETAMOUNT ,
LIQUIDATINGAMOUNT ,
[code]....
I want to add some new columns in an existing table that has over 10 millions rows, but not at the end of existing columns, my requirement is such i want new columns between existing existing columns. Is it possible.....
View 16 Replies View Related