Re: alter session
"Goulet, Dick" wrote: > > That only affects how Oracle finds objects. If for instance you would have to > access dba_users normally as sys.dba_users then using the alter session command > means you can drop the 'sys.' portion thereof. It has no affect on your > priviledges. Down side is that if you then want to reference one of your personal > tables you have to say so. > > Dick Goulet > Senior Oracle DBA > Oracle Certified 8i DBA > It *no longer* affects your privileges (since 7.0.4 ...) -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: alter session
That only affects how Oracle finds objects. If for instance you would have to access dba_users normally as sys.dba_users then using the alter session command means you can drop the 'sys.' portion thereof. It has no affect on your priviledges. Down side is that if you then want to reference one of your personal tables you have to say so. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, October 08, 2003 9:04 PM To: Multiple recipients of list ORACLE-L Exactly what it says: set the current schema to sys At 05:44 PM 10/8/2003, you wrote: >List, what does the following do ? > >alter session set current_schema=sys; >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: <[EMAIL PROTECTED] > INET: [EMAIL PROTECTED] > >Fat City Network Services-- 858-538-5051 http://www.fatcity.com >San Diego, California-- Mailing list and web hosting services >- >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: alter session
Paul, Not true. It has no effect on privileges. If you can't access the table normally using alter session won't change that. Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: Paul Drake [mailto:[EMAIL PROTECTED]Sent: Wednesday, October 08, 2003 10:39 PMTo: Multiple recipients of list ORACLE-LSubject: Re: alter session it raises the DBA's blood pressure by 50 mm Hg. if found, it prevents the user from having an unlocked account. if found, it is possible that it gets the user a termination notice. you're in the sys schema for what purpose? testing recovery from dictionary corruption? Pd[EMAIL PROTECTED] wrote: List, what does the following do ?alter session set current_schema=sys;-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: <[EMAIL PROTECTED]INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!?The New Yahoo! Shopping - with improved product search
RE: alter session
Well my blood pressure has been raised by alter session set current_schema = ... today. Here are a couple of interesting things I have found about that "feature": Case A) If you sign on as userA, set current_schema to userB, then use dbms_sql to 'create table t': in 7.3.4 and 8.0 - table will be created belonging to userA in 8.1 and higher - table will be created belonging to userB Case B) In 8.1.7 If you set current_schema to userA and try to add a constraint to a table belonging to userB, it works if you are signed in as a DBA user, but you get ORA-01031 if you are signed on as SYSDBA. How does that make sense?!?! Case A) sample script connect userA/userA alter session set current_schema = userB ; declare ignore pls_integer ; c_dynsql pls_integer ; begin c_dynsql := dbms_sql.open_cursor ; dbms_sql.parse (c_dynsql, 'create table my_test_table (n number)', dbms_sql.native) ; ignore := dbms_sql.execute (c_dynsql) ; dbms_sql.close_cursor (c_dynsql) ; end ; / select owner from dba_tables where table_name = 'MY_TEST_TABLE' ; -- + -- in 7.3 and 8.0: table owner will be userA -- in 8.1 and higher: table owner will be userB Case B) sample script -- + -- jrk, a, b are all users with DBA role -- + connect jrk/&password create table a.t (n1 number, n2 number) ; alter table a.t add (constraint t_uq1 unique (n1)) ; alter session set current_schema = b ; -- statement below: no error when signed on as JRK alter table a.t add (constraint t_uq2 unique (n2)) ; drop table a.t ; -- + connect jrk/&password as sysdba create table a.t (n1 number, n2 number) ; alter table a.t add (constraint t_uq1 unique (n1)) ; alter session set current_schema = b ; -- statement below: fails when signed on as SYSDBA alter table a.t add (constraint t_uq2 unique (n2)) ; drop table a.t ; results: SQL> -- + SQL> -- jrk, a, b are all users with DBA role SQL> -- + SQL> connect jrk/&password Connecté. SQL> create table a.t (n1 number, n2 number) ; Table créée. SQL> alter table a.t add (constraint t_uq1 unique (n1)) ; Table modifiée. SQL> alter session set current_schema = b ; Session modifiée. SQL> -- statement below: no error when signed on as JRK SQL> alter table a.t add (constraint t_uq2 unique (n2)) ; Table modifiée. SQL> drop table a.t ; Table supprimée. SQL> -- + SQL> connect jrk/&password as sysdba Connecté. SQL> create table a.t (n1 number, n2 number) ; Table créée. SQL> alter table a.t add (constraint t_uq1 unique (n1)) ; Table modifiée. SQL> alter session set current_schema = b ; Session modifiée. SQL> -- statement below: fails when signed on as SYSDBA SQL> alter table a.t add (constraint t_uq2 unique (n2)) ; alter table a.t add (constraint t_uq2 unique (n2)) * ERREUR à la ligne 1 : ORA-01031: privilèges insuffisants SQL> drop table a.t ; Table supprimée. -Original Message- Paul Drake it raises the DBA's blood pressure by 50 mm Hg. if found, it prevents the user from having an unlocked account. if found, it is possible that it gets the user a termination notice. you're in the sys schema for what purpose? testing recovery from dictionary corruption? [EMAIL PROTECTED] wrote: List, what does the following do ? alter session set current_schema=sys; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: alter session
it raises the DBA's blood pressure by 50 mm Hg. if found, it prevents the user from having an unlocked account. if found, it is possible that it gets the user a termination notice. you're in the sys schema for what purpose? testing recovery from dictionary corruption? Pd[EMAIL PROTECTED] wrote: List, what does the following do ?alter session set current_schema=sys;-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: <[EMAIL PROTECTED]INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!? The New Yahoo! Shopping - with improved product search
Re: alter session
Exactly what it says: set the current schema to sys At 05:44 PM 10/8/2003, you wrote: List, what does the following do ? alter session set current_schema=sys; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: alter session
This will set your current schema to act like "sys" so that you no longer need a qualifier like sys.v_$instance . This is another way to avoid public sunonym . ( btw, do the people here use public synonym , i see tom kyte is against it ) -ak - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, October 08, 2003 4:44 PM > List, what does the following do ? > > alter session set current_schema=sys; > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: <[EMAIL PROTECTED] > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ALTER SESSION SET EVENTS '...'
True enough, but the level of detail describing most events is distressingly minimal. Unless you already know what the event does, setting it is probably a REALLY bad idea. Of course, what I like to do is create a small throwaway database. Then I can set events to my heart's content, just to see what happens. If I kill the database, I just trash it and start over. Having a throwaway database to play in is essential for such experimentation. ;-) -Mark On Fri, 2002-10-25 at 14:14, Rachel Carmichael wrote: > You can also get an explanation of these event codes in the oraus.msg > file, which is NOT supplied on Windows installations, but can be found > in $ORACLE_HOME/rdbms/mesg on Unix > > Rachel > > --- [EMAIL PROTECTED] wrote: > > > > Mike, > > > > These events are generally set at the advice of Oracle Support, or > > when the > > DBA needs to do some tracing. You might not find these in the docs, > > but > > Metalink should have notes on them. > > > > Raj > > > > > > > > > > > > > > Michael > > > > Ivanov To: Multiple recipients > > of list ORACLE-L <[EMAIL PROTECTED]> > > > > > a.ru>Subject: ALTER SESSION > > SET EVENTS '...' > > Sent by: > > > > root@fatcity. > > > > com > > > > > > > > > > > > October 25, > > > > 2002 12:44 PM > > > > Please > > > > respond to > > > > ORACLE-L > > > > > > > > > > > > > > > > > > > > I often met SQL phrase in sql scripts as "ALTER SESSION SET EVENTS > > '...' " > > for example: > > ALTER SESSION SET EVENTS '10520 TRACE NAME CONTEXT FOREVER, LEVEL > > 10'; > > (Oracle 8.1.7.3 patches install instuctions) > > or > > alter session set events '10046 trace name context forever, level > > '; > > where Level is one of 0,1,4,8,12 to trace your session > > > > But I can't found some explanations for ALTER SESSION SET EVENTS > > '...' in > > Oracle documentation. > > Who can me point some explanation to this command in Oracle docs ? > > > > Best regards > > Michael Ivanov, TD "ERA" > > > > > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > __ > Do you Yahoo!? > Y! Web Hosting - Let the expert host your web site > http://webhosting.yahoo.com/ -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] "It is not enough to have a good mind. The main thing is to use it well." -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark J. Bobak INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing lis
Re: ALTER SESSION SET EVENTS '...'
You can also get an explanation of these event codes in the oraus.msg file, which is NOT supplied on Windows installations, but can be found in $ORACLE_HOME/rdbms/mesg on Unix Rachel --- [EMAIL PROTECTED] wrote: > > Mike, > > These events are generally set at the advice of Oracle Support, or > when the > DBA needs to do some tracing. You might not find these in the docs, > but > Metalink should have notes on them. > > Raj > > > > > > > Michael > > Ivanov To: Multiple recipients > of list ORACLE-L <[EMAIL PROTECTED]> > > a.ru>Subject: ALTER SESSION > SET EVENTS '...' > Sent by: > > root@fatcity. > > com > > > > > > October 25, > > 2002 12:44 PM > > Please > > respond to > > ORACLE-L > > > > > > > > > > I often met SQL phrase in sql scripts as "ALTER SESSION SET EVENTS > '...' " > for example: > ALTER SESSION SET EVENTS '10520 TRACE NAME CONTEXT FOREVER, LEVEL > 10'; > (Oracle 8.1.7.3 patches install instuctions) > or > alter session set events '10046 trace name context forever, level > '; > where Level is one of 0,1,4,8,12 to trace your session > > But I can't found some explanations for ALTER SESSION SET EVENTS > '...' in > Oracle documentation. > Who can me point some explanation to this command in Oracle docs ? > > Best regards > Michael Ivanov, TD "ERA" > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Y! Web Hosting - Let the expert host your web site http://webhosting.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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ALTER SESSION SET EVENTS '...'
Mike, These events are generally set at the advice of Oracle Support, or when the DBA needs to do some tracing. You might not find these in the docs, but Metalink should have notes on them. Raj Michael Ivanov To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: ALTER SESSION SET EVENTS '...' Sent by: root@fatcity. com October 25, 2002 12:44 PM Please respond to ORACLE-L I often met SQL phrase in sql scripts as "ALTER SESSION SET EVENTS '...' " for example: ALTER SESSION SET EVENTS '10520 TRACE NAME CONTEXT FOREVER, LEVEL 10'; (Oracle 8.1.7.3 patches install instuctions) or alter session set events '10046 trace name context forever, level '; where Level is one of 0,1,4,8,12 to trace your session But I can't found some explanations for ALTER SESSION SET EVENTS '...' in Oracle documentation. Who can me point some explanation to this command in Oracle docs ? Best regards Michael Ivanov, TD "ERA" -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: alter session set events 'immediate trace name redohdr level 10'
Linda, This information is covered in the Backup and Recovery Internals course offered by Oracle. In addition to the info provided by Riyaj, here are some more details: flg values: 0x01 log has been archived 0x02 no more space available in log 0x04 the next log to be used 0x08 the current log 0x10 log is being cleared hws: header write sequence I'm not sure what this is used for, but I imagine it's another cross check mechanism to ensure that the correct version of the block is being accessed. Lest you get the idea to manually hack the file headers, a checksum is stored in the file header. HTH, -- Anita --- [EMAIL PROTECTED] wrote: > Hi Linda > Welcome to Oracle! I am aware that db2 has > wealth of documentation > and manuals, but you would n't find these > information any where in the > Oracle documentation.. > Anyway , here is the info that you are looking for: > Again, this is from my > memory so use caution.. > > siz: Indicates the size of the log file in log block > size. So your log > file size would be 20480 * 512 =10M > seq: Log sequence # in hex > hws: heck, I don't remember this:-( Been a while > looking at this.. > bsz:log block size. same as 'select lebsz from > x$kccle' > nab:next available block. > flg:Status of the log, like current etc. If I > remember correctly, this is > a bitmap to indicate various statuses. > dup: # of members in the group. > > Thanks > Riyaj "Re-yas" Shamsudeen > Certified Oracle DBA > i2 technologies www.i2.com > > > > > "Hagedorn, Linda" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 05/30/01 03:38 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject:alter session set events > 'immediate trace name redohdr level 10' > > > Hello, > I'm looking for documentation to name and label the > output from the dump > of redo headers and logs. For example, > siz: 0x5000 seq: 0x1087 hws: 0x2 bsz: 512 nab: > 0x5001 flg: 0x0 dup: 2 > The size parameter is what, the header size in hex? > x5000 = decimal > 20480? > In the DB2 world, this kind of information is > readily available and > provided to licensed customers in the DB2 Diagnosis > Manual. I would think > the Oracle diagnosis info should be as available as > IBM's. Yes? A search > in MetaLink for this list returned zero hits: siz > seq hws bsz nab flg dup > > > Any information or referral to documentation is > appreciated. Thanks, > Linda. Oracle DBA, former IBM DB2 L2. > > __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen 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: alter session set events 'immediate trace name redohdr level
Hi Riyaj, Thank you! It's very nice to be here among such kind and knowledgeable people. I appreciate the definitions. I'll write again as I move through the log maps, as I'm sure other questions will arise. Regards, Linda -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Sent: Wednesday, May 30, 2001 3:07 PMTo: Multiple recipients of list ORACLE-LSubject: Re: alter session set events 'immediate trace name redohdr level 10'Hi Linda Welcome to Oracle! I am aware that db2 has wealth of documentation and manuals, but you would n't find these information any where in the Oracle documentation.. Anyway , here is the info that you are looking for: Again, this is from my memory so use caution.. siz: Indicates the size of the log file in log block size. So your log file size would be 20480 * 512 =10M seq: Log sequence # in hex hws: heck, I don't remember this:-( Been a while looking at this.. bsz:log block size. same as 'select lebsz from x$kccle' nab:next available block. flg:Status of the log, like current etc. If I remember correctly, this is a bitmap to indicate various statuses. dup: # of members in the group. ThanksRiyaj "Re-yas" ShamsudeenCertified Oracle DBAi2 technologies www.i2.com "Hagedorn, Linda" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 05/30/01 03:38 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: alter session set events 'immediate trace name redohdr level 10'Hello, I'm looking for documentation to name and label the output from the dump of redo headers and logs. For example, siz: 0x5000 seq: 0x1087 hws: 0x2 bsz: 512 nab: 0x5001 flg: 0x0 dup: 2 The size parameter is what, the header size in hex? x5000 = decimal 20480? In the DB2 world, this kind of information is readily available and provided to licensed customers in the DB2 Diagnosis Manual. I would think the Oracle diagnosis info should be as available as IBM's. Yes? A search in MetaLink for this list returned zero hits: siz seq hws bsz nab flg dup Any information or referral to documentation is appreciated. Thanks, Linda. Oracle DBA, former IBM DB2 L2.
Re: alter session set events 'immediate trace name redohdr level 10'
Hi Linda Welcome to Oracle! I am aware that db2 has wealth of documentation and manuals, but you would n't find these information any where in the Oracle documentation.. Anyway , here is the info that you are looking for: Again, this is from my memory so use caution.. siz: Indicates the size of the log file in log block size. So your log file size would be 20480 * 512 =10M seq: Log sequence # in hex hws: heck, I don't remember this:-( Been a while looking at this.. bsz:log block size. same as 'select lebsz from x$kccle' nab:next available block. flg:Status of the log, like current etc. If I remember correctly, this is a bitmap to indicate various statuses. dup: # of members in the group. Thanks Riyaj "Re-yas" Shamsudeen Certified Oracle DBA i2 technologies www.i2.com "Hagedorn, Linda" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 05/30/01 03:38 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: alter session set events 'immediate trace name redohdr level 10' Hello, I'm looking for documentation to name and label the output from the dump of redo headers and logs. For example, siz: 0x5000 seq: 0x1087 hws: 0x2 bsz: 512 nab: 0x5001 flg: 0x0 dup: 2 The size parameter is what, the header size in hex? x5000 = decimal 20480? In the DB2 world, this kind of information is readily available and provided to licensed customers in the DB2 Diagnosis Manual. I would think the Oracle diagnosis info should be as available as IBM's. Yes? A search in MetaLink for this list returned zero hits: siz seq hws bsz nab flg dup Any information or referral to documentation is appreciated. Thanks, Linda. Oracle DBA, former IBM DB2 L2.
Re: ALTER SESSION SET NLS_DATE_FORMAT
It will set the NLS_DATE_FORMAT for that session only. HTH, Ruth - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, March 15, 2001 1:10 PM > > Hello: > > Can anyone explain to me what exactly 'Alter Session' will do? > Will it alter the session all the way until it close? > How do I find out what kind of mode is my SQLPLUS session currently on? > > I have following script like: > > ALTER SESSION SET NLS_DATE_FORMAT = '-MM-DD-HH24.MI.SS'; > SELECT ... > FROM ... > WHERE ... > (TO_DATE(SUBSTR(KOMP.DATCRE,1,19),'-MM-DD-HH24.MI.SS') < TO_DATE(SUBSTR(KOMP.DATCHG,1,19),'-MM-DD-HH24.MI.SS')) and > (TO_DATE(TO_CHAR(SYSDATE-1),'-MM-DD-HH24.MI.SS') < TO_DATE(SUBSTR(KOMP.DATCHG,1,19),'-MM-DD-HH24.MI.SS')); > > When I run the script as above, it will only return 2 records, which is correct. > > However, when I remove "ALTER SESSION SET NLS_DATE_FORMAT = '-MM-DD-HH24.MI.SS';" > the query will return 8000 records which is wrong. > > What function does 'ALTER SESSION' have here? > > Thanks > > > > > > - > Do You Yahoo!? > Yahoo! Auctions - Buy the things you want at great prices! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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: ALTER SESSION SET NLS_DATE_FORMAT
Title: RE: ALTER SESSION SET NLS_DATE_FORMAT
>-Original Message-
>From: Wendy Y [mailto:[EMAIL PROTECTED]]
>
>Can anyone explain to me what exactly 'Alter Session' will do?
>Will it alter the session all the way until it close?
>How do I find out what kind of mode is my SQLPLUS session currently on?
>I have following script like:
>ALTER SESSION SET NLS_DATE_FORMAT = '-MM-DD-HH24.MI.SS';
>SELECT ...
>FROM ...
>WHERE ...
> (TO_DATE(SUBSTR(KOMP.DATCRE,1,19),'-MM-DD-HH24.MI.SS') <
> TO_DATE(SUBSTR>(KOMP.DATCHG,1,19),'-MM-DD-HH24.MI.SS')) and
> (TO_DATE(TO_CHAR(SYSDATE-1),'-MM-DD-HH24.MI.SS') <
> TO_DATE(SUBSTR(KOMP.DATCHG,1,19),'-MM-DD-HH24.MI.SS'));
>When I run the script as above, it will only return 2 records, which is correct.
>However, when I remove "ALTER SESSION SET NLS_DATE_FORMAT = '-MM-DD-HH24.MI.SS';"
>the query will return 8000 records which is wrong.
>What function does 'ALTER SESSION' have here?
The alter session command will set the default date format. See examples below.
You can see what your current session format is with the following query:
select * from nls_session_parameters ;
SQL> alter session set nls_date_format = 'MMDD' ;
Session altered.
SQL> select sysdate from dual ;
SYSDATE
20010315
SQL> alter session set nls_date_format = 'DD/MM/' ;
Session altered.
SQL> select sysdate from dual ;
SYSDATE
--
15/03/2001
When you compare two dates, you don't need to use a to_date function on a field that is already a date.
e.g. I could say
select *
from ...
where date_field < sysdate ;
or
select *
from ...
where date_field < to_date ('20010619', 'MMDD') ;
In your example above, I am guessing that the difference in the rows returned comes from this expression: "TO_DATE(TO_CHAR(SYSDATE-1),'-MM-DD-HH24.MI.SS')" since you are using a to_date conversion on sysdate (unnecessarily) and the to_date conversion without a format depends on the nls_date_format, as seen in the example below.
SQL> alter session set nls_date_format = 'YYMMDD' ;
Session altered.
SQL> select to_char (to_date ('010203'), '/MM/DD') from dual ;
TO_CHAR(TO
--
2001/02/03
SQL> alter session set nls_date_format = 'DDMMYY' ;
Session altered.
SQL> select to_char (to_date ('010203'), '/MM/DD') from dual ;
TO_CHAR(TO
--
2003/02/01
--
any ignorant comments made are the sole responsibility of J. R. Kilchoer and should not reflect adversely upon my employer.
Jacques R. Kilchoer
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com
