SQL & PL/SQL :: ORA-14652 / Reference Partitioning FOREIGN KEY Not Supported

Mar 30, 2011

I am trying to use reference partitioning the parent table records moved using the row movement . but the child table records are not moving below is my sample .

CREATE TABLE ref_parent1 (
table_name VARCHAR2(30),
order_date DATE,
num_rows NUMBER);
INSERT INTO ref_parent1 VALUES ('TEST',SYSDATE,100);
INSERT INTO ref_parent1 VALUES ('TEST1',SYSDATE,1000);
INSERT INTO ref_parent1 VALUES ('TEST2',SYSDATE,1000);
COMMIT;

[code]....

ORA-14652: reference partitioning FOREIGN KEY IS NOT supported

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Add Constraint Foreign Key Reference With PK Field

Mar 6, 2013

I've successfully created the below two tables (disregard my use of "char" variable type; beyond the scope of my question):

create table Test_req (
Req_ID varchar2(4) not null,
Req_Comment varchar2(50) null
);
create table Test_n_cycle (
Req_ID varchar2(4) not null,

[code].....

Then I successfully performed the two alter statements:

Alter table test_n_cycle
add constraint test_n_cycle_pk primary key (req_id, test_id);
Alter table test_req
add constraint test_req_pk primary key (req_id);

Then tried this freign key reference statement

Alter table test_n_cycle
add constraint
foreign key (req_id) references test_req (req_id)
Error report:
SQL Error: ORA-00902: invalid datatype
00902. 00000 - "invalid datatype"

Is this because req_id is also defined in the Primary key definitions on these two tables, thus making the field not valid for referential constraint definition?

View 3 Replies View Related

Forms :: Table B Has Foreign Key Reference To Table A

Jan 11, 2012

There are 2 tables : Table A and Table B.Table B has foreign key reference to Table A.There are 2 forms in the application based on table A (form 1) and table B (form 2).

Now when we open form 2, the functionality is such that it acquires a lock on table B for the selected record during the search criteria. Lock is acquired by using "select 1 from table_B where column = :column for update no wait".So when the form 2 is opened by any other user/session and same record is tried to be selected, then an exception is raised to the user that the current record is being edited by some other user and does not allow him to edit that record.

Now imagine if User has opened screen 2 (One record in Table B has been locked). With this lock existing, we open form 1, and click a button which performs a COMMIT_FORM. At this point the form hangs. On checking the locked objects, there is a lock on both table A and table B. When the Form 2 is closed, then the Form 1 which was hanging a while ago starts responding.

When the foreign key relationship is dropped and the above scenario is tried, there is no issue encountered. Form 1 works fine even if form 2 is open.We are not supposed to drop the Foreign key relationship as well.

View 5 Replies View Related

SQL & PL/SQL :: Primary And Foreign Key On Same Column And Foreign Key Index

Aug 25, 2010

Create table Car
(Car_cd VARCHAR2(5),
Car_Desc VARCHAR2(50)
);
alter table Car
add constraint Car_PK primary key (Car_CD);
INSERT INTO Car (Car_Cd, Car_Desc) VALUES ('CORLA','COROLLA');
Commit;
[code]....

The requirement necessitates a new table to map car to manufacturer. This mapping table may later be expanded to contain other attributes Engine, MPG, etc to hold details specific to a car.But this is in future.

Create Table Car_Mapping_Details
(Car_Cd VARCHAR2(5),
Manufacturer_Cd VARCHAR2(5));

--Primary Key Constraint
ALTER TABLE Car_Mapping_Details
ADD CONSTRAINT Car_Mapping_Details_pk PRIMARY KEY (Car_Cd );

--Not able to create this as Car_cd is already a PK in this table and therefore has Unique Index
ALTER TABLE Car_Mapping_Details ADD CONSTRAINT Car_Mapping_Details_fk1
FOREIGN KEY (Car_Cd)REFERENCES Car (Car_Cd);
[code]....

But in this case the Car_Mapping_Details.Car_cd is itself is a primary key and therefore has Unique index.Although I was able to create foreign key constraint on Car_mapping_details.car_cd column (which is also Primary Key), I was not able to create Foreign Key Index on this column. It gives me Quote:ORA-01408: such column list already indexed.In other words, not creating foreign index for foreign key column lead to table-level lock? Or will the Unique Index in that primary key column be sufficient to avoid table-level lock?

View 2 Replies View Related

SQL & PL/SQL :: How To Transfer Data From Non-partitioning Table To Partitioning Table

Apr 4, 2013

I have problem to transfer data in non partitioning table to partitioning table.

I have non partitioning table and i create new table partitioning that have same column and type like in non partitioning. So how can i transfer data from table in non partitioning to table in partitioning?

View 10 Replies View Related

Distributed Operation Not Supported

Jun 12, 2013

I am using the dblink to merge the data. I am using the following merge statement.

merge into APP_USER.USR_NEW_RIGHTS@NEW_RIGHTS t
Using (select 'test' GRANTEE,'TESTxxx'ROLE from dual ) s
on (t.GRANTEE = s.GRANTEE and t.ROLE = s.ROLE)
when not matched then
insert (ID,GRANTEE,ROLE,XRIGHT,COMPANY,OWNER,TABLENAME)
values ('','test','TESTxxx',null, null, null, null);

I know that I have to set a commit and it's working when I insert information's with a normal insert statement via database link, but it seems that merging doesn't work.

View 1 Replies View Related

What Type Of Storage Is Supported By ASM

May 25, 2012

what type of storage is supported by ASM? I know of RAW storage but does it run on something else?

We are running on Power7, AIX 6.1.

View 1 Replies View Related

Maximum Tables Supported

Jan 3, 2013

We are having a production database configured in oracle 10.2.0.4 ( standard edition) and its contains near to 50000 tables . The database is accumulated by more than 100 tables everyday, and my question is is there is any table limits in oracle database ? especially in standard edition ??

View 8 Replies View Related

How To Determine Number Of TPS Supported On Solaris

Mar 24, 2013

How to calculate the number of TPS supported by any solaris server for example one server with below configuration .

Sun Blade X6270 with two 4-core processors
2 x 300 GB internal disk drives

View 1 Replies View Related

Forms :: Reports 4.5 Supported On Database 9i

Feb 29, 2008

IS Oracle Forms/Reports 4.5 supported on database 9i?

My application is running on Form/Reprots 4.5 and 7.3.4 database in WIN2003 server. we have upgraded the database to 9i Release 2. After the upgrade terminate, i become unable to connect to my database from my application forms 4.5.

TNSNAME is OK.
Listener is OK.

Can i connect from Forms/Reports 4.5 to 9i Release 2 Oracle database?

View 28 Replies View Related

Forms :: Plugin Is Not Supported Error

Jul 14, 2013

I am having a problem in the process of run the Form on web browser.We learned builder Use the Oracle 10g and Oracle Developer Suite 10g

View 6 Replies View Related

SQL & PL/SQL :: ORA-02064 - Distributed Operation Not Supported?

Apr 20, 2011

When I try to call a database procedure written in Oracle 8.1.7.4.0 with OUT parameter and COMMIT statement from my Oracle 10g environment, I am getting error like "ORA-02064: distributed operation not supported".

I cannot omit OUT/COMMIT statement from the procedure because it is also updating another table from called procedure. I have tried some solutions from my end, but it is not working and same error generating. Like:

1) Moved the update statement with COMMIT statement to another procedure and calling that procedure from main called procedure
2) Creating a job to run the newly created procedure and submit the job from called procedureetc.

View -1 Replies View Related

Forms :: Supported IE Browser Version For 10g

Oct 9, 2013

Currently i am using IE 6 for running my forms 10g, I like to know upto which IE Versions i can update so it must not affect my Oracle forms 10g running.

i like to update to IE 8, but it must not affect my forms runtime functionality!

View 3 Replies View Related

RAC & Failsafe :: Is Oracle 11G Supported On VMWARE

Jan 17, 2012

Is Oracle Failsafe 11G supported on VMWARE, we plan to create a cluster of two vm servers, is it a supported configuration?

View 4 Replies View Related

Forms :: Reports 4.5 Supported On Database 10g

Mar 11, 2010

Does Oracle Forms/Reports 4.5 supported on database 10g? We have legacy application in Forms4.5 we are planning to migrate backend from 9i to 10g.

View 7 Replies View Related

ODP.NET :: Is Failover Supported In Managed Driver

Nov 7, 2013

I can see that some failover events etc aren't supported (Differences between the ODP.NET Managed Driver and Unmanaged Driver), but is failover supported at all? I don't need to get notified, just to get the failover (clustered node switching) working. Is it supported in the managed driver?

View 1 Replies View Related

Networking And Gateways :: How Many Listener Supported In One Database

May 30, 2010

How many listener we can have in one database? How many users can be support by one listener?

View 3 Replies View Related

Performance Tuning :: AWR Report In 11g Standard Not Supported?

Jan 15, 2011

I was trying to generate AWR report, but the report which got generated consist most of the sections without data. Later i came to know that AWR report is not fully supported in 11g? Is that true?

View 6 Replies View Related

Forms :: ORA-12703 Character Set Conversion Is Not Supported

Jan 29, 2013

There are two application servers we have, one is windows based with 10g and linux based 11gr2. Our main login form throwing error message in linux server 'ora-12703 this character set conversion is not supported'. The same coding form in 10g running without any problem. Both application servers accessing the same oracle db server 11gr2.

View 3 Replies View Related

SQL & PL/SQL :: Version Of Java Supported By Oracle Database 11g And 10g

Feb 22, 2011

what version of java is supported by Oracle Database 10g and 11g. Actually i am writing a User Defined Function in Java and was asking this question to know which version of java i need to compile the source file before uploading the jar.

View 2 Replies View Related

What Object Oriented Extensions Are Supported By Oracle

Jan 13, 2007

"What object oriented extensions are supported by oracle?"

View 1 Replies View Related

SQL & PL/SQL :: Expression Not Supported Error For Query Rewrite

Aug 13, 2013

I am trying to create a materialized view with the following script :

CREATE MATERIALIZED VIEW "MRT"."MV_RV_SMALL_BUSINESS"
(
"batch_id"
,"small_business_flag"
,"account_count"
,"naics_count"
,"mra_count"
[code].......

IT GIVES ME THE FOLLOWING ERROR:

SQL Error: ORA-30353: expression not supported for query rewrite
30353. 00000 - "expression not supported for query rewrite"
*Cause: The select clause referenced UID, USER, ROWNUM, SYSDATE,
CURRENT_TIMESTAMP, MAXVALUE, a sequence number, a bind variable,
correlation variable, a set result,a trigger return variable, a
parallel table queue column, collection iterator, etc.

View 2 Replies View Related

SQL & PL/SQL :: FORALL INSERT Not Supported On Remote Tables?

Nov 4, 2012

how to transfer a large amount of data from remote table by db link into the local table.

I try to do that by plsq below but I got error:

PLS-00739: FORALL INSERT/UPDATE/DELETE not supported on remote tables

DECLARE
type t_varchar is table of varchar2(100);
l_data t_varchar ;
CURSOR r IS
SELECT id
FROM TMP_MAPE_WEB_ID;
BEGIN

[code]....

View 6 Replies View Related

Installation :: Database 10.2.0.3.0 Supported Redhat Linux 64 Bit OS?

Oct 2, 2012

We have 3 databases with version 10.2.0.3.0 running on RHEL AS 4.5 32 bit operating system. We are planning to move these databases to RHEL 64 bit Operating system.

let me know the highest 64 bit RHEL version supported by these databases(10.2.0.3). Will it support with RHEL 5.5 64 bit version?

View 7 Replies View Related

How To Practice Partitioning

Sep 20, 2012

I want to practice partitioning, I have schema which has sales table but that is already partitioned. I want to know if there are some schema available for download which has non partitioned tables and records more than 10000.

View 8 Replies View Related

Index On Partitioning Key

Mar 13, 2012

There is a very large fact table that is range partitioned by a column DATE_KEY of type NUMBER(38), such that every hour is a different partition. There is a bitmap index BX$FACT#DATE_KEY on field DATE_KEY, which also is a foreign key referencing DATE_DIM (DATE_KEY). There is a different DATE_KEY for every hour, generated as YYYYMMDDHH24.

When I run

"SELECT * FROM FACT WHERE DATE_KEY >= 2012031207 AND DATE_KEY < 2012031208"

to get all the records for 7 am on March 12th, partition pruning kicks in and sees that only one partition is used. The CBO then decides to do a full scan of the partition. This behavior is correct/desired.

If, however, I run

"SELECT * FROM FACT WHERE DATE_KEY = 2012031207 AND DATE_KEY < 2012031209"

to get all the records for 7 and 8 am, Oracle knows that it will have to scan two partitions. The CBO then decides that using the BX$FACT#DATE_KEY must be a good idea and, instead of doing a full scan of the partitions, does access by local index rowid, which is many times slower.

I think I understand the cause - when more than a single partition is involved, Oracle has to use the global index stats (instead of the local ones, like in the first scenario) and the CBO decides to use it because that the selectivity for the global index is great, when in fact the query will return all the rows for that particular partition (no selectivity).

How to get the CBO to choose a full scan in the second scenario as well? I need to support ad-hoc queries generated by a BI tool, so I cannot add hints to the queries. I also can't get rid of the index on DATE_KEY, because in real life the predicates are on fields of the date dimension, not directly on the key, so I need to join on it.

View 1 Replies View Related

PL/SQL :: Partitioning A Table?

Jul 27, 2012

How to partition a table which is already having data.I have a STUDENT table along with following fields which is having million of rows.

studentid name class Gender

Now I want to partition this table based on gender MALE and FEMALE.

View 8 Replies View Related

Get Table Partitioning?

Sep 23, 2013

I have a partitioned table with 1 lakh records

if i disable the partition feature im my database will it affect my table data.

View 1 Replies View Related

Server Administration :: Are Parallel Queries Supported In Oracle XE

Nov 18, 2010

I'm not sure is parallel query supported in xe ?

View 2 Replies View Related

Exadata :: Find ACFS Supported But Is Not Mentioned For DBFS

Apr 23, 2013

Does RMAN backupset (backup to ) on DBFS is supported. I can find ACFS supported but is not mentioned for DBFS. My current customer is thinking backup to DBFS then copy to tape as the interim solution before getting ZFS next year.

View 5 Replies View Related







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