DB Upgrade From 11.1 To 11.2 - Statement Running For Long
Feb 21, 2011
After Migrating from 11.1 to 11.2 SQL Statements are running for a long time. Our DBA told that they don't have any execution plans for the SQL Statements in 11.1
I don't want to rewrite the SQl Statement as this is working fine in 11.1. Our DBA conveyed us that they are using DBMS_STATS.AUTO_SAMPLE_SIZE for Schema Level.
But we have planned to generate DBMS_STATS.GATHER_TABLE_STATS Estimate Percentage ==> 100 for only the tables that are used in SQl Statements.
Of course we can change the parameters.. But we are afraid it may have some side effects.
View 3 Replies
ADVERTISEMENT
Oct 7, 2010
I ran one long running update statement in my sqlplus session, where it took hours together since we have millions of rows on the table.lock the system and left the office , the next day I found my system is rebooted, hope the session would have performed the long running update statement.
Now, I need to issue a commit for the session, since my system is rebooted I don't have opened session with me. so, How to issue commit to the session, which I have issued long running update statement.
View 3 Replies
View Related
Nov 23, 2011
An user is running some queries and that's making database hang. I want to view what queries this user is running. Can this be done by session auditing turning on?
View 13 Replies
View Related
Jul 31, 2013
I have a Datamart DB which has a continuously increasing volume. I run a daily optimize job, to have the data analysed using the below:-
EXECUTE dbms_stats.gather_schema_stats(ownname=> 'xxx' ,estimate_percent=> 25 , DEGREE => 4, cascade=> TRUE );
analyze table xxx.abc compute statistics; But this optimization itself is taking nearly 4 hrs to complete and I can't afford to have the delay.
Is there a better way of running this optimization?
View 9 Replies
View Related
Oct 25, 2011
We have a program that is taking about 13-14 hours to run and we need to generate traces to see where it is taking so long. I usually use 10046 for the tracing, I'm wondering if the traces can be built incremently so that it doesn't become one huge trace file.
View 14 Replies
View Related
Apr 27, 2012
I have a Query(report) which is running in <5 mins in one Scheme, where as the same is running for a long time in second schema. I have identified that an Index is scanning for more than 2000 Millions of records in second Schema, but this is scanning only 440 Millions in First Schema and hence it is fast. I am expecting the same to be done in Second schema.
I have verified the following
All records in tables in 2 schemas are same.
All indexes are same
Analyzed the tables
Gathered Histogram on all the columns as per the first schema.
But now i still have the same problem, don't know what could be the problem.
Table_nameNum_RowsBlocks
PRPSL_LST_T5866107159
PRPSL_WKFLW_ACTVTY_T5829904030
ITEM_CHR_VAL_T5134340104049020
ITEM_RGN_ASSN_T8571220137215
Also attached 2 screen shots of OEM Plans..
View 2 Replies
View Related
Feb 16, 2011
Recently we have upgraded from 11.1 to 11.2 . But after upgrade SQL statements that are running fine in 11.1 was running for hours in 11.2. Statistics are collected 100%...
View 2 Replies
View Related
Sep 22, 2010
I have an issue with rather complicated function.Basically it is using DBMS_SQL to execute a very long statement with many parameters (~6000 of them) and binding them with DBMS_SQL.BIND_VARIABLE. Variables are called :1,:2,...,:6000.
When this arguments set is too large - I am receiving error "ORA-00939: too many arguments for function".
Currently I am thinking about dividing the query into subqueries and executing them all with performance decrease.
View 10 Replies
View Related
Sep 20, 2010
We are firing a normal Drop command on our database and the database version is 10.2.0.4.The database is running on AIX v5.The command is taking more time than usual .
When i am monitoring the session i can see that a call is being made to procedure "aw_drop_proc".Could i ask you if this is something that is taking more time than usual.
We are not having any partitions on the nested tables .We have a pack of tables and we are dropping this pack through a procedure.The pack comprises of nested tables & normal tables.To drop a nested table it is taking around 6 seconds(Table with no rows) and a normal table(With no rows) it is taking 17 milli seconds.We have a partition on Normal table.
The same operation in windows is taking very less time when compared to AIX.
View 5 Replies
View Related
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
Feb 6, 2012
with the code below i have created 2 varaibles v_start_date and v_finish_date, i have inputs for them, thats fine the problem is when the inputs are empty, i have put a ELSIF statement in the begin section of the code, now if the variables have input from the textbox's then it works fine but if they are empty, the programme reads into the if statement executes that bit of code but doesn't seem to open the cursor up and execute that bit of code.
DECLARE
v_start_date enrollments.enrollment_date%TYPE:= :START_DATE;
v_finish_date enrollments.enrollment_date%TYPE := :FINISH_DATE;
CURSOR no_grades_cur IS
SELECT class_id,stu_id, status
FROM enrollments
[code].....
View 13 Replies
View Related
Apr 29, 2013
how do I detect I/O before running sql statement?
View 3 Replies
View Related
Jan 23, 2009
I am getting ORA-00933 after running below mentioned delete statement;
DELETE FROM REPOSITORY.MEDIASEGMENT MS
INNER JOIN REPOSITORY.ROUTINGEVENT RE ON TRIM(MS.Segment_Key) = TRIM(RE.uuid)
INNER JOIN REPOSITORY.TEMPCONTACT TC ON TRIM(RE.Contact_Key) = TRIM(TC.vduid)
WHERE TC.CREATETIME BETWEEN (TO_DATE('04/24/2008 00:00:00','MM/DD/YYYY, HH24:MI:SS')) AND
(TO_DATE('04/30/2008 23:59:00','MM/DD/YYYY, HH24:MI:SS'))
View 2 Replies
View Related
Dec 5, 2011
how can i run dml statement on the oracle transparent gateway for sql server ,such as insert ,update,delete.
View 4 Replies
View Related
Aug 25, 2010
Is there any way i can Get how many rows are processing with UPDATE statement while the Update statement is still running.
View 2 Replies
View Related
Dec 22, 2012
I got an exception when I was using sesame adapter to dump a turtle file which contains long texts as objects into oracle semantic database. The exception information is:
org.openrdf.repository.RepositoryException: org.openrdf.sail.SailException: java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column
ORA-06512: in "SF.ORACLE_ORARDF_ADDHELPER", line 1
ORA-06512: in line 1
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802) ...
View 1 Replies
View Related
Sep 26, 2012
resolve problem with move lob objects ? I move table partition and lob (BLOB) from one tablespace to another :
alter table EBIF.APO_T_VER_DISP_ACC_RESP MOVE PARTITION P1M20120901 LOB(SIGNATURE_PATTERN) STORE AS (TABLESPACE tmp) t
able EBIF.APO_T_VER_DISP_ACC_RESP MOVE PARTITION have : pbeb_ap1.SYS>select partition_name , tablespace_name from dba_lob_partitions where table_name='APO_T_VER_DISP_ACC_RESP';
PARTITION_NAME |TABLESPACE_NAME
------------------------------|------------------------------
P1M20110901 |TD1M20110901
P1M20111001 |TMP
P1M20111101 |TMP
P1M20111201 |TMP
P1M20120101 |TD1M20120101
[code]....
I used skrip to generate move :
select 'alter table '||table_owner||'.'||table_name||' MOVE PARTITION '||partition_name||' LOB('||COLUMN_NAME||') STORE AS (TABLESPACE TD_PART_RW) PARALLEL 4;'
from dba_lob_partitions where tablespace_name='TMP';
when I started loadink into dis table I get : ORA-01461: can bind a LONG value only for insert into a LONG column
when I recreate this table ALL work ok , but new table is not partitioned .
View 2 Replies
View Related
Jun 26, 2013
I'm planning to upgrade a small database (~150GB) from 10.2.0.3 on windows 2003 23bit to 11.2.0.3 RAC on Linux 5.8.The database contains oracle spatial too. A suitable method and link to document to be followed.
View 2 Replies
View Related
Nov 19, 2012
I recently performed an upgrade on a new server from oracle 10gr2 to oracle 11gr2 (11.2.0.3).
I take the rman backup from oracle 10g server and restore it on new server where I installed oracle 11gr2.
But on my previous oracle 10gr2 server I enabled the auditing. After doing successful upgrade now when I try to login with any user except sys I receive the following error:
SQL> conn scott/tiger
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "OBJ$EDITION": invalid identifier
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "OBJ$EDITION": invalid identifier
I got the workaround by setting the parameter audit_trail=FALSE (Previous value was DB_EXTENDED) .But I want my auditing to be enabled as per y requirements.
View 1 Replies
View Related
Sep 22, 2012
is the upgrade patch the same for standard edition and enterprise edition or there are two separate patches?
View 5 Replies
View Related
Oct 22, 2012
find reference note IDs for DB upgrade from 11.2.0.2 to 11.2.0.3.2, as I am finding only Exadata which I don't want but I want to find for Ebiz database, on OS - Solaris 10 9/10 s10s_u9wos_14a SPARC.
View 1 Replies
View Related
Jun 6, 2012
Along with existing RMAN backups we do Exports - of our DB using and OS User and Oracle Wallet.Of the DB's we have upgraded the Data Pump Directory
Select * from dba_directories; (there are other commands to get this info as well).
I captured screens from the DBUA upgrades, but did not see an option to change this information.Is there a way to feed this information to the install moving forward. IE, ./DBUA -silent ?
Also, anyone tracked the percentage of storage increase from 10.2/11.1 to 11.2.
View 4 Replies
View Related
Nov 7, 2012
All my sys tables are very slow after my database upgrade from 10.2.0.4 to 11.2.0.3 on AIX 6.1
For example
select * from ALL_TAB_COLUMNS; -- taking 19 seconds in 11.2.0.3 and few millisec in 10.2.0.4
I have deleted and updated fixed and dictionary table statistics , till I facing this issue
View 5 Replies
View Related
Jun 14, 2012
Know the Patch set number to upgrade a Oracle 10.2.0.1 to 10.2.0.5 on Solaris 10 Spark 64bits?
View 1 Replies
View Related
Jul 19, 2013
I am going to upgrade database from 11.1.0.7 to 11.2.0.3
1) If compatible is set to 10.2.0 in 11.2.0.3, will it work ?
2) If compatible set to maximum level, will it affect our application ?
3) Whether any code related problem occurred after upgrading like PL/SQL codes ?
View 6 Replies
View Related
Mar 13, 2013
We are planning to upgrade our database from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi to Oracle Standard Edition 11g . We also have oracle apex installed on Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi database with oracle apex 3.1
No our plan to upgrade the database and oracle apex to 4.2. Since Oracle Enterprise Edition is licensing is very expensive we though of buying standard edition and upgrade to this version.
can we upgrade the oracle database from enterprise edition to latest standard edition?
View 3 Replies
View Related
Aug 6, 2012
Iam doing some oracle database upgrade from 9.2.0 to 11gr2 . is it possible to upgrade directly from this 9i version to 11gr2 .
Version : 9.2.0 to 11gr2
View 4 Replies
View Related
May 21, 2013
Why COMPATIBLE Parameter is important to be at a certain value before you UPGRADE(10.2.0.4 to 11.2.0.3) to 11g ?
View 2 Replies
View Related
Mar 23, 2013
I try to upgrade 11.2.0.2 to 11.2.0.3 on Linux environment. I have to apply patch P1253900 before the upgrade per oracle doc.
however I got following conflict:
Verifying environment and performing prerequisite checks...
Checking skip_duplicate
Checking skip_subset
Conflicts/Supersets for each patch are:
Patch : 12539000
Conflict with 12827726
Conflict details:
/u01/app/grid/product/11.2.0/grid/lib/libserver11.a:/ksxp.o
Following patches have conflicts: [ 12827726 12539000 ] Refer to My Oracle Support Note 1299688.1 for instructions on resolving patch conflicts.
I checked above oracle note, and did not say any resolution.
View 1 Replies
View Related
Jun 24, 2012
I am trying to upgrade the database 32bit from 10.2.0.1 to 10.2.0.4 version in lunux 32 bit . I faced the version incompatibilty error during patchset installation and hence ran the patchset installation with -ignoresysprereqs option.Now during the patchset installation i encountered the below error in in the install logfile.
INFO: Start output from spawned process:
INFO: ----------------------------------
INFO:
INFO: /u01/app/oracle/product/10.2.0/bin/genclntsh
INFO: genclntsh: genclntsh: Could not locate /u01/app/oracle/product/10.2.0/network/admin/shrept.lst
[code]....
Need certified os and versions for oracle db 10gr2
View 2 Replies
View Related
