RE: Removing Older version of Oracle on NT Machine
Hi All, This question pops up once in a while. I did not pay much attention to replies before (not much of an NT person), but I think I will now, since I am also running into similar situation and it's always good to learn a few new things. If anyone has a step-by-step procedure to do this right the first time, please post it. All NT users will thank you :) If not, add on to (or correct as appropriate) the following check list: 1) Shutdown the database (assuming only one is running) 2) Stop the database service (via MS gui or otherwise) 3) Stop the listener service (via MS gui or otherwise) 4) Manually remove all database files (optional?) 5) Remove the above services ( a sample command syntax please ... ) 6) Use Oracle Installer to remove stuff (shall we say 'yes' to all those silly questions about shared programs, ddl etc. ?) 7) Back up registry (exactly how?) 8) Edit registry to remove Oracle under (blah blah please provide the dirs/subdirs names.) 9) Check and clean up Environment variables, if set for Oracle 10) Reboot server (I would, since Registry was fiddled with) 11) Check for any if leftovers.. (if found, what to do?) 12) ...anything else? Once we have a 'proven' checklist, any volunteer's to post it on their web site? I don't have one (yet).. Jared/Chris? Others? Thanks. Regards, - Kirti Deshpande Verizon Information Services http://www.superpages.com -Original Message- From: Post, Ethan [SMTP:[EMAIL PROTECTED]] Sent: Monday, June 11, 2001 5:26 PM To: Multiple recipients of list ORACLE-L Subject: RE: Removing Older version of Oracle on NT Machine My solution is to uninstall everything you can with O-installer, then delete oracle home and the oracle subdirectory under HKEY_LOCK_MACHINE/SOFTWARE in the registry. Perhaps there are some other things you should do but this has always worked for me in the past. Make sure you get rid of all databases and services first also. - Ethan -Original Message- Sent: Monday, June 11, 2001 2:56 PM To: Multiple recipients of list ORACLE-L Hi, Is there any white papers for removing one version of Oracle from an NT machine? I have 8.0 and 8i and would like to fully remove all of 8.0 including registry entries etc. I attempted to use Universal Installer, but it won't let me select any of the 8.0 components to remove them. I'm gonna keep working it and see where I get. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sunrise 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). -- This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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: Deshpande, Kirti 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).
Recording Destructive SQL Trapping Alter Table Drop Column So
Yes, but how does one do that? I was really hoping someone would post an answer since
there were other people wanting to be able to the same or something very similar.
Failing to get a response. I wrote my own; actually, I took some code provided by
Steve Adams for a different purpose and put instr and sessionid restrictions on
it.Be warned the code provided below has not been thoroughly tested.
select
'x'
from
sys.x$_kglpn p,
sys.x$_kglcursor c,
v$_session s
where
p.kglpnhdl = c.kglhdadr and
p.kglpnses = s.saddr and
instr(lower(replace(c.kglnaobj,' ', null)),'dropcolumn') 0 and
instr(lower(c.kglnaobj),'select') = 0 and
s.audsid = sys_context('USERENV', 'SESSIONID');
All this does is to make sure drop column was part of the statement and select
is not. The check for the alter table is done in the event trigger. The full text
of the trigger is
-
CREATE or replace TRIGGER record_destructive_ddl_trigger before drop or alter or
truncate ON DATABASE
Declare
pcolumn_name dba_tab_columns.column_name%type;
cursor get_column_name is
select column_name from dba_tab_columns where
owner = ora_dict_obj_owner and table_name = ora_dict_obj_name;
sql_dummy varchar2(1);
cursor is_drop_column is
select
'x'
from
sys.x$_kglpn p,
sys.x$_kglcursor c,
v$session s
where
p.kglpnhdl = c.kglhdadr and
p.kglpnses = s.saddr and
instr(lower(replace(c.kglnaobj,' ', null)),'select') = 0 and
instr(lower(replace(c.kglnaobj,' ', null)),'dropcolumn') 0 and
s.audsid = sys_context('USERENV', 'SESSIONID');
Begin
if (((ora_sysevent = 'DROP') and (ora_dict_obj_type = 'TABLE'))
or ora_sysevent = 'TRUNCATE') and ora_dict_obj_owner != 'SYS' THEN
object_security.record_destructive_ddl(null);
elsif ora_sysevent = 'ALTER' and ora_dict_obj_type = 'TABLE'
and ora_dict_obj_owner != 'SYS' THEN
open is_drop_column;
fetch is_drop_column into sql_dummy;
close is_drop_column;
if sql_dummy = 'x' then
open get_column_name;
loop
fetch get_column_name into pcolumn_name;
exit when get_column_name%notfound;
if ora_is_drop_column(pcolumn_name) then
object_security.record_destructive_ddl(pcolumn_name);
end if;
end loop;
end if;
close get_column_name;
end if;
end;
/
--
object_security is a package I wrote which records the information about the
destructive DDL into a table. It uses
the autonomous transaction pragma and many of the event attribute functions described
in Chapter 13 of the Application Developers Guide - Fundamentals manual.
Create or replace package object_security is
procedure RECORD_destructive_ddl(cname varchar2);
end object_security;
/
create or replace package body object_security is
procedure RECORD_destructive_ddl(cname varchar2) is
PRAGMA AUTONOMOUS_TRANSACTION;
Begin
INSERT INTO oracle.DESTRUCTIVE_DDL_JOURNAL VALUES
(ora_login_user, ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type,
cname,ora_sysevent,ora_client_ip_address, sysdate);
commit;
end RECORD_destructive_ddl;
end object_security;
/
---
The idea again is to timestamp the destructive DDL. Again, the code has not been
thoroughly tested. Also, it needs improvements such as excluding routinely truncated
tables.
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-Original Message-
Sent: Monday, June 11, 2001 11:26 AM
To: Multiple recipients of list ORACLE-L
You are getting current SQL but you need to go thru all SQL for this session
to find out if there was alter table drop (column).
Alex Hillman
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: MacGregor, Ian A.
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 reports
You could try something like select item, sum(decode(sign(transaction_date - trunc(sysdate,'Month'),-1,0, 0,nvl(value,0), 1,nvl(value,0)) mtd, sum(decode(sign(transaction_date - trunc(sysdate,'Year'),-1,0, 0,nvl(value,0), 1,nvl(value,0)) ytd from transactions_table group by item The sign should work out whether the transaction date is greater than start of month or start of years and if not add 0 to the total. The nvls are just in case you might have any null values lurking about. You may be able to do it better in reports but I don't have it to hand right now. HTH Iain Nicoll -Original Message- Sent: 11 June 2001 21:20 To: Multiple recipients of list ORACLE-L I am creating a report based on a couple tables. The report should show the total amount sold for the current month and another field for total amount sold Year To Date. How can I created the report so that the month to date value for a particular item is adjacent to the total amount for Year to date. Please see below for desired reports output. Item # Item DescriptionAmount Sold (Current Month) Amount Sold (YTD) 54654 Door jams 20,245.00 465,475.00 46545 Windows Casing 1500.00 124,445.57 Does anyone know what reference material I should purchase for oracle reports? Thanks, Mark -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Liggayu 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: Current Osuser
Title: Current Osuser
select
sys_context('USERENV', 'OS_USER') FROM DUAL;
Ian MacGregor
Stanford Linear Accelerator
Center
[EMAIL PROTECTED]
-Original Message-From: Behar, Rivaldi
[mailto:[EMAIL PROTECTED]]Sent: Monday, June 11,
2001 2:26 PMTo: Multiple recipients of list
ORACLE-LSubject: Current Osuser
Hi,
We can know the current oracle user (the username you are
using to connect to database) by 'select user from
dual'; How to know what is the current osuser (not the
list of osuser) ?
Tia.
Rivaldi
Re: how to substr '%' from the data?
Helen,
try this instead,, It should help
select substr(mystring,1,(instr(mystring,'%') -1))
from mytable
eg below
DEVcreate mytable (mystring varchar2(10));
Table created
DEVinsert into mytable values ('abcdefgh%jkl');
1 row created
DEVinsert into mytable values ('a%cdefghjkl');
1 row created
DEVcommit;
Commit complete
DEVselect substr(mystring,1,(instr(mystring,'%') -1))
2 from mytable
3 /
substr(mystring,1,(
--
abcdefgh
a
2 rows selected
DEV
Brian.
Helen rwulfjeq [EMAIL PROTECTED]@fatcity.com on 06/11/2001 06:06:35 PM
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Hello,
I have data like following, how do I substr the string (or use any other
function) to get until % and return without %:
*
RC-SF-DAL-nd-%
AD-LE-%-%-%
RC-DD-LKF-01-RENTAL%
CS-%-%-%-%
CS-%-ABC-%-%
*
I did select RTRIM('CS-%-%-%-%', '%-%') from DUAL, but it can not return
correct for the last record
eg, select RTRIM(CS-%-ABC-%-%', '%-%') from DUAL -- will not work
correctly
Thanks for help
Do You Yahoo!?
Yahoo! Mail Personal Address - Get email at your own domain with Yahoo!
Mail.
--
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: how to substr '%' from the data?
Not exactly sure what you're after
but possibly the below if you're simply looking to stop before the first
occurence of '%'
select substr('CS-%-ABC-%-%', 1, instr('CS-%-ABC-%-%','%') - 1) from dual;
-Original Message-
Sent: 12 June 2001 00:07
To: Multiple recipients of list ORACLE-L
Hello,
I have data like following, how do I substr the string (or use any other
function) to get until % and return without %:
*
RC-SF-DAL-nd-%
AD-LE-%-%-%
RC-DD-LKF-01-RENTAL%
CS-%-%-%-%
CS-%-ABC-%-%
*
I did select RTRIM('CS-%-%-%-%', '%-%') from DUAL, but it can not return
correct for the last record
eg, select RTRIM(CS-%-ABC-%-%', '%-%') from DUAL -- will not work
correctly
Thanks for help
_
Do You Yahoo!?
Yahoo! Mail http://personal.mail.yahoo.com/?.refer=mailiyfoot Personal
Address - Get email at your own domain with Yahoo! Mail.
--
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: Removing Older version of Oracle on NT Machine
Keep in mind though, I have two versions of oracle running and just want to remove the older version. I think there are issues with the registry. I'll be trying it tonight. Ken - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, June 11, 2001 4:11 PM Hi All, This question pops up once in a while. I did not pay much attention to replies before (not much of an NT person), but I think I will now, since I am also running into similar situation and it's always good to learn a few new things. If anyone has a step-by-step procedure to do this right the first time, please post it. All NT users will thank you :) If not, add on to (or correct as appropriate) the following check list: 1) Shutdown the database (assuming only one is running) 2) Stop the database service (via MS gui or otherwise) 3) Stop the listener service (via MS gui or otherwise) 4) Manually remove all database files (optional?) 5) Remove the above services ( a sample command syntax please ... ) 6) Use Oracle Installer to remove stuff (shall we say 'yes' to all those silly questions about shared programs, ddl etc. ?) 7) Back up registry (exactly how?) 8) Edit registry to remove Oracle under (blah blah please provide the dirs/subdirs names.) 9) Check and clean up Environment variables, if set for Oracle 10) Reboot server (I would, since Registry was fiddled with) 11) Check for any if leftovers.. (if found, what to do?) 12) ...anything else? Once we have a 'proven' checklist, any volunteer's to post it on their web site? I don't have one (yet).. Jared/Chris? Others? Thanks. Regards, - Kirti Deshpande Verizon Information Services http://www.superpages.com -Original Message- From: Post, Ethan [SMTP:[EMAIL PROTECTED]] Sent: Monday, June 11, 2001 5:26 PM To: Multiple recipients of list ORACLE-L Subject: RE: Removing Older version of Oracle on NT Machine My solution is to uninstall everything you can with O-installer, then delete oracle home and the oracle subdirectory under HKEY_LOCK_MACHINE/SOFTWARE in the registry. Perhaps there are some other things you should do but this has always worked for me in the past. Make sure you get rid of all databases and services first also. - Ethan -Original Message- Sent: Monday, June 11, 2001 2:56 PM To: Multiple recipients of list ORACLE-L Hi, Is there any white papers for removing one version of Oracle from an NT machine? I have 8.0 and 8i and would like to fully remove all of 8.0 including registry entries etc. I attempted to use Universal Installer, but it won't let me select any of the 8.0 components to remove them. I'm gonna keep working it and see where I get. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sunrise 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). -- This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE
RE: how to substr '%' from the data?
By
using the instr function.Here is an example. N.B.,
the space between the words "and" and "seven" is
returned.
select substr('Four score and seven years
ago',1, instr('Four score and seven years ago', 'seven')
-1) from dual
SUBSTR('FOURSCO---Four score and
Ian
MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-Original
Message-From: Helen rwulfjeq
[mailto:[EMAIL PROTECTED]]Sent: Monday, June 11, 2001 4:07
PMTo: Multiple recipients of list ORACLE-LSubject: how
to substr '%' from the data?
Hello,
I have data like following, how do I substr the string (or use any other
function)to get until "%" and return without "%":
*
RC-SF-DAL-nd-%
AD-LE-%-%-%
RC-DD-LKF-01-RENTAL%
CS-%-%-%-%
CS-%-ABC-%-%
*
I did "select RTRIM('CS-%-%-%-%', '%-%') from DUAL",
but it can not return correct for the last record
eg, "select RTRIM(CS-%-ABC-%-%', '%-%') from
DUAL" -- will not work correctly
Thanks for help
Do You Yahoo!?Yahoo! Mail Personal
Address - Get email at your own domain with Yahoo! Mail.
set feedback off in procedure
Title: Current Osuser Hi, Can we use the following commands inside the pl/sql procedure.(due to application requirement) set feedback off set fedback on If yes at what point in procedure. Thanks Harvinder
RE: how to substr '%' from the data?
Thanks a lot, that works.
"MacGregor, Ian A." [EMAIL PROTECTED] wrote:
By using the instr function.Here is an example. N.B., the space between the words "and" and "seven" is returned.
select substr('Four score and seven years ago',1, instr('Four score and seven years ago', 'seven') -1) from dual
SUBSTR('FOURSCO---Four score and
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-Original Message-From: Helen rwulfjeq [mailto:[EMAIL PROTECTED]]Sent: Monday, June 11, 2001 4:07 PMTo: Multiple recipients of list ORACLE-LSubject: how to substr '%' from the data?
Hello,
I have data like following, how do I substr the string (or use any other function)to get until "%" and return without "%":
*
RC-SF-DAL-nd-%
AD-LE-%-%-%
RC-DD-LKF-01-RENTAL%
CS-%-%-%-%
CS-%-ABC-%-%
*
I did "select RTRIM('CS-%-%-%-%', '%-%') from DUAL", but it can not return correct for the last record
eg, "select RTRIM(CS-%-ABC-%-%', '%-%') from DUAL" -- will not work correctly
Thanks for help
Do You Yahoo!?Yahoo! Mail Personal Address - Get email at your own domain with Yahoo! Mail. Do You Yahoo!?
Yahoo! Mail Personal Address -
Get email at your own domain with Yahoo! Mail.
Re: sys.v_$parameter
In fact, I use these fully qualified views all the time. A couple of notes: - V$ views are, in fact, synonyms for the V_$ views. - V_$ views are indeed views on the GV$ views which, as has been previous been stated, are views on the X$ tables. - There is no real performance gain to using the view name itself rather than the synonym. There are two advantages, though. (1) the v$ synonyms are public synonyms and therefore clutter the library cache with references to non-existent objects; (2) versions of Oracle prior to 7.3 do not have these public synonyms and so references to dba_data_files, for example, must be made as sys.dba_data_files. As a result of all this, I use this syntax almost exclusively. Hope this helps. -- Jon Walthour, BSCD Oracle 8i Certified Database Administrator (OCDBA) Computer Horizons Corporation Cincinnati, Ohio From: Mark Leith [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California Reply-To: [EMAIL PROTECTED] Date: Mon, 11 Jun 2001 06:25:50 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: sys.v_$parameter To bypass the view? Is it in a huge script? Could be to glean every bit of performance from the script.. Mark -Original Message- Sent: Monday, June 11, 2001 09:25 To: Multiple recipients of list ORACLE-L Why would someone write a SQL*Plus script against sys.v_$parameter when you want to find the value of an init parm, when instead you could code against v$parameter? - Greg -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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: Mark Leith 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: Jon Walthour 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).
LOB Storage Characteristics
Greetings All, I am using Oracle 81630 on Solaris 7 and I have a table which contains a LOB (CLOB actually). The DDL statement is as follows ... CREATE TABLE B_STATEMENT_TEXT_ORA (stmtnum NUMBER(10) NOT NULL, statement_content CLOB) STORAGE (INITIAL 100M NEXT 100M PCTINCREASE 0) TABLESPACE energy_proc_data_100M_02 LOB (statement_content) STORE AS b_statement_text_ora_lob (TABLESPACE energy_proc_lob_100M DISABLE STORAGE IN ROW STORAGE (INITIAL 100M NEXT 100M PCTINCREASE 0) CHUNK 8192 PCTVERSION 10 ); The details from user_lobs are ... SEGMENT_NAME TABLESPACE_NAME SEGMENT_TYPE BYTES EXTENTS -- -- -- -- -- B_STATEMENT_TEXT_ORA ENERGY_PROC_DATA_100M_02 TABLE 104857600 1 SYS_IL046179C2$$ ENERGY_PROC_LOB_100M LOBINDEX 104857600 1 B_STATEMENT_TEXT_ORA_LOB ENERGY_PROC_LOB_100M LOBSEGMENT 104857600 1 The DML operations on the table include SELECT, INSERT and DELETE. There is no UPDATE activity. Typically the data for each CLOB entry is larger than 4K so I am specifying that it is stored in the lobsegment and not the table. My question is one related to data access for the table, lobsegment and lobindex. Would there be any I/O performance benefit in having the table, lobsegment and lobindex located in different tablespaces (which would be located on different physical drives)? As you can see I can split the table and lobsegment/lobindex into separate tablespaces. I have tried to split the lobsegment and lobindex up into separate tablespaces but according to the 816 documentation this is no longer supported under 8i (depracation of the LOB_index_clause). According to Metalink the lobsegment and lobindex are co-located in 8i for a specific reason - but I cannot find what that is! Also, is there any way to specify a name for the lobindex, rather than rely upon the system generated name? Thanks Glen -- Glen Mitchell NZ Phone: +64 9 3730400 Energy Research Lab URL: http://www.peace.com Peace Software Email: [EMAIL PROTECTED]
RE: LOB Storage Characteristics
uh - Stripe And Mirror Everything. what's a tablespace? add more NVRAM (cache). he he he he ... its going to be another late one ... sales critter. -Original Message- Sent: Monday, June 11, 2001 9:51 PM To: Multiple recipients of list ORACLE-L Greetings All, I am using Oracle 81630 on Solaris 7 and I have a table which contains a LOB (CLOB actually). The DDL statement is as follows ... My question is one related to data access for the table, lobsegment and lobindex. Would there be any I/O performance benefit in having the table, lobsegment and lobindex located in different tablespaces (which would be located on different physical drives)? As you can see I can split the table and lobsegment/lobindex into separate tablespaces. I have tried to split the lobsegment and lobindex up into separate tablespaces but according to the 816 documentation this is no longer supported under 8i (depracation of the LOB_index_clause). According to Metalink the lobsegment and lobindex are co-located in 8i for a specific reason - but I cannot find what that is! Thanks Glen -- Glen Mitchell NZ Phone: +64 9 3730400 Energy Research Lab URL: http://www.peace.com http://www.peace.com Peace Software Email: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Drake 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: Veritas Quick I/0 and Oracle/ Asycnchronous I/O
Kevin's point is probably, that if each index has a depth (blevel) of three, then each index will add three LIO's to any DELETE or INSERT statement. For UPDATE statements, only the indexes on the updated columns count. LIO's mean cpu usage and latch pressure, which is why we want to mininize them in many situations. Mohan, Ross wrote: I suspect, as with most 'rules of thumb', that Kevin's does not scale well. || -Original Message- || From: Henry Poras [mailto:[EMAIL PROTECTED]] || Sent: Monday, June 11, 2001 4:42 PM || To: Multiple recipients of list ORACLE-L || Subject: RE: Veritas Quick I/0 and Oracle/ Asycnchronous I/O || || || Let's see. What was that rule of thumb I heard from Kevin || Loney? I think it || was that each index slows down DML by a factor of 3 (at || least for batch jobs || where you have to worry about recursive SQL). So 23 indexes || would run about || 70 times slower than no indexes. Do I sense a hardware || throwing contest?? || || Henry || || -Original Message- || Sent: Monday, June 11, 2001 4:01 PM || To: Multiple recipients of list ORACLE-L || || || On Monday 11 June 2001 09:06, Rachel Carmichael wrote: || OLTP system, main order table had 23 indexes on it. || Because they wanted to || || ?!?!?! || || That's just nuts. || || be able to search by customer first name, customer last || name, recipient || first name, recipient last name and had foreign keys || all over the || place. || || Sounds like Data Mart was a term they were unfamiliar with. || || Jared || -- || Please see the official ORACLE-L FAQ: http://www.orafaq.com || -- || Author: Jared Still || 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: Henry Poras || 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: Mohan, Ross 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). -- Venlig hilsen Mogens Nørgaard Technical Director Miracle A/S, Denmark Web: http://MiracleAS.dk Mobile: +45 2527 7100 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mogens =?iso-8859-1?Q?N=F8rgaard?= 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: Veritas Quick I/0 and Oracle/ Asycnchronous I/O
Alex, I think most everyone knew it was a joke. Not everyone will get your jokes. Not everyone gets my jokes. I wouldn't have it any other way. ;) Jared On Monday 11 June 2001 13:36, Hillman, Alex wrote: I hoped that everybody understand that this was a joke. I use damagement and :-) in the end. I hoped that it was grotesc. Apparently I was wrong. My apologies for all offended. For people who do not understand humor I repeate again that it was a joke. Alex Hillman -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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: set feedback off in procedure
harvinder..these are sql*plus commands...and will not work inside a pl/sql block... -- From: Harvinder Singh[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Tuesday, June 12, 2001 6:50 AM To: Multiple recipients of list ORACLE-L Subject: set feedback off in procedure Hi, Can we use the following commands inside the pl/sql procedure.(due to application requirement) set feedback off set fedback on If yes at what point in procedure. Thanks Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul 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: Enforced Costraints ??
Anshuman, whenever u add a constraint to a table, oracle will make sure all the data (if any) confirms to the constraint added you can enable or disable a constraint (even defer)... dont confuse urself with the term enforcing a constraint. Regards Rahul -- From: Anshumn[SMTP:[EMAIL PROTECTED]] Sent: Tuesday, June 12, 2001 11:26 AM To: LazyDBA.com Discussion Subject: Enforced Costraints ?? Hi gurus, Please help me to know the difference between enabling and enforcing a constraint. I need it urgently. As per my knowledge, enabling a constraint checks for the existing data to apply that constraint and then the constraint is applied for the forthcoming data. Whereas enforcing a constraint does not check the existing records and the constraint is only applied to the forthcoming data only. Is it correct ? Suppose I have a 'unique' constraint. If I enforce this constraint, then for checking the uniqueness criteria, will not it check the existing records to determine whether the coming record is unique or not ? If it checks for the uniqueness among the forthcoming records only, then I have a doubt. In this case I may have loaded a record, with a value which is already present in the existing data(Since the existing data was not checked while enforcing the constraint). Now if I enable the constraint, then that time it will check all the records for uniqueness. How does it work in that case ? Won't it give me an error ? Then how good is the use of an enforced constraint ? Please give your valuable feedback to clear this doubt. I need it urgently. Thanks in advance, Anshumn 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul 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: Urgenr : Sql Loader Question
Hi friend, i didn't succeed it with the format X'9' for the datafile delemited by 'tab'. Could u help me in this. the followin is the control file of mine: load data infile * into table abba1 fields terminated by X'9' (a,b) begindata 2384'vnb' 398489 'dke' create table abba1(a number, b varchar2(30)); Nirmal. -Original Message- From: Darlene Marley [SMTP:[EMAIL PROTECTED]] Sent: Friday, June 08, 2001 1:21 AM To: Multiple recipients of list ORACLE-L Subject: Re: Urgenr : Sql Loader Question FIELDS TERMINATED BY X'9' Chowdary, Suren wrote: How wud i load a TAB delimited data file through SQL*Loader into a Table. Like i use FIELDS TERMINATED BY , (for a comma seperated data file) what shud i use for TAB delimited file... Thanx for ur help, Chowday File: Card for Darlene Marley -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nirmal Kumar Muthu Kumaran 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).
Changing Java pool size to 0
Hi List The following is config on my initsid.ora on NT4 /Oracle 816, DB size 25GB. shared_pool_size = 52428800 # INITIAL large_pool_size = 614400 java_pool_size = 20971520 Now my Question is Can I reduce java_pool_size to 0 as I am not using any Java module. I am using BLOB in my db also I am using DBstudio for DB operations on NT box (Unix users please excuse!!). Please suggest the effects and side effects of changing java_pool_size to 0. or suggest the figure. TIA Ramesh Papnoi (BrainBench Brainbuzz Certified Oracle 8/8i DBA Developer) The obstacles you face are mental barriers which can be broken by adopting a more positive approach. -- -- 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: regarding isqlPlus
can a version earlier than 8.1.7 be used as the database server in the 3-tier-architecture. i have 8.1.5 on NT 4.0 service pack 5. saurabh - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, June 11, 2001 9:11 PM Got it working at home on Windows 2000 with 8.1.7.0.0. Works just fine, but .sql files are not able to read SQL*Plus commands (i.e. column xxx format a10). As I recall the install was fairly simple. Jon Baker Database Engineer (1/5) [EMAIL PROTECTED] www.netsec.net Saurabh Sharma wrote: hi all, has anybody using iSqlPlus tool, provided by oracle. what i know is , it's the internat enabled extension of sql * plus, utilising the 3-tier architecture. they say oracle 8.1.7 is required for the implementation. any suggestions. saurabh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Baker 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: Saurabh Sharma 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: isqlPlus
if u r a member of oracle.com web account(u can simply register at the site) u can download a free version of iSQL * Plus from www.oracle.com/download select from tools section and just fill the acceptance form. its in zip format. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, June 11, 2001 11:10 PM I'm interested in using iSQL but I can't figure out where to install it from? 9iAS, 8i1.7, DEVELOPER 6i ? Could somebody shed some light upon me, there are way to many CDs to go through them all. TIA Tavo -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Saurabh Sharma 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: Changing Java pool size to 0
Title: RE: Changing Java pool size to 0 Hi Ramesh The java_pool_size is use want you work with the JServer. Do you have a Java stored proc, EJB or CORBA in your DB ? If not, you don't need the java_pool_size. You could set it to 0. The default is 1m. Luc -Message d'origine- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Date: Tuesday, June 12, 2001 8:10 AM À: Multiple recipients of list ORACLE-L Objet: Changing Java pool size to 0 Hi List The following is config on my initsid.ora on NT4 /Oracle 816, DB size 25GB. shared_pool_size = 52428800 # INITIAL large_pool_size = 614400 java_pool_size = 20971520 Now my Question is Can I reduce java_pool_size to 0 as I am not using any Java module. I am using BLOB in my db also I am using DBstudio for DB operations on NT box (Unix users please excuse!!). Please suggest the effects and side effects of changing java_pool_size to 0. or suggest the figure. TIA Ramesh Papnoi (BrainBench Brainbuzz Certified Oracle 8/8i DBA Developer) The obstacles you face are mental barriers which can be broken by adopting a more positive approach. -- -- 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).
