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;
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 ?
I am on 11.2.0.3 Enterprise Edition. We are using the new feature "Composite Domain Index" for a Domain index on a very large table (>250.000.000 rows). It really works with mixed queries. We added two number columns using FILTER BY.We have lots of DML on this table. Therefore, we are executing synchronize and optimize once the week. The synch behaves pretty normal. But "optimize_index" takes a very very long time to complete. I have switsched on 'logging' for the optimize process. The $I table takes some time but is finished normally. But the optimization of the $S table (that is the table created for the CDI feature) is running over 12 hours now - and far from being finished. From the logfile, I can see that it optimizes 1000 rows every 20 minutes. Here is the output of the logfile:
Oracle Text, 11.2.0.3.0 14:33:05 06/26/12 begin logging 14:33:05 06/26/12 event 14:33:05 06/26/12 process $N for optimize: SEQDEV.GEN_GES_DESCRIPTION_CTX_I 14:33:16 06/26/12 14:33:16 06/26/12 [code]....
I haven't found a recommendation from Oracle not to use "optimize_index" for Domain Indexes with CDI. But in my case, it would be much faster just to drop and recreate the Domain Index in question.
I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.
I am facing the error "ORA-01502: index or partition of such index is in unusable state " while loading the text data using sql loader with direct path (direct = Y ,rows = 10000) option. Table consists an composite non unique index. If I query the dba indexes for the effected index it shows the index status as VALID. There was no maintaince done on the effected table or index. I have tried loading the same data using conventional path but didn't found any issues for the same.
where @var is user supplied input at runtime...We had a index on a.c2 . The CBO would use this index to generate an opitimised query plan.We found some records from table "b" were dropping due to inner join. So we made a change in join. It'd be like
a.c1(+)=b.c1 and nvl(a.c2,@var)=@var
This query is no longer using the index, instead its doing a full table scan causing the query to slowdown.I have tried creating index on nvl(a.c2,'31-dec-9999')
But the CBO won't use it.Anyway to create index on this col so that full table scan can be avoided?
We have occurrences of enq : TX - index contentions in the database. Using the SQL ID, we have identified the INSERT statement and the table which they are trying to insert.
This table has almost 25 different indexes, some of which are unique as well.I am wondering how to identify the actual index causing issue, out of these 25 indexes.
Is there any way to pin point to the name of index which is causing the lock?My plan is, once the index is identified, I would like to check the extents and inittrans and other attributes of this index to fix.
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.
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:
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$';
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.
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.
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
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 ?
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 ?
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?
I am having set of sql files i wish to run one by one if there is any error at one file i need to notify to the user.I have created Bat file in-order to execute in a sequence.how to validate in bat file
say
01.sql ---Sucess 02.sql --- Fail -- intimate and not to execute rest of sql files 03.sql 04.sql
We are looking for validating our product upgrade changes. As part of product upgrade, there will be changes to the schema.
Like addition of tables, addition of columns to existing tables, change the length of character columns, add new index, etc. We want to verify on the database, that these SQL are run successfully on the database.
Is there a table/view, that can be queried to figure out whether such sql was run and the status of the execution, after some time (day or two) after these are actually run on the database.
I have a production database with 2 node Physical RAC ENV on two separate machines , and we are going to enable audit trail parameter to DB values to capture all failed login attempts on a database.
we have done this on our testing ENV but dont know the procedure to deploy this steps on 2 node RAC ENV . I have done the below steps on our standalone testing ENV
1.Log in as SYS and connect with the SYSDBA privilege. oUser Name: SYS oPassword: Enter your password. oConnect As: SYSDBA
2.To enable audit on DB use below command SQL>alter system set audit_trail=DB scope=SPFILE;
3.Then restart the database.
4.After successful startup use the below command to capture log SQL> audit create session whenever not successful;
steps to enable audit_trail to capture failed login attempts on two node RAC ENV.
I'm using toad 9.2.7.5 to do my plsql development. How I can enable the Profiling option so that I can analyse the performance of my stuff - currently the grey profiler icon is greyed out
