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 RepliesTo find out column in which table it is located.
View 1 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
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".
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 RelatedI 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 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?
I 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.
I 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.
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.
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
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;
Quote: 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 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) ?
A query to find the table fragmentation.
View 1 Replies View Relatedi 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 Relatedwhy 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
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].....
SQL> select count(*) from tab;
COUNT(*)
----------
63
I need to find out how many rows in each table.I've tried with cursor.
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';
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 .
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
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].........