SQL & PL/SQL :: Enabling Constraint With Enable No Validate?
Sep 5, 2013
Is there any way to enable the constraint with out validating for the existing information.
As We can accomplish the same functionality when we are adding the constraint for the first time.
Ex: alter table scott.emp add constraint fk_deptno foreign key(deptno) references scott.dept(deptno) enable novalidate
The above SQL statement will enable the constraint with out validating for the existing information. So there is possibility of data will be there only in the child table but not in the parent table for existing information.
But in the future it will not allow to do so,if the constraint is in ENABLE staus.So i am just trying to disable the constraint to insert only into child table & trying to enable it with NOVALIDATE option .
But the following exception is coming .
SQL> alter table scott.emp ENABLE constraint fk_deptno enable NOVALIDATE;
alter table scott.emp ENABLE constraint fk_deptno enable NOVALIDATE
ORA-00905: missing keyword
SQL> alter table scott.emp ENABLE constraint fk_deptno NOVALIDATE;
alter table scott.emp ENABLE constraint fk_deptno NOVALIDATE
ORA-00933: SQL command not properly ended
Instead of dropping & recreating the same constraint , is there any alternate way to do this ?
View 3 Replies
ADVERTISEMENT
Nov 1, 2011
I have a table, it has 3 terabytes of data. We are sure whatever the data we have in that table is valid. I need to add the index to table it should be enabled and need not to be validated for existing data.
I am using following command
CREATE INDEX U_IXN_MTRS_ORGIDMTRNOMTRTYPEID ON MTRS (nlssort(METERNO), METERTYPEID) TABLESPACE OLTP_IDX_TS ENABLE NOVALIDATE;
it is giving me problem
ORA-02158: invalid CREATE INDEX option error
View 4 Replies
View Related
Aug 22, 2010
Is there any logical difference between DISABLE CONSTRAINT and DISABLE NOVALIDATE, in order to accept any new values into the table regardless of violating anything?
View 2 Replies
View Related
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
Sep 28, 2011
In our database without enabling audit file, we are getting audit file.find the audit parameter output
SQL> show parameter aud
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /app/oracle10g/admin/ytr/adu
mp
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string NONE
View 1 Replies
View Related
Jun 22, 2011
I have an existing form which i need to customize..In the form in the query block whatever strings we enter..and press the search button..it sets the where clause and use execute query.which populates the detail block which is read only and only 1 column is enabled.
For the Enabled column X I need to enable a button.It has to be done only if the cursor go in the column X
View 1 Replies
View Related
May 1, 2012
I developed a custom form upon which I developed a query find block to query the data on the main form. I have WHO columns in the table and referencing them in the main form. But Some reason when I query the data by pressing the find button on the query block it is retreiving the data properly, but record history is not enabling.
In the query find block I have 3 text fields, 1 check box and 2 date fields.
View 1 Replies
View Related
Sep 26, 2010
I have scheduled a ADHOC Oracle JOB in OEM to gather statistics for the database with 100% estimation during weekend. This JOB currently fails because of a batch Job that runs at the same time and drops few old partitions. This OEM job runs for 1 hour and fails because few partitions are getting dropped at the same time.
How i can enable this statistics JOB to skip/Ignore errors and to continue to run to gather complete statistics of the entire database. I have scheduled this job in OEM. how can i enable this SKIP/IGNORE errors for this job.
View 1 Replies
View Related
Dec 6, 2012
My requirement is as below,
I have one main form and there is one button when i click on it, another form should be opened which should display multi records with check box for each record.
I have implemented so far. When the new form is opened by default all the records should be checked. if user does not want some of them then it can be unchecked(This can done manually)..
I have put below code in when-new-block instance trigger
set_item_property('LOCKBOX_DETAILS.CB_SUBMIT', ENABLED, PROPERTY_TRUE);
Unable to load the block with all the records checkboxes checked.
View 4 Replies
View Related
Jun 22, 2012
I have a partioned table that has close to 2 billion rows and a PK of all columns. Becuase of time constrains my APP team wants the PK disabled while they pump into hundreds of thousands of rows with a batch process.
Now I am finding when I enable the PK its eating up close to close to 200GB of temp space.
Is there something I can do to reduce the amount of temp space being used?
View 6 Replies
View Related
Aug 25, 2012
differences of enabling thread as public or private?
lets assume that i have two threads (1, 2) for two instances (1, 2) and each of thread consists of 2 redolog groups - minimum configuration.in case i need add third node i need third thread.
what will be difference if i set this thread as public instead private?
View 7 Replies
View Related
Apr 19, 2013
Enabling of auditing in oracle database, can this be done on some tables or applied on the whole schema/user.
View 5 Replies
View Related
Jun 20, 2012
i want to enable block change tracking as our incremental level1 backup take a lot of time but i have a worry which i found it on oracle support and tha is as follows Database Instance Hang at Database Checkpoint With Block Change Tracking Enabled. [ID 1326886.1]
View 2 Replies
View Related
Jan 10, 2013
OS: oel6.3 - 2.6.39-300.17.2.el6uek.x86_64
Grid and DB: 11.2.0.3.4
This is a two node Standard Edition cluster.
The node crashes upon restart of clusterware after following the instructions from note:751343.1 (RAC Support for RDS Over Infiniband) to enable RDS. The cluster is running fine using ipoib for the cluster_interconnect.
1) As the ORACLE_HOME/GI_HOME owner, stop all resources (database, listener, ASM etc) that's running from the home. When stopping database, use NORMAL or IMMEDIATE option.
2) As root, if relinking 11gR2 Grid Infrastructure (GI) home, unlock GI home: GI_HOME/crs/install/rootcrs.pl -unlock
3) As the ORACLE_HOME/GI_HOME owner, go to ORACLE_HOME/GI_HOME and cd to rdbms/lib
4) As the ORACLE_HOME/GI_HOME owner, issue "make -f ins_rdbms.mk ipc_rds ioracle"
5) As root, if relinking 11gR2 Grid Infrastructure (GI) home, lock GI home: GI_HOME/crs/install/rootcrs.pl -patch
Looks to abend when asm tries to start with the message below on the console. I have a service request open for this issue but
kernel BUG at net/rds/ib_send.c:547!
invalid opcode: 0000 [#1] SMP
CPU 2
[code]....
View 9 Replies
View Related
Nov 24, 2012
For applications and Timesten databases on the same server we can use direct model to gain the base performance. But I want to know that how big heap size of JVM to be set for my java application when enabling direct model?
Does my application need more head memory when direct model than other local communication protocols, such as Unix domain socket or IPC? Supposing my Timesten database takes 12GB memory from OS, does it mean I need specify the same size for JVM heap(-Xmx12G)?
View 2 Replies
View Related
Mar 30, 2011
I am trying to execute procedure using dbms scheduler.but i am getting below errors
ORA-06550: line ORA-06550: line 1, column 407:
PLS-00103: Encountered the symbol "AMANORATEST" when expecting one of the following:
:= . ( @ % ; immediate
The symbol ":=" was substituted for "AMANORATEST" to continue.
, column :
but procedure is executing fine with sql commmand line.
View 1 Replies
View Related
Nov 11, 2011
I have the table is partitioned.
Can I set the table table to "alter table x enable row movement" even when it is index organized table?
How do I know if the table is INDEX ORGANIZED TABLE.
View 3 Replies
View Related
Dec 15, 2010
In PL/SQL Plus, i can enable/disable auditing when i connect as sysdba by using these command:
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE
SQL> shutdown
SQL> startup
I've done it successfully with PL/SQL Plus command line. But in PHP, how can i do that?How to execute "shutdown" and "startup" from PHP?
I've found this code for connect to oracle as sysdba:
oci_connect("/", "", null, null, OCI_SYSDBA);
From the following link:
[URL]......
But, i still can't execute "shutdown", "startup";
View 4 Replies
View Related
Jul 5, 2012
I facing the below issue while enable the audit on table.
SQL> audit delete on test;
audit delete on test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01400: cannot insert NULL into ("SYS"."AUDIT_DDL"."DICT_OBJ_TYPE")
ORA-06512: at line 2
===
Related Information
===
SQL> select version from v$instance;
VERSION
-----------------
11.2.0.2.0
SQL> show parameter audit;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /orasw/product/11.2.0
/rdbms/audit
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
SQL> SELECT TABLESPACE_NAME,owner from dba_tables where table_name='AUD$';
TABLESPACE_NAME OWNER
------------------------------ ------------------------------
AUDIT_DATA SYS
View 4 Replies
View Related
Feb 20, 2013
I have Apex 4.1.1.00.23 and I want enable Session State Protection (just for testing and for learning how this works).
When I go to Home --> Application Builder --> Application 121 --> Shared Components --> Session State Protection and I click Set protection button I get to the page where I have 3 options (Disable, enable and Configure SSP).
After clicking on enable option, I get to page [URL].......
There is no Finish or submit or complete or confirm button, only cancel and previous (print screen is on picture). I found out that SSP is already enabled so I can't enable it again. But other question is still open, how to get it now to work in real.
View 0 Replies
View Related
Aug 27, 2013
1.) Whatever statics/data should we gather to compare the database performance before and after enable the audit on database level?
2.) How can we enable audit on database level?
3.)what is pros and crons of enable audit database level?
View 1 Replies
View Related
Aug 18, 2010
I need to know if PARALLEL is enabled in my session. Would this be a session parameter or something else? Is there a view I have to query or some SQL*Plus command to execute?
P.S. Is there a way to correct the title spelling after submitting.
View 3 Replies
View Related
Dec 8, 2011
How to enable auditing in oracle ?
View 2 Replies
View Related
Apr 8, 2010
I knew, how to enable or disable a constraint on a table.
>alter table <table_name> disable constraint <constraint_name>;
But if we know all the data is correct, it's better to disable all constraints to improve the performance.
We are allowed to Disable or Enable only a single constraint.
Enable or Disable All Constraints.
View 11 Replies
View Related
Jul 12, 2013
I have installed database in one server. I would like to enable AWR into it. Statistics_level is set to Typical. While running the below script to enable the AWR, its gives error -
SQL> exec dbms_scheduler.enable('GATHER_STATS_JOBS');
BEGIN dbms_scheduler.enable('GATHER_STATS_JOBS'); END;
*
ERROR at line 1:
ORA-27476: "SYS.GATHER_STATS_JOBS" does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 4343
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2802
ORA-06512: at line 1
make AWR automatical generation.
View 3 Replies
View Related
Jul 3, 2012
I have a job that runs the following commands for each table in a schema.. (I just inherited it ugggg)
alter table ODS.ODS_MONTHLY_MF_AUM enable row movement;
alter table ODS.ODS_MONTHLY_MF_AUM shrink space;
alter table ODS.ODS_MONTHLY_MF_AUM disable row movement;
On occassion I get this error when enabling row movement
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
Other than dealing with apps that depend on ROWID's is there any negative impact to always keeping row movement enabled for tables.
Secondly, does any body have a script that can determine if the space really needs to be shrunk for a table.
View 13 Replies
View Related
Jun 16, 2011
How can I enable NOLOGGING in an External Table ?
My Table FT_PRICE_COST_MASTER is taking a long time(more than 1 hour) to excute inside the procedure. The statement is:
SELECT SUBSTR(m.sr_no,1,16) SR_NO, m.SKU_CODE, m.location_code, m.start_date, m.end_date, m.COST, m.tax, m.octroi, m.freight, m.cash_disc, m.scheme_disc, m.mrp, m.csp, m.rsp1_qty, m.rsp1_value, m.rsp2_qty, m.rsp2_value, m.rsp3_qty, m.rsp3_value, m.net_cost, m.final_cost, m.current_margin, m.pc_effect, m.created_by, m.creation_date, m.modified_by, m.modification_date, m.change_ind, m.tax_code,
[code]......
When I am recreating the External table with NOLOGGING keyword at the bottom of the synatx, it's showing "operation not supported". Else are there any other points that I should look to increase the execution time of the above statement ?
View 2 Replies
View Related
Oct 10, 2012
i enable auditing on the database 10.2 for users,
once i query SQL> SELECT username,
extended_timestamp,
owner,
obj_name,
action_name
FROM dba_audit_trail
WHERE owner = <Username>
there are many many rows , my question is , are you enable to truncate it from time to time , if not ,is it effect on the performance of the database ?
View 3 Replies
View Related
Jun 7, 2013
how to enable dbms_job to run on every day at 4am excluding Fridays.
View 7 Replies
View Related
Nov 21, 2011
i am writing a script for inserting rows in a table where primary key of that table consists of 4 columns. I want to add a new row where 3 columns of that table will be same but change the value of the last one. this is giving an error saying that i am violating the unique constraint. I tried to disable that constraint/PK by following statement but still this error is coming
alter table sections_budgets modify primary key disable cascade;
OR
alter table sections_budgets modify constraints sec_bdgt_pk disable cascade;
the insert statement which i am using. The first four columns are part of PK
insert into sections_budgets (SELECT sb.bdgt_cd, sb.bdgt_dpf_cd, sb.bdgt_yr, :sec_code, sb.usf_usr_id,
sb.aty, sb.comaty, sb.comytd, sb.expn_ytd, sb.sys_dt, sb.cat,
sb.start_aty, sb.expn_lmt
FROM sections_budgets sb
WHERE sb.bdgt_dpf_cd = :b_d_code AND sb.bdgt_yr = :year);
is there any other way of inserting records in this table without droping the whole table because it contains too many triggers and constraints?
View 24 Replies
View Related