Search results for query: *

Hi Oasis, You have to re-write your query to make use of your index (RPIN) to something like what Dagon suggested. Otherwise, your SQL will just do full table scans. Robbie Robbie "The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

Hi Maka, When I say slow on my side, it took a few minutes to run your SQL not hours. Since ALL_OBJECTS is a system view and it uses data dictionary objects, can you confirm whether or not the sys.gather_stats_job runs every night? This job also collects stats for the data dictionary...

Hi Maka, I suggest, you modify this SQL and continue tracing your database for other performance degradation brought by the upgrade. Robbie Robbie "The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

Hi Maka, I encountered the same problem when I run your SQL against my 10.20.4, somehow it was too slow for these two predicates; AND (LOWER(OWNER) = LOWER(:c1) OR (1<2)) AND (OBJECT_NAME = :c2 OR (1<2)) So, I changed it to; AND (LOWER(OWNER) = LOWER(NVL(:c1,OWNER))) AND (OBJECT_NAME =...

Hi Maka, When you say that the query does not work, I am getting the impression here that the SQL DOES WORK but returning 0 (zero) records. Is that right? But if the SQL is totally not working and returning some "ORA-*" errors then you have problems with your user. Regards, Robbie Robbie...

Hi Maka, Mufasa is right, maybe you forgot to assign values for your bind variables. You define the variables first, in sqlplus; var c1 varchar2(10) var c2 varchar2(30) var c3 varchar2(30) var c4 varchar2(30) var c5 varchar2(30) var c6 varchar2(30) var c7 varchar2(30) Then, assign values to...

I am using 10.2.0.4 and your query works for me. Here's how I tested it with minor modifications to make it a bit faster; in sqlplus> var c1 varchar2(10) var c2 varchar2(30) var c3 varchar2(30) var c4 varchar2(30) var c5 varchar2(30) var c6 varchar2(30) var c7 varchar2(30) -- to get all...

If you're the sys or dba user other than the user that you just created, you can "see" your table using sqlplus by specifying the schema (which is the user) of the tables created. e.g. (assuming SCOTT is the user); SQL> SELECT * FROM SCOTT.EMP; Are you saying that you can not see the tables...

Just an idea. How about using an inline query instead and placing your holiday date criteria outside your business days query, like; select mem_id, cme_hmp_code, cme_date_begin from cme_case_management_episode, hmp_health_mgmnt_program, mem_member, (select bdate from ( select bdate...

Hi Guys, I think size is not the only consideration here. Based on my experience in this migration (from 8.1.7.4), there were just too many variables to consider since there were a lot of changes in terms of db objects and parameters since 8i. Actually, I did a lot of mini-migrations as...

Actually, this migration that I did was just a continuation of another DBA's work proposal which was signed and agreed by our client. Unfortunately, that DBA resigned already. That is why, for the actual cutover, I will be doing exp/imp instead of going through the same process again. Robbie...

Just an update on this thread. I finally manged to migrade my 8i databases on a new server to 10g using dbua (Database Upgrade Assistant) without any problem at all. Here's what I did (just a quick summary). 1. Shutdown all the dbs from the old 8i server 2. Tar/Copied the ORACLE_HOME from old...

Hi Guys, I am a bit new to RMAN but I need to restore a database on a new server with the current rman hot backup like below; run { ALLOCATE CHANNEL c1 TYPE DISK; BACKUP DATABASE TAG = 'DATABASE_DAILY' SETSIZE = 2097152 FORMAT...

Hi Ken, So when you upgrade from 9i to 10g, did you just copy your database files (without installing the 9i software components) in your 10g server then fired off the upgrade or did you install 9i software first, then copied 9i datafiles, then installed 10g software, then run dbua? I would...

Hi Ken, You mentioned using dbua in one of your systems. Have you tried migrating an old version to 10g on a new server? If yes, did you just install 10g on the new server then copy the files of your old db then use dbua without installing the old database's software component, in my case it's...

Hi Mufasa, Based on the Oracle 10g upgrade docs, the terminal version allowed for direct upgrade for 8i is 8.1.7.4 which is our version. Exp/imp (dump) is plan B for me. Robbie &quot;The rule is, not to besiege walled cities if it can possibly be avoided&quot; -- Art of War

I read these documents and planning (pre-upgrade, upgrade, post-upgrade) to use dbua for my upgrade. But there was no mention if it is possible to upgrade without installing the software components of the old version. Can I just copy the cold backup of the 8i db (less the Ora8i software) to a...

Hi guys, I will migrate an 8i from an old server to 10g on a new server. Just wondering if I can do this without installing the Oracle 8i software component in my new server first. My DBA friend told me to install Oracle 8i first, copy the datafiles etc, then this will become my base for the...

How about this one: receive_dt date &quot;TO_DATE(SUBSTR(:receive_dt,1,19),'YYYY-MM-DD HH24:MI:SS')&quot; Robbie &quot;The rule is, not to besiege walled cities if it can possibly be avoided&quot; -- Art of War

This is because in Oracle, all roles acquired are disbled inside the Stored Procedure so you need an explicit grant of the priv. instead of the one acquire through roles. Robbie &quot;The rule is, not to besiege walled cities if it can possibly be avoided&quot; -- Art of War