SQL & PL/SQL :: Import With A Default Constraint On Table?

May 28, 2012

I have a table as below. This table is not partitioned.

create table t1
(
d1 date,
n1 number not null
);

[Code]....

I took an export dump of the above table and after that I renamed the table t1 to t1_old. Then I recreated the table as below with a default constraint on d1 field.

create table t1
(
d1 date default to_date('01/01/1100','DD/MM/YYYY','NLS_CALENDAR=GREGORIAN'),
n1 number not null

[Code]....

But the problem here is the data import is taking too much time than what I expected. I can't afford a maxvalue partition here as of my DBA team mentioned if you add maxvalue partition adding partition later in a stage is difficult on this table

apply in this scenario and make the import faster. I am using oracle 10.2.0.1.0 version.

View 2 Replies


ADVERTISEMENT

Add Constraint Default Value For Table Column With Name?

Oct 26, 2009

I would like to add a constraint "default value" for a table column, with a name.

I know how to do it for a constraint "not null" : ALTER TABLE tablename MODIFY columnname CONSTRAINT constraintname NOT NULL;

But I don't know how to do it for a constraint "default value".How can I do ?

View 2 Replies View Related

Get Table Name / Constraint Name / Constraint Type With Join Processes In String Type

Dec 25, 2007

i want to get table name, constraint name, constraint type with join processes in string type. this is what i want: alter table tablename add constraint constraintname constrainttype(columnname)

View 1 Replies View Related

Server Utilities :: Data Pump Import Order - Causing Constraint Violation

Nov 16, 2010

We are trying to import data into existing tables in a schema using data pump

However the foreign key tables are being imported first and then the master table data thus violating the constraints

Apparently it seems larger tables are being imported first regardless of referential integrity constraints thus causing constraint violation (contrary to my understanding)

Is it a normal behaviour during data pump import?

Is it possible that the keys being sequence generated are causing this?

As I understand import will commit after each table In that case can we defer commit at all at the expense of large undo, set constraints to deferrable and try the import?

View 3 Replies View Related

Server Utilities :: Default Path Of Log File After Import Dump In Oracle 10g

Feb 25, 2011

What is the Default Path of Log File after Import Dump in Oracle 10g.

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

Application Express :: ORA-02291 / Constraint Wwv_flows_fk Violated While Application Import

Nov 4, 2013

Each time when I import an application into my workspace in local 10g XE using the APEX 4.2 Application Builder, I get the "ORA-02291, constraint wwv_flows_fk violated..". The application I whant to import  was originally exported from [URL]. I found out, that it has to to with wwv_flow_application_install.get_workspace_id in the installation script, which obviously returns NULL and not my current workspace id as I suppose it should. I replaced wwv_flow_application_install.get_workspace_id by '<my worskspace id>' and then the import does work. 

View 0 Replies View Related

Server Utilities :: How To Import Dump Into Specific Tablespace Instead Of Default Tablespace Users

Jan 18, 2012

How to import dump into specific tablespace instead of default tablespace users.

I want to import my dump file to newly created tablespace ,so how can i do that . I have created new user called cvm and while creating it i mentioned default tablespace to newly created tablespace . But when i try to import my dumo file it goes to users tablespace .

View 2 Replies View Related

SQL & PL/SQL :: Primary Constraint On Table Affecting Procedure To Insert Rest Of Rows In Table?

Jun 12, 2012

primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.

CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 (
dt DATE
)
IS
v_sql_error VARCHAR2 (100); -- added by sanjiv
v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv

[code]...

View 2 Replies View Related

Export/Import/SQL Loader :: Table Import Takes Long Time And Still Running?

Jun 22, 2012

MY DB Version: 10.2.o

OS: Windows Server 2003

I am trying to import on table which i have the export dump file which i take using expdp previously when i load that table on the same host

by using below command:

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

after that i zip that dump and move it to external usb and now i need that table i copy that table and unzip that that dump

Command i am using to do the import is :

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

But the query of import is still runing even not showing any amount of rows to be imported.

i already make the tablespace in which the table was previosuly before dropping but when i check the sapce of tablespace that is also not consuming one error i got preiviously while performing this task is:

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "CDR"."SYS_IMPORT_TABLE_03" successfully loaded/unloaded
Starting "CDR"."SYS_IMPORT_TABLE_03":  cdr/********@tsiindia directory=TEST_DIR dumpfile=CAT_IN_DATA_042012.DMP tables=CAT_IN_DATA_042012 logfile=impdpCAT_IN_DATA_042012.log

[code]....

i check streams_pool_size it will show zero and then i make it to 48M and after that

SQL> show parameter streams_pool_size;
NAME                                 TYPE        VALUE
-----------
streams_pool_size                    big integer 48M

But still it takes time

View 13 Replies View Related

Export/Import/SQL Loader :: How To Import Data From Excel File To Table Through Procedure

Jul 2, 2012

How to import data from excel(.xls) file to data base table

I have excel sheet(.xls) data details, I neet to upload details to data base table using procedure

excel sheet is not CSV file, so SQL Loader is not using

any alternative solution for this issue

View 3 Replies View Related

Export/Import/SQL Loader :: Import To New Table That Has Additional Fields

Dec 22, 2012

I am trying to migrate a table to a new table that has the field sequence changed and also has a new field added. My main question is if it is possible to have datapump add values to the new field in the target table.For example:

-original table has fields a, b, d, c
-new table has fields b, c, d, a, e

I want to load the new table and also include adding values for field e. In this case, field e is a year field, so it should be loaded with '2012'..Does datapump have the ability to do this? Is reorganizing the fields going to cause me any problems? We are on oracle version 11.2.0.3

View 7 Replies View Related

Creating Table With A Constraint?

Oct 16, 2006

how to i insert a constraint of words into the table example below.I am new to this stuff man.

Create table Orders
(
orderID Number(8) Primary Key,
orderDate Date Not Null,
methPmt Varchar2(10),
custID Number(5),
orderSource Number(2),
Foreign Key(custID) Reference Customer(custID),
Foreign Key(ordersource) Reference OrderSource(ordersource)
);

The catch is I am required to enter a constraint of the methPmt will only take values of "CASH", "CREDIT" or "CHEQUE" only.

How am I suppose to enter this constraint value into the creation of this table?

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

SQLPlus - How To Create New Table With Constraint

Mar 25, 2009

I'm trying to create a new table which has a date field (date of birth) and I want to include a constraint which will not allow me to add values if they are over 21 years old. I'm using SQLPlus .

View 3 Replies View Related

SQL & PL/SQL :: Modifying Existing Constraint On Table

Apr 1, 2013

Can we modifying the existing constraint on table?I have table level UNIQUE constraint on 3 columns of table.I need to modify the UNIQUE constraint to 2 columns? Instead of dropping/recreating the constraint, is there any option to modify the existing constraint

Ex

CREATE TABLE TEST_CONST(NUM1 NUMBER , NUM2 NUMBER , NUM3 NUMBER , UNIQUE (NUM1 ,NUM2,NUM3)); ;

SELECT * FROM USER_CONS_COLUMNS UCC WHERE UCC.TABLE_NAME LIKE 'TEST_CONST';

ALTER TABLE TEST_CONST MODIFY CONSTRAINT SYS_C0025132 UNIQUE(NUM1,NUM2);

ORA-00933: SQL command not properly ended

Third statement is not executing fine .

View 1 Replies View Related

SQL & PL/SQL :: Temporary Disable All Constraint In Particular Table?

Jan 7, 2012

i have one table HR.employees

SQL> desc hr.employees
Name Null? Type
----------------------------------------- -------- ----------------------------

EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)

SQL>

in this table i want to temporary disable all constraint in this employees table

View 4 Replies View Related

SQL & PL/SQL :: Possible To Get Exact Name Of Constraint From Any System Table

Sep 1, 2010

There was one constraint define on the column of table that the value of column should be in range of 100 to 200. Some one has deleted the constraint. Is it possible to get the exact name of constraint from any system table.

View 7 Replies View Related

SQL & PL/SQL :: Create A Table With Constraint And Index

Apr 7, 2010

I use Oracle 10.0.2.0.1.If I create a table with constraint key; after that I create an unique index key, I got an error. Does it mean when I create a table with constraint the unique index are automatically created and I could not create index key as I did as below?

create table test_const(ename varchar2(50) not null,
key_num number not null,
descr varchar2(100),
constraint constraint_test_const unique (ename, key_num));
create unique index test_const_idx on test_const
(
"ENAME","KEY_NUM"
)
tablespace tmp_data;

Error report: SQL Error: ORA-01408: such column list already indexed 01408. 00000 - "such column list already indexed" added [pre] tags by Sriram

View 3 Replies View Related

SQL & PL/SQL :: Drop Index Which Has Constraint On Table?

May 18, 2011

How to drop an index which has a constraint on the table?

while droping the index on the table wheather i should remove the

constraint or disable the constraint or without droping the

constraint

View 1 Replies View Related

PL/SQL :: How To Get Constraint Name / Table And Column Names

Sep 12, 2012

I am a junior DBA. I want to check how many columns are under PRIMARY KEY constraint.

I used dba_constraints view. Please find below details.

SQL> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from dba_constraints
2 where TABLE_NAME='DSET_PRODUCT_S';
OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ----------------------------------------
SCOTT SYS_C10202456 C EMPLOYEE
SCOTT SYS_C234576 C DEPT

View 6 Replies View Related

SQL & PL/SQL :: Default Values / Distinguishing Between Passed And Default Nulls

Nov 16, 2010

I was looking for a way to see if a default value for a procedure was passed NULL or it got NULL by default. [URL]

View 11 Replies View Related

SQL & PL/SQL :: Local Unique Constraint On Partition Table

Aug 2, 2011

I have created a table below, my TL asked me to create a local unique constraint for the below table.

I went through all sites and could not find the correct solution, how to create LOCAL UNIQUE CONSTRAINT ON SUB PARTITION TABLE and LOCAL UNIQUE INDEX ON PARTITION TABLE. Creating Local Unique constraint should take care of creating local unique index creation.

Unique key columns are DET,GDS,ARRIVE_DT

CREATE TABLE SUB_PAR_TAB
(
ID VARCHAR2(100) NOT NULL,
REGION VARCHAR2(40) NOT NULL,
SOURCE VARCHAR2(80) NOT NULL,
DET VARCHAR2(80) NOT NULL,
GDS VARCHAR2(40) NOT NULL,
ARRIVE_DTDATE,
SYS_SOURCE VARCHAR2(25) ,
[code]........

View 5 Replies View Related

SQL & PL/SQL :: Why Cannot Create Null Constraint On Table Level

May 19, 2011

Why can't we create null constraint on table level?

View 20 Replies View Related

SQL & PL/SQL :: Multiple Of Same Constraint Keys In Parent Table?

Jun 9, 2011

I used

SELECT USER_CONS_COLUMNS.CONSTRAINT_NAME,
COLUMN_NAME,
CONSTRAINT_TYPE,

[Code]...

to create a parent key table sort of thing, but for some reason there are rows that display multiple times

CONSTRAINT_NAME COLUMN_NAME CONSTRAINT_TYPE R_CONSTRAINT_NAME DELETE_RU
-------------------- -------------------- --------------- ------------------------------ ---------
SYS_C00135225 PATIENT_STATUS C
SYS_C00135226 BALANCE C
SYS_C00135227 PATIENT_STATUS C
PK_PATIENT PATIENT_NUMBER C

[Code] ..........

is it something to do with the join command

View 4 Replies View Related

SQL & PL/SQL :: ORA-12014 / Table CDA_FUNCTION Does Not Contain A Primary Key Constraint

Dec 9, 2010

I am trying to create snapshot using the following script but I am getting the error

ERROR at line 22:ORA-12014: table 'CDA_FUNCTION' does not contain a primary key constraint

SQL> CREATE SNAPSHOT MEDA.cda_function
PCTFREE 10
PCTUSED 40
MAXTRANS 255
TABLESPACE users
STORAGE (
INITIAL 40960

[code]....

I check in dba_constraint data dictionary table for the constraints in the table CDA_FUNCTION.it is showing as follows

owner constraint_name constraint_type table_name search_condition status deferrable deferred validated generated
WEDA SYS_C0032310 C CDA_FUNCTION (LONG) ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME
WEDA SYS_

View 4 Replies View Related

SQL & PL/SQL :: Create Normal Primary Constraint During Table Creation

Mar 23, 2010

i'm new to oracle environment.how can i specify NONCLUSTERD INDEX on Primary cloumn during table creation.By default it will create clusterd index but i need non-clusterd index on it.

I'm using following stmt to create normal primary constarint during table creation,

CONSTRAINT PKFORM_PROPS PRIMARY KEY (FORM_PROPS_PK) USING INDEX TABLESPACE DB123_INDEX

how can i change the above query, so that it should create NONCLUSTERED INDEX on Primary key column.

View 5 Replies View Related

SQL & PL/SQL :: Set Default Value For A Column Of Table

Aug 30, 2010

How to set default value of particular column of a table ?

View 5 Replies View Related

SQL & PL/SQL :: Find Constraint Name From User Table - Illegal Use Of LONG Datatype

Apr 30, 2010

I want to find the constraint name from User_Constraints table using the following query:

Select * From User_Constraints Where Table_Name='CHARGECODE' and Constraint_Type='C' And Search_Condition = '"PERCENTAGE" IS NOT NULL';

Then it shows "ORA-00997: illegal use of LONG datatype" error.

Is there any way to compare with long type value.

View 11 Replies View Related

PL/SQL :: Use Asterisk Along With Default Value While Selecting On A Table

Jul 18, 2012

Is there any way to use * along with the default value while selecting on a table...I need something as follows:

select *, 'JUNK' from table_name;

(or)

select 'JUNK', * from table_name;I know it will not work.

View 4 Replies View Related







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