The following shows how exceptions can be captured. -- Create exceptions table SQL> @?/rdbms/admin/utlexcpt.sql Table created. SQL> desc exceptions; Name Null? Type ----------------------------------------- -------- ------------------------ ROW_ID ROWID OWNER VARCHAR2(30) TABLE_NAME VARCHAR2(30) CONSTRAINT VARCHAR2(30) SQL> select * from xx; X ---------- 11 11 -- add constraint but disable it SQL> alter table xx add constraint xx_pk … Continue reading How to generate a list of exceptions generated due to constraint violations?
Tag: alter
How to delete/reset a parameter in spfile using ALTER SYSTEM?
Using ALTER SYSTEM RESET one can delete parameter from spfile if the parameter is in the spfile. Example: -- shows the parameter is in the spfile $ pwd /u01/oracle/product/10.2.0/db_1/dbs $ strings spfileTEST.ora | grep open *.open_cursors=100 -- Login to SQL*Plus, scope has to spfile and sid has to be supplied. When '*' is specified it … Continue reading How to delete/reset a parameter in spfile using ALTER SYSTEM?
How to tell if a parameter can be modified in a session/system?
By querying V$parameter one can find if the parameter can be modified in session and/or system. If isses_modifiable is true then value can be modified in session, if FALSE then it can't be modified using ALTER SESSION SET.. If issys_modifiable = DEFERRED, the value can be changed with scope=spfile specified (if spfile is used) and … Continue reading How to tell if a parameter can be modified in a session/system?
alexlima.com 