SQL & PL/SQL :: Table Is Missing In User_segments Data Dictionary Table

May 20, 2012

In my database I found some anomalies like

SQL> select SEGMENT_NAME, SEGMENT_TYPE, BLOCKS, EXTENTS, BYTES/1024
2 from user_segments
3 where SEGMENT_TYPE='TABLE'
4 ORDER BY SEGMENT_NAMe;
[code]....

The countries table is missing in user_segments data dictionary view.But I can queries the countries using select statement.

SQL> SELECT * FROM COUNTRIES;

CO COUNTRY_NAME REGION_ID
-- ---------------------------------------- ----------
AR Argentina 2
AU Australia 3
BE Belgium 1
BR Brazil 2
[code]...

why the country table is missing in user_segments data dictionary table.

View 8 Replies


ADVERTISEMENT

Data Dictionary - Find All References Of A Table?

Sep 14, 2011

I wanna to DROP a table called EMPLOYEES, but when I execute the DROP TABLE EMPLOYEES, I get a ERROR saying that I cant do it because this table do reference to another table(s).

I tried to use the DBA_CONS_COLUMNS and DBA_CONSTRAINTS data tables, but its not enough to find it.

How can I find these references?

View 4 Replies View Related

PL/SQL :: Data Dictionary To See Foreign And Primary Key Of Table

Feb 17, 2013

which data dictionary should i choose to see the foreign key and primary key of table

View 2 Replies View Related

Find Missing Data From Parent Table?

Aug 8, 2013

SELECT c.table_name CHILD_TABLE, p.table_name PARENT_TABLE
FROM user_constraints p, user_constraints c
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
AND c.table_name = UPPER('ODS_TSAF_MES_PC');

and output is

child table parent table
ODS_TSAF_MES_PC ODS_TSAF_MES_PCTYP
ODS_TSAF_MES_PC ODS_TSAF_MES_PC
ODS_TSAF_MES_PC ODS_TSAF_MES_PCSTAT

i tried

SELECT A.piecestatus from ods_TSAF_MES_PCSTAT A WHERE NOT EXISTS
(SELECT * FROM ODS_TSAF_MES_PC B WHERE B.piecestatus = A.piecestatus);

and i found one piecestatus values is 'I' but i am not getting where it is related to the table and in which row it is getting affected?

View 6 Replies View Related

How To Make System Table Space Extent Management Dictionary

Dec 21, 2010

I want to create system table space's extent management dictionary with the syntax:

CODEcreate database
logfile
group 1 ('/u01/app/oradata/anand/redo1a.log') size 100M,
group 2 ('/u01/app/oradata/anand/redo2a.log') size 100M,
group 3 ('/u01/app/oradata/anand/redo3a.log') size 100M
datafile '/u01/app/oradata/anand/system.dbf' size 400M extent management dictionary
sysaux datafile '/u01/app/oradata/anand/sysaux.dbf' size 300M
default temporary tablespace temp tempfile '/u01/app/oradata/anand/temp.dbf' size 50M

but it is giving error
ERROR at line 6:
ORA-25141: invalid EXTENT MANAGEMENT clause

how can I make system tablespace's extent management dictionary?

View 3 Replies View Related

SQL & PL/SQL :: Creating Table - Missing Or Invalid Option Error

Jun 10, 2011

I am getting error while trying to create a table

SQL>
SQL> CREATE OR REPLACE TABLE CEE_OSPCM_DETAILS
2 (
3 ORD_NBR VARCHAR2(10) NOT NULL,
4 ORD_APPNDX NUMBER(2) NOT NULL,
5 FRCOI_NBR NUMBER,
6 PRINT_NBR VARCHAR2(4),

[code]....

View 3 Replies View Related

SQL & PL/SQL :: Creating Partition Table - Missing Or Invalid Option

Aug 4, 2011

I got a error while creating a partition table

I did following steps

SQL> show user
USER is "ROSE"
SQL>
SQL> create table sales (year number(4),
2 product varchar2(10),amt number(10,2))
3 partition by range (year)

[code]....

ORA-00922: missing or invalid option

SQL>
SQL> SELECT NAME FROM V$TABLESPACE;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS

[code]....

View 8 Replies View Related

PL/SQL :: Insert Results To Fact Table - Missing Right Parenthesis

Nov 14, 2013

im trying to insert these results to my fact table (fact_apartments) with the following: 

INSERT INTO fact_apartments(avg_price, segmentid, projectid)  
SELECT (avg(price), segmentid, projectid)    
FROM projectdetails      
WHERE projectid = '1'; 

but it return with the missing right parenthesis.

View 11 Replies View Related

SQL & PL/SQL :: Data Dictionary View Not Available

Nov 1, 2011

Question 1) I have read the following statement in a PL/SQL book.

Quote:To check whether an existing procedure is compiled for native execution or not, you can query the following data dictionary views:

[USER | ALL | DBA]_STORED_SETTINGS
[USER | ALL | DBA ]_PLSQL_OBJECTS

However, I when i query the view USER_PLSQL_OBJECTS I get the following error message:

Quote:ORA-00942: table or view does not exist

Question 2) I have read the PLSQL_WARNING can be set to DEFERRED at the system level. However, I am unable to defer it. tell me how to apply defer caluse to following statement:

Quote:ALTER SYSTEM SET PLSQL_WARNINGS ='DISABLE:ALL'

View 10 Replies View Related

Forms :: PDE-PLI011 A Tools Common Schema Table Missing From Object Store

May 26, 2011

while trying to open an exisitng Form, an Error message appears as:

PDE-PLI011 A tools common schema table is missing from the object store.

and failed to open. what's the problem ? what i have to do ? it was working fine before.

View 2 Replies View Related

SQL & PL/SQL :: Can Modify Oracle Data Dictionary

Feb 6, 2011

can i modify oracle data dictionary

View 6 Replies View Related

SQL & PL/SQL :: DML Sentences Role In Data Dictionary?

May 21, 2012

Changing Data dictionary by DML Sentences (e.g Insert, Delete, Update,...), Will be possible? Or just by DDL Sentences?

View -1 Replies View Related

List Only Date Columns In Data Dictionary / Views

Sep 6, 2012

Oracle version : 11.2.0.3

Some DBA_views are huge with lots of columns ; a DESC command's output becomes less readable ; example DESC command's output of V$SESSION view.

Lets say I want to see only the date columns in a DBA_view or V$view ; How can I do that in a DESC like output or similair ?

View 2 Replies View Related

Performance Tuning :: Data Dictionary Cache - Dc_objects

Jul 4, 2011

In order to improve the performance of our live server, I am trying to do an exhaustive comparison with our test environment which is quite quick in spite of the fact that we port the data from Live every month.

There are no obviously slow queries appearing in the the top SQLs of AWR, we have optimised such things already. Right now it is about general uplift rather than SQL based tuning.

I picked up random SQLs and I noticed a marked differences in the execution time. Typically they are 3 to 4 times and there are cases much more than that.

1. I observed that, while the explain plan of the queries are same, trace of the queries give a different picture. I have observed that the recursive calls, consistent gets and sorts(memory) are quite high on Live.
2. I have no solid reasons to say this but my instincts tell me that the recursive calls is the major contributing factor. It is sometimes 2000+ for an SQL.
3. On googling more on that, it finally made me compare the data dictionary on the AWR report of test and Live.

The dc_objects caught my eyes. In that 4 hour AWR, there were about 10 million get requests and the pct miss was ~10. For similar load, the test server had 5 million gets with 0.08 PCT miss for 4 hours.

View 27 Replies View Related

Server Utilities :: Geometric Data From Text To Table And Wrong CTL Upload Into Table

Jul 11, 2013

I have a requirement to import text files which are generated from 3d modelling software xsteel where it records all geometric information and i want to import this information into oracle table.

CREATE TABLE dstv_head ( wo_no VARCHAR2(12),struct VARCHAR2(12),rev_no NUMBER,
mark VARCHAR2(12),pos VARCHAR2(12),grade VARCHAR2(12),qty NUMBER,PROFILE VARCHAR2(24),TYPE VARCHAR2(12),
len NUMBER,width_web NUMBER,width_bottom NUMBER,flange_thk NUMBER,web_thk NUMBER,radius NUMBER,kgm NUMBER,
kgm1 NUMBER,kgm2 NUMBER,bevel_plus NUMBER,bevel_minus NUMBER,holes_yn VARCHAR2(1),holes_v_yn VARCHAR2(1),
hole_x_dim NUMBER,hole_y_dim NUMBER,hole_dia NUMBER,no_of_holes NUMBER)

-- All the data which has to go under specific field for example **9005.nc1 will go into wo_no field, 1239401A will go under struct.

ST
** 9005.nc1 --WO_NO
1239401A - STRUCT
1 -REV_NO
9005 -MARK
9005 --POS
S275JR --GRADE
2 --QTY
[code]....

View 24 Replies View Related

SQL & PL/SQL :: Regular Table Or Materialized View - Clone Table Data In Another Database

Jul 19, 2010

There is a requirement to make a table data in a database (eg: HR database) available in another database (eg: EMP database), instead of accessing it using database link. In EMP database(where data needs to be cloned), data will only be queried and no write operation will be done. Data in remote database (eg: HR DATABASE) will be occassionally fully truncated and reinserted. The plan is to do a similar truncate and reinsert of data (from HR database) into EMP database monthly once using dbms scheduler job. So basically data in just one table needs to be cloned in another database.

Question: For this situation, is a regular table or Materialized view the right choice to clone the table in EMP database and why? The table in HR database (remote database) is not very big.

View 19 Replies View Related

Server Administration :: Possible To Write Triggers On Data Dictionary Tables And Views

Sep 30, 2011

Is it possible to create trigger on the various tables and views exists (i.e. dynamic performance views) in data dictionary, when ever any DML operations performs by Oracle it self?

View 6 Replies View Related

SQL & PL/SQL :: Pull In Last Loaded Data From Staging Table To Target Table?

Mar 9, 2011

I have a staging table and a target table. How do I pull in last loaded data from staging table to target table?

View 4 Replies View Related

Forms :: Read Data From Table And Insert To Another Table With A Cursor?

Feb 20, 2013

I have a table with a BLOB column ;

I want read data from table and insert to another table with a cursor

My code is :

procedure read_data is
cursor get_data is
select id,image from picture1;
id1 number;
pic blob;
begin
open get_data;

[code]....

when I run form , error FRM-40734 occurred

error in line " fetch .... "

View 1 Replies View Related

SQL & PL/SQL :: Select Data From Test-1 Table Where ID Values In Table Exists In 2?

Aug 31, 2010

I have the below data in table test_1.

select * from test_1
IDNameTotal
-----------
1A100
2B100
3C100
4D100

test_2 table contains the concatination of ID's with comma seperated. Actually in this table ID column is of datatype varchar2.
select * from test_2
ID
----
1,2,3

My requirement is to select the data from test_1 table where the id values in this table exists in test_2 table. I tried with the belowselect statement, but could not get any data.

SELECT * FROM test_1 WHERE to_char(id) IN (SELECT id FROM test_2)

create table test_1 (id number, name varchar2(100), total number)
create table test_2(id varchar2(100))
insert into test_1 values (1,'A',100)
insert into test_1 values (2,'B',100)
insert into test_1 values (3,'C',100)
insert into test_1 values (4,'D',100)

View 4 Replies View Related

SQL & PL/SQL :: Inserting Data In Target Table From Source Table

Nov 22, 2012

I need to insert data in Table A from Table B where most of the fields are identical and might some of the fields will be more in Table A.

ex: Table A: a,b,c,d,e,f
Table B: a.b,c,g,h

How to insert this using user_tab_columns in cursor and if I am giving the i/P as my table names . This needs to be configurable and reusable rather i mention all the fields in my logic.

View 4 Replies View Related

SQL & PL/SQL :: How To Join A Table Column Names With Data From Other Table

Jul 18, 2012

I am trying to join column names from a table with data from a different table. I think i should be able to pass the parameter to a 'select list' in a query. Look at my sample data below. And the data in sales table can grow till 15 rows and similarly corresponding columns in saleshist.

CREATE TABLE SALESHIST
(
PRODUCT VARCHAR2(30 BYTE),
Q1 VARCHAR2(30),
Q2 VARCHAR2(30),
Q3 VARCHAR2(30),
Q4 VARCHAR2(30)
)
[code]......

View 6 Replies View Related

PL/SQL :: Load Data From Index By Table To Table

Jun 27, 2013

We need to load data from index by table to table.Below code is working fine. 

declare
query varchar2(200);
Type l_emp is TABLE OF emp%rowtype INDEX BY Binary_Integer;
rec_1 l_emp;
begin

[Code]....

But data from source table and target table is dynamic.Ex:In above code, emp(source) and target table is emp_b are static. But for our scenario is depends on the source table , target would change as below.If source is emp then target is emp_bIf source is emp1 then target is emp_b1 ............ 

create or replace procedure p(source in varchar2, target in varchar2)
as
query varchar2(200);
source varchar2(200);
Type l_emp is TABLE OF emp%rowtype INDEX BY Binary_Integer;
rec_1 l_emp;

[Code]....

Its throwing. How to implement this scenario .

View 2 Replies View Related

Moving Data From Table To Table

Dec 13, 2010

I need to move data from non-partinioned table to partitioned. The volume is about 60 millions rows. What is the fastest way to do that? I think about pareller insert and nologging. What do you think about this? May data pump be faster?

View 4 Replies View Related

SQL & PL/SQL :: Data Migration From Table To Table?

Nov 18, 2010

I Have two DB s with same tables, I need to insert the data from db1 tables to db2 tables. Primary ID starts from 1 on both the tables.

EXample:

Table1:
ID Name c3 c4 c5
1 Oracle1 x y b
2 Oracle2 x n b
3 Oracle3 x f b
4 Oracle4 x f b
5 Oracle5 x f b

Table2:

ID Name c3 c4 c5
1 ONT1 t y t
2 ONT2 t m h
3 ONT2 t b n

All the table1 data should be moved to table2..... Desired out put should be like following

ID Name c3 c4 c5
1 ONT1 t y t
2 ONT2 t m h
3 ONT2 t b n
4 Oracle1 x y b
5 Oracle2 x n b
6 Oracle3 x f b
7 Oracle4 x f b
8 Oracle5 x f b

Data volume is huge... There are 1500 tables and on an average there 10 Million records per table. Currently I am using DB links

View 2 Replies View Related

PL/SQL :: Moving Data Into From One Table To Another Table

Sep 2, 2013

 I have a table called Daily_usage (only 1 day data) which contains daily transaction records. After a day I have to move this data to another table named Daily_30days_usage table. this table contained the 30 days data. After 30 days the 31st day data should be deleted from Daily_30days_usage table. 

How can I implement this requirement without INSERT statement?

View 4 Replies View Related

Export/Import/SQL Loader :: Import Table Without Messing Up Existing Data In Table

Sep 6, 2012

table already exist & its little data too, may have to imp rest of lost data, is this the right command?

imp SYSTEM/password FILE=file.dmp FROMUSER=black TOUSER=blake TABLES=(vcr_mappings, tablename2) ignore=Y CONSTRAINTS=n

scenerio2 (if have to drop & recreate the entire table) is this the right command?

imp SYSTEM/password FILE=file.dmp FROMUSER=black TOUSER=blake TABLES=(vcr_mappings, tablename2) ignore=Y

just for single table imp

View 2 Replies View Related

SQL & PL/SQL :: Missing Data

Nov 8, 2012

i ahave a table with below

01-oct-12,ARTACT001,Memory
04-oct-12,ARTACT001,Memory
01-oct-12,ARTACT001,OS
05-oct-12,ARTBRIO,CPU

Required output as below which dates are not available my table

02-oct-12,ARTACT001,Memory
03-oct-12,ARTACT001,Memory
01-oct-12,ARTACT001,OS
02-oct-12,ARTACT001,OS

[Code]...

View 5 Replies View Related

PL/SQL :: Oracle Sql To Find Missing Data?

Aug 10, 2012

Database version

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for HPUX: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

I have this query

select dept_id,qc_subtype_id,equip_code,drive_id
from
(select distinct dept_id,
decode(qc_subtype_id,

[code]...

View 3 Replies View Related

PL/SQL :: Closest Date Missing Data

Jul 19, 2012

My requirement is to find the closest date from col B(a.p_date) to the dates in col A(d.p_date). I got the following which works great:

SELECT
d.p_no_s,
d.p_date,
MIN(a.p_date),
MIN(a.p_date)-d.p_date||' Number of Days' NUM_OF_DAYS

[Code]...

Output:

p_no_s p_date MIN(a.p_date) MIN(a.p_date)-d.p
-------------------- ---------- ---------- -------------------------------------------------------

Z1575560 15/06/2008 29/07/2008 44 Number of Days
Z1575560 15/07/2008 29/07/2008 14 Number of Days
Z1575560 21/11/2008 27/12/2008 36 Number of Days
Z1575560 17/12/2008 27/12/2008 10 Number of Days

Problem:

For 1st and 2nd row,

I am getting 29/07/2008 as a.p_date for both 15/06/2008 and 15/07/2008 which is wrong in my scenario. This is because data is missing in the second table for row 1 (similarly for row 3). So What I want is :

Z1575560 15/06/2008
Z1575560 15/07/2008 29/07/2008 14 Number of Days
Z1575560 21/11/2008
Z1575560 17/12/2008 27/12/2008 10 Number of Days

Say for eg: a person is in city A. He is departing on (15/06/2008 ) and arriving on 29/07/2008 and again departing on 15/07/2008 which is not possible.

It should be departing on (15/06/2008 ) and arriving between 15/06/2008 and 15/07/2008 (missing data hence null is required here) and departing on 15/07/2008 and arriving on 29/07/2008 .

let me know how to achieve this.

View 4 Replies View Related







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