Getting sysdate across a DB link
Title: Getting sysdate across a DB link I'm trying to get the value of SYSDATE on a remote server. I have a database link to the server, but I'm not sure how to force SYSDATE to be evaluated on the remote machine. Following query gives same results, both dates are same. select sysdate local_date, remote_date from ( select sysdate remote_date from [EMAIL PROTECTED]) How can this be done? Thanks
RE: Getting sysdate across a DB link
Title: Getting sysdate across a DB link The dates are different. Currentlyremote serverisGMT andlocalis EST and this can change. I was looking for something without creating any database object like function or viewon the remote server. Thanks -Original Message-From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]Sent: Monday, January 26, 2004 1:36 PMTo: '[EMAIL PROTECTED]'Cc: '[EMAIL PROTECTED]'Subject: RE: Getting sysdate across a DB link Ashish, Why do you think that the dates would be different on the two machines - is one across the international date line? Shouldn't the dates be the same? How about getting the time from both servers - they *might be* different by a few seconds. Tom Mercadante Oracle Certified Professional -Original Message-From: Ashish Sahasrabudhe [mailto:[EMAIL PROTECTED]Sent: Monday, January 26, 2004 11:14 AMTo: Multiple recipients of list ORACLE-LSubject: Getting sysdate across a DB link I'm trying to get the value of SYSDATE on a remote server. I have a database link to the server, but I'm not sure how to force SYSDATE to be evaluated on the remote machine. Following query gives same results, both dates are same. select sysdate local_date, remote_date from ( select sysdate remote_date from [EMAIL PROTECTED]) How can this be done? Thanks
[no subject]
HELP
SQL Code release
Title: SQL Code release Is there a tool available to release same set of DDLs, DMLs, PL/SQL code in different database environments like QA, Unit Testing, Production Support etc. I guess this can done by writing shell or batch scripts. But I am looking for a GUI tools to do this. Thanks
RE: SQL Code release
Title: SQL Code release It seems we also need to write our own tool. -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Wednesday, January 21, 2004 11:20 AMTo: Multiple recipients of list ORACLE-LSubject: RE: SQL Code release OEM has such a tool built into it ... we went through a research, ended up writing our own because each environment is different. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Ashish Sahasrabudhe [mailto:[EMAIL PROTECTED]Sent: Wednesday, January 21, 2004 11:10 AMTo: Multiple recipients of list ORACLE-LSubject: SQL Code release Is there a tool available to release same set of DDLs, DMLs, PL/SQL code in different database environments like QA, Unit Testing, Production Support etc. I guess this can done by writing shell or batch scripts. But I am looking for a GUI tools to do this. Thanks **This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.**4
dc_sequences
Title: dc_sequences In statspack report on RAC 9.2.0.2 database, under section Dictionary Cache Stats for DB there are 64.3% miss for dc_sequences. What causes this? Thanks
DB link to standby
Title: DB link to standby On standby database server, you can connect to / as sysdba and query fixed tables/views. Is it possible to create a database link which connects to sysdba user to query the fixed tables/view? I tried, but getting following error ORA-02068: following severe error from TEST_SB ORA-01033: ORACLE initialization or shutdown in progress Thanks Ashish
RE: SQL Area Reloads
Title: Re: SQL Area Reloads Shared pool is free all the time. There are no DDLs executed, no analyze or no package compiles still there are SQL AREA reloads. The database is on 9i RAC. The reloads are appearing on RAC instances. -Original Message-From: Tim Gorman [mailto:[EMAIL PROTECTED]Sent: Wednesday, December 10, 2003 11:05 PMTo: Multiple recipients of list ORACLE-LSubject: Re: SQL Area ReloadsSure, it's free now. But has that always been the case?on 12/10/03 3:14 PM, Ashish Sahasrabudhe at [EMAIL PROTECTED] wrote: The statspack report is showing 10,684 reload for SQL AREA with 2.2% misses. == Library Cache Activity for DB: MAIN Instance: MAIN Snaps: 18089 -18090 -"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --- -- -- -- -- BODY 12,186 0.0 12,186 0.0 0 0 CLUSTER 95 0.0 10 0.0 0 0 INDEX 466 0.0 461 0.0 0 0 SQL AREA 44,142 0.1 500,987 2.2 10,684 0 TABLE/PROCEDURE 208,498 0.1 584,219 0.0 1 0 TRIGGER 2,746 0.0 2,746 0.0 0 0 == The 250 MB of shared pool is free. Why would it cause reloads if there is so much free space available? Any help is appreciated. Ashish
SQL Area Reloads
Title: SQL Area Reloads The statspack report is showing 10,684 reload for SQL AREA with 2.2% misses. == Library Cache Activity for DB: MAIN Instance: MAIN Snaps: 18089 -18090 -Pct Misses should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --- -- -- -- -- BODY 12,186 0.0 12,186 0.0 0 0 CLUSTER 95 0.0 10 0.0 0 0 INDEX 466 0.0 461 0.0 0 0 SQL AREA 44,142 0.1 500,987 2.2 10,684 0 TABLE/PROCEDURE 208,498 0.1 584,219 0.0 1 0 TRIGGER 2,746 0.0 2,746 0.0 0 0 == The 250 MB of shared pool is free. Why would it cause reloads if there is so much free space available? Any help is appreciated. Ashish
RE: Memory strangeness on Win2k
You need to increase value for SGA_MAX_SIZE parameter. Ashish OCP DBA -Original Message- Baumgartel Sent: Friday, April 04, 2003 5:04 PM To: Multiple recipients of list ORACLE-L So I have DB (9.2.0.1) running on Win2K, with db_cache_size of 32M. Windows Task Manager shows 600+ MB of free physical memory. ALTER SYSTEM SET DB_CACHE_SIZE=50248000 SCOPE=BOTH fails with ORA-00384: Insufficient memory to grow cache. I'm going to try ...SCOPE=SPFILE, then bouncing the instance, but any other ideas would be appreciated. I really prefer running Oracle on Unix. TIA, Paul Baumgartel __ Do you Yahoo!? Yahoo! Tax Center - File online, calculators, forms, and more http://tax.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ashish INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL- cursors and commits
Title: PL/SQL- cursors and commits As the book says, it fails with following error(9.2.0.1 on Win2k). declare*ERROR at line 1:ORA-01002: fetch out of sequenceORA-06512: at line 12 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Wiegand, KurtSent: Friday, April 04, 2003 9:39 AMTo: Multiple recipients of list ORACLE-LSubject: PL/SQL- cursors and commits I've been 'experimenting' with the following code in 8.1.5 and it seems to work fine. However, my "ORACLE PL/SQL" book from O'REILLY (Steven Feuerstein Bill Pribyl 1997) leads me to believe that it should not work. They state "As soon as a cursor with a FOR UPDATE is OPENed, all rows...are locked. When [a COMMIT]..occurs, the locks...are released. As a result, you cannot execute another FETCH against a FOR UPDATE cursor after you COMMIT.." They go further to suggest an ORA-01002 would be returned. Any comments? Thanks. Kurt Wiegand [EMAIL PROTECTED] declare local_f1 ctest.f1%TYPE := 0; local_f2 ctest.f2%TYPE := 0; batch_count number(6) := 0; cursor c_select is select f1,f2 from ctest for update; begin open c_select; loop fetch c_select into local_f1, local_f2; exit when c_select%NOTFOUND; update ctest set f2 = f2 + 1 where current of c_select; batch_count := batch_count + 1; if batch_count 99 then batch_count := 0; commit; end if; end loop; close c_select; commit; end;
Sub-query in order by clause
Hello list, In Oracle 9.2.0.1, you can now order by using a single-row subquery Here is an example: SELECT emp.deptno, empno, enameFROM empORDER BY (SELECT deptnoFROM deptWHERE dept.deptno = emp.deptno ); Thequestion I have is what is theusefulness of this?Under which circumstances this can be used? Any ideas? -Ashish OCP DBA
