RE: anyone see my DBA_AUDIT_TRAIL ??? (solved) bug or Architecture ?
Hi, Is this characteristic of a bug or is the Oracle Architecture ? SQL desc dba_audit_exists ERROR: ORA-24372: invalid object for describe SQL select count(*) from dba_audit_exists; COUNT(*) -- 0 SQL desc dba_audit_exists Name Null?Type - OS_USERNAMEVARCHAR2(255) USERNAME VARCHAR2(30) USERHOST VARCHAR2(128) TERMINAL VARCHAR2(255) TIMESTAMP NOT NULL DATE OWNER VARCHAR2(30) OBJ_NAME VARCHAR2(128) ACTION_NAMEVARCHAR2(27) NEW_OWNER VARCHAR2(30) NEW_NAME VARCHAR2(128) OBJ_PRIVILEGE VARCHAR2(16) SYS_PRIVILEGE VARCHAR2(40) GRANTEEVARCHAR2(30) SESSIONID NOT NULL NUMBER ENTRYID NOT NULL NUMBER STATEMENTID NOT NULL NUMBER RETURNCODENOT NULL NUMBER Sinardy -Original Message- From: Sinardy Xing Sent: 08 April 2002 11:41 Subject: anyone see my DBA_AUDIT_TRAIL ??? Hi guys, Please help me solved this SQL show user USER is SYS SQL select owner, object_name, object_type from all_objects where object_name = 'DBA_AUDIT_TRAIL'; OWNER OBJECT_NAMEOBJECT_TYPE -- -- -- SYSDBA_AUDIT_TRAILVIEW PUBLIC DBA_AUDIT_TRAILSYNONYM SQL desc DBA_AUDIT_TRAIL ERROR: ORA-24372: invalid object for describe Why I can not describe my DBA_AUDIT_TRAIL ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinardy Xing INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
OAS 4.0.8.2
Hi I am using OAS 4082 on Sun Solaris, with Oracle 8.1.6. I was wondering if there is any way of Using 2 DAD's (Data Access Descriptors) with one plsql cartridge agent ? Regards Saj -- Sajid Iqbal Database Team Leader Email: [EMAIL PROTECTED] Website: http://www.vianetworks.co.uk -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
How to Audit a table
Hi, How can I audit a table whether is selected as SELECT col1, col2 FROM tab WHERE col1 = 'condition'; and NOT all the SELECT queries I'm using Oracle 8i Thanks Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinardy Xing INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
AW: two listeners problem ???
What's about PORT 2481 for GIOP, I think you also have to use different ports for GIOP (2481 für LISTENER817 and 2482 for LISTENER816). regards Volker Schoen E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: Janet Linsy [mailto:[EMAIL PROTECTED]] Gesendet: Samstag, 6. April 2002 08:08 An: Multiple recipients of list ORACLE-L Betreff: two listeners problem ??? Hi, I have two databases ORCL817 and ORCL816 in version 817, 816 on the same machine Sun 5.7. I'd like to configure seperate listener for each database. I used different port, listener name (1521 and LISTENER817 for ORCL817 , 1522 and LISTENER816 for ORCL816), but somehow the listener on 1522 cannot be started. Why??? Here is the listener.ora and tnsnames.ora for both database: listener.ora for 816 = LISTENER816 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1522)) ) ) (DESCRIPTION = (PROTOCOL_STACK = (PRESENTATION = GIOP) (SESSION = RAW) ) (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 2481)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCL816) (ORACLE_HOME = /export/apps/oracle/admin/product/8.1.6) (SID_NAME = ORCL816) ) tnsnames.ora for 816 = ORCL816 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1522)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL816) ) ) listener.ora for 817 = LISTENER817 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1521)) ) ) (DESCRIPTION = (PROTOCOL_STACK = (PRESENTATION = GIOP) (SESSION = RAW) ) (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 2481)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCL817) (ORACLE_HOME = /export/apps/oracle/admin/product/8.1.7) (SID_NAME = ORCL817) ) ) tnsnames.ora = ORCL817 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL817) ) ) Anyone knows what went wrong? Thank you! Janet __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
THANKS - a PL/SQL question - how to catch errors without going i
Many thanks to all who replied ! Have a nice day ! DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -Original Message- Sent: Thu, April 04, 2002 9:37 PM To: Multiple recipients of list ORACLE-L ex Have you considered just adding another exception handler: i := 1; while i 10 loop begin select the_name from the_table into myvar where the_id = i ; exception when others then null; - or whatever you want to do; end; end loop; The net effect is the same. HTH - Brian -Original Message- Bronfin Sent: Thursday, April 04, 2002 12:49 PM To: Multiple recipients of list ORACLE-L ex ps , i meant i := 1; while i 10 loop select the_name from the_table into myvar where the_id = i ; end loop; DBAndre -Original Message- From: Andrey Bronfin Sent: Thu, April 04, 2002 9:50 PM To: [EMAIL PROTECTED] (E-mail); [EMAIL PROTECTED] (E-mail); oralist@lists (E-mail) Subject: a PL/SQL question - how to catch errors without going into exceptions block Dear gurus ! I'm wondering whtether i can catch an SQL error (from inside a PL/SQL proc) without jumping to the EXCEPTION block OR is there a way to jump back to the body of the proc from the EXCEPTION block (i know that GOTO can not do it). For example , assume i have users with IDs 1,2,5,6 in my table and i want to do some loop like this i := 1; while i 10 loop select the_name from the_table into myvar where the_id = 1; end loop; . I will be thrown to the EXCEPTION block as soon as i becomes 3. And i can never go back to the loop from the EXCEPTION block , in order to continue looping ;-( So , can i just tell PL/SQL something like never mind if U fail (i.e. an exception is thrown) , just go to the next iteration ... I'm wondering if there is something similar to PERL's next if . Thanks a lot Andre -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Brian McGraw INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Pl/SQL code help
Hallo,
I have some trouble with this pl/sql procedure. I would like
that this lvsql to be run only if the field Borttags_flagg = 0 but i get an error in
the if statement, whatis wrong with this? It get the erromressage:
LS-00103: Encountered the symbol ||AvdNr|| when expecting one of the following:
. ( * % = - + / at in mod not rem then
an exponent (**) or != or ~= = = and or like
betwe..
when I compile the whole procedure. Please help me with this.
(If the borttags_flagg = 1 then it will continue the loop and check for next one.
etc...
If A'||AvdNr||'.ICA_ARTIKEL'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 '' ||
then
(this lvsql is to be run only if field borttags_flagg = 0)
lvSQL := 'SELECT
ICA_ARTIKEL.FSGPRIS,ICA_ARTIKEL.BORTTAGS_FLAGG,ICA_ARTIKEL.PANTBELOPP ' ||
--PBK.LPKORGEANREL.EANREL,PBK.LPKORGEANREL.VARUTYP ' ||
'FROM
A'||AvdNr||'.ICA_ARTIKEL'||LookUpServerName|| ' ' ||
--PBK.LPKORGEANREL ' ||
'WHERE ICA_ARTIKEL.EAN=' || EanLPVara || ' '
||
'AND
ICA_ARTIKEL.DATUMTO_DATE('''||inDatum||''',''-MM-DD'') ' ||
-- 'AND ICA_ARTIKEL.BORTTAGS_FLAGG = 0 '
||
'ORDER BY DATUM DESC';
The whole procedure you can see in the file:
(See attached file: testplsql.SQL)
Thanks in advance
Roland S
testplsql.SQL
Description: Binary data
data block stockage capacity
How can I calculate the appropriate stocakge capacity space for a block (8K). It is exact to use data BLOCk_SIZE*(PCTUSED/100). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Bernard, Gilbert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Oracle9i init.ora / Re: ORACLE-L Digest -- Volume 2002, Number 086
We had a visit today from oracle support. The guy took a look on our NT plaything for testing oracle 9i and said: How come you have only 512MB memory on this? Your database and OEM console alone use all this ram. UPGRADE Yechiel Adar, Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, March 27, 2002 9:43 PM BOFH: you might want to check and see if your BIOS supports 512MB RAM and if you have any open slots, if so, assuming they aren't locked, you can steal some RAM from other people's computers in your office (they probably won't notice any difference, othrewise mumble something about how the last router upgrade had driver problems, and the network must be why things seem so slow). otherwise find out where the LAN guys hide the keys (promise them Krispy Creme doughnuts?). ORACLE-L Digest -- Volume 2002, Number 086 -- From: Mark Leith [EMAIL PROTECTED] Date: Tue, 26 Mar 2002 16:58:42 - Subject: RE: pL/SQL PROCEDUR LMAO Obligatory Oracle Question: Does anybody have a sample init.ora file that they use for a 9.0.1 instance, for a sandbox database on Win2K with 512Mb RAM? Basically for my desktop PC play database.. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Oracle Replication - is it on by default?
Hi folks, we've never identified any requirement here for using any kind of replication. Consequently I know nothing whatsoever about Oracle Replication. Now I've been asked whether or not we use Oracle Replication and, if so, whether it can be disabled. So can anyone tell me whether Replication is a feature automatically included in Oracle 8i Enterprise Edition? And is there an easy way of telling whether or not it's on? If it's on, can it be turned off (if that's a meaningful question!), and if so, how? I've tried briefly RTFMing, but although the manuals contain a wealth of info about how to use Replication, I can see nothing about how to tell whether it's active or not, and how to switch it on or off. Any pointers, please, anyone? Paul Vincent -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Vincent INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
a theoretical question
Deart gurus ! I'm just wondering what happens in this situation : i issue the following SQL : update AAA set BBB=0 where ROWID='X' ; Now , if the value of the BBB column is already 0 for the given row (or a set of rows) , what will actually happen behind the curtains ? I mean, will Oracle somehow detect that actually the update does not need to occur , or will it perform the update regardless of the current values of the columns . Thanks a lot, DBAndrey -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: anyone see my DBA_AUDIT_TRAIL ??? (solved) bug or Architecture ?
You don't say which version you are using but some versions of 8 had a problem with desc and synonyms. Try desc sys.dba_audit_trail and see if that works. I know this was a problem in early 8.0 (8.03, 8.0.4) but thought it was fixed by 8i. John [EMAIL PROTECTED] wrote: Hi, Is this characteristic of a bug or is the Oracle Architecture ? SQL desc dba_audit_exists ERROR: ORA-24372: invalid object for describe SQL select count(*) from dba_audit_exists; COUNT(*) -- 0 SQL desc dba_audit_exists Name Null?Type - OS_USERNAMEVARCHAR2(255) USERNAME VARCHAR2(30) USERHOST VARCHAR2(128) TERMINAL VARCHAR2(255) TIMESTAMP NOT NULL DATE OWNER VARCHAR2(30) OBJ_NAME VARCHAR2(128) ACTION_NAMEVARCHAR2(27) NEW_OWNER VARCHAR2(30) NEW_NAME VARCHAR2(128) OBJ_PRIVILEGE VARCHAR2(16) SYS_PRIVILEGE VARCHAR2(40) GRANTEEVARCHAR2(30) SESSIONID NOT NULL NUMBER ENTRYID NOT NULL NUMBER STATEMENTID NOT NULL NUMBER RETURNCODENOT NULL NUMBER Sinardy -Original Message- From: Sinardy Xing Sent: 08 April 2002 11:41 Subject: anyone see my DBA_AUDIT_TRAIL ??? Hi guys, Please help me solved this SQL show user USER is SYS SQL select owner, object_name, object_type from all_objects where object_name = 'DBA_AUDIT_TRAIL'; OWNER OBJECT_NAMEOBJECT_TYPE -- -- -- SYSDBA_AUDIT_TRAILVIEW PUBLIC DBA_AUDIT_TRAILSYNONYM SQL desc DBA_AUDIT_TRAIL ERROR: ORA-24372: invalid object for describe Why I can not describe my DBA_AUDIT_TRAIL ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ora NT DBA INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: a theoretical question
It will perform the update regardless of current value of the column. The value checking is only done if 1. You have specified a where clause 2. There is a check constraint that would require such check 3. You have a explicit condition check in pre or post update trigger that would require it. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, April 08, 2002 9:23 AM To: Multiple recipients of list ORACLE-L Deart gurus ! I'm just wondering what happens in this situation : i issue the following SQL : update AAA set BBB=0 where ROWID='X' ; Now , if the value of the BBB column is already 0 for the given row (or a set of rows) , what will actually happen behind the curtains ? I mean, will Oracle somehow detect that actually the update does not need to occur , or will it perform the update regardless of the current values of the columns . Thanks a lot, DBAndrey -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). ***1 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 ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
Another RMAN Problem --- Urgent !!
Dear All,
I am totally out of my depth here and this is a production db which needs to
be restored urgently.
I shall explain my scenario first. Our application team had lost some data
after last Wednesday night's
backup. They have a tool to have a dump of the data from the database from
which they can extract the
data if needed. This dump was taken on Thursday morning. Due to some ***hole
they lost some more data
on Thursday during the day. They recovered this data from the database dump
which they had taken on
Thursday morning.
They now want the data which was lost on Wednesday. Since there was no
database dump available prior to
this, I suggested that we can create a duplicate database upto a point of
time (as on Wednesday night) to a
different location on the server. They could then take a database dump with
their tool and extract the lost records.
This is my script which I compiled with help from Metalink and an old
post from John Hallas (the backup of our target database is taken on tape
with Veritas Netbackup) :
#!/bin/ksh
rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary /
run {
set until time to_date('20-MAR-2002 20:00:00','DD-MON- HH24:MI:SS);
allocate channel c_dlt1 type 'SBT_TAPE';
allocate auxiliary channel dupdb_d1 type disk;
setlimit channel c_dlt1 kbytes 67108864 maxopenfiles 128 readrate 256;
setlimit channel dupdb_d1 kbytes 67108864 maxopenfiles 128 readrate 256;
set newname for datafile
'/disk01/oradata/sid1/data/system01sid1.dbf'
to '/disk01/oradata/test/data/system01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/rbs01sid1.dbf'
to '/disk01/oradata/test/data/rbs01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/temp01sid1.dbf'
to '/disk01/oradata/test/data/temp01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/tools01sid1.dbf'
to '/disk01/oradata/test/data/tools01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/users01sid1.dbf'
to '/disk01/oradata/test/data/users01sid1.dbf';
duplicate target database to test
logfile
group 1 '/disk03/oradata/test/logs/log01a01sid1.dbf' size 180M,
group 2 '/disk03/oradata/test/logs/log02a01sid1.dbf' size 180M,
group 3 '/disk03/oradata/test/logs/log03a01sid1.dbf' size 180M;
}
What happens is that RMAN connects to the target, catalog and auxiliary
database and goes into the
RMAN prompt and just hangs thereafter some time, when I type 'exit'
there out of frustration, I
get the following errors :
./create_dupdb.sh[5]: run: not found
./create_dupdb.sh[9]: allocate: not found
./create_dupdb.sh[11]: allocate: not found
./create_dupdb.sh[13]: setlimit: not found
./create_dupdb.sh[14]: setlimit: not found
./create_dupdb.sh[17]: /disk01/oradata/sid1/data/system01sid1.dbf: cannot
execute
./create_dupdb.sh[18]: to: not found
./create_dupdb.sh[21]: /disk01/oradata/sid1/data/rbs01sid1.dbf: cannot
execute
./create_dupdb.sh[22]: to: not found
./create_dupdb.sh[29]: /disk01/oradata/sid1/data/temp01sid1.dbf: cannot
execute
./create_dupdb.sh[30]: to: not found
./create_dupdb.sh[33]: /disk01/oradata/sid1/data/tools01sid1.dbf: cannot
execute
./create_dupdb.sh[34]: to: not found
./create_dupdb.sh[37]: /disk01/oradata/sid1/data/users01sid1.dbf: cannot
execute
./create_dupdb.sh[38]: to: not found
./create_dupdb.sh[77]: duplicate: not found
./create_dupdb.sh[79]: logfile: not found
./create_dupdb.sh[80]: group: cannot execute
./create_dupdb.sh[81]: group: cannot execute
./create_dupdb.sh[82]: group: cannot execute
./create_dupdb.sh[83]: syntax error at line 84 : `}' unexpected
Could anybody please help me in identifying what is the problem and why does
it say 'run:not found' and the
other subsequent lines ?? This is highly urgent for me !!
Thanks and Regards,
Samir
Samir Sarkar
Oracle DBA
SchlumbergerSema
Email : [EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone : +44 (0) 115 - 957 6028
EPABX : +44 (0) 115 - 957 6418 Ext. 76028
Fax : +44 (0) 115 - 957 6018
___
This email is confidential and intended solely for the use of the
individual to whom it is addressed. Any views or opinions presented are
solely those of the author and do not necessarily represent those of
SchlumbergerSema.
If you are not the intended recipient, be advised that you have received this
email in error and that any use, dissemination, forwarding, printing, or
copying of this email is strictly prohibited.
If you have received this email in error please notify the SchlumbergerSema Helpdesk
by telephone on +44 (0) 121 627 5600.
___
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: SARKAR, Samir
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
Re: Oracle Replication - is it on by default?
I believe the scripts to create the various stored procedures come with the Enterprise Edition but Replication is not something that Oracle does on its own. At best, the packages are automatically created when you create the database. There is a LOT of work you have to do on your own to set up replication. I think you can state with confidence that you are not using replication. --- Paul Vincent [EMAIL PROTECTED] wrote: Hi folks, we've never identified any requirement here for using any kind of replication. Consequently I know nothing whatsoever about Oracle Replication. Now I've been asked whether or not we use Oracle Replication and, if so, whether it can be disabled. So can anyone tell me whether Replication is a feature automatically included in Oracle 8i Enterprise Edition? And is there an easy way of telling whether or not it's on? If it's on, can it be turned off (if that's a meaningful question!), and if so, how? I've tried briefly RTFMing, but although the manuals contain a wealth of info about how to use Replication, I can see nothing about how to tell whether it's active or not, and how to switch it on or off. Any pointers, please, anyone? Paul Vincent -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Vincent INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Another RMAN Problem --- Urgent !!
Samir,
It looks like your Rman commands are not being passed to Rman, and are
instead being executed by ksh.
Try moving all of your Rman command into a separate file, and changing your
rman command to:
rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary / cmdfile (rman
command file name) msglog {output log file name)
I know there is ane asier way to do this within ksh, but this would be the
fastest way for me.
hope this helps.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Monday, April 08, 2002 9:44 AM
To: Multiple recipients of list ORACLE-L
Dear All,
I am totally out of my depth here and this is a production db which needs to
be restored urgently.
I shall explain my scenario first. Our application team had lost some data
after last Wednesday night's
backup. They have a tool to have a dump of the data from the database from
which they can extract the
data if needed. This dump was taken on Thursday morning. Due to some ***hole
they lost some more data
on Thursday during the day. They recovered this data from the database dump
which they had taken on
Thursday morning.
They now want the data which was lost on Wednesday. Since there was no
database dump available prior to
this, I suggested that we can create a duplicate database upto a point of
time (as on Wednesday night) to a
different location on the server. They could then take a database dump with
their tool and extract the lost records.
This is my script which I compiled with help from Metalink and an old
post from John Hallas (the backup of our target database is taken on tape
with Veritas Netbackup) :
#!/bin/ksh
rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary /
run {
set until time to_date('20-MAR-2002 20:00:00','DD-MON- HH24:MI:SS);
allocate channel c_dlt1 type 'SBT_TAPE';
allocate auxiliary channel dupdb_d1 type disk;
setlimit channel c_dlt1 kbytes 67108864 maxopenfiles 128 readrate 256;
setlimit channel dupdb_d1 kbytes 67108864 maxopenfiles 128 readrate 256;
set newname for datafile
'/disk01/oradata/sid1/data/system01sid1.dbf'
to '/disk01/oradata/test/data/system01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/rbs01sid1.dbf'
to '/disk01/oradata/test/data/rbs01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/temp01sid1.dbf'
to '/disk01/oradata/test/data/temp01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/tools01sid1.dbf'
to '/disk01/oradata/test/data/tools01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/users01sid1.dbf'
to '/disk01/oradata/test/data/users01sid1.dbf';
duplicate target database to test
logfile
group 1 '/disk03/oradata/test/logs/log01a01sid1.dbf' size 180M,
group 2 '/disk03/oradata/test/logs/log02a01sid1.dbf' size 180M,
group 3 '/disk03/oradata/test/logs/log03a01sid1.dbf' size 180M;
}
What happens is that RMAN connects to the target, catalog and auxiliary
database and goes into the
RMAN prompt and just hangs thereafter some time, when I type 'exit'
there out of frustration, I
get the following errors :
./create_dupdb.sh[5]: run: not found
./create_dupdb.sh[9]: allocate: not found
./create_dupdb.sh[11]: allocate: not found
./create_dupdb.sh[13]: setlimit: not found
./create_dupdb.sh[14]: setlimit: not found
./create_dupdb.sh[17]: /disk01/oradata/sid1/data/system01sid1.dbf: cannot
execute
./create_dupdb.sh[18]: to: not found
./create_dupdb.sh[21]: /disk01/oradata/sid1/data/rbs01sid1.dbf: cannot
execute
./create_dupdb.sh[22]: to: not found
./create_dupdb.sh[29]: /disk01/oradata/sid1/data/temp01sid1.dbf: cannot
execute
./create_dupdb.sh[30]: to: not found
./create_dupdb.sh[33]: /disk01/oradata/sid1/data/tools01sid1.dbf: cannot
execute
./create_dupdb.sh[34]: to: not found
./create_dupdb.sh[37]: /disk01/oradata/sid1/data/users01sid1.dbf: cannot
execute
./create_dupdb.sh[38]: to: not found
./create_dupdb.sh[77]: duplicate: not found
./create_dupdb.sh[79]: logfile: not found
./create_dupdb.sh[80]: group: cannot execute
./create_dupdb.sh[81]: group: cannot execute
./create_dupdb.sh[82]: group: cannot execute
./create_dupdb.sh[83]: syntax error at line 84 : `}' unexpected
Could anybody please help me in identifying what is the problem and why does
it say 'run:not found' and the
other subsequent lines ?? This is highly urgent for me !!
Thanks and Regards,
Samir
Samir Sarkar
Oracle DBA
SchlumbergerSema
Email : [EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone : +44 (0) 115 - 957 6028
EPABX : +44 (0) 115 - 957 6418 Ext. 76028
Fax : +44 (0) 115 - 957 6018
___
This email is confidential and intended solely for the use of the
individual to whom it is addressed. Any views or opinions presented are
solely those of the author and do not necessarily represent those of
SchlumbergerSema.
If you are not the intended recipient, be advised that you have
RE: Oracle 8i Study Guide
Helen: You could try http://www.examcram.com. Also, I just found Oracle's list of test objectives. http://www.oracle.com/education/certification/objectives/index.html?content. html You may just want to consider going ahead and buying the study books. Here are the Sybex books. http://www.amazon.com/exec/obidos/ASIN/0782126855/qid=1018271515/sr=2-1/ref= sr_2_1/002-7587220-4526465 Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, April 05, 2002 7:38 PM To: Multiple recipients of list ORACLE-L I just got a job after being out of work in Denver for the past eight months. I need to complete the Oracle certification. I will completing Oracle's Mixed Release Path program. Does anyone know of a free site where I can get study guide material for the Oracle 8i exams? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Helen J Mitchell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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:Please help resolving report generation performance probl
Three words: TUNE THE SQL. Database performance is 80% SQL Tuning and 20% database tuning. It would sound like you've got a statement or two that is doing a whole bunch of full table scans, probably somewhere inside a nested loop. Take a look inside $ORACLE_HOME\sqlplus\admin for the plustrce.sql file. If you've no tuning tools it will help. Dick Goulet Reply Separator Author: Denmark Weatherburne [EMAIL PROTECTED] Date: 4/7/2002 10:18 AM Hi DBA's, I've been trying to isolate the bottleneck with our Oracle database. I work as an Oracle DBA for the Government of a developing country (Belize). Recently, as it is income tax time, the department has to reconcile all witholdings by the employer with their payment receipt records. This involves some data entry and a report generation by employer (witholder) which lists all witholdings by each employee. This report can generate lots of pages depending on the number of employees. In some cases, the report has to be run overnight, as it takes too long (several hours) to generate. I've tried giving more resources to Oracle. I've tried creating a copy of the production database on another machine to use only for generating reports. I've increased the size and number of rollback segments. I've tuned some parameters. However, I have not observed any significant improvement in the report generation performance. I know tuning the SQL might be required, however, I don't have much experience in this area. The SQL statemements were written by consultants who have long left. We do have the source code though. We are running Oracle 8.0.5.2.1 on NT 4.0 The NT server is a Dell 4400 with Dual CPU and 1GB RAM We are using hardware RAID 5. Our database is OLTP with reporting. It is a small database (exported data is about 150 MB). I would appreciate your recommendations and advice. Thanks in advance, Denmark Weatherburne _ Chat with friends online, try MSN Messenger: http://messenger.msn.com The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. Report_SQL.zip Description: Zip archive The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. Hourly_Bstat_Estat_Reports.zip Description: Zip archive
RE: RE: Oracle vs. MS SQL
Gene - The $40K is the Enterprise Edition pricing as I recall. Can you move to Standard Edition? If you are using EE features, then chances are that MS SQL won't do the job. Also you can point out the eWeek benchmark between Oracle and MS SQL. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, April 05, 2002 6:23 PM To: Multiple recipients of list ORACLE-L OK, timing is impeccable. My boss just got the Oracle Bill, new licensing model $40k per processor for web based apps and flipped. I have some MSCE's working here pushing him to switch to SQL*server. Does anyone know where I can find reasons to stay w/ Oracle? Some things already mentioned here, but the MSCE's would say this list is bias, go figure :) Does SQLServer 2000 support blobs, row level locking, etc? Thanks, Gene PS. Do I move on to another Oracle shop or switch to SQLserver? OMG, the thought of working only on windoze makes me puke. I know this answer! [EMAIL PROTECTED] 04/05/02 14:11 PM There are some technical points worth considering. For example, SQL Server does not have true row level locking. It's table level locking, or some really creative SQL to fake it. This has a direct impact on scalability and performance. --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Oops, a couple of items I didn't make clear: - I was never able to compare the cost of Oracle support with the cost of Microsoft support. Oracle prices annual maintenance, which includes the right to upgrade to a new version of Oracle. MS prices out per incident or for all MS software at a location. If you can estimate the number of calls/month, then you could compare. - Oracle DBA salary vs. MS SQL DBA salary. I feel the difference is primarily due to less experience, training. I find it ironic that this probably causes less reliability for Microsoft (Microsoft has even complained that organizations don't assign their best people to administer MS products), yet then Microsoft brags about how you can save money because their people ar cheaper. -Original Message- Sent: Friday, April 05, 2002 11:14 AM To: Multiple recipients of list ORACLE-L I recently prepared a total cost comparison between Oracle and MS SQL. I appreciate the support several people on this list provided me. In return, here are some of the main points I learned. - For smaller systems, investigate whether Oracle Standard Edition will meet your requirements. For example, most people assume that to use replication, you need EE. For our purposes the basic replication that comes with SE was adequate. - Microsoft also offers SQL in both EE and SE versions. Thanks very much to Gints Plivna for providing me a feature-by-feature comparison between the different versions. MS SE is not equivalent to Oracle SE. In most cases, the more valid comparison is between MS SQL EE and Oracle SE. - For maintenance, there are two parts to consider: Upgrade privilege and support. Oracle bundles both of these together. Make sure Microsoft is priced with Software Assurance, which gives upgrade privilege. Microsoft prices support by the incident or by the location (all Microsoft software). I was never able to get a comparison. - MS SQL EE with Software Assurance is actually more expensive than Oracle SE. Priced by the CPU. - Since pricing is by CPU and RISC systems offer higher database performance (according to many people on this list) and Oracle offers higher performance in a head-to-head comparison (according to the recently published Eweek benchmark), I compared Oracle SE on a 1-CPU Sun box with MS SQL on a 2-CPU Intel box. The Intel box was cheaper, but those two CPUs really kill you on licensing! In my mind I am convinced that both setups could offer equivalent performance. - I was provided figures that the average DBA salary (including health, vacation, etc.) on Oracle is $85,000 and on MS SQL $68,500. A lading industry analyst stated that the main reason MS SQL sites have less reliability is because there are few processes to ensure high availability, high performance. Developing these processes in the MS SQL world is more trial-and-error while these are well-documented in the Oracle world. I would add that several authors that participate in this list have created that documentation. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
Foreign Objects in the System Tablespace.
I am trying to determine what Oracle officially considers foreign objects in the SYSTEM tablespace. If you check out Note 122669.1, section 7.1, Oracle recommends a query to find foreign objects in your system tablespace. This query will report such users as: AURORA$JIS$UTILITY$ CTXSYS MDSYS ORDSYS OSE$HTTP$ADMIN OUTLN It is part of our normal procedures to setup a TOOLS tablespace, and set this as the default tablespace for the user SYSTEM. Objects such as SQLPLUS_PRODUCT_PROFILE will be created in this tablespace. I've browsed around MetaLink and posted in one of the forums, but I'm not really getting any concrete answers as to which users should be permitted to have objects in the SYSTEM tablespace. I do know that it is OK to move OUTLN to another tablespace. Comments would be appreciated. Thanks, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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:data block stockage capacity
Close, but no cookie. Look in the Admin guide, appendix A-1. There is some overhead that you also need to take into consideration. Dick Goulet Reply Separator Author: Bernard; Gilbert [EMAIL PROTECTED] Date: 4/8/2002 4:58 AM How can I calculate the appropriate stocakge capacity space for a block (8K). It is exact to use data BLOCk_SIZE*(PCTUSED/100). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Bernard, Gilbert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: RE: Oracle vs. MS SQL
Here are excerpts from a thread posted by Jim Hawkins and Jared Still back in February. Subj: Just Got Back from SqlServer 2000 training On Mon, 18 Feb 2002, Jim Hawkins wrote: During the class, I kept a list of all the I can't believe this is really the case with SQL*Server... items, and thought you might all like to see it. These are just notes I took on a Palm Pilot, so forgive me if they are a litte undetailed. I walked away from the class thinking, this is just MS Access with bells and whistles. I'm not saying it doesn't have its place in the database market, but I just don't see how it competes with Oracle and DB2. If you even want to think about scaling, you have to implement Windows clustering, which is one of the hidden costs I see that Microsoft doesn't come right out and say. *Row size cannot span multiple 8k pages, therefore max row size = 8k *Cannot take DB out of archivelog mode. Can limit what is posted to txn log, but cannot stop it. *Txn logs not mirrored. Must rely on RAID or other mirroring software. *Separate permissions for RI checking. Requires two permission grants if foreign key exists - one for child table and one for parent table. Called REFERENCES permission. *Recommended that ALL production objects owned by DBO - not conducive to multi-schema instances. *Activities that are restricted during backups: 1. Creating or modifying databases. 2. Performing autogrow operations. 3. Creating indexes. 4. Performing nonlogged operations. 5. Shrinking a database. *Backups directly to tape require the tape to be attached locally to SQL Server. *When txn log fills up, have to just truncate the log in order for processing to continue. Leaves system vulnerable until you get a full DB backup. *If you have a 100GB DB that is full, your backup will be 100GB. No compression of backups! Jared, I was going to respond, but you did a great job for me. Your points were my points exactly. I really tried to go to the SQL*Server class with an open mind thinking I'm adding a skill set, but I found myself constantly comparing to Oracle. I didn't mean to start the Holy War again, but thought it would make an interesting conversation. A bit more: Having databases in noarchivelog mode, especially during batch loads for data warehouses/datamarts is extremely important for a large database shop like ours. In terms of RAID, I was just pointing out that while we mirror our redo logs to at least two different groups with two different members, I was shocked that the transaction log in SQL*Server was in no way mirrored by SQL*Server. It was either do it at the hardware/OS level or risk it. Not a Mission Critical mentality. As for transferring 10GB over the network, this would be just backing up our archive logs, not to mention the datafiles themselves. We do it every day around the clock using our tape silo. We use RMAN with hotbackups directly to tape via Veritas NetBackup enterprise wide. 10GB is trivial in the Oracle world, however, judging by the response I got, not so trivial in the SQL*Server world. One last thing: Having been to the Oracle education classes, I was expecting to learn in depth how SQL*Server uses memory to buffer the database, shared SQL, etc. thinking this would be a major tuning strategy for SQL*Server. Based on the nature of your system, you could gear the equivalent of an SGA accordingly. I almost spit up my two cups of coffee when the instructor showed me the GUI slide-bar that controls memory allocation to SQL*Server. If you need more, just slide the bar to the right... I still chuckle... Jim Hawkins Oracle Database Administrator [EMAIL PROTECTED] wrote: Couldn't resist responding to this. *Cannot take DB out of archivelog mode. Can limit what is posted to txn log, but cannot stop it. Why would you want to? So you have the remote possibility of ending up with a corrupt, unrecoverable database if the power supply on the system fails? JS: Taking a database out of archive mode is certainly valid for large load operations. Let's see, I want to load 50 gig of raw data into my data warehouse tonight, that will generate about 800 gig of redo. Do I really want to do generate that much redo, deal with the overhead, and back it up besides? Or would it be easier to put the DW back in archive mode and back up the new data? *Txn logs not mirrored. Must rely on RAID or other mirroring software. Hardware RAID/mirrors are much better than software, so if you are comparing Oracle software based mirrors to the hardware based ones we use then our way is much faster JS: No mention of reliability there though is there? If I don't have control over the hardware layout, I want Oracle to mirror the logs, period. Backups directly to tape require the tape to be attached locally to SQL Server. Okay, if you really want to transfer your 10+GB database over the network each night, I suppose
Re: Another RMAN Problem --- Urgent !!
Your input to RMAN should be a script or here list. Scrap the shell script, put
your run script into a file, run RMAN from the command line and call the rman run
script that you just created.. Also, set NLS_DATE_FORMAT and NLS_LANG. Depending on
your database version the to_date function will not work as you have it in set
until time.
Jay
[EMAIL PROTECTED] 04/08/02 09:43AM
Dear All,
I am totally out of my depth here and this is a production db which needs to
be restored urgently.
I shall explain my scenario first. Our application team had lost some data
after last Wednesday night's
backup. They have a tool to have a dump of the data from the database from
which they can extract the
data if needed. This dump was taken on Thursday morning. Due to some ***hole
they lost some more data
on Thursday during the day. They recovered this data from the database dump
which they had taken on
Thursday morning.
They now want the data which was lost on Wednesday. Since there was no
database dump available prior to
this, I suggested that we can create a duplicate database upto a point of
time (as on Wednesday night) to a
different location on the server. They could then take a database dump with
their tool and extract the lost records.
This is my script which I compiled with help from Metalink and an old
post from John Hallas (the backup of our target database is taken on tape
with Veritas Netbackup) :
#!/bin/ksh
rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary /
run {
set until time to_date('20-MAR-2002 20:00:00','DD-MON- HH24:MI:SS);
allocate channel c_dlt1 type 'SBT_TAPE';
allocate auxiliary channel dupdb_d1 type disk;
setlimit channel c_dlt1 kbytes 67108864 maxopenfiles 128 readrate 256;
setlimit channel dupdb_d1 kbytes 67108864 maxopenfiles 128 readrate 256;
set newname for datafile
'/disk01/oradata/sid1/data/system01sid1.dbf'
to '/disk01/oradata/test/data/system01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/rbs01sid1.dbf'
to '/disk01/oradata/test/data/rbs01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/temp01sid1.dbf'
to '/disk01/oradata/test/data/temp01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/tools01sid1.dbf'
to '/disk01/oradata/test/data/tools01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/users01sid1.dbf'
to '/disk01/oradata/test/data/users01sid1.dbf';
duplicate target database to test
logfile
group 1 '/disk03/oradata/test/logs/log01a01sid1.dbf' size 180M,
group 2 '/disk03/oradata/test/logs/log02a01sid1.dbf' size 180M,
group 3 '/disk03/oradata/test/logs/log03a01sid1.dbf' size 180M;
}
What happens is that RMAN connects to the target, catalog and auxiliary
database and goes into the
RMAN prompt and just hangs thereafter some time, when I type 'exit'
there out of frustration, I
get the following errors :
./create_dupdb.sh[5]: run: not found
./create_dupdb.sh[9]: allocate: not found
./create_dupdb.sh[11]: allocate: not found
./create_dupdb.sh[13]: setlimit: not found
./create_dupdb.sh[14]: setlimit: not found
./create_dupdb.sh[17]: /disk01/oradata/sid1/data/system01sid1.dbf: cannot
execute
./create_dupdb.sh[18]: to: not found
./create_dupdb.sh[21]: /disk01/oradata/sid1/data/rbs01sid1.dbf: cannot
execute
./create_dupdb.sh[22]: to: not found
./create_dupdb.sh[29]: /disk01/oradata/sid1/data/temp01sid1.dbf: cannot
execute
./create_dupdb.sh[30]: to: not found
./create_dupdb.sh[33]: /disk01/oradata/sid1/data/tools01sid1.dbf: cannot
execute
./create_dupdb.sh[34]: to: not found
./create_dupdb.sh[37]: /disk01/oradata/sid1/data/users01sid1.dbf: cannot
execute
./create_dupdb.sh[38]: to: not found
./create_dupdb.sh[77]: duplicate: not found
./create_dupdb.sh[79]: logfile: not found
./create_dupdb.sh[80]: group: cannot execute
./create_dupdb.sh[81]: group: cannot execute
./create_dupdb.sh[82]: group: cannot execute
./create_dupdb.sh[83]: syntax error at line 84 : `}' unexpected
Could anybody please help me in identifying what is the problem and why does
it say 'run:not found' and the
other subsequent lines ?? This is highly urgent for me !!
Thanks and Regards,
Samir
Samir Sarkar
Oracle DBA
SchlumbergerSema
Email : [EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone : +44 (0) 115 - 957 6028
EPABX : +44 (0) 115 - 957 6418 Ext. 76028
Fax : +44 (0) 115 - 957 6018
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Hostetter
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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
RE: Another RMAN Problem --- Urgent !!
Hi Samir,
Have you got your environment variables set up correctly. These would be the
following ones
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
export NB_ORA_SERV=server name
export NB_ORA_CLIENT=client name
export NB_ORA_SCHED=
export NB_ORA_CLASS=
These will all be set up in the rman backup scripts (I expect)
John
-Original Message-
Sent: 08 April 2002 14:44
To: Multiple recipients of list ORACLE-L
Dear All,
I am totally out of my depth here and this is a production db which needs to
be restored urgently.
I shall explain my scenario first. Our application team had lost some data
after last Wednesday night's
backup. They have a tool to have a dump of the data from the database from
which they can extract the
data if needed. This dump was taken on Thursday morning. Due to some ***hole
they lost some more data
on Thursday during the day. They recovered this data from the database dump
which they had taken on
Thursday morning.
They now want the data which was lost on Wednesday. Since there was no
database dump available prior to
this, I suggested that we can create a duplicate database upto a point of
time (as on Wednesday night) to a
different location on the server. They could then take a database dump with
their tool and extract the lost records.
This is my script which I compiled with help from Metalink and an old
post from John Hallas (the backup of our target database is taken on tape
with Veritas Netbackup) :
#!/bin/ksh
rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary /
run {
set until time to_date('20-MAR-2002 20:00:00','DD-MON- HH24:MI:SS);
allocate channel c_dlt1 type 'SBT_TAPE';
allocate auxiliary channel dupdb_d1 type disk;
setlimit channel c_dlt1 kbytes 67108864 maxopenfiles 128 readrate 256;
setlimit channel dupdb_d1 kbytes 67108864 maxopenfiles 128 readrate 256;
set newname for datafile
'/disk01/oradata/sid1/data/system01sid1.dbf'
to '/disk01/oradata/test/data/system01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/rbs01sid1.dbf'
to '/disk01/oradata/test/data/rbs01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/temp01sid1.dbf'
to '/disk01/oradata/test/data/temp01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/tools01sid1.dbf'
to '/disk01/oradata/test/data/tools01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/users01sid1.dbf'
to '/disk01/oradata/test/data/users01sid1.dbf';
duplicate target database to test
logfile
group 1 '/disk03/oradata/test/logs/log01a01sid1.dbf' size 180M,
group 2 '/disk03/oradata/test/logs/log02a01sid1.dbf' size 180M,
group 3 '/disk03/oradata/test/logs/log03a01sid1.dbf' size 180M;
}
What happens is that RMAN connects to the target, catalog and auxiliary
database and goes into the
RMAN prompt and just hangs thereafter some time, when I type 'exit'
there out of frustration, I
get the following errors :
./create_dupdb.sh[5]: run: not found
./create_dupdb.sh[9]: allocate: not found
./create_dupdb.sh[11]: allocate: not found
./create_dupdb.sh[13]: setlimit: not found
./create_dupdb.sh[14]: setlimit: not found
./create_dupdb.sh[17]: /disk01/oradata/sid1/data/system01sid1.dbf: cannot
execute
./create_dupdb.sh[18]: to: not found
./create_dupdb.sh[21]: /disk01/oradata/sid1/data/rbs01sid1.dbf: cannot
execute
./create_dupdb.sh[22]: to: not found
./create_dupdb.sh[29]: /disk01/oradata/sid1/data/temp01sid1.dbf: cannot
execute
./create_dupdb.sh[30]: to: not found
./create_dupdb.sh[33]: /disk01/oradata/sid1/data/tools01sid1.dbf: cannot
execute
./create_dupdb.sh[34]: to: not found
./create_dupdb.sh[37]: /disk01/oradata/sid1/data/users01sid1.dbf: cannot
execute
./create_dupdb.sh[38]: to: not found
./create_dupdb.sh[77]: duplicate: not found
./create_dupdb.sh[79]: logfile: not found
./create_dupdb.sh[80]: group: cannot execute
./create_dupdb.sh[81]: group: cannot execute
./create_dupdb.sh[82]: group: cannot execute
./create_dupdb.sh[83]: syntax error at line 84 : `}' unexpected
Could anybody please help me in identifying what is the problem and why does
it say 'run:not found' and the
other subsequent lines ?? This is highly urgent for me !!
Thanks and Regards,
Samir
Samir Sarkar
Oracle DBA
SchlumbergerSema
Email : [EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone : +44 (0) 115 - 957 6028
EPABX : +44 (0) 115 - 957 6418 Ext. 76028
Fax : +44 (0) 115 - 957 6018
___
This email is confidential and intended solely for the use of the
individual to whom it is addressed. Any views or opinions presented are
solely those of the author and do not necessarily represent those of
SchlumbergerSema.
If you are not the intended recipient, be advised that you have received
this
email in error and that any use, dissemination, forwarding, printing, or
copying of this email is strictly prohibited.
If you have received this email in error please notify the
Re: data block stockage capacity
It is truly impossible to calculate the exact storage capacity for an Oracle database block, for several reasons: * some data structures (ITLs, table chart, row chart, etc) grow and shrink due to the current state of transactions on rows in the block * uncommited DELETEs and UPDATEs which shrink a row reserve their space until COMMIT or ROLLBACK, again forming a dependency on the current state of transactions on rows in the block * most importantly, most Oracle datatypes (except DATEs and CHARs) are variable-length, adjusting the length dependent on data values PCTUSED is merely a threshold value, the percentage value under which used-space in the block must fall in order to return to the free list. Personally, I just tend to round the DB_BLOCK_SIZE down to the nearest 000 (i.e. 8192 down to 8000) in my own mind. It's far from scientific and far from exact, but when it's impossible to be exact, who cares? If you are trying to estimate how much space X rows of a specific table are going to consume, the best (and easiest) method is extrapolation. Obtain a relatively small sample of data (i.e. 10,000 rows), insert them into a table previously truncated (with DROP STORAGE) and then ANALYZE COMPUTE the table. The value of BLOCKS is the number of blocks populated. The value of #-rows/BLOCKS is your average density of rows per block, so take the number of rows you eventually expect (i.e. X) and divide that by the average density of rows per block to get the expected number of blocks. Sure, the last populated block in the table might be short a few rows, so if you feel like correcting for that, you can do so by querying FILE# and BLOCK# from the ROWID and doing a GROUP BY to COUNT(*) the number of rows per block. Usually you'll find that the highest block is a little short by Y rows, so recalculate using something like (10,000 - Y) / (BLOCKS - 1)... It's just as accurate as any other method and a helluva lot faster and easier to calculate. Hope this helps... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 08, 2002 6:58 AM How can I calculate the appropriate stocakge capacity space for a block (8K). It is exact to use data BLOCk_SIZE*(PCTUSED/100). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Bernard, Gilbert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
OCP-Network Exam
Hi, Greetings, I am on the preparation for my OCP 8i Network Administration exam. I welcome any tips or questions or model papers. So that i can prepare myself well for the exam. Looking forward to your inputs, With best regards, Muths _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Muthaiah INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Another RMAN Problem --- Urgent !!
I'm hoping that you have a sev 1 TAR open on this, as well. Keep
pestering them if you haven't had a response; keep the TAR status at
IMMEDIATE RESPONSE REQUIRED (by responding multiple times) whenever you
hit the ball back to them. Also, be sure that all relevant facts are
recorded online through MetaLink, in case this later degenerates into a
finger-pointing game. You cannot play that game with verbal records...
---
You are connecting both TARGET and AUXILIARY to the same database instance
(i.e. /). For a DUPLICATE DATABASE operation, you don't have to connect
to the TARGET at all, if I recall correctly; a DUPLICATE DATABASE operation
doesn't involve the TARGET. Sounds funny, until you consider that the
operation is reading from tape to the new AUXILIARY database instance.
For the AUXILIARY, be sure that you have already created another empty
database instance (using most of the TARGET databases init.ora parms, with
obvious exceptions like CONTROL_FILE) which is started up NOMOUNT before
starting this DUPLICATE DATABASE operation.
If what I suspect is correct, then I think you ought to be *glad* that RMAN
just hung instead of following its directions! Of course, a relevant
error message would a lot *nicer* than just hanging that way...
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, April 08, 2002 7:43 AM
Dear All,
I am totally out of my depth here and this is a production db which needs
to
be restored urgently.
I shall explain my scenario first. Our application team had lost some data
after last Wednesday night's
backup. They have a tool to have a dump of the data from the database from
which they can extract the
data if needed. This dump was taken on Thursday morning. Due to some
***hole
they lost some more data
on Thursday during the day. They recovered this data from the database
dump
which they had taken on
Thursday morning.
They now want the data which was lost on Wednesday. Since there was no
database dump available prior to
this, I suggested that we can create a duplicate database upto a point of
time (as on Wednesday night) to a
different location on the server. They could then take a database dump
with
their tool and extract the lost records.
This is my script which I compiled with help from Metalink and an old
post from John Hallas (the backup of our target database is taken on tape
with Veritas Netbackup) :
#!/bin/ksh
rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary /
run {
set until time to_date('20-MAR-2002 20:00:00','DD-MON- HH24:MI:SS);
allocate channel c_dlt1 type 'SBT_TAPE';
allocate auxiliary channel dupdb_d1 type disk;
setlimit channel c_dlt1 kbytes 67108864 maxopenfiles 128 readrate 256;
setlimit channel dupdb_d1 kbytes 67108864 maxopenfiles 128 readrate 256;
set newname for datafile
'/disk01/oradata/sid1/data/system01sid1.dbf'
to '/disk01/oradata/test/data/system01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/rbs01sid1.dbf'
to '/disk01/oradata/test/data/rbs01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/temp01sid1.dbf'
to '/disk01/oradata/test/data/temp01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/tools01sid1.dbf'
to '/disk01/oradata/test/data/tools01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/users01sid1.dbf'
to '/disk01/oradata/test/data/users01sid1.dbf';
duplicate target database to test
logfile
group 1 '/disk03/oradata/test/logs/log01a01sid1.dbf' size 180M,
group 2 '/disk03/oradata/test/logs/log02a01sid1.dbf' size 180M,
group 3 '/disk03/oradata/test/logs/log03a01sid1.dbf' size 180M;
}
What happens is that RMAN connects to the target, catalog and auxiliary
database and goes into the
RMAN prompt and just hangs thereafter some time, when I type
'exit'
there out of frustration, I
get the following errors :
./create_dupdb.sh[5]: run: not found
/create_dupdb.sh[9]: allocate: not found
/create_dupdb.sh[11]: allocate: not found
/create_dupdb.sh[13]: setlimit: not found
/create_dupdb.sh[14]: setlimit: not found
/create_dupdb.sh[17]: /disk01/oradata/sid1/data/system01sid1.dbf: cannot
execute
/create_dupdb.sh[18]: to: not found
/create_dupdb.sh[21]: /disk01/oradata/sid1/data/rbs01sid1.dbf: cannot
execute
/create_dupdb.sh[22]: to: not found
/create_dupdb.sh[29]: /disk01/oradata/sid1/data/temp01sid1.dbf: cannot
execute
/create_dupdb.sh[30]: to: not found
/create_dupdb.sh[33]: /disk01/oradata/sid1/data/tools01sid1.dbf: cannot
execute
/create_dupdb.sh[34]: to: not found
/create_dupdb.sh[37]: /disk01/oradata/sid1/data/users01sid1.dbf: cannot
execute
/create_dupdb.sh[38]: to: not found
/create_dupdb.sh[77]: duplicate: not found
/create_dupdb.sh[79]: logfile: not found
/create_dupdb.sh[80]: group: cannot execute
/create_dupdb.sh[81]: group: cannot execute
/create_dupdb.sh[82]: group: cannot execute
anyone know how to change tablespace names?
is there a back-end way to effect an alter tablespace rename to ... type of thing? yes, I know Oracle doesn't support mucking with the data dictionary, but . . . thx -bill Bill Magaliff Framework, Inc. 914-631-2322 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
utl_file_dir question
I have couple question. We need to set up 15 trainer in the training room by tomorrow for a pl/sql class. I need to set up utl_file_dir for them. This is NT environment. question 1: If I create 15 users in oracle, do I need to ask Nt admin to create system users for them as well? 2: It should be 15 different directories for each of them. How to set it in int.ora? 3: Since the training room are remote access to server. The utl_file_dir will write to local or server? Those are sound pretty easy question. I never set this up before and they want it now. I will check the manual as well. Thanks in advance, Joan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: OCP-Network Exam
1.You should study gui's. There are about 10 questions . 2.There are a few question about command line utilities and their parameters. 3.Connection Manager is important. thats all which I can remember - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 08, 2002 5:58 PM Hi, Greetings, I am on the preparation for my OCP 8i Network Administration exam. I welcome any tips or questions or model papers. So that i can prepare myself well for the exam. Looking forward to your inputs, With best regards, Muths _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Muthaiah INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Arslan Bahar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Another RMAN Problem --- Urgent !!
Jay and Tom,
Thanks a million for your replies. I have now ran the script by creating it
and
then executing it from the RMAN prompt. It worked but I got the following
error :
rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary /
Recovery Manager: Release 8.1.7.2.0 - Production
RMAN-06005: connected to target database: SID1 (DBID=647056675)
RMAN-06008: connected to recovery catalog database
RMAN-06020: connected to auxiliary database
RMAN run {execute script dup_db;}
RMAN-03021: executing script: dup_db
RMAN-03022: compiling command: set
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: dupdb_d1
RMAN-08500: channel dupdb_d1: sid=43 devtype=SBT_TAPE
RMAN-08526: channel dupdb_d1: VERITAS NetBackup for Oracle8 - Release 3.4GA
(030800)
RMAN-03022: compiling command: set limit
RMAN-03023: executing command: set limit
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: Duplicate Db
RMAN-03026: error recovery releasing channel resources
RMAN-08031: released channel: dupdb_d1
RMAN-00571: ===
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===
RMAN-03015: error occurred in stored script dup_db
RMAN-03002: failure during compilation of command
RMAN-03013: command type: Duplicate Db
RMAN-05500: the auxiliary database must be not mounted when issuing a
DUPLICATE command
Can you tell me if there is anything wrong with my Connect String ?? If so,
how do I
specify the connect string correctly ?? The auxiliary is DEFINITELY not
mounted here since
I have started it in NOMOUNT mode. The target db is the one I am trying to
duplicate and
the auxiliary db is the duplicate db I am attempting to create. The target
db is obviously
open. I am not using a password file since I am working directly on the
server.
Regards,
Samir
Samir Sarkar
Oracle DBA
SchlumbergerSema
Email : [EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone : +44 (0) 115 - 957 6028
EPABX : +44 (0) 115 - 957 6418 Ext. 76028
Fax : +44 (0) 115 - 957 6018
-Original Message-
Sent: 08 April 2002 14:06
To: [EMAIL PROTECTED]; SARKAR, Samir
Your input to RMAN should be a script or here list. Scrap the shell
script, put your run script into a file, run RMAN from the command line
and call the rman run script that you just created.. Also, set
NLS_DATE_FORMAT and NLS_LANG. Depending on your database version the
to_date function will not work as you have it in set until time.
Jay
[EMAIL PROTECTED] 04/08/02 09:43AM
Dear All,
I am totally out of my depth here and this is a production db which needs to
be restored urgently.
I shall explain my scenario first. Our application team had lost some data
after last Wednesday night's
backup. They have a tool to have a dump of the data from the database from
which they can extract the
data if needed. This dump was taken on Thursday morning. Due to some ***hole
they lost some more data
on Thursday during the day. They recovered this data from the database dump
which they had taken on
Thursday morning.
They now want the data which was lost on Wednesday. Since there was no
database dump available prior to
this, I suggested that we can create a duplicate database upto a point of
time (as on Wednesday night) to a
different location on the server. They could then take a database dump with
their tool and extract the lost records.
This is my script which I compiled with help from Metalink and an old
post from John Hallas (the backup of our target database is taken on tape
with Veritas Netbackup) :
#!/bin/ksh
rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary /
run {
set until time to_date('20-MAR-2002 20:00:00','DD-MON- HH24:MI:SS);
allocate channel c_dlt1 type 'SBT_TAPE';
allocate auxiliary channel dupdb_d1 type disk;
setlimit channel c_dlt1 kbytes 67108864 maxopenfiles 128 readrate 256;
setlimit channel dupdb_d1 kbytes 67108864 maxopenfiles 128 readrate 256;
set newname for datafile
'/disk01/oradata/sid1/data/system01sid1.dbf'
to '/disk01/oradata/test/data/system01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/rbs01sid1.dbf'
to '/disk01/oradata/test/data/rbs01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/temp01sid1.dbf'
to '/disk01/oradata/test/data/temp01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/tools01sid1.dbf'
to '/disk01/oradata/test/data/tools01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/users01sid1.dbf'
to '/disk01/oradata/test/data/users01sid1.dbf';
duplicate target database to test
logfile
group 1 '/disk03/oradata/test/logs/log01a01sid1.dbf' size 180M,
group 2
RE: Oracle Replication - is it on by default?
Paul - The only book I've found specific to Oracle replication is: Oracle Distributed Systems by Charles Dye. It is pretty good. Replication isn't the easiest thing to learn. The fundamental questions to ask are: is your application designed to work with replication? Are you just trying to replicate for reporting purposes? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 08, 2002 8:13 AM To: Multiple recipients of list ORACLE-L Hi folks, we've never identified any requirement here for using any kind of replication. Consequently I know nothing whatsoever about Oracle Replication. Now I've been asked whether or not we use Oracle Replication and, if so, whether it can be disabled. So can anyone tell me whether Replication is a feature automatically included in Oracle 8i Enterprise Edition? And is there an easy way of telling whether or not it's on? If it's on, can it be turned off (if that's a meaningful question!), and if so, how? I've tried briefly RTFMing, but although the manuals contain a wealth of info about how to use Replication, I can see nothing about how to tell whether it's active or not, and how to switch it on or off. Any pointers, please, anyone? Paul Vincent -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Vincent INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Foreign Objects in the System Tablespace.
Jay, I always set up my production databases having SYSTEM, SYS, and DBSNMP with default tblsp of SYSTEM; SYS gets temp tblsp of TEMP. Users like ORACLE, OUTLN, TRACESVR, ORDSYS, etc. get TOOLS tblsp for default (and TEMP for temp tblsp). I have never had any problems doing it this way. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Monday, April 08, 2002 10:09 AM To: Multiple recipients of list ORACLE-L I am trying to determine what Oracle officially considers foreign objects in the SYSTEM tablespace. If you check out Note 122669.1, section 7.1, Oracle recommends a query to find foreign objects in your system tablespace. This query will report such users as: AURORA$JIS$UTILITY$ CTXSYS MDSYS ORDSYS OSE$HTTP$ADMIN OUTLN It is part of our normal procedures to setup a TOOLS tablespace, and set this as the default tablespace for the user SYSTEM. Objects such as SQLPLUS_PRODUCT_PROFILE will be created in this tablespace. I've browsed around MetaLink and posted in one of the forums, but I'm not really getting any concrete answers as to which users should be permitted to have objects in the SYSTEM tablespace. I do know that it is OK to move OUTLN to another tablespace. Comments would be appreciated. Thanks, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Sherman, Paul R. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Buf Hit Ratio
I am running the following (Oracle suggested - 8i) query to get the biffer hit ratio. This is our production Apps database. The numbers for logical and physical reads seem VERY high (especially phys_reads, which obviously causes our hit ratio to drop to 0). What could have caused this? SQL list 1 select A.value + B.value logical_reads, 2 C.value phys_reads, 3 D.value phy_writes, 4 (A.value+B.value)-C.value log_minus_phys, 5 round(100 * ((A.value+B.value)-C.value) / (A.value+B.value)) 6 Buffer Hit Ratio 7 from V$SYSSTAT A, V$SYSSTAT B, V$SYSSTAT C, V$SYSSTAT D 8 where A.statistic# = 38 9 AND B.statistic# = 39 10 AND C.statistic# = 40 11* AND D.statistic# = 44 SQL / logical_reads phys_reads phy_writes log_minus_phys Buffer Hit Ratio 18,446,744,070,414,253,130 18,446,744,069,433,707,5592,043,488 980,545,5710 Here are some other stats; DTSTAMPPHYSICAL_READSDB_BLOCK_GETS CONSISTENT_GETS PHYSICAL_WRITES --- PHYSICAL_READS_DIRECT PHYSICAL_WRITES_DIRECT SESSION_LOGICAL_READS -- - 08-APR-2002 10:35:16 18,446,744,069,434,437,169 171,781,916 18,446,744,070,280,471,598 2,103,859 18,446,744,047,946,114,866966,679 1,032,014,671 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 code help
Roland,
I think you have a quote in the wrong place:
If A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 '' ||
Should probably read:
If 'A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 ' ||
Cheers,
John Thomas
-Original Message-
Sent: 08 April 2002 13:43
To: Multiple recipients of list ORACLE-L
Hallo,
I have some trouble with this pl/sql procedure. I would like
that this lvsql to be run only if the field Borttags_flagg = 0 but i get an
error in the if statement, whatis wrong with this? It get the erromressage:
LS-00103: Encountered the symbol ||AvdNr|| when expecting one of the
following:
. ( * @ % = - + / at in mod not rem then
an exponent (**) or != or ~= = = and or like
betwe..
when I compile the whole procedure. Please help me with this.
(If the borttags_flagg = 1 then it will continue the loop and check for
next one. etc...
If A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG =
0 '' ||
then
(this lvsql is to be run only if field borttags_flagg = 0)
lvSQL := 'SELECT
ICA_ARTIKEL.FSGPRIS,ICA_ARTIKEL.BORTTAGS_FLAGG,ICA_ARTIKEL.PANTBELOPP ' ||
--PBK.LPKORGEANREL.EANREL,PBK.LPKORGEANREL.VARUTYP ' ||
'FROM
A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| ' ' ||
--PBK.LPKORGEANREL ' ||
'WHERE ICA_ARTIKEL.EAN=' ||
EanLPVara || ' ' ||
'AND
ICA_ARTIKEL.DATUMTO_DATE('''||inDatum||''',''-MM-DD'') ' ||
-- 'AND
ICA_ARTIKEL.BORTTAGS_FLAGG = 0 ' ||
'ORDER BY DATUM DESC';
The whole procedure you can see in the file:
(See attached file: testplsql.SQL)
Thanks in advance
Roland S
**
Legal Disclaimer : This e-mail is for the intended recipient only.
If an addressing or transmission error has misdirected this e-mail,
please notify the sender by replying to this e-mail. If you are not
the intended recipient you must not use, disclose, distribute, copy,
print or rely on this e-mail. The opinions expressed in this message
are those of the sender and not those of TelesensKSCL.
This footnote also confirms that this e-mail has been swept by
MIMEsweeper for the presence of computer viruses.
Please note that TelesensKSCL monitors both incoming and
outgoing emails in accordance with our email policy.
**
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Thomas, John
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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: utl_file_dir question
2. like this utl_file_dir =g:\oracle\utl_file, g:\oracle\utl_file\change_svceng, g:\oracle\utl_file\change_usage 3. server - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 08, 2002 6:13 PM I have couple question. We need to set up 15 trainer in the training room by tomorrow for a pl/sql class. I need to set up utl_file_dir for them. This is NT environment. question 1: If I create 15 users in oracle, do I need to ask Nt admin to create system users for them as well? 2: It should be 15 different directories for each of them. How to set it in int.ora? 3: Since the training room are remote access to server. The utl_file_dir will write to local or server? Those are sound pretty easy question. I never set this up before and they want it now. I will check the manual as well. Thanks in advance, Joan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Arslan Bahar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Another RMAN Problem --- Urgent !!
Samir,
Glad I could help with your initial problem.
I'm afraid I can't help you here. I've never done this before. Over to
Jay...
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Monday, April 08, 2002 10:25 AM
To: 'Jay Hostetter'
Cc: '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
Jay and Tom,
Thanks a million for your replies. I have now ran the script by creating it
and
then executing it from the RMAN prompt. It worked but I got the following
error :
rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary /
Recovery Manager: Release 8.1.7.2.0 - Production
RMAN-06005: connected to target database: SID1 (DBID=647056675)
RMAN-06008: connected to recovery catalog database
RMAN-06020: connected to auxiliary database
RMAN run {execute script dup_db;}
RMAN-03021: executing script: dup_db
RMAN-03022: compiling command: set
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: dupdb_d1
RMAN-08500: channel dupdb_d1: sid=43 devtype=SBT_TAPE
RMAN-08526: channel dupdb_d1: VERITAS NetBackup for Oracle8 - Release 3.4GA
(030800)
RMAN-03022: compiling command: set limit
RMAN-03023: executing command: set limit
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: Duplicate Db
RMAN-03026: error recovery releasing channel resources
RMAN-08031: released channel: dupdb_d1
RMAN-00571: ===
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===
RMAN-03015: error occurred in stored script dup_db
RMAN-03002: failure during compilation of command
RMAN-03013: command type: Duplicate Db
RMAN-05500: the auxiliary database must be not mounted when issuing a
DUPLICATE command
Can you tell me if there is anything wrong with my Connect String ?? If so,
how do I
specify the connect string correctly ?? The auxiliary is DEFINITELY not
mounted here since
I have started it in NOMOUNT mode. The target db is the one I am trying to
duplicate and
the auxiliary db is the duplicate db I am attempting to create. The target
db is obviously
open. I am not using a password file since I am working directly on the
server.
Regards,
Samir
Samir Sarkar
Oracle DBA
SchlumbergerSema
Email : [EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone : +44 (0) 115 - 957 6028
EPABX : +44 (0) 115 - 957 6418 Ext. 76028
Fax : +44 (0) 115 - 957 6018
-Original Message-
Sent: 08 April 2002 14:06
To: [EMAIL PROTECTED]; SARKAR, Samir
Your input to RMAN should be a script or here list. Scrap the shell
script, put your run script into a file, run RMAN from the command line
and call the rman run script that you just created.. Also, set
NLS_DATE_FORMAT and NLS_LANG. Depending on your database version the
to_date function will not work as you have it in set until time.
Jay
[EMAIL PROTECTED] 04/08/02 09:43AM
Dear All,
I am totally out of my depth here and this is a production db which needs to
be restored urgently.
I shall explain my scenario first. Our application team had lost some data
after last Wednesday night's
backup. They have a tool to have a dump of the data from the database from
which they can extract the
data if needed. This dump was taken on Thursday morning. Due to some ***hole
they lost some more data
on Thursday during the day. They recovered this data from the database dump
which they had taken on
Thursday morning.
They now want the data which was lost on Wednesday. Since there was no
database dump available prior to
this, I suggested that we can create a duplicate database upto a point of
time (as on Wednesday night) to a
different location on the server. They could then take a database dump with
their tool and extract the lost records.
This is my script which I compiled with help from Metalink and an old
post from John Hallas (the backup of our target database is taken on tape
with Veritas Netbackup) :
#!/bin/ksh
rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary /
run {
set until time to_date('20-MAR-2002 20:00:00','DD-MON- HH24:MI:SS);
allocate channel c_dlt1 type 'SBT_TAPE';
allocate auxiliary channel dupdb_d1 type disk;
setlimit channel c_dlt1 kbytes 67108864 maxopenfiles 128 readrate 256;
setlimit channel dupdb_d1 kbytes 67108864 maxopenfiles 128 readrate 256;
set newname for datafile
'/disk01/oradata/sid1/data/system01sid1.dbf'
to '/disk01/oradata/test/data/system01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/rbs01sid1.dbf'
to '/disk01/oradata/test/data/rbs01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/temp01sid1.dbf'
to '/disk01/oradata/test/data/temp01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/tools01sid1.dbf'
to
Re: anyone know how to change tablespace names?
you really don't want to do this by mucking in the data dictionary. the only SUPPORTED way is to export, drop the tablespace, recreate it with the correct name,grant the user (or users) quota on the new tablespace while revoking quota on all others (so they are forced into this one) and import. You could create the objects in the new tablespace before the import, instead of removing the quota on the other tablespaces. --- Magaliff, Bill [EMAIL PROTECTED] wrote: is there a back-end way to effect an alter tablespace rename to ... type of thing? yes, I know Oracle doesn't support mucking with the data dictionary, but . . . thx -bill Bill Magaliff Framework, Inc. 914-631-2322 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: utl_file_dir question
Hi 1.No, Utl_file writes as the Oracle process 2. I believe it is per instance and not per user. (you can create 15 directories and put all 15 in the init.ora file) 3. Utl_file writes on the server, not client jack Joan Hsieh joan.hsieh@tuftsTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .educc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: utl_file_dir question [EMAIL PROTECTED] 08-04-2002 17:13 Please respond to ORACLE-L I have couple question. We need to set up 15 trainer in the training room by tomorrow for a pl/sql class. I need to set up utl_file_dir for them. This is NT environment. question 1: If I create 15 users in oracle, do I need to ask Nt admin to create system users for them as well? 2: It should be 15 different directories for each of them. How to set it in int.ora? 3: Since the training room are remote access to server. The utl_file_dir will write to local or server? Those are sound pretty easy question. I never set this up before and they want it now. I will check the manual as well. Thanks in advance, Joan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). == De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the
RE: Another RMAN Problem --- Urgent !!
Hi,
Forgive my ignorance but if I look at your connect string you connect to
both the target as auxiliary with the /
This in my opinion means that they both connect to the same instance and
judging from the error and what you wrote it looks as if you are connecting
to production with your auxiliary connection. Be glad it fails!!!
Jack
SARKAR, Samir
[EMAIL PROTECTED]To: Multiple recipients
of list ORACLE-L [EMAIL PROTECTED]
ma.slb.comcc: (bcc: Jack van
Zanen/nlzanen1/External/MEY/NL)
Sent by: [EMAIL PROTECTED] Subject: RE: Another RMAN
Problem --- Urgent !!
08-04-2002 17:24
Please respond to ORACLE-L
Jay and Tom,
Thanks a million for your replies. I have now ran the script by creating it
and
then executing it from the RMAN prompt. It worked but I got the following
error :
rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary /
Recovery Manager: Release 8.1.7.2.0 - Production
RMAN-06005: connected to target database: SID1 (DBID=647056675)
RMAN-06008: connected to recovery catalog database
RMAN-06020: connected to auxiliary database
RMAN run {execute script dup_db;}
RMAN-03021: executing script: dup_db
RMAN-03022: compiling command: set
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: dupdb_d1
RMAN-08500: channel dupdb_d1: sid=43 devtype=SBT_TAPE
RMAN-08526: channel dupdb_d1: VERITAS NetBackup for Oracle8 - Release 3.4GA
(030800)
RMAN-03022: compiling command: set limit
RMAN-03023: executing command: set limit
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: Duplicate Db
RMAN-03026: error recovery releasing channel resources
RMAN-08031: released channel: dupdb_d1
RMAN-00571: ===
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===
RMAN-03015: error occurred in stored script dup_db
RMAN-03002: failure during compilation of command
RMAN-03013: command type: Duplicate Db
RMAN-05500: the auxiliary database must be not mounted when issuing a
DUPLICATE command
Can you tell me if there is anything wrong with my Connect String ?? If so,
how do I
specify the connect string correctly ?? The auxiliary is DEFINITELY not
mounted here since
I have started it in NOMOUNT mode. The target db is the one I am trying to
duplicate and
the auxiliary db is the duplicate db I am attempting to create. The target
db is obviously
open. I am not using a password file since I am working directly on the
server.
Regards,
Samir
Samir Sarkar
Oracle DBA
SchlumbergerSema
Email : [EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone : +44 (0) 115 - 957 6028
EPABX : +44 (0) 115 - 957 6418 Ext. 76028
Fax : +44 (0) 115 - 957 6018
-Original Message-
Sent: 08 April 2002 14:06
To: [EMAIL PROTECTED]; SARKAR, Samir
Your input to RMAN should be a script or here list. Scrap the shell
script, put your run script into a file, run RMAN from the command line
and call the rman run script that you just created.. Also, set
NLS_DATE_FORMAT and NLS_LANG. Depending on your database version the
to_date function will not work as you have it in set until time.
Jay
[EMAIL PROTECTED] 04/08/02 09:43AM
Dear All,
I am totally out of my depth here and this is a production db which needs
to
be restored urgently.
I shall explain my scenario first. Our application team had lost some data
after last Wednesday night's
backup. They have a tool to have a dump of the
RE: Foreign Objects in the System Tablespace.
Paul, what's your reasoning behind this? I have always changed the default and temp tablespace of everything except SYS to something else. I was under the impression the SYSTEM tablespace should only hold the data dictionary. Lisa Koivu Oracle Database Tank Rash. Fairfield Resorts, Inc. 954-935-4117 -Original Message- From: Sherman, Paul R. [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 08, 2002 11:29 AM To: Multiple recipients of list ORACLE-L Subject: RE: Foreign Objects in the System Tablespace. Jay, I always set up my production databases having SYSTEM, SYS, and DBSNMP with default tblsp of SYSTEM; SYS gets temp tblsp of TEMP. Users like ORACLE, OUTLN, TRACESVR, ORDSYS, etc. get TOOLS tblsp for default (and TEMP for temp tblsp). I have never had any problems doing it this way. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Monday, April 08, 2002 10:09 AM To: Multiple recipients of list ORACLE-L I am trying to determine what Oracle officially considers foreign objects in the SYSTEM tablespace. If you check out Note 122669.1, section 7.1, Oracle recommends a query to find foreign objects in your system tablespace. This query will report such users as: AURORA$JIS$UTILITY$ CTXSYS MDSYS ORDSYS OSE$HTTP$ADMIN OUTLN It is part of our normal procedures to setup a TOOLS tablespace, and set this as the default tablespace for the user SYSTEM. Objects such as SQLPLUS_PRODUCT_PROFILE will be created in this tablespace. I've browsed around MetaLink and posted in one of the forums, but I'm not really getting any concrete answers as to which users should be permitted to have objects in the SYSTEM tablespace. I do know that it is OK to move OUTLN to another tablespace. Comments would be appreciated. Thanks, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Sherman, Paul R. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Koivu, Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
PL/SQL help - need some quick help
Hallo,
I have some trouble to get this pl/sql code right. Anyone whpom could help me with
this. It is important.
Hallo,
I have some trouble with this pl/sql procedure. I would like
that this lvsql to be run only if the field Borttags_flagg = 0 but i get an error in
the if statement, whatis wrong with this? It get the erromressage:
LS-00103: Encountered the symbol ||AvdNr|| when expecting one of the following:
. ( * % = - + / at in mod not rem then
an exponent (**) or != or ~= = = and or like
betwe..
when I compile the whole procedure. Please help me with this.
(If the borttags_flagg = 1 then it will continue the loop and check for next one.
etc...
If A'||AvdNr||'.ICA_ARTIKEL'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 '' ||
then
(this lvsql is to be run only if field borttags_flagg = 0)
lvSQL := 'SELECT
ICA_ARTIKEL.FSGPRIS,ICA_ARTIKEL.BORTTAGS_FLAGG,ICA_ARTIKEL.PANTBELOPP ' ||
--PBK.LPKORGEANREL.EANREL,PBK.LPKORGEANREL.VARUTYP ' ||
'FROM
A'||AvdNr||'.ICA_ARTIKEL'||LookUpServerName|| ' ' ||
--PBK.LPKORGEANREL ' ||
'WHERE ICA_ARTIKEL.EAN=' || EanLPVara || ' '
||
'AND
ICA_ARTIKEL.DATUMTO_DATE('''||inDatum||''',''-MM-DD'') ' ||
-- 'AND ICA_ARTIKEL.BORTTAGS_FLAGG = 0 '
||
'ORDER BY DATUM DESC';
The whole procedure you can see in the file:
(See attached file: testplsql.SQL)
Thanks in advance Would really appreciate this.
Roland S
(See attached file: testplsql.SQL)
Thanks in advance.
Roland
testplsql.SQL
Description: Binary data
Re: Slightly OT: Perl Q./THX
Hi All, We forgot to export the variables export. Jack Jared Still [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] om cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: Re: Slightly OT: Perl Q. [EMAIL PROTECTED] 05-04-2002 19:49 Please respond to ORACLE-L Jack, How about examples? 1) a shell script to set some ENV vars 2) a perl script that should see the newly set vars 3) your command line for running them. Jared On Wednesday 03 April 2002 00:53, Jack van Zanen wrote: Hi All (Jared in particular), OS: AIX We are trying the following: We have a script that executes and sets all sorts of environment variables. Than after this we execute a perl script that reads the environment variables, however the environment variables set in the first script are not picked up. We tried executing the variables script with [ . script] (dot space scriptname) as well but still no luck. How can we make the perl script pick up these variables? TIA and sorry for the OT but I am not using perl so much as to join another mailing list Jack === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED]
Re: utl_file_dir question
Not an easy question. Set up a folder (directory) on the NT server called STUDENTS. Set your utl_file_dir parameter to that folder. From the server's SERVICES panel, stop and start the instance (so the parameter will take effect). Under STUDENTS, create folders for each of your students and make them sharable. You will need to have file sharing enabled on the server. To do that, right click on the NETWORK NEIGHBORHOOD icon on the server. Pick PROPERTIES and under that pick FILE AND PRINT SHARING. Tell it that you want to be able to give others access to your files. From the student's client machines, attach each student's folder as a network drive. You'll probably want to make it so that they will automatically reconnect on logon. Joan Hsieh joan.hsieh To: Multiple recipients of list ORACLE-L @tufts.edu [EMAIL PROTECTED] Sent by: rootcc: Subject: utl_file_dir question 04/08/2002 11:13 AM Please respond to ORACLE-L I have couple question. We need to set up 15 trainer in the training room by tomorrow for a pl/sql class. I need to set up utl_file_dir for them. This is NT environment. question 1: If I create 15 users in oracle, do I need to ask Nt admin to create system users for them as well? 2: It should be 15 different directories for each of them. How to set it in int.ora? 3: Since the training room are remote access to server. The utl_file_dir will write to local or server? Those are sound pretty easy question. I never set this up before and they want it now. I will check the manual as well. Thanks in advance, Joan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Ang: RE: Pl/SQL code help
But when I have fixed that error and then have the
then statement
put before the lvsql statement
I get this errormessage:
What does that mean? How to fix it?
PLS-00103: Encountered the symbol THEN when expecting one of the following:
( - + mod null an identifier
a double-quoted delimited-identifier a bind variable avg
count current max mi
Thanks in advance
Roland
Thomas, John [EMAIL PROTECTED]@fatcity.com den 2002-04-08 07:24 PST
Sänd svar till [EMAIL PROTECTED]
Sänt av: [EMAIL PROTECTED]
Till: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Kopia:
Roland,
I think you have a quote in the wrong place:
If A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 '' ||
Should probably read:
If 'A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 ' ||
Cheers,
John Thomas
-Original Message-
Sent: 08 April 2002 13:43
To: Multiple recipients of list ORACLE-L
Hallo,
I have some trouble with this pl/sql procedure. I would like
that this lvsql to be run only if the field Borttags_flagg = 0 but i get an
error in the if statement, whatis wrong with this? It get the erromressage:
LS-00103: Encountered the symbol ||AvdNr|| when expecting one of the
following:
. ( * @ % = - + / at in mod not rem then
an exponent (**) or != or ~= = = and or like
betwe..
when I compile the whole procedure. Please help me with this.
(If the borttags_flagg = 1 then it will continue the loop and check for
next one. etc...
If A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG =
0 '' ||
then
(this lvsql is to be run only if field borttags_flagg = 0)
lvSQL := 'SELECT
ICA_ARTIKEL.FSGPRIS,ICA_ARTIKEL.BORTTAGS_FLAGG,ICA_ARTIKEL.PANTBELOPP ' ||
--PBK.LPKORGEANREL.EANREL,PBK.LPKORGEANREL.VARUTYP ' ||
'FROM
A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| ' ' ||
--PBK.LPKORGEANREL ' ||
'WHERE ICA_ARTIKEL.EAN=' ||
EanLPVara || ' ' ||
'AND
ICA_ARTIKEL.DATUMTO_DATE('''||inDatum||''',''-MM-DD'') ' ||
-- 'AND
ICA_ARTIKEL.BORTTAGS_FLAGG = 0 ' ||
'ORDER BY DATUM DESC';
The whole procedure you can see in the file:
(See attached file: testplsql.SQL)
Thanks in advance
Roland S
**
Legal Disclaimer : This e-mail is for the intended recipient only.
If an addressing or transmission error has misdirected this e-mail,
please notify the sender by replying to this e-mail. If you are not
the intended recipient you must not use, disclose, distribute, copy,
print or rely on this e-mail. The opinions expressed in this message
are those of the sender and not those of TelesensKSCL.
This footnote also confirms that this e-mail has been swept by
MIMEsweeper for the presence of computer viruses.
Please note that TelesensKSCL monitors both incoming and
outgoing emails in accordance with our email policy.
**
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Thomas, John
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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: Another RMAN Problem --- Urgent !!
The auxiliary database needs to have a remote_login_password file. This can
be created running the orapwd command
$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs orapwSID password = xx
entries=10
Then add the line remote_login_passwordfile=exclusive to the init.ora and
start the database in exclusive mode (Ensure that this works by performing a
sqlplus internal @tnsnames_alias with the correct password and ensuring that
a connection has been made.
I have had some problems trying to remotely connect using a service so I add
alias to my tnsnames.ora using a SID rather than a service.
John
-Original Message-
Sent: 08 April 2002 16:25
To: Multiple recipients of list ORACLE-L
Jay and Tom,
Thanks a million for your replies. I have now ran the script by creating it
and
then executing it from the RMAN prompt. It worked but I got the following
error :
rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary /
Recovery Manager: Release 8.1.7.2.0 - Production
RMAN-06005: connected to target database: SID1 (DBID=647056675)
RMAN-06008: connected to recovery catalog database
RMAN-06020: connected to auxiliary database
RMAN run {execute script dup_db;}
RMAN-03021: executing script: dup_db
RMAN-03022: compiling command: set
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: dupdb_d1
RMAN-08500: channel dupdb_d1: sid=43 devtype=SBT_TAPE
RMAN-08526: channel dupdb_d1: VERITAS NetBackup for Oracle8 - Release 3.4GA
(030800)
RMAN-03022: compiling command: set limit
RMAN-03023: executing command: set limit
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: Duplicate Db
RMAN-03026: error recovery releasing channel resources
RMAN-08031: released channel: dupdb_d1
RMAN-00571: ===
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===
RMAN-03015: error occurred in stored script dup_db
RMAN-03002: failure during compilation of command
RMAN-03013: command type: Duplicate Db
RMAN-05500: the auxiliary database must be not mounted when issuing a
DUPLICATE command
Can you tell me if there is anything wrong with my Connect String ?? If so,
how do I
specify the connect string correctly ?? The auxiliary is DEFINITELY not
mounted here since
I have started it in NOMOUNT mode. The target db is the one I am trying to
duplicate and
the auxiliary db is the duplicate db I am attempting to create. The target
db is obviously
open. I am not using a password file since I am working directly on the
server.
Regards,
Samir
Samir Sarkar
Oracle DBA
SchlumbergerSema
Email : [EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone : +44 (0) 115 - 957 6028
EPABX : +44 (0) 115 - 957 6418 Ext. 76028
Fax : +44 (0) 115 - 957 6018
-Original Message-
Sent: 08 April 2002 14:06
To: [EMAIL PROTECTED]; SARKAR, Samir
Your input to RMAN should be a script or here list. Scrap the shell
script, put your run script into a file, run RMAN from the command line
and call the rman run script that you just created.. Also, set
NLS_DATE_FORMAT and NLS_LANG. Depending on your database version the
to_date function will not work as you have it in set until time.
Jay
[EMAIL PROTECTED] 04/08/02 09:43AM
Dear All,
I am totally out of my depth here and this is a production db which needs to
be restored urgently.
I shall explain my scenario first. Our application team had lost some data
after last Wednesday night's
backup. They have a tool to have a dump of the data from the database from
which they can extract the
data if needed. This dump was taken on Thursday morning. Due to some ***hole
they lost some more data
on Thursday during the day. They recovered this data from the database dump
which they had taken on
Thursday morning.
They now want the data which was lost on Wednesday. Since there was no
database dump available prior to
this, I suggested that we can create a duplicate database upto a point of
time (as on Wednesday night) to a
different location on the server. They could then take a database dump with
their tool and extract the lost records.
This is my script which I compiled with help from Metalink and an old
post from John Hallas (the backup of our target database is taken on tape
with Veritas Netbackup) :
#!/bin/ksh
rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary /
run {
set until time to_date('20-MAR-2002 20:00:00','DD-MON- HH24:MI:SS);
allocate channel c_dlt1 type 'SBT_TAPE';
allocate auxiliary channel dupdb_d1 type disk;
setlimit channel c_dlt1 kbytes 67108864 maxopenfiles 128 readrate 256;
setlimit channel dupdb_d1 kbytes 67108864 maxopenfiles 128 readrate 256;
set newname for datafile
'/disk01/oradata/sid1/data/system01sid1.dbf'
to
Re: two listeners problem ???
Please excuse my idiotic curiosity. Why do you need 2 listeners on the same machine? I always install the listener of the higher version and disable the previous one. Yechiel Adar. Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, April 06, 2002 8:08 AM Hi, I have two databases ORCL817 and ORCL816 in version 817, 816 on the same machine Sun 5.7. I'd like to configure seperate listener for each database. I used different port, listener name (1521 and LISTENER817 for ORCL817 , 1522 and LISTENER816 for ORCL816), but somehow the listener on 1522 cannot be started. Why??? Here is the listener.ora and tnsnames.ora for both database: listener.ora for 816 = LISTENER816 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1522)) ) ) (DESCRIPTION = (PROTOCOL_STACK = (PRESENTATION = GIOP) (SESSION = RAW) ) (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 2481)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCL816) (ORACLE_HOME = /export/apps/oracle/admin/product/8.1.6) (SID_NAME = ORCL816) ) tnsnames.ora for 816 = ORCL816 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1522)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL816) ) ) listener.ora for 817 = LISTENER817 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1521)) ) ) (DESCRIPTION = (PROTOCOL_STACK = (PRESENTATION = GIOP) (SESSION = RAW) ) (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 2481)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCL817) (ORACLE_HOME = /export/apps/oracle/admin/product/8.1.7) (SID_NAME = ORCL817) ) ) tnsnames.ora = ORCL817 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL817) ) ) Anyone knows what went wrong? Thank you! Janet __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
SQL*Loader
I am trying to user SQL*Loader to load some tables in my 8i database. The data will not load. It seems to have to do with the format. In particular the date format. Can anybody help? I have messed with this for 2 days. Here is my data file (only 5 rows displayed): 80,3614,32,0,1,2,'12/20/2001 2:34:42 PM',1860,0,,0 81,3619,32,0,1,1,'12/20/2001 2:38:42 PM',1861,0,,0 82,3620,32,0,1,1,'12/20/2001 2:41:37 PM',1861,0,,0 83,3621,32,0,1,2,'12/20/2001 2:42:30 PM',1861,0,,0 84,3622,32,0,1,2,'12/20/2001 2:42:15 PM',1861,0,,0 Here is my control file: LOAD DATA INSERT INTO TABLE APP_DEV.TESTCASEUATSTATUS FIELDS TERMINATED BY , ENCLOSED BY '' TRAILING NULLCOLS (TESTCASESTATUSID INTEGER, TESTCASEID INTEGER, USERID INTEGER, CORDID INTEGER, UATASSIGNED INTEGER, PASSFAILSTATUSID INTEGER, DATETESTED char to_date(:DateTested,'mm/dd/ hh:mi:ss pm'), TASKID INTEGER, RETEST INTEGER, ASSID INTEGER, NONVALID INTEGER) I have also tried: DATETESTED date 'mm/dd/ hh:mi:ss pm' this string for the date field. David Ehresmann Oracle DBA 8i OCP MCI Worldcom [EMAIL PROTECTED] 972.656.1015 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Ehresmann INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: utl_file_dir question
Thanks Arslan, I appreciate your timing response. Now the last question, for the r/w permission on the directory, if I created all the users in oracle, like user01,user02... I am not sure how to grant permission to the directory? Joan Arslan Bahar wrote: 2. like this utl_file_dir =g:\oracle\utl_file, g:\oracle\utl_file\change_svceng, g:\oracle\utl_file\change_usage 3. server - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 08, 2002 6:13 PM I have couple question. We need to set up 15 trainer in the training room by tomorrow for a pl/sql class. I need to set up utl_file_dir for them. This is NT environment. question 1: If I create 15 users in oracle, do I need to ask Nt admin to create system users for them as well? 2: It should be 15 different directories for each of them. How to set it in int.ora? 3: Since the training room are remote access to server. The utl_file_dir will write to local or server? Those are sound pretty easy question. I never set this up before and they want it now. I will check the manual as well. Thanks in advance, Joan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Arslan Bahar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Buf Hit Ratio
Given that a 1 GHz CPU can do about 100,000 logical I/Os per second, and the largest machine is currently (I think) only 128 CPUs, I can only guess three possibilities: Your instance has been up for the last 500 years The statistics numbers don't match the ones that the Oracle code is supposed to match. You are running a 64-bit version of Oracle, and the code the exposes v$sysstat (or rather the x$ underlying it) is not quite in-line with the actual memory content. Jonathan Lewis http://www.jlcomp.demon.co.uk Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 08 April 2002 16:15 |I am running the following (Oracle suggested - 8i) query to get the biffer hit ratio. This is our production Apps database. The numbers for logical and physical reads seem VERY high (especially phys_reads, which obviously causes our hit ratio to drop to 0). What could have caused this? | |SQL list | 1 select A.value + B.value logical_reads, | 2 C.value phys_reads, | 3 D.value phy_writes, | 4 (A.value+B.value)-C.value log_minus_phys, | 5 round(100 * ((A.value+B.value)-C.value) / (A.value+B.value)) | 6 Buffer Hit Ratio | 7 from V$SYSSTAT A, V$SYSSTAT B, V$SYSSTAT C, V$SYSSTAT D | 8 where A.statistic# = 38 | 9 AND B.statistic# = 39 | 10 AND C.statistic# = 40 | 11* AND D.statistic# = 44 |SQL / | | logical_reads phys_reads phy_writes log_minus_phys Buffer Hit Ratio | --- - | 18,446,744,070,414,253,130 18,446,744,069,433,707,559 2,043,488 980,545,5710 | |Here are some other stats; | |DTSTAMPPHYSICAL_READSDB_BLOCK_GETS CONSISTENT_GETS PHYSICAL_WRITES | -- -- --- | PHYSICAL_READS_DIRECT PHYSICAL_WRITES_DIRECT SESSION_LOGICAL_READS | -- - |08-APR-2002 10:35:16 18,446,744,069,434,437,169 171,781,916 18,446,744,070,280,471,598 2,103,859 | 18,446,744,047,946,114,866966,679 1,032,014,671 |-- |Please see the official ORACLE-L FAQ: http://www.orafaq.com |-- | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Foreign Objects in the System Tablespace.
I am with Lisa. But then again there are folks out there who are still scared to change the pctincrease to 0 on the SYSTEM tablespace. I no longer care unless its on one of my databases. Sometimes you just can't teach a dog new tricks:-( -Original Message- Sent: Monday, April 08, 2002 8:53 AM To: Multiple recipients of list ORACLE-L Paul, what's your reasoning behind this? I have always changed the default and temp tablespace of everything except SYS to something else. I was under the impression the SYSTEM tablespace should only hold the data dictionary. Lisa Koivu Oracle Database Tank Rash. Fairfield Resorts, Inc. 954-935-4117 -Original Message- From: Sherman, Paul R. [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 08, 2002 11:29 AM To: Multiple recipients of list ORACLE-L Subject: RE: Foreign Objects in the System Tablespace. Jay, I always set up my production databases having SYSTEM, SYS, and DBSNMP with default tblsp of SYSTEM; SYS gets temp tblsp of TEMP. Users like ORACLE, OUTLN, TRACESVR, ORDSYS, etc. get TOOLS tblsp for default (and TEMP for temp tblsp). I have never had any problems doing it this way. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Monday, April 08, 2002 10:09 AM To: Multiple recipients of list ORACLE-L I am trying to determine what Oracle officially considers foreign objects in the SYSTEM tablespace. If you check out Note 122669.1, section 7.1, Oracle recommends a query to find foreign objects in your system tablespace. This query will report such users as: AURORA$JIS$UTILITY$ CTXSYS MDSYS ORDSYS OSE$HTTP$ADMIN OUTLN It is part of our normal procedures to setup a TOOLS tablespace, and set this as the default tablespace for the user SYSTEM. Objects such as SQLPLUS_PRODUCT_PROFILE will be created in this tablespace. I've browsed around MetaLink and posted in one of the forums, but I'm not really getting any concrete answers as to which users should be permitted to have objects in the SYSTEM tablespace. I do know that it is OK to move OUTLN to another tablespace. Comments would be appreciated. Thanks, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Sherman, Paul R. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Koivu, Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: two listeners problem ???
I joined in a bit late on this thread, so you might have your problem fixed. Your SID_LIST_LISTENER entry in the listener.ora should say SID_LIST_LISTENER816 and SID_LIST_LISTENER817. When starting the listener, do the following: (w/environment set for 816) lsnrctl start listener816 (w/environment set for 817) lsnrctl start listener817 In theory, you don't have to specify the sids, because the databases can register themselves with the listener, but I have had issues with this, so I just use the old sid list method. Which reminds me, you databases will all register with the default listener (on port 1521) unless you tell them otherwise with this init.ora entry: local_listener = (address = (protocol = tcp)(host = gatech-devner1)(port = 1522)) I forget when this local_listener entry was implemented, but I know it is there for 8.1.7. One reason for having different listeners is for running databases in a cluster. Depending on how you have your cluster configured, you will need at least one listener per node. Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 04/08/02 12:16PM Please excuse my idiotic curiosity. Why do you need 2 listeners on the same machine? I always install the listener of the higher version and disable the previous one. Yechiel Adar. Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, April 06, 2002 8:08 AM Hi, I have two databases ORCL817 and ORCL816 in version 817, 816 on the same machine Sun 5.7. I'd like to configure seperate listener for each database. I used different port, listener name (1521 and LISTENER817 for ORCL817 , 1522 and LISTENER816 for ORCL816), but somehow the listener on 1522 cannot be started. Why??? Here is the listener.ora and tnsnames.ora for both database: listener.ora for 816 = LISTENER816 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1522)) ) ) (DESCRIPTION = (PROTOCOL_STACK = (PRESENTATION = GIOP) (SESSION = RAW) ) (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 2481)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCL816) (ORACLE_HOME = /export/apps/oracle/admin/product/8.1.6) (SID_NAME = ORCL816) ) tnsnames.ora for 816 = ORCL816 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1522)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL816) ) ) listener.ora for 817 = LISTENER817 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1521)) ) ) (DESCRIPTION = (PROTOCOL_STACK = (PRESENTATION = GIOP) (SESSION = RAW) ) (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 2481)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCL817) (ORACLE_HOME = /export/apps/oracle/admin/product/8.1.7) (SID_NAME = ORCL817) ) ) tnsnames.ora = ORCL817 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL817) ) ) Anyone knows what went wrong? Thank you! Janet -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Buf Hit Ratio
Glenn - V$SYSSTAT holds cumulative values since the instance was started. You need to measure it over a period of time. Note the values, then note them again in one hour, and measure the difference. Or use Oracle's utility STATSPACK or the older utlbstat/utlestat. Even better, get Oracle Performance Tuning 101 and learn why these ratio aren't the best way to tune your database. http://www.amazon.com/exec/obidos/ASIN/0072131454/qid=1018280809/sr=8-1/ref= sr_8_7_1/002-7587220-4526465 Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 08, 2002 10:39 AM To: Multiple recipients of list ORACLE-L I am running the following (Oracle suggested - 8i) query to get the biffer hit ratio. This is our production Apps database. The numbers for logical and physical reads seem VERY high (especially phys_reads, which obviously causes our hit ratio to drop to 0). What could have caused this? SQL list 1 select A.value + B.value logical_reads, 2 C.value phys_reads, 3 D.value phy_writes, 4 (A.value+B.value)-C.value log_minus_phys, 5 round(100 * ((A.value+B.value)-C.value) / (A.value+B.value)) 6 Buffer Hit Ratio 7 from V$SYSSTAT A, V$SYSSTAT B, V$SYSSTAT C, V$SYSSTAT D 8 where A.statistic# = 38 9 AND B.statistic# = 39 10 AND C.statistic# = 40 11* AND D.statistic# = 44 SQL / logical_reads phys_reads phy_writes log_minus_phys Buffer Hit Ratio 18,446,744,070,414,253,130 18,446,744,069,433,707,5592,043,488 980,545,5710 Here are some other stats; DTSTAMPPHYSICAL_READSDB_BLOCK_GETS CONSISTENT_GETS PHYSICAL_WRITES --- PHYSICAL_READS_DIRECT PHYSICAL_WRITES_DIRECT SESSION_LOGICAL_READS -- - 08-APR-2002 10:35:16 18,446,744,069,434,437,169 171,781,916 18,446,744,070,280,471,598 2,103,859 18,446,744,047,946,114,866966,679 1,032,014,671 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
pl/sql statement
How can I do something like this; select count(*) from prod.consenid into usercnt_tmp from dual I want to send the amount of COUNT(*) into a variable. I get the following error ORA-06550: line 5, column 36: PLS-00103: Encountered the symbol INTO when expecting one of the following: . , @ ; for an identifier a double-quoted delimited-identifier group having intersect minus order start union where connect ORA-06550: line 6, column 1: PLS-00103: Encountered the symbol END Is this something that I can do. I am probably overlooking something very obvious. Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Another RMAN Problem --- Urgent !!
Tim Gorman wrote For a DUPLICATE DATABASE operation, you don't have to connect to the TARGET at all, if I recall correctly; a DUPLICATE DATABASE operation doesn't involve the TARGET. Sounds funny, until you consider that the operation is reading from tape to the new AUXILIARY database instance. However logical all that sounds Tim, it is incorrect. For some bizarre reason when duplicating a database you have to have 3 connections open (assuming you have a recovery catalogue) The target (which I prefer to call source), the auxiliary (which is the target in my view) and the catalogue. I have raised this question before with Oracle and I did get an answer as to why you still needed to connect to the target database. I cannot remember what the answer was but I though it pretty weak at the time. John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: SQL*Loader
David, What does your log file show as the reason for failure? John -Original Message- Sent: 08 April 2002 17:16 To: Multiple recipients of list ORACLE-L I am trying to user SQL*Loader to load some tables in my 8i database. The data will not load. It seems to have to do with the format. In particular the date format. Can anybody help? I have messed with this for 2 days. Here is my data file (only 5 rows displayed): 80,3614,32,0,1,2,'12/20/2001 2:34:42 PM',1860,0,,0 81,3619,32,0,1,1,'12/20/2001 2:38:42 PM',1861,0,,0 82,3620,32,0,1,1,'12/20/2001 2:41:37 PM',1861,0,,0 83,3621,32,0,1,2,'12/20/2001 2:42:30 PM',1861,0,,0 84,3622,32,0,1,2,'12/20/2001 2:42:15 PM',1861,0,,0 Here is my control file: LOAD DATA INSERT INTO TABLE APP_DEV.TESTCASEUATSTATUS FIELDS TERMINATED BY , ENCLOSED BY '' TRAILING NULLCOLS (TESTCASESTATUSID INTEGER, TESTCASEID INTEGER, USERID INTEGER, CORDID INTEGER, UATASSIGNED INTEGER, PASSFAILSTATUSID INTEGER, DATETESTED char to_date(:DateTested,'mm/dd/ hh:mi:ss pm'), TASKID INTEGER, RETEST INTEGER, ASSID INTEGER, NONVALID INTEGER) I have also tried: DATETESTED date 'mm/dd/ hh:mi:ss pm' this string for the date field. David Ehresmann Oracle DBA 8i OCP MCI Worldcom [EMAIL PROTECTED] 972.656.1015 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Ehresmann INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 statement
select count(*) into usercnt_tmp from prod.consenid; Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! ***1 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 ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
RE: Send mail from pl/sql
Ahh...the files on the link are for Forms sending from a Windows client via DDE (in which case the e-mail client matters), and not thru PL/SQL (in which case the e-mail client doesn't matter), like the original question stated. That's why I was confused. Thx! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Abdul Aleem [mailto:[EMAIL PROTECTED]] Sent: Saturday, April 06, 2002 3:53 AM To: Multiple recipients of list ORACLE-L Subject: RE: Send mail from pl/sql The client's importance is because the site I mentioned perhaps has forms for two types of e-mail clients. I did sent you a procedure/function using UTL_SMTP with the e-mail address of original sender. Peter Koltez's site address is: http://ourworld.compuserve.com/homepages/peter_koletzke/tip.ht m#formstips1 HTH! Aleem -Original Message- Sent: Friday, April 05, 2002 8:33 PM To: Multiple recipients of list ORACLE-L Subject: RE: Send mail from pl/sql Two questions: 1) Why should the client matter? 2) What is the web address of the site you mention? TIA! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Abdul Aleem [mailto:[EMAIL PROTECTED]] Sent: Friday, April 05, 2002 3:18 AM To: Multiple recipients of list ORACLE-L Subject: RE: Send mail from pl/sql Which mail client are you looking it for. If it is for MS outlook, you can check at Perter Koltez's site Aleem -Original Message- Sent: Friday, April 05, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Subject:Send mail from pl/sql Hallo, Does anyone have a good example on a procedure how to send email from Pl/SQL. What I want to happen is to do a select statement and then send a mail with the result to the address [EMAIL PROTECTED] Please help me with this. I would appreciate it very miuch. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ORACLE-L Digest -- Volume 2002, Number 094
I got the same error when connecting in NT with a user that is not an NT administrator. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, April 05, 2002 2:13 AM the after trigger is missing the lone ranger? ORACLE-L Digest -- Volume 2002, Number 094 -- From: Kimberly Smith [EMAIL PROTECTED] Date: Wed, 3 Apr 2002 05:29:24 -0800 Subject: RE: sysdba Well, with all this info I can't imagine that someone will not be able to help you. -Original Message- paPIpapupapePO Sent: Tuesday, April 02, 2002 9:18 PM To: Multiple recipients of list ORACLE-L i cant connect as sysdba. ORA-01031 - insuffecient privileges. can anybody help me? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 statement
If you've declared the variable usercnt_tmp, then you should be fine if you just leave out the from dual bit and re-order the statements, e.g., select count(*) intousercnt_tmp fromprod.consenid ; hth, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Monday, April 08, 2002 9:57 AM To: Multiple recipients of list ORACLE-L How can I do something like this; select count(*) from prod.consenid into usercnt_tmp from dual I want to send the amount of COUNT(*) into a variable. I get the following error ORA-06550: line 5, column 36: PLS-00103: Encountered the symbol INTO when expecting one of the following: . , @ ; for an identifier a double-quoted delimited-identifier group having intersect minus order start union where connect ORA-06550: line 6, column 1: PLS-00103: Encountered the symbol END Is this something that I can do. I am probably overlooking something very obvious. Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: very interesting problem with V$SESSION and web applications....
Hello Bunyamin We have the same problem. I think that you can solve it with LDAP and enterprise users. Yechiel Adar Mehish - Original Message - From: Bunyamin K. Karadeniz To: Multiple recipients of list ORACLE-L Sent: Friday, April 05, 2002 11:15 PM Subject: very interesting problem with V$SESSION and web applications Dear Gurus , I have a comic question . ? We have a db and ias and portal . users log in by using portal login page . The problem is : because application server connects to db , in v$session the machines are all the application server machine . Although the users are db users , when you login from portal , the usernames are portal30 and portal30_sso .. So how will I know which user is which session ? V$session gives no help ... May be comic :) But can not find an answer .. Investigating portal for writing into v$session as the real username ..But no other thing comes into my mind Any idea please ... Bunyamin K. Karadeniz Oracle DBA / DeveloperCivilian IT DepartmentHavelsan A.S. Eskisehir yolu 7.km Ankara TurkeyPhone: +90 312 2873565 / 1217Mobile : +90 535 3357729 The degree of normality in a database is inversely proportional to that of its DBA.
RE: SQL*Loader
Hum, I could be reading this wrong but it looks like you are converting a char to a date and then putting it into a char column If its a char on the table then don't convert it to a date first. Otherwise change the data type to date. DATETESTED char to_date(:DateTested,'mm/dd/ hh:mi:ss pm'), -Original Message- Sent: Monday, April 08, 2002 10:10 AM To: Multiple recipients of list ORACLE-L David, What does your log file show as the reason for failure? John -Original Message- Sent: 08 April 2002 17:16 To: Multiple recipients of list ORACLE-L I am trying to user SQL*Loader to load some tables in my 8i database. The data will not load. It seems to have to do with the format. In particular the date format. Can anybody help? I have messed with this for 2 days. Here is my data file (only 5 rows displayed): 80,3614,32,0,1,2,'12/20/2001 2:34:42 PM',1860,0,,0 81,3619,32,0,1,1,'12/20/2001 2:38:42 PM',1861,0,,0 82,3620,32,0,1,1,'12/20/2001 2:41:37 PM',1861,0,,0 83,3621,32,0,1,2,'12/20/2001 2:42:30 PM',1861,0,,0 84,3622,32,0,1,2,'12/20/2001 2:42:15 PM',1861,0,,0 Here is my control file: LOAD DATA INSERT INTO TABLE APP_DEV.TESTCASEUATSTATUS FIELDS TERMINATED BY , ENCLOSED BY '' TRAILING NULLCOLS (TESTCASESTATUSID INTEGER, TESTCASEID INTEGER, USERID INTEGER, CORDID INTEGER, UATASSIGNED INTEGER, PASSFAILSTATUSID INTEGER, DATETESTED char to_date(:DateTested,'mm/dd/ hh:mi:ss pm'), TASKID INTEGER, RETEST INTEGER, ASSID INTEGER, NONVALID INTEGER) I have also tried: DATETESTED date 'mm/dd/ hh:mi:ss pm' this string for the date field. David Ehresmann Oracle DBA 8i OCP MCI Worldcom [EMAIL PROTECTED] 972.656.1015 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Ehresmann INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Another RMAN Problem --- Urgent !!
John, Jack, Tom, Jay and all those who replied, Thanks so much for ur help. I have been able to resolve my problem. I was connecting to both the target and auxiliary databases with '/' as the connect string and as a result, RMAN was getting confused. I had to necessarily create a password file and connect to RMAN using the connect string : rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary internel/password@test as John and Jay suggested. I also had to put an entry in the tnsnames.ora file for the service name of the auxiliary db which was named 'test'. I also added an entry for the auxiliary db in the listener.ora and restarted it. As for the script, I had to make another change..the line : duplicate target database to test had to be modified to :duplicate target database to test The Restore operation is in progress now...its a big db so it will take some time.will bother you folks again if it fails ;-) Regards, Samir Samir Sarkar Oracle DBA SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6028 EPABX : +44 (0) 115 - 957 6418 Ext. 76028 Fax : +44 (0) 115 - 957 6018 ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SARKAR, Samir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
SQL Tuning - How to avoid TOCHAR function against a date
I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: SQL*Loader
Here is an example of my logfile that it is generated: SQL*Loader: Release 8.1.6.0.0 - Production on Mon Apr 8 10:06:31 2002 (c) Copyright 1999 Oracle Corporation. All rights reserved. Control File: app_dev.testcaseuatstatus.ctl Data File: app_dev.testcaseuatstatus.dat Bad File: app_dev.testcaseuatstatus.bad Discard File: none specified (Allow all discards) Number to load: ALL Number to skip: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 65536 bytes Continuation:none specified Path used: Conventional Table APP_DEV.TESTCASEUATSTATUS, loaded from every logical record. Insert option in effect for this table: INSERT TRAILING NULLCOLS option in effect Column Name Position Len Term Encl Datatype -- -- - -- --- TESTCASESTATUSIDFIRST 4 INTEGER TESTCASEID NEXT 4 INTEGER USERID NEXT 4 INTEGER CORDID NEXT 4 INTEGER UATASSIGNED NEXT 4 INTEGER PASSFAILSTATUSID NEXT 4 INTEGER DATETESTED NEXT * , CHARACTER SQL string for column : to_date(:DateTested,'mm/dd/ hh:mi:ss pm') TASKID NEXT 4 INTEGER RETEST NEXT 4 INTEGER ASSIDNEXT 4 INTEGER NONVALID NEXT 4 INTEGER Record 1: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 2: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 3: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 4: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 5: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Table APP_DEV.TESTCASEUATSTATUS: 0 Rows successfully loaded. 5 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Space allocated for bind array: 19072 bytes(64 rows) Space allocated for memory besides bind array:0 bytes Total logical records skipped: 0 Total logical records read: 5 Total logical records rejected: 5 Total logical records discarded:0 Run began on Mon Apr 08 10:06:31 2002 Run ended on Mon Apr 08 10:06:36 2002 Elapsed time was: 00:00:05.96 CPU time was: 00:00:03.92 thanks alot, David Ehresmann. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of John Hallas Sent: Monday, April 08, 2002 12:10 PM To: Multiple recipients of list ORACLE-L Subject: RE: SQL*Loader David, What does your log file show as the reason for failure? John -Original Message- Sent: 08 April 2002 17:16 To: Multiple recipients of list ORACLE-L I am trying to user SQL*Loader to load some tables in my 8i database. The data will not load. It seems to have to do with the format. In particular the date format. Can anybody help? I have messed with this for 2 days. Here is my data file (only 5 rows displayed): 80,3614,32,0,1,2,'12/20/2001 2:34:42 PM',1860,0,,0 81,3619,32,0,1,1,'12/20/2001 2:38:42 PM',1861,0,,0 82,3620,32,0,1,1,'12/20/2001 2:41:37 PM',1861,0,,0 83,3621,32,0,1,2,'12/20/2001 2:42:30 PM',1861,0,,0 84,3622,32,0,1,2,'12/20/2001 2:42:15 PM',1861,0,,0 Here is my control file: LOAD DATA INSERT INTO TABLE APP_DEV.TESTCASEUATSTATUS FIELDS TERMINATED BY , ENCLOSED BY '' TRAILING NULLCOLS (TESTCASESTATUSID INTEGER, TESTCASEID INTEGER, USERID INTEGER, CORDID INTEGER, UATASSIGNED INTEGER, PASSFAILSTATUSID INTEGER, DATETESTED char to_date(:DateTested,'mm/dd/ hh:mi:ss pm'), TASKID INTEGER, RETEST INTEGER, ASSID INTEGER, NONVALID INTEGER) I have also tried: DATETESTED date 'mm/dd/ hh:mi:ss pm' this string for the date field. David Ehresmann Oracle DBA 8i OCP MCI Worldcom [EMAIL PROTECTED] 972.656.1015 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Ehresmann INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
RE: Another RMAN Problem --- Urgent !!
Congrats, glad we have been of use. And probably a faster response than logging a tar John -Original Message- Sent: 08 April 2002 18:38 To: Multiple recipients of list ORACLE-L John, Jack, Tom, Jay and all those who replied, Thanks so much for ur help. I have been able to resolve my problem. I was connecting to both the target and auxiliary databases with '/' as the connect string and as a result, RMAN was getting confused. I had to necessarily create a password file and connect to RMAN using the connect string : rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary internel/password@test as John and Jay suggested. I also had to put an entry in the tnsnames.ora file for the service name of the auxiliary db which was named 'test'. I also added an entry for the auxiliary db in the listener.ora and restarted it. As for the script, I had to make another change..the line : duplicate target database to test had to be modified to :duplicate target database to test The Restore operation is in progress now...its a big db so it will take some time.will bother you folks again if it fails ;-) Regards, Samir Samir Sarkar Oracle DBA SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6028 EPABX : +44 (0) 115 - 957 6418 Ext. 76028 Fax : +44 (0) 115 - 957 6018 ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SARKAR, Samir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RMAN madness !!
Hi All,
I am back againand yes, my script didn't work completely. It did
write the data files
to the respective locations but it returned the following error during the
process of duplicating
the db :
RMAN-03021: executing script: Memory Script
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: restore
RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel dupdb_d1: starting datafile backupset restore
RMAN-08502: set_count=335 set_stamp=456984311 creation_time=20-MAR-2002
RMAN-08089: channel dupdb_d1: specifying datafile(s) to restore from backup
set
RMAN-08523: restoring datafile 1 to
/disk01/oradata/test/data/system01sid1.dbf
RMAN-08523: restoring datafile 2 to
/disk01/oradata/test/data/rbs01sid1.dbf
RMAN-08523: restoring datafile 3 to
/disk01/oradata/test/data/rbslarge01sid1.dbf
RMAN-08523: restoring datafile 4 to
/disk01/oradata/test/data/temp01sid1.dbf
RMAN-08523: restoring datafile 5 to
/disk01/oradata/test/data/tools01sid1.dbf
RMAN-08523: restoring datafile 6 to
/disk01/oradata/test/data/users01sid1.dbf
RMAN-08023: channel dupdb_d1: restored backup piece 1
RMAN-08511: piece handle=DB_BKUP_INCR_0_SID1_456984311_335_1
tag=DB_BKUP_INCR_0 params=NULL
RMAN-08024: channel dupdb_d1: restore complete
RMAN-06162: sql statement: CREATE CONTROLFILE REUSE SET DATABASE test
RESETLOGS ARCHIVELOG
MAXLOGFILES 48
MAXLOGMEMBERS 2
MAXDATAFILES 1000
MAXINSTANCES10
MAXLOGHISTORY 2042
LOGFILE
GROUP 1 ( '/disk03/oradata/test/logs/log01a01sid1.dbf' ) SIZE 188743680
,
GROUP 2 ( '/disk03/oradata/test/logs/log02a01sid1.dbf' ) SIZE 188743680
,
GROUP 3 ( '/disk03/oradata/test/logs/log03a01sid1.dbf' ) SIZE 188743680
DATAFILE
'/disk01/oradata/test/data/system01sid1.dbf'
CHARACTER SET WE8ISO8859P1
RMAN-03027: printing stored script: Memory Script
{
switch clone datafile all;
}
RMAN-03021: executing script: Memory Script
RMAN-03022: compiling command: switch
RMAN-03023: executing command: switch
RMAN-08015: datafile 2 switched to datafile copy
RMAN-08507: input datafilecopy recid=1 stamp=458671845
filename=/disk01/oradata/test/data/rbs01sid1.dbf
RMAN-08015: datafile 3 switched to datafile copy
RMAN-08507: input datafilecopy recid=2 stamp=458671845
filename=/disk01/oradata/test/data/rbslarge01sid1.dbf
RMAN-08015: datafile 4 switched to datafile copy
RMAN-08507: input datafilecopy recid=3 stamp=458671845
filename=/disk01/oradata/test/data/temp01sid1.dbf
RMAN-08015: datafile 5 switched to datafile copy
RMAN-08507: input datafilecopy recid=4 stamp=458671845
filename=/disk01/oradata/test/data/tools01sid1.dbf
RMAN-08015: datafile 6 switched to datafile copy
RMAN-08507: input datafilecopy recid=5 stamp=458671845
filename=/disk01/oradata/test/data/users01sid1.dbf
RMAN-08015: datafile 7 switched to datafile copy
RMAN-03027: printing stored script: Memory Script
{
set until time 'to_date(''20-MAR-2002 20:00:00'',''DD-MON-
HH24:MI:SS'')';
RMAN-00571: ===
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===
RMAN-00601: fatal error in recovery manager
RMAN-03012: fatal error during compilation of command
RMAN-03028: fatal error code: 600
RMAN-03013: command type: Duplicate Db
RMAN-00600: internal error, arguments [15120] [] [] [] []
RMAN-01005: syntax error: found single-quoted-string: expecting one of:
;
RMAN-01007: at line 2 column 30 file: Memory Script
Any ideas anybody ?? My head is all mitered by now.
Thanks and Regards,
Samir
Samir Sarkar
Oracle DBA
SchlumbergerSema
Email : [EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone : +44 (0) 115 - 957 6028
EPABX : +44 (0) 115 - 957 6418 Ext. 76028
Fax : +44 (0) 115 - 957 6018
___
This email is confidential and intended solely for the use of the
individual to whom it is addressed. Any views or opinions presented are
solely those of the author and do not necessarily represent those of
SchlumbergerSema.
If you are not the intended recipient, be advised that you have received this
email in error and that any use, dissemination, forwarding, printing, or
copying of this email is strictly prohibited.
If you have received this email in error please notify the SchlumbergerSema
RE: SQL*Loader
Dave, I just ran the following and it worked just fine: Sql_Loader file: LOAD DATA INFILE 'test_loader1.dat' replace INTO TABLE test ( DATETIME POSITION(01:21) CHAR to_date(rtrim(ltrim(:datetime,),),'-mm-dd-hh24.mi.ss'), USERID POSITION(23:30) CHAR) Data File: '1999-02-05-09.26.13' 416000ZHUP '1999-02-05-09.26.13' 416000ZHUP '1999-02-05-09.26.13' 416000ZHUP Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 08, 2002 1:57 PM To: Multiple recipients of list ORACLE-L Here is an example of my logfile that it is generated: SQL*Loader: Release 8.1.6.0.0 - Production on Mon Apr 8 10:06:31 2002 (c) Copyright 1999 Oracle Corporation. All rights reserved. Control File: app_dev.testcaseuatstatus.ctl Data File: app_dev.testcaseuatstatus.dat Bad File: app_dev.testcaseuatstatus.bad Discard File: none specified (Allow all discards) Number to load: ALL Number to skip: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 65536 bytes Continuation:none specified Path used: Conventional Table APP_DEV.TESTCASEUATSTATUS, loaded from every logical record. Insert option in effect for this table: INSERT TRAILING NULLCOLS option in effect Column Name Position Len Term Encl Datatype -- -- - -- --- TESTCASESTATUSIDFIRST 4 INTEGER TESTCASEID NEXT 4 INTEGER USERID NEXT 4 INTEGER CORDID NEXT 4 INTEGER UATASSIGNED NEXT 4 INTEGER PASSFAILSTATUSID NEXT 4 INTEGER DATETESTED NEXT * , CHARACTER SQL string for column : to_date(:DateTested,'mm/dd/ hh:mi:ss pm') TASKID NEXT 4 INTEGER RETEST NEXT 4 INTEGER ASSIDNEXT 4 INTEGER NONVALID NEXT 4 INTEGER Record 1: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 2: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 3: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 4: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 5: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Table APP_DEV.TESTCASEUATSTATUS: 0 Rows successfully loaded. 5 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Space allocated for bind array: 19072 bytes(64 rows) Space allocated for memory besides bind array:0 bytes Total logical records skipped: 0 Total logical records read: 5 Total logical records rejected: 5 Total logical records discarded:0 Run began on Mon Apr 08 10:06:31 2002 Run ended on Mon Apr 08 10:06:36 2002 Elapsed time was: 00:00:05.96 CPU time was: 00:00:03.92 thanks alot, David Ehresmann. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of John Hallas Sent: Monday, April 08, 2002 12:10 PM To: Multiple recipients of list ORACLE-L Subject: RE: SQL*Loader David, What does your log file show as the reason for failure? John -Original Message- Sent: 08 April 2002 17:16 To: Multiple recipients of list ORACLE-L I am trying to user SQL*Loader to load some tables in my 8i database. The data will not load. It seems to have to do with the format. In particular the date format. Can anybody help? I have messed with this for 2 days. Here is my data file (only 5 rows displayed): 80,3614,32,0,1,2,'12/20/2001 2:34:42 PM',1860,0,,0 81,3619,32,0,1,1,'12/20/2001 2:38:42 PM',1861,0,,0 82,3620,32,0,1,1,'12/20/2001 2:41:37 PM',1861,0,,0 83,3621,32,0,1,2,'12/20/2001 2:42:30 PM',1861,0,,0 84,3622,32,0,1,2,'12/20/2001 2:42:15 PM',1861,0,,0 Here is my control file: LOAD DATA INSERT INTO TABLE APP_DEV.TESTCASEUATSTATUS FIELDS TERMINATED BY , ENCLOSED BY '' TRAILING NULLCOLS (TESTCASESTATUSID INTEGER, TESTCASEID INTEGER, USERID INTEGER, CORDID INTEGER, UATASSIGNED INTEGER, PASSFAILSTATUSID INTEGER, DATETESTED char to_date(:DateTested,'mm/dd/ hh:mi:ss pm'), TASKID INTEGER, RETEST INTEGER, ASSID INTEGER, NONVALID INTEGER) I have also tried: DATETESTED date 'mm/dd/ hh:mi:ss pm' this string for the date field. David Ehresmann Oracle DBA 8i OCP MCI Worldcom [EMAIL PROTECTED] 972.656.1015
RE: two listeners problem ???
Hello, It should not be all that uncommon. We have 7 databases each on our development and test machines that are tied to their production counterparts, and, over time, some of the databases have lagged behind others (lots of reasons for that, esp. mgt. priorities), leaving us with multiple listeners (7.3.4, 8.1.6, and 8.1.7) on some of the machines. It takes a bit longer to recycle, and you have to think a minute before you act, but not too bad. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Monday, April 08, 2002 12:16 PM To: Multiple recipients of list ORACLE-L Please excuse my idiotic curiosity. Why do you need 2 listeners on the same machine? I always install the listener of the higher version and disable the previous one. Yechiel Adar. Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, April 06, 2002 8:08 AM Hi, I have two databases ORCL817 and ORCL816 in version 817, 816 on the same machine Sun 5.7. I'd like to configure seperate listener for each database. I used different port, listener name (1521 and LISTENER817 for ORCL817 , 1522 and LISTENER816 for ORCL816), but somehow the listener on 1522 cannot be started. Why??? Here is the listener.ora and tnsnames.ora for both database: listener.ora for 816 = LISTENER816 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1522)) ) ) (DESCRIPTION = (PROTOCOL_STACK = (PRESENTATION = GIOP) (SESSION = RAW) ) (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 2481)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCL816) (ORACLE_HOME = /export/apps/oracle/admin/product/8.1.6) (SID_NAME = ORCL816) ) tnsnames.ora for 816 = ORCL816 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1522)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL816) ) ) listener.ora for 817 = LISTENER817 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1521)) ) ) (DESCRIPTION = (PROTOCOL_STACK = (PRESENTATION = GIOP) (SESSION = RAW) ) (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 2481)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCL817) (ORACLE_HOME = /export/apps/oracle/admin/product/8.1.7) (SID_NAME = ORCL817) ) ) tnsnames.ora = ORCL817 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL817) ) ) Anyone knows what went wrong? Thank you! Janet __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Sherman, Paul R. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL
RE: SQL Tuning - How to avoid TOCHAR function against a date
I have not got a system to test this out on at the moment but can you do a substr on the to_char so that the format matches the date_key Something like substr((TO_CHAR(:b1,'DD-MON-'),11) John -Original Message- [EMAIL PROTECTED] Sent: 08 April 2002 18:57 To: Multiple recipients of list ORACLE-L I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: two listeners problem ???
Leave your tnsnames.ora files alone for now - they aren't relevant to getting the listeners started. Post your current listener.ora files and the output from your commands to start the listeners. Make sure they are down before you try to start them. Jay [EMAIL PROTECTED] 04/08/02 01:56PM My problem still NOT solved, I have setup lister817 separatley and check my tnsname.ora also when I try to start the listener817 got the listener already started message but when I run lsnrctl services , I can not see the service in the list. My next test is to add the 817 listener to 816 and start it under 816, what do you think , any idea? I have done all the recommendation from the list but still no hope. Thanks all for your support. -Original Message- Sent: Monday, April 08, 2002 9:52 AM To: Multiple recipients of list ORACLE-L I joined in a bit late on this thread, so you might have your problem fixed. Your SID_LIST_LISTENER entry in the listener.ora should say SID_LIST_LISTENER816 and SID_LIST_LISTENER817. When starting the listener, do the following: (w/environment set for 816) lsnrctl start listener816 (w/environment set for 817) lsnrctl start listener817 In theory, you don't have to specify the sids, because the databases can register themselves with the listener, but I have had issues with this, so I just use the old sid list method. Which reminds me, you databases will all register with the default listener (on port 1521) unless you tell them otherwise with this init.ora entry: local_listener = (address = (protocol = tcp)(host = gatech-devner1)(port = 1522)) I forget when this local_listener entry was implemented, but I know it is there for 8.1.7. One reason for having different listeners is for running databases in a cluster. Depending on how you have your cluster configured, you will need at least one listener per node. Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 04/08/02 12:16PM Please excuse my idiotic curiosity. Why do you need 2 listeners on the same machine? I always install the listener of the higher version and disable the previous one. Yechiel Adar. Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, April 06, 2002 8:08 AM Hi, I have two databases ORCL817 and ORCL816 in version 817, 816 on the same machine Sun 5.7. I'd like to configure seperate listener for each database. I used different port, listener name (1521 and LISTENER817 for ORCL817 , 1522 and LISTENER816 for ORCL816), but somehow the listener on 1522 cannot be started. Why??? Here is the listener.ora and tnsnames.ora for both database: listener.ora for 816 = LISTENER816 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1522)) ) ) (DESCRIPTION = (PROTOCOL_STACK = (PRESENTATION = GIOP) (SESSION = RAW) ) (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 2481)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCL816) (ORACLE_HOME = /export/apps/oracle/admin/product/8.1.6) (SID_NAME = ORCL816) ) tnsnames.ora for 816 = ORCL816 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1522)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL816) ) ) listener.ora for 817 = LISTENER817 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1521)) ) ) (DESCRIPTION = (PROTOCOL_STACK = (PRESENTATION = GIOP) (SESSION = RAW) ) (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 2481)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCL817) (ORACLE_HOME = /export/apps/oracle/admin/product/8.1.7) (SID_NAME = ORCL817) ) ) tnsnames.ora = ORCL817 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL817) ) ) Anyone knows what went wrong? Thank you! Janet -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
RE: SQL Tuning - How to avoid TOCHAR function against a date
Cherie, Couldn't you do SELECT DATE_KEY FROM DATE_DIM WHERE ORACLE_DATE = trunc(:b1) and oracle_date trunc(:b1) + 1 which should at least give a range scan. Iain Nicoll -Original Message- Sent: Monday, April 08, 2002 6:57 PM To: Multiple recipients of list ORACLE-L I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Nicoll, Iain (Calanais) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: two listeners problem ???
Why not just run the 817 listener for both databases? Only one listener can be bound to a port, so using your config you could run 816 on 1521 817 on 1522. But I'd stick with using just the 817 listener on ports 1521 1526. Janet Linsy wrote: Hi, I have two databases ORCL817 and ORCL816 in version 817, 816 on the same machine Sun 5.7. I'd like to configure seperate listener for each database. I used different port, listener name (1521 and LISTENER817 for ORCL817 , 1522 and LISTENER816 for ORCL816), but somehow the listener on 1522 cannot be started. Why??? Here is the listener.ora and tnsnames.ora for both database: listener.ora for 816 = LISTENER816 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1522)) ) ) (DESCRIPTION = (PROTOCOL_STACK = (PRESENTATION = GIOP) (SESSION = RAW) ) (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 2481)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCL816) (ORACLE_HOME = /export/apps/oracle/admin/product/8.1.6) (SID_NAME = ORCL816) ) tnsnames.ora for 816 = ORCL816 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1522)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL816) ) ) listener.ora for 817 = LISTENER817 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1521)) ) ) (DESCRIPTION = (PROTOCOL_STACK = (PRESENTATION = GIOP) (SESSION = RAW) ) (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 2481)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCL817) (ORACLE_HOME = /export/apps/oracle/admin/product/8.1.7) (SID_NAME = ORCL817) ) ) tnsnames.ora = ORCL817 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL817) ) ) Anyone knows what went wrong? Thank you! Janet __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Suzy Vordos INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: RMAN madness !!
You need to change this line
set until time 'to_date(''20-MAR-2002 20:00:00'',''DD-MON-HH24:MI:SS'')';
to
set until time '2002032020';
This is bug 1887009. This isn't fixed until 9.0.2.
You must set
NLS_DATE_FORMAT=MMDDHH24MISS (or your favorite date format)
and
NLS_LANG= (whatever your nls lang is for the database)
I learned the hard way, that if you don't set NLS_LANG, then NLS_DATE_FORMAT is
ignored (at least in 8.1.7 on Tru64).
Don't forget to shutdown your auxiliary db, delete it's files, then start it up again,
NOMOUNT.
Jay
[EMAIL PROTECTED] 04/08/02 02:03PM
Hi All,
I am back againand yes, my script didn't work completely. It did
write the data files
to the respective locations but it returned the following error during the
process of duplicating
the db :
RMAN-03021: executing script: Memory Script
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: restore
RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel dupdb_d1: starting datafile backupset restore
RMAN-08502: set_count=335 set_stamp=456984311 creation_time=20-MAR-2002
RMAN-08089: channel dupdb_d1: specifying datafile(s) to restore from backup
set
RMAN-08523: restoring datafile 1 to
/disk01/oradata/test/data/system01sid1.dbf
RMAN-08523: restoring datafile 2 to
/disk01/oradata/test/data/rbs01sid1.dbf
RMAN-08523: restoring datafile 3 to
/disk01/oradata/test/data/rbslarge01sid1.dbf
RMAN-08523: restoring datafile 4 to
/disk01/oradata/test/data/temp01sid1.dbf
RMAN-08523: restoring datafile 5 to
/disk01/oradata/test/data/tools01sid1.dbf
RMAN-08523: restoring datafile 6 to
/disk01/oradata/test/data/users01sid1.dbf
RMAN-08023: channel dupdb_d1: restored backup piece 1
RMAN-08511: piece handle=DB_BKUP_INCR_0_SID1_456984311_335_1
tag=DB_BKUP_INCR_0 params=NULL
RMAN-08024: channel dupdb_d1: restore complete
RMAN-06162: sql statement: CREATE CONTROLFILE REUSE SET DATABASE test
RESETLOGS ARCHIVELOG
MAXLOGFILES 48
MAXLOGMEMBERS 2
MAXDATAFILES 1000
MAXINSTANCES10
MAXLOGHISTORY 2042
LOGFILE
GROUP 1 ( '/disk03/oradata/test/logs/log01a01sid1.dbf' ) SIZE 188743680
,
GROUP 2 ( '/disk03/oradata/test/logs/log02a01sid1.dbf' ) SIZE 188743680
,
GROUP 3 ( '/disk03/oradata/test/logs/log03a01sid1.dbf' ) SIZE 188743680
DATAFILE
'/disk01/oradata/test/data/system01sid1.dbf'
CHARACTER SET WE8ISO8859P1
RMAN-03027: printing stored script: Memory Script
{
switch clone datafile all;
}
RMAN-03021: executing script: Memory Script
RMAN-03022: compiling command: switch
RMAN-03023: executing command: switch
RMAN-08015: datafile 2 switched to datafile copy
RMAN-08507: input datafilecopy recid=1 stamp=458671845
filename=/disk01/oradata/test/data/rbs01sid1.dbf
RMAN-08015: datafile 3 switched to datafile copy
RMAN-08507: input datafilecopy recid=2 stamp=458671845
filename=/disk01/oradata/test/data/rbslarge01sid1.dbf
RMAN-08015: datafile 4 switched to datafile copy
RMAN-08507: input datafilecopy recid=3 stamp=458671845
filename=/disk01/oradata/test/data/temp01sid1.dbf
RMAN-08015: datafile 5 switched to datafile copy
RMAN-08507: input datafilecopy recid=4 stamp=458671845
filename=/disk01/oradata/test/data/tools01sid1.dbf
RMAN-08015: datafile 6 switched to datafile copy
RMAN-08507: input datafilecopy recid=5 stamp=458671845
filename=/disk01/oradata/test/data/users01sid1.dbf
RMAN-08015: datafile 7 switched to datafile copy
RMAN-03027: printing stored script: Memory Script
{
set until time 'to_date(''20-MAR-2002 20:00:00'',''DD-MON-
HH24:MI:SS'')';
RMAN-00571: ===
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===
RMAN-00601: fatal error in recovery manager
RMAN-03012: fatal error during compilation of command
RMAN-03028: fatal error code: 600
RMAN-03013: command type: Duplicate Db
RMAN-00600: internal error, arguments [15120] [] [] [] []
RMAN-01005: syntax error: found single-quoted-string: expecting one of:
;
RMAN-01007: at line 2 column 30 file: Memory Script
Any ideas anybody ?? My head is all mitered by now.
Thanks and Regards,
Samir
Samir Sarkar
Oracle DBA
SchlumbergerSema
Email : [EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone : +44 (0) 115 - 957 6028
EPABX : +44 (0) 115 - 957 6418 Ext. 76028
Fax : +44 (0) 115 - 957
RE: SQL Tuning - How to avoid TOCHAR function against a date
Could you maybe calculate a range of date values that encompasses the period you want and use BETWEEN on the raw date column? I'm thinking something along the lines of: SELECT DATE_KEY FROM DATE_DIM WHERE ORACLE_DATE BETWEEN TRUNC(:b1) AND TRUNC(:b1) + .9 ; but like, more elegant. 8^) HTH, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Monday, April 08, 2002 10:57 AM To: Multiple recipients of list ORACLE-L I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: SQL Tuning - How to avoid TOCHAR function against a date
John, I will test it out. Thanks for your helpful recommendation. Cherie John Hallas john.hallas@hcresour To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ces.co.uk cc: Sent by:Subject: RE: SQL Tuning - How to avoid TOCHAR function against a date [EMAIL PROTECTED] 04/08/02 01:20 PM Please respond to ORACLE-L I have not got a system to test this out on at the moment but can you do a substr on the to_char so that the format matches the date_key Something like substr((TO_CHAR(:b1,'DD-MON-'),11) John -Original Message- [EMAIL PROTECTED] Sent: 08 April 2002 18:57 To: Multiple recipients of list ORACLE-L I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list,
Re: SQL Tuning - How to avoid TOCHAR function against a date
I don't think you can do it.. I mean, you could change it to trunc the oracle_date field (that eliminates the minutes) and then do a to_date of :b1 but you will still be operating on the oracle_date field. Okay, I HATE to suggest this, but since the table is small: add another field to the table oracle_date_2 as a date field. Update the table set oracle_date_2=trunc(oracle_date) add a trigger to fill in oracle_date_2 when you insert a row or update the oracle_date column create an index on oracle_date_2 and change the query to use that column --- [EMAIL PROTECTED] wrote: I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Seeking opinions
Paul, I know what. . . to prevent whining in the future, suggest creating a hidden file for the rollback tablespace on /u004 and a soft link from the proper location. No wait, don't do that, they might go for it. ;) Mike -Original Message- Sent: Friday, April 05, 2002 9:53 AM To: Multiple recipients of list ORACLE-L What happens when you run out of disk space on a mount point? It happened to me. I created a new datafile on another mount point, and then had to hear staff DBAs whining, why is there a rollback segment tablespace file on /u004?, as though it was a major disaster. Really, when people get into this mindset of file A MUST be in directory B, they lose all sight of what they should be doing (ensuring a smoothly running database) and concentrate more on whether every file is in the right place so that they don't have to query the data dictionary. OK, I feel better now. :-) --- Rachel Carmichael [EMAIL PROTECTED] wrote: I had an SA teach me that one, and it's saved me from making a REALLY stupid mistake a number of times. As for your client's standards, I can see why they want to impose standards and that's a good thing. But they are a bit too rigid with it, as others have said, what happens if you run out of disk space on a mount point? I probably have bored most of the people on this list to death with this, but I still believe that the ONLY way to make sure if a datafile is part of the database is to query the database. All you need is one forgetful person who misplaces or misnames a file. Rachel --- Paul Baumgartel [EMAIL PROTECTED] wrote: That's a great idea. Henceforth I'm going to do the same! Thanks. --- Rachel Carmichael [EMAIL PROTECTED] wrote: and, on a Unix box, I ALWAYS do an fuser before deleting a file. Just in case. --- Jonathan Gennick [EMAIL PROTECTED] wrote: On Tue, 02 Apr 2002 07:43:34 -0800, you wrote: Great point. I had recently created a DB file and forgot to put the .dbf extension on it. If someone didn't query the DD of the DB first, they might have thought it was a junk/temp file (they would have to ignore the file's timestamp) and deleted it. Yeah, naming convention or no, I can't imagine not looking at v$datafile or dba_datafiles just to be sure. Jonathan Gennick --- Brighten the corner where you are mailto:[EMAIL PROTECTED] http://Gennick.com * http://MichiganWaterfalls.com * http://ValleySpur.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: RMAN madness !!
Jay,
Thanks so much for ur help. Could u please tell me whether I shall have to
set the NLS_DATE_FORMAT and NLS_LANG in the parameter file of the auxiliary
db alone or on
the target db (the main prod db) as well ???
Regards,
Samir
Samir Sarkar
Oracle DBA
SchlumbergerSema
Email : [EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone : +44 (0) 115 - 957 6028
EPABX : +44 (0) 115 - 957 6418 Ext. 76028
Fax : +44 (0) 115 - 957 6018
-Original Message-
Sent: 08 April 2002 18:28
To: [EMAIL PROTECTED]; SARKAR, Samir
You need to change this line
set until time 'to_date(''20-MAR-2002
20:00:00'',''DD-MON-HH24:MI:SS'')';
to
set until time '2002032020';
This is bug 1887009. This isn't fixed until 9.0.2.
You must set
NLS_DATE_FORMAT=MMDDHH24MISS (or your favorite date format)
and
NLS_LANG= (whatever your nls lang is for the database)
I learned the hard way, that if you don't set NLS_LANG, then NLS_DATE_FORMAT
is ignored (at least in 8.1.7 on Tru64).
Don't forget to shutdown your auxiliary db, delete it's files, then start it
up again, NOMOUNT.
Jay
[EMAIL PROTECTED] 04/08/02 02:03PM
Hi All,
I am back againand yes, my script didn't work completely. It did
write the data files
to the respective locations but it returned the following error during the
process of duplicating
the db :
RMAN-03021: executing script: Memory Script
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: restore
RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel dupdb_d1: starting datafile backupset restore
RMAN-08502: set_count=335 set_stamp=456984311 creation_time=20-MAR-2002
RMAN-08089: channel dupdb_d1: specifying datafile(s) to restore from backup
set
RMAN-08523: restoring datafile 1 to
/disk01/oradata/test/data/system01sid1.dbf
RMAN-08523: restoring datafile 2 to
/disk01/oradata/test/data/rbs01sid1.dbf
RMAN-08523: restoring datafile 3 to
/disk01/oradata/test/data/rbslarge01sid1.dbf
RMAN-08523: restoring datafile 4 to
/disk01/oradata/test/data/temp01sid1.dbf
RMAN-08523: restoring datafile 5 to
/disk01/oradata/test/data/tools01sid1.dbf
RMAN-08523: restoring datafile 6 to
/disk01/oradata/test/data/users01sid1.dbf
RMAN-08023: channel dupdb_d1: restored backup piece 1
RMAN-08511: piece handle=DB_BKUP_INCR_0_SID1_456984311_335_1
tag=DB_BKUP_INCR_0 params=NULL
RMAN-08024: channel dupdb_d1: restore complete
RMAN-06162: sql statement: CREATE CONTROLFILE REUSE SET DATABASE test
RESETLOGS ARCHIVELOG
MAXLOGFILES 48
MAXLOGMEMBERS 2
MAXDATAFILES 1000
MAXINSTANCES10
MAXLOGHISTORY 2042
LOGFILE
GROUP 1 ( '/disk03/oradata/test/logs/log01a01sid1.dbf' ) SIZE 188743680
,
GROUP 2 ( '/disk03/oradata/test/logs/log02a01sid1.dbf' ) SIZE 188743680
,
GROUP 3 ( '/disk03/oradata/test/logs/log03a01sid1.dbf' ) SIZE 188743680
DATAFILE
'/disk01/oradata/test/data/system01sid1.dbf'
CHARACTER SET WE8ISO8859P1
RMAN-03027: printing stored script: Memory Script
{
switch clone datafile all;
}
RMAN-03021: executing script: Memory Script
RMAN-03022: compiling command: switch
RMAN-03023: executing command: switch
RMAN-08015: datafile 2 switched to datafile copy
RMAN-08507: input datafilecopy recid=1 stamp=458671845
filename=/disk01/oradata/test/data/rbs01sid1.dbf
RMAN-08015: datafile 3 switched to datafile copy
RMAN-08507: input datafilecopy recid=2 stamp=458671845
filename=/disk01/oradata/test/data/rbslarge01sid1.dbf
RMAN-08015: datafile 4 switched to datafile copy
RMAN-08507: input datafilecopy recid=3 stamp=458671845
filename=/disk01/oradata/test/data/temp01sid1.dbf
RMAN-08015: datafile 5 switched to datafile copy
RMAN-08507: input datafilecopy recid=4 stamp=458671845
filename=/disk01/oradata/test/data/tools01sid1.dbf
RMAN-08015: datafile 6 switched to datafile copy
RMAN-08507: input datafilecopy recid=5 stamp=458671845
filename=/disk01/oradata/test/data/users01sid1.dbf
RMAN-08015: datafile 7 switched to datafile copy
RMAN-03027: printing stored script: Memory Script
{
set until time 'to_date(''20-MAR-2002 20:00:00'',''DD-MON-
HH24:MI:SS'')';
RMAN-00571: ===
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===
RMAN-00601: fatal error in recovery manager
RMAN-03012: fatal error during compilation of command
RE: Foreign Objects in the System Tablespace.
If you let the Assistant create scripts to create the database, you will find that Oracle now automatically changes SYSTEM's default tablespace to TOOLS. The ONLY account that should have a default tablespace of SYSTEM is SYS. Rachel --- Sherman, Paul R. [EMAIL PROTECTED] wrote: Jay, I always set up my production databases having SYSTEM, SYS, and DBSNMP with default tblsp of SYSTEM; SYS gets temp tblsp of TEMP. Users like ORACLE, OUTLN, TRACESVR, ORDSYS, etc. get TOOLS tblsp for default (and TEMP for temp tblsp). I have never had any problems doing it this way. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Monday, April 08, 2002 10:09 AM To: Multiple recipients of list ORACLE-L I am trying to determine what Oracle officially considers foreign objects in the SYSTEM tablespace. If you check out Note 122669.1, section 7.1, Oracle recommends a query to find foreign objects in your system tablespace. This query will report such users as: AURORA$JIS$UTILITY$ CTXSYS MDSYS ORDSYS OSE$HTTP$ADMIN OUTLN It is part of our normal procedures to setup a TOOLS tablespace, and set this as the default tablespace for the user SYSTEM. Objects such as SQLPLUS_PRODUCT_PROFILE will be created in this tablespace. I've browsed around MetaLink and posted in one of the forums, but I'm not really getting any concrete answers as to which users should be permitted to have objects in the SYSTEM tablespace. I do know that it is OK to move OUTLN to another tablespace. Comments would be appreciated. Thanks, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Sherman, Paul R. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: SQL Tuning - How to avoid TOCHAR function against a date
Iain, I will do some tests. Theoretically, yes, a range scan should be better than a full table scan. Thanks for your helpful recommendation. Cherie Nicoll, Iain (Calanais)To: '[EMAIL PROTECTED]' [EMAIL PROTECTED] iain.nicoll@cal cc: '[EMAIL PROTECTED]' [EMAIL PROTECTED] anais.com Subject: RE: SQL Tuning - How to avoid TOCHAR function against a date 04/08/02 12:37 PM Cherie, Couldn't you do SELECT DATE_KEY FROM DATE_DIM WHERE ORACLE_DATE = trunc(:b1) and oracle_date trunc(:b1) + 1 which should at least give a range scan. Iain Nicoll -Original Message- Sent: Monday, April 08, 2002 6:57 PM To: Multiple recipients of list ORACLE-L I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: SQL Tuning - How to avoid TOCHAR function against a date
Something like: WHERE ORACLE_DATE between trunc(:b1) and trunc(:b1) + 1 - 1/(24*60*60) -Original Message- Sent: Monday, April 08, 2002 12:57 PM To: Multiple recipients of list ORACLE-L I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Norrell, Brian INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: SQL Tuning - How to avoid TOCHAR function against a date
Cherie, How about using the TRUNC function on the date field. That will use only thre ,MM,DD of the ORACLE_DATE column. Then you will be comparing like columns without going through the to_char conversion. WHERE TRUNC(ORACLE_DATE) = TRUNC(:b1) Ron ROR mª¿ªm [EMAIL PROTECTED] 04/08/02 01:56PM I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: SQL*Loader
David, In the FIELD clause, shouldn't it be OPTIONALLY ENCLOSED BY ? -Original Message- Sent: Monday, April 08, 2002 12:16 PM To: Multiple recipients of list ORACLE-L I am trying to user SQL*Loader to load some tables in my 8i database. The data will not load. It seems to have to do with the format. In particular the date format. Can anybody help? I have messed with this for 2 days. Here is my data file (only 5 rows displayed): 80,3614,32,0,1,2,'12/20/2001 2:34:42 PM',1860,0,,0 81,3619,32,0,1,1,'12/20/2001 2:38:42 PM',1861,0,,0 82,3620,32,0,1,1,'12/20/2001 2:41:37 PM',1861,0,,0 83,3621,32,0,1,2,'12/20/2001 2:42:30 PM',1861,0,,0 84,3622,32,0,1,2,'12/20/2001 2:42:15 PM',1861,0,,0 Here is my control file: LOAD DATA INSERT INTO TABLE APP_DEV.TESTCASEUATSTATUS FIELDS TERMINATED BY , ENCLOSED BY '' TRAILING NULLCOLS (TESTCASESTATUSID INTEGER, TESTCASEID INTEGER, USERID INTEGER, CORDID INTEGER, UATASSIGNED INTEGER, PASSFAILSTATUSID INTEGER, DATETESTED char to_date(:DateTested,'mm/dd/ hh:mi:ss pm'), TASKID INTEGER, RETEST INTEGER, ASSID INTEGER, NONVALID INTEGER) I have also tried: DATETESTED date 'mm/dd/ hh:mi:ss pm' this string for the date field. David Ehresmann Oracle DBA 8i OCP MCI Worldcom [EMAIL PROTECTED] 972.656.1015 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Ehresmann INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: RMAN madness !!
I am a bit confused now Samir. Your earlier script did not mention a
I cannot see where you have the line duplicate target database to
test
Can you post you're revised script and we can check it out
John
-Original Message-
Sent: 08 April 2002 19:04
To: Multiple recipients of list ORACLE-L
Hi All,
I am back againand yes, my script didn't work completely. It did
write the data files
to the respective locations but it returned the following error during the
process of duplicating
the db :
RMAN-03021: executing script: Memory Script
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: restore
RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel dupdb_d1: starting datafile backupset restore
RMAN-08502: set_count=335 set_stamp=456984311 creation_time=20-MAR-2002
RMAN-08089: channel dupdb_d1: specifying datafile(s) to restore from backup
set
RMAN-08523: restoring datafile 1 to
/disk01/oradata/test/data/system01sid1.dbf
RMAN-08523: restoring datafile 2 to
/disk01/oradata/test/data/rbs01sid1.dbf
RMAN-08523: restoring datafile 3 to
/disk01/oradata/test/data/rbslarge01sid1.dbf
RMAN-08523: restoring datafile 4 to
/disk01/oradata/test/data/temp01sid1.dbf
RMAN-08523: restoring datafile 5 to
/disk01/oradata/test/data/tools01sid1.dbf
RMAN-08523: restoring datafile 6 to
/disk01/oradata/test/data/users01sid1.dbf
RMAN-08023: channel dupdb_d1: restored backup piece 1
RMAN-08511: piece handle=DB_BKUP_INCR_0_SID1_456984311_335_1
tag=DB_BKUP_INCR_0 params=NULL
RMAN-08024: channel dupdb_d1: restore complete
RMAN-06162: sql statement: CREATE CONTROLFILE REUSE SET DATABASE test
RESETLOGS ARCHIVELOG
MAXLOGFILES 48
MAXLOGMEMBERS 2
MAXDATAFILES 1000
MAXINSTANCES10
MAXLOGHISTORY 2042
LOGFILE
GROUP 1 ( '/disk03/oradata/test/logs/log01a01sid1.dbf' ) SIZE 188743680
,
GROUP 2 ( '/disk03/oradata/test/logs/log02a01sid1.dbf' ) SIZE 188743680
,
GROUP 3 ( '/disk03/oradata/test/logs/log03a01sid1.dbf' ) SIZE 188743680
DATAFILE
'/disk01/oradata/test/data/system01sid1.dbf'
CHARACTER SET WE8ISO8859P1
RMAN-03027: printing stored script: Memory Script
{
switch clone datafile all;
}
RMAN-03021: executing script: Memory Script
RMAN-03022: compiling command: switch
RMAN-03023: executing command: switch
RMAN-08015: datafile 2 switched to datafile copy
RMAN-08507: input datafilecopy recid=1 stamp=458671845
filename=/disk01/oradata/test/data/rbs01sid1.dbf
RMAN-08015: datafile 3 switched to datafile copy
RMAN-08507: input datafilecopy recid=2 stamp=458671845
filename=/disk01/oradata/test/data/rbslarge01sid1.dbf
RMAN-08015: datafile 4 switched to datafile copy
RMAN-08507: input datafilecopy recid=3 stamp=458671845
filename=/disk01/oradata/test/data/temp01sid1.dbf
RMAN-08015: datafile 5 switched to datafile copy
RMAN-08507: input datafilecopy recid=4 stamp=458671845
filename=/disk01/oradata/test/data/tools01sid1.dbf
RMAN-08015: datafile 6 switched to datafile copy
RMAN-08507: input datafilecopy recid=5 stamp=458671845
filename=/disk01/oradata/test/data/users01sid1.dbf
RMAN-08015: datafile 7 switched to datafile copy
RMAN-03027: printing stored script: Memory Script
{
set until time 'to_date(''20-MAR-2002 20:00:00'',''DD-MON-
HH24:MI:SS'')';
RMAN-00571: ===
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===
RMAN-00601: fatal error in recovery manager
RMAN-03012: fatal error during compilation of command
RMAN-03028: fatal error code: 600
RMAN-03013: command type: Duplicate Db
RMAN-00600: internal error, arguments [15120] [] [] [] []
RMAN-01005: syntax error: found single-quoted-string: expecting one of:
;
RMAN-01007: at line 2 column 30 file: Memory Script
Any ideas anybody ?? My head is all mitered by now.
Thanks and Regards,
Samir
Samir Sarkar
Oracle DBA
SchlumbergerSema
Email : [EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone : +44 (0) 115 - 957 6028
EPABX : +44 (0) 115 - 957 6418 Ext. 76028
Fax : +44 (0) 115 - 957 6018
___
This email is confidential and intended solely for the use of the
individual to whom it is addressed. Any views or opinions presented are
solely those of the author and do not necessarily represent those of
RE: SQL Tuning - How to avoid TOCHAR function against a date
Ron, the TRUNC function will also prevent the use of an index on the oracle_date column. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 08, 2002 2:54 PM To: Multiple recipients of list ORACLE-L Cherie, How about using the TRUNC function on the date field. That will use only thre ,MM,DD of the ORACLE_DATE column. Then you will be comparing like columns without going through the to_char conversion. WHERE TRUNC(ORACLE_DATE) = TRUNC(:b1) Ron ROR mª¿ªm [EMAIL PROTECTED] 04/08/02 01:56PM I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: RMAN madness !!
Sure John..here it is :
run
{
set until time to_date('20-MAR-2002 20:00:00','DD-MON- HH24:MI:SS');
allocate auxiliary channel dupdb_d1 type 'SBT_TAPE';
setlimit channel c_dlt1 kbytes 67108864 maxopenfiles 128 readrate 256;
setlimit channel dupdb_d1 kbytes 67108864 maxopenfiles 128 readrate 256;
set newname for datafile
'/disk01/oradata/sid1/data/system01sid1.dbf' to
'/disk01/oradata/test/data/system01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/rbs01sid1.dbf' to
'/disk01/oradata/test/data/rbs01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/rbslarge01sid1.dbf' to
'/disk01/oradata/test/data/rbslarge01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/temp01sid1.dbf' to
'/disk01/oradata/test/data/temp01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/tools01sid1.dbf' to
'/disk01/oradata/test/data/tools01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/users01sid1.dbf' to
'/disk01/oradata/test/data/users01sid1.dbf';
duplicate target database to test
logfile
group 1 ('/disk03/oradata/test/logs/log01a01sid1.dbf') size 180M,
group 2 ('/disk03/oradata/test/logs/log02a01sid1.dbf') size 180M,
group 3 ('/disk03/oradata/test/logs/log03a01sid1.dbf') size 180M;
}
By the way, Jay suggested that I will have to set the NLS_DATE_FORMAT to
MONDDHH24MISS and
issue the command
set until time '2002032020';
He referred to this as a bug. He also asked me to set the NLS_LANG. Any idea
of how to set this ??
I do not think it is set in the parameter file. My character set is
WE8ISO8859P1.
Thanks and Regards,
Samir
Samir Sarkar
Oracle DBA
SchlumbergerSema
Email : [EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone : +44 (0) 115 - 957 6028
EPABX : +44 (0) 115 - 957 6418 Ext. 76028
Fax : +44 (0) 115 - 957 6018
-Original Message-
Sent: 08 April 2002 19:59
To: Multiple recipients of list ORACLE-L
I am a bit confused now Samir. Your earlier script did not mention a
I cannot see where you have the line duplicate target database to
test
Can you post you're revised script and we can check it out
John
-Original Message-
Sent: 08 April 2002 19:04
To: Multiple recipients of list ORACLE-L
Hi All,
I am back againand yes, my script didn't work completely. It did
write the data files
to the respective locations but it returned the following error during the
process of duplicating
the db :
RMAN-03021: executing script: Memory Script
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: restore
RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel dupdb_d1: starting datafile backupset restore
RMAN-08502: set_count=335 set_stamp=456984311 creation_time=20-MAR-2002
RMAN-08089: channel dupdb_d1: specifying datafile(s) to restore from backup
set
RMAN-08523: restoring datafile 1 to
/disk01/oradata/test/data/system01sid1.dbf
RMAN-08523: restoring datafile 2 to
/disk01/oradata/test/data/rbs01sid1.dbf
RMAN-08523: restoring datafile 3 to
/disk01/oradata/test/data/rbslarge01sid1.dbf
RMAN-08523: restoring datafile 4 to
/disk01/oradata/test/data/temp01sid1.dbf
RMAN-08523: restoring datafile 5 to
/disk01/oradata/test/data/tools01sid1.dbf
RMAN-08523: restoring datafile 6 to
/disk01/oradata/test/data/users01sid1.dbf
RMAN-08023: channel dupdb_d1: restored backup piece 1
RMAN-08511: piece handle=DB_BKUP_INCR_0_SID1_456984311_335_1
tag=DB_BKUP_INCR_0 params=NULL
RMAN-08024: channel dupdb_d1: restore complete
RMAN-06162: sql statement: CREATE CONTROLFILE REUSE SET DATABASE test
RESETLOGS ARCHIVELOG
MAXLOGFILES 48
MAXLOGMEMBERS 2
MAXDATAFILES 1000
MAXINSTANCES10
MAXLOGHISTORY 2042
LOGFILE
GROUP 1 ( '/disk03/oradata/test/logs/log01a01sid1.dbf' ) SIZE 188743680
,
GROUP 2 ( '/disk03/oradata/test/logs/log02a01sid1.dbf' ) SIZE 188743680
,
GROUP 3 ( '/disk03/oradata/test/logs/log03a01sid1.dbf' ) SIZE 188743680
DATAFILE
'/disk01/oradata/test/data/system01sid1.dbf'
CHARACTER SET WE8ISO8859P1
RMAN-03027: printing stored script: Memory Script
{
switch clone datafile all;
}
RMAN-03021: executing script: Memory Script
RMAN-03022: compiling command: switch
RMAN-03023: executing command: switch
RMAN-08015: datafile 2 switched to datafile copy
RMAN-08507: input datafilecopy recid=1 stamp=458671845
RE: SQL Tuning - How to avoid TOCHAR function against a date
let's face it Rachel, the date column is probably incorrect as the table was designed. knowing that it is important in queries, and that the minutes cause problems during query, your suggestion should have been incorporated in the original design (or truncing the oracle_date field via a trigger). both the blessing and curse of the DATE column. great for performing date math, but a pain when it comes to queries. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 08, 2002 2:54 PM To: Multiple recipients of list ORACLE-L I don't think you can do it.. I mean, you could change it to trunc the oracle_date field (that eliminates the minutes) and then do a to_date of :b1 but you will still be operating on the oracle_date field. Okay, I HATE to suggest this, but since the table is small: add another field to the table oracle_date_2 as a date field. Update the table set oracle_date_2=trunc(oracle_date) add a trigger to fill in oracle_date_2 when you insert a row or update the oracle_date column create an index on oracle_date_2 and change the query to use that column --- [EMAIL PROTECTED] wrote: I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
Re[2]: SQL Tuning - How to avoid TOCHAR function against a date
How about something like: SELECT DATE_KEY FROM DATE_DIM WHERE ORACLE_DATE between trunc(:b1) and trunc(:b1)+86399/86400; It's not the prettiest thing in the world, but it keeps the use of the index on ORACLE_DATE and an adjacent comment that there are 86400 seconds in the day should make it readable enough. -rje R I don't think you can do it.. I mean, you could change it to trunc the R oracle_date field (that eliminates the minutes) and then do a to_date R of :b1 but you will still be operating on the oracle_date field. R Okay, I HATE to suggest this, but since the table is small: R add another field to the table oracle_date_2 as a date field. Update R the table set oracle_date_2=trunc(oracle_date) R add a trigger to fill in oracle_date_2 when you insert a row or update R the oracle_date column R create an index on oracle_date_2 and change the query to use that R column R --- [EMAIL PROTECTED] wrote: I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Eskridge INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Decyphering LMT space bitmap
Out of curiosity I decided I wanted to look at what composed the extent map in locally-managed tablespaces. I dumped the first 5 blocks of the tablespace's first datafile with 'alter system dump datafile ...' The results surprised me, as they appeared to consist of almost no data. The LMT in question contains a variety of segments and extents. How is the LMT bitmap organized? Start dump data blocks tsn: 1 file#: 2 minblk 1 maxblk 1 Block 1 (file header) not dumped: use dump file header command Start dump data blocks tsn: 1 file#: 2 minblk 2 maxblk 2 frmt: 0x02 chkval: 0x type: 0x1d=KTFB Bitmapped File Space Header File Space Header Block: Header Control: RelFno: 2, Unit: 8192, Size: 524352, Flag: 1 Initial Area: 3, Tail: 524292, First: 30, Free: 34 Start dump data blocks tsn: 1 file#: 2 minblk 3 maxblk 3 frmt: 0x02 chkval: 0x type: 0x1e=KTFB Bitmapped File Space Bitmap File Space Bitmap Block: BitMap Control: RelFno: 2, BeginBlock: 5, Flag: 0, First: 30, Free: 128994 FF3F ... all zeros Start dump data blocks tsn: 1 file#: 2 minblk 4 maxblk 4 frmt: 0x02 chkval: 0x type: 0x1e=KTFB Bitmapped File Space Bitmap File Space Bitmap Block: BitMap Control: RelFno: 2, BeginBlock: 1056964613, Flag: 0, First: 0, Free: 129024 ... all zeros FWIW: SQL select count (*) from dba_extents where file_id = 2; COUNT(*) -- 30 SQL select extent_management from dba_data_files df, dba_tablespaces ts where df.tablespace_name = ts.tablespace_name and file_id = 2; EXTENT_MAN -- LOCAL -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: utl_file_dir question
Hi, I am still not clear how to do this, I created 15 users in oracle...user1~user15. On the server site, utl_file_dir should be set to d:\dataware\sisfiles\user1. to user2. I checked the property of d:\dataware\sisfiles, we have sis group have all the permission on the folder. How can I set all the 15 users assign them to sis group? I think my question is how all the users can get the r/w permission? Thanks, Joan [EMAIL PROTECTED] wrote: Not an easy question. Set up a folder (directory) on the NT server called STUDENTS. Set your utl_file_dir parameter to that folder. From the server's SERVICES panel, stop and start the instance (so the parameter will take effect). Under STUDENTS, create folders for each of your students and make them sharable. You will need to have file sharing enabled on the server. To do that, right click on the NETWORK NEIGHBORHOOD icon on the server. Pick PROPERTIES and under that pick FILE AND PRINT SHARING. Tell it that you want to be able to give others access to your files. From the student's client machines, attach each student's folder as a network drive. You'll probably want to make it so that they will automatically reconnect on logon. Joan Hsieh joan.hsieh To: Multiple recipients of list ORACLE-L @tufts.edu [EMAIL PROTECTED] Sent by: rootcc: Subject: utl_file_dir question 04/08/2002 11:13 AM Please respond to ORACLE-L I have couple question. We need to set up 15 trainer in the training room by tomorrow for a pl/sql class. I need to set up utl_file_dir for them. This is NT environment. question 1: If I create 15 users in oracle, do I need to ask Nt admin to create system users for them as well? 2: It should be 15 different directories for each of them. How to set it in int.ora? 3: Since the training room are remote access to server. The utl_file_dir will write to local or server? Those are sound pretty easy question. I never set this up before and they want it now. I will check the manual as well. Thanks in advance, Joan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: SQL Tuning - How to avoid TOCHAR function against a date
I suppose if you wanted to collect statistics about hourly usage, then the minutes info would be necessary but then, most people don't think about how they really want to use the date when they add a date field --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: let's face it Rachel, the date column is probably incorrect as the table was designed. knowing that it is important in queries, and that the minutes cause problems during query, your suggestion should have been incorporated in the original design (or truncing the oracle_date field via a trigger). both the blessing and curse of the DATE column. great for performing date math, but a pain when it comes to queries. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 08, 2002 2:54 PM To: Multiple recipients of list ORACLE-L I don't think you can do it.. I mean, you could change it to trunc the oracle_date field (that eliminates the minutes) and then do a to_date of :b1 but you will still be operating on the oracle_date field. Okay, I HATE to suggest this, but since the table is small: add another field to the table oracle_date_2 as a date field. Update the table set oracle_date_2=trunc(oracle_date) add a trigger to fill in oracle_date_2 when you insert a row or update the oracle_date column create an index on oracle_date_2 and change the query to use that column --- [EMAIL PROTECTED] wrote: I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
RE: Foreign Objects in the System Tablespace.
Hello, Well, if that's the case, then, going forward, I suppose it's ok to keep SYSTEM objects in TOOLS, but it sure seems strange. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Monday, April 08, 2002 2:43 PM To: Multiple recipients of list ORACLE-L If you let the Assistant create scripts to create the database, you will find that Oracle now automatically changes SYSTEM's default tablespace to TOOLS. The ONLY account that should have a default tablespace of SYSTEM is SYS. Rachel --- Sherman, Paul R. [EMAIL PROTECTED] wrote: Jay, I always set up my production databases having SYSTEM, SYS, and DBSNMP with default tblsp of SYSTEM; SYS gets temp tblsp of TEMP. Users like ORACLE, OUTLN, TRACESVR, ORDSYS, etc. get TOOLS tblsp for default (and TEMP for temp tblsp). I have never had any problems doing it this way. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Monday, April 08, 2002 10:09 AM To: Multiple recipients of list ORACLE-L I am trying to determine what Oracle officially considers foreign objects in the SYSTEM tablespace. If you check out Note 122669.1, section 7.1, Oracle recommends a query to find foreign objects in your system tablespace. This query will report such users as: AURORA$JIS$UTILITY$ CTXSYS MDSYS ORDSYS OSE$HTTP$ADMIN OUTLN It is part of our normal procedures to setup a TOOLS tablespace, and set this as the default tablespace for the user SYSTEM. Objects such as SQLPLUS_PRODUCT_PROFILE will be created in this tablespace. I've browsed around MetaLink and posted in one of the forums, but I'm not really getting any concrete answers as to which users should be permitted to have objects in the SYSTEM tablespace. I do know that it is OK to move OUTLN to another tablespace. Comments would be appreciated. Thanks, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Sherman, Paul R. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Sherman, Paul R. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Oracle Replication - is it on by default?
Simple snapshot replication (provided by materialized views or mviews) is enabled when you use the Enterprise Edition of the database at any time as long as job_queue_processes is 0, and, of course, you have defined some mviews. This is one way replication. If you want 2 way (advanced, multi-master) replication, then in 8i you will need to run catrep.sql after running catalog and catproc. In 9i, catrep is run as a part of catproc, so with 9i you will also have advanced replication installed by default. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, April 08, 2002 11:25 AM To: Multiple recipients of list ORACLE-L Paul - The only book I've found specific to Oracle replication is: Oracle Distributed Systems by Charles Dye. It is pretty good. Replication isn't the easiest thing to learn. The fundamental questions to ask are: is your application designed to work with replication? Are you just trying to replicate for reporting purposes? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 08, 2002 8:13 AM To: Multiple recipients of list ORACLE-L Hi folks, we've never identified any requirement here for using any kind of replication. Consequently I know nothing whatsoever about Oracle Replication. Now I've been asked whether or not we use Oracle Replication and, if so, whether it can be disabled. So can anyone tell me whether Replication is a feature automatically included in Oracle 8i Enterprise Edition? And is there an easy way of telling whether or not it's on? If it's on, can it be turned off (if that's a meaningful question!), and if so, how? I've tried briefly RTFMing, but although the manuals contain a wealth of info about how to use Replication, I can see nothing about how to tell whether it's active or not, and how to switch it on or off. Any pointers, please, anyone? Paul Vincent -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Vincent INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: SQL Tuning - How to avoid TOCHAR function against a date
Tom, I realize that there would not be an index but I was trying to eliminate some overhead by using the TRUNC function as compaired to the to_char for the fields. Cherie, If the table is not to large how about pinning it to save on disk reads? Ron ROR mª¿ªm [EMAIL PROTECTED] 04/08/02 03:35PM Ron, the TRUNC function will also prevent the use of an index on the oracle_date column. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 08, 2002 2:54 PM To: Multiple recipients of list ORACLE-L Cherie, How about using the TRUNC function on the date field. That will use only thre ,MM,DD of the ORACLE_DATE column. Then you will be comparing like columns without going through the to_char conversion. WHERE TRUNC(ORACLE_DATE) = TRUNC(:b1) Ron ROR mª¿ªm [EMAIL PROTECTED] 04/08/02 01:56PM I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Ma il 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.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego,
RE: Decyphering LMT space bitmap
Following on from my previous note: Jeremiah, From your bitmap control, You have FF occurring 3 times followed by 3F which is 255, 255, 255, 63 which is 0011 So, least signficant bit first, 1100 which is used, used, ... (30 times) , free, free This corresponds with the first: 30 (the bit before the first free bit) Paul -Original Message- Sent: Monday, April 08, 2002 3:23 PM To: '[EMAIL PROTECTED]' From the 'Data Management and Storage Internal notes, Bitmapped Tablespace File Structure A new bitmapped tablespace file has the following structure: File Header 1 block Bitmapped File Space Header 1 block Head portion of of Bitmap BlocksN blocks Useful file blocks U units (A unit is a number of blocks) Tail portion of Bitmap Blocks M blocks If a Unit = B blocks, then the total file size = 1 + 1 + N + U*B + M Bitmapped File Space Header .. (lots to type, I can if you really need it) Bitmap blocks have 2 parts : Bitmap control structure Vector Dump The fields in the bitmap control structure are: RelFNo: Relative file number to which the bitmap belongs BeginBlock: Which block number does the first bit represent Flag: Zero for permanent files, one for temp files First: Where to start looking for the free space (bit before first free bit) Free: Number of free slots (bits) in the bitmap (not the file) To read the bitmap, take each two-byte pair, least significant bit first. If there are not eight bits, pad to eight bits with zeroes. Hence 0x0F = 15 = . When written least significant bit first, the bitmap looks like this -- used, used, used, used, free, free, free, free Scanning for the first free extent will start at the 4th bit. HTH Paul -Original Message- Sent: Monday, April 08, 2002 3:44 PM To: Multiple recipients of list ORACLE-L Out of curiosity I decided I wanted to look at what composed the extent map in locally-managed tablespaces. I dumped the first 5 blocks of the tablespace's first datafile with 'alter system dump datafile ...' The results surprised me, as they appeared to consist of almost no data. The LMT in question contains a variety of segments and extents. How is the LMT bitmap organized? Start dump data blocks tsn: 1 file#: 2 minblk 1 maxblk 1 Block 1 (file header) not dumped: use dump file header command Start dump data blocks tsn: 1 file#: 2 minblk 2 maxblk 2 frmt: 0x02 chkval: 0x type: 0x1d=KTFB Bitmapped File Space Header File Space Header Block: Header Control: RelFno: 2, Unit: 8192, Size: 524352, Flag: 1 Initial Area: 3, Tail: 524292, First: 30, Free: 34 Start dump data blocks tsn: 1 file#: 2 minblk 3 maxblk 3 frmt: 0x02 chkval: 0x type: 0x1e=KTFB Bitmapped File Space Bitmap File Space Bitmap Block: BitMap Control: RelFno: 2, BeginBlock: 5, Flag: 0, First: 30, Free: 128994 FF3F ... all zeros Start dump data blocks tsn: 1 file#: 2 minblk 4 maxblk 4 frmt: 0x02 chkval: 0x type: 0x1e=KTFB Bitmapped File Space Bitmap File Space Bitmap Block: BitMap Control: RelFno: 2, BeginBlock: 1056964613, Flag: 0, First: 0, Free: 129024 ... all zeros FWIW: SQL select count (*) from dba_extents where file_id = 2; COUNT(*) -- 30 SQL select extent_management from dba_data_files df, dba_tablespaces ts where df.tablespace_name = ts.tablespace_name and file_id = 2; EXTENT_MAN -- LOCAL -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
Re: two listeners problem ???
Hi, After I issued lsnrctl start ORACL816, the 816 listener started, but the status shows the listener supports no services. STATUS of the LISTENER Alias LISTENER816 Version TNSLSNR for Solaris: Version 8.1.6.0.0 - Production Start Date08-APR-2002 13:23:25 Uptime0 days 0 hr. 7 min. 16 sec Trace Level off Security OFF SNMP OFF Listener Parameter File /export/apps/oracle/admin/product/8.1.6/network/admi listener.ora Listener Log File /export/apps/oracle/admin/product/8.1.6/network/log/ stener816.log The listener supports no services The command completed successfully I shutdown and restart the instance, it still cannot be registered in the listener. As a result, when I tried to log in, I got: ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor Any suggestion? Much appreciated! Janet --- Oracle User [EMAIL PROTECTED] wrote: Did you try starting them as start listener_name. In your case, it should be start listener816/listener817 Also try giving 1525 instead of 1522. You can also try to enable logging so you can find out exactly what went wrong. Did you get any errors while trying to start the listener? Hope this helps. If not get back to me and I shall help you. Rgds Raj From: Janet Linsy [EMAIL PROTECTED] To: LazyDBA.com Discussion [EMAIL PROTECTED] Subject: two listeners problem ??? Date: Fri, 5 Apr 2002 22:09:15 -0800 (PST) Hi, I have two databases ORCL817 and ORCL816 in version 817, 816 on the same machine Sun 5.7. I'd like to configure seperate listener for each database. I used different port, listener name (1521 and LISTENER817 for ORCL817 , 1522 and LISTENER816 for ORCL816), but somehow the listener on 1522 cannot be started. Why??? Here is the listener.ora and tnsnames.ora for both database: listener.ora for 816 = LISTENER816 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1522)) ) ) (DESCRIPTION = (PROTOCOL_STACK = (PRESENTATION = GIOP) (SESSION = RAW) ) (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 2481)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCL816) (ORACLE_HOME = /export/apps/oracle/admin/product/8.1.6) (SID_NAME = ORCL816) ) tnsnames.ora for 816 = ORCL816 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1522)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL816) ) ) listener.ora for 817 = LISTENER817 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1521)) ) ) (DESCRIPTION = (PROTOCOL_STACK = (PRESENTATION = GIOP) (SESSION = RAW) ) (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 2481)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCL817) (ORACLE_HOME = /export/apps/oracle/admin/product/8.1.7) (SID_NAME = ORCL817) ) ) tnsnames.ora = ORCL817 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gatech-denver1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL817) ) ) Anyone knows what went wrong? Thank you! Janet __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list,
RE: Urgent --- Locking problem
Hello List, I am seeing Locks and the OS process is SNP process , I have to run the same job which will refresh the MVs. I am stuck due to the locks on the database , when I have tried to kill the session , it says me its is Marked for kill. Can anybody suggest me what to do ??? Its one kind of urgent Thanks Madhu -Original Message- Sent: Monday, April 08, 2002 3:23 PM To: Multiple recipients of list ORACLE-L hello All, I tried to kill a session and now it is showing me as marked as killed. But It is still holding the Locks. And I need to rerun the same . Its holding a lock type of 'JI' in exclusive mode . Can anybody suggest me what to do now ??? PS: Its a job which refreshes the Materialized views , so it will be using the SNP process Thanks, Madhu V Reddy Database Support Services (952) 324-0392 ( work ) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reddy, Madhusudana INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Reddy, Madhusudana INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Decyphering LMT space bitmap
Jeremiah, Let me explain before guru X$GOPAL woke up. I guess time is midnight in India. Here are the some lines before your dump: Start dump data blocks tsn: 1 file#: 2 minblk 3 maxblk 3 frmt: 0x02 chkval: 0x type: 0x1e=KTFB Bitmapped File Space Bitmap File Space Bitmap Block: BitMap Control: RelFno: 2, BeginBlock: 5, Flag: 0, First: 30, Free: 128994 FF3F ... all zeros Let's convert your HEX bitmap vectors to binary. It's 0xFF3F in your case. In other words, it consists of 4 bytes. They are 0xFF, 0xFF, 0xFF, 0x3F. The binary equivalents: , , , 0011 . To read this bitmap, we should take the least significant bit the first for each byte pair: The new bitmap will become: , , , 1100 Now, Each '1' represents used extent, each '0' represents free extent. If We look at the position of first '0', you will see that it's 31th position. Note that this is one more than the First value in the dump given above. Because Oracle starts looking for free space from the First value in the dump. You had found 30 extents in dba_extents. Now, we saw it in the block dump too. There is no problem. btw, I had a free product named iOraDumpReader at http://www.unal-bilisim.com/products/ioradumpreader/ioradumpreader.html . It interepretes almost all block dumps. But, since there is no enough hit to this page, I'm not currently working on it. regards... Jeremiah Wilton wrote: Out of curiosity I decided I wanted to look at what composed the extent map in locally-managed tablespaces. I dumped the first 5 blocks of the tablespace's first datafile with 'alter system dump datafile ...' The results surprised me, as they appeared to consist of almost no data. The LMT in question contains a variety of segments and extents. How is the LMT bitmap organized? Start dump data blocks tsn: 1 file#: 2 minblk 1 maxblk 1 Block 1 (file header) not dumped: use dump file header command Start dump data blocks tsn: 1 file#: 2 minblk 2 maxblk 2 frmt: 0x02 chkval: 0x type: 0x1d=KTFB Bitmapped File Space Header File Space Header Block: Header Control: RelFno: 2, Unit: 8192, Size: 524352, Flag: 1 Initial Area: 3, Tail: 524292, First: 30, Free: 34 Start dump data blocks tsn: 1 file#: 2 minblk 3 maxblk 3 frmt: 0x02 chkval: 0x type: 0x1e=KTFB Bitmapped File Space Bitmap File Space Bitmap Block: BitMap Control: RelFno: 2, BeginBlock: 5, Flag: 0, First: 30, Free: 128994 FF3F ... all zeros Start dump data blocks tsn: 1 file#: 2 minblk 4 maxblk 4 frmt: 0x02 chkval: 0x type: 0x1e=KTFB Bitmapped File Space Bitmap File Space Bitmap Block: BitMap Control: RelFno: 2, BeginBlock: 1056964613, Flag: 0, First: 0, Free: 129024 ... all zeros FWIW: SQL> select count (*) from dba_extents where file_id = 2; COUNT(*) -- 30 SQL> select extent_management from dba_data_files df, dba_tablespaces ts where df.tablespace_name = ts.tablespace_name and file_id = 2; EXTENT_MAN -- LOCAL -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists 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). -- Danisment Gazi Unal http://www.unal-bilisim.com
Re: Decyphering LMT space bitmap
Jeremiah,
Everything looks normal. Block #3 shows the first couple bytes as
FF3F000 When you decipher FF3F, you will see 30 (out of
a possible 32) bits in those 4 bytes (a.k.a. 8 nibbles) set to 1 instead
of 0, indicating that those are used extents. Your COUNT(*) on
DBA_SEGMENTS confirms exactly that number...
Looks pretty straightforward. Now, is your LMT using UNIFORM SIZE or
AUTOALLOCATE (i.e. default)?
Thanks!
-Tim
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, April 08, 2002 1:44 PM
Out of curiosity I decided I wanted to look at what composed the
extent map in locally-managed tablespaces.
I dumped the first 5 blocks of the tablespace's first datafile with
'alter system dump datafile ...' The results surprised me, as they
appeared to consist of almost no data. The LMT in question contains a
variety of segments and extents. How is the LMT bitmap organized?
Start dump data blocks tsn: 1 file#: 2 minblk 1 maxblk 1
Block 1 (file header) not dumped: use dump file header command
Start dump data blocks tsn: 1 file#: 2 minblk 2 maxblk 2
frmt: 0x02 chkval: 0x type: 0x1d=KTFB Bitmapped File Space Header
File Space Header Block:
Header Control:
RelFno: 2, Unit: 8192, Size: 524352, Flag: 1
Initial Area: 3, Tail: 524292, First: 30, Free: 34
Start dump data blocks tsn: 1 file#: 2 minblk 3 maxblk 3
frmt: 0x02 chkval: 0x type: 0x1e=KTFB Bitmapped File Space Bitmap
File Space Bitmap Block:
BitMap Control:
RelFno: 2, BeginBlock: 5, Flag: 0, First: 30, Free: 128994
FF3F
.. all zeros
Start dump data blocks tsn: 1 file#: 2 minblk 4 maxblk 4
frmt: 0x02 chkval: 0x type: 0x1e=KTFB Bitmapped File Space Bitmap
File Space Bitmap Block:
BitMap Control:
RelFno: 2, BeginBlock: 1056964613, Flag: 0, First: 0, Free: 129024
.. all zeros
FWIW:
SQL select count (*) from dba_extents where file_id = 2;
COUNT(*)
--
30
SQL select extent_management from dba_data_files df, dba_tablespaces
ts where df.tablespace_name = ts.tablespace_name and file_id = 2;
EXTENT_MAN
--
LOCAL
--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jeremiah Wilton
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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.com
--
Author: Tim Gorman
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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: RMAN madness !!
Set the environment variables for your shell. You don't have to change your database
settings.
e.g. (in ksh)
NLS_DATE_FORMAT=MMDDHH24MISS
export NLS_DATE_FORMAT
NLS_LANG=AMERICAN_AMERICA.WE8DEC
export NLS_LANG
Jay
SARKAR, Samir [EMAIL PROTECTED] 04/08/02 01:44PM
Jay,
Thanks so much for ur help. Could u please tell me whether I shall have to
set the NLS_DATE_FORMAT and NLS_LANG in the parameter file of the auxiliary
db alone or on
the target db (the main prod db) as well ???
Regards,
Samir
Samir Sarkar
Oracle DBA
SchlumbergerSema
Email : [EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone : +44 (0) 115 - 957 6028
EPABX : +44 (0) 115 - 957 6418 Ext. 76028
Fax : +44 (0) 115 - 957 6018
-Original Message-
Sent: 08 April 2002 18:28
To: [EMAIL PROTECTED]; SARKAR, Samir
You need to change this line
set until time 'to_date(''20-MAR-2002
20:00:00'',''DD-MON-HH24:MI:SS'')';
to
set until time '2002032020';
This is bug 1887009. This isn't fixed until 9.0.2.
You must set
NLS_DATE_FORMAT=MMDDHH24MISS (or your favorite date format)
and
NLS_LANG= (whatever your nls lang is for the database)
I learned the hard way, that if you don't set NLS_LANG, then NLS_DATE_FORMAT
is ignored (at least in 8.1.7 on Tru64).
Don't forget to shutdown your auxiliary db, delete it's files, then start it
up again, NOMOUNT.
Jay
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Hostetter
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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).
