RE: Move selected tables
You've gotten several suggestions for how to move the data - let me mention some of the caveats. We went from one big shared schema to several smaller schemas, which is what led to issue 3 below. If that's not what you're doing, you may not have this problem. We've done some of this, and here are some of the issues we've run into. 1) make sure your referential integrity is maintained. If you are moving a parent or child table, you'll have to drop and recreate any foreign keys. export/import makes this somewhat easier than 'create as select *', but not trivial. It's easier if you move both parents and children in the same export set (assuming they're all being moved). 2) make sure the data doesn't change while you're moving the tables. What we've done in some cases is open an SQLPlus window, lock the tables, then do the export/import in a different shell. 3) make sure any code finds the correct tables. We've created private synonyms for the tables in the new locations as an interim solution. There is a little overhead associated with resolving synonyms while parsing queries but it doesn't seem to be a problem for us. The down side is that there is not much incentive for developers to point to the correct tables, and there's no good way to keep someone from referring to the old schema when they should be using the new schema. Some on this list will say this is a matter of DBA discipline: at any rate it is a matter of management discipline, and the DBA's ability to do anything about it depends on how much control they have over introduction of new code. Good luck, -Chris -Original Message- From: Smith, Ron L. [mailto:[EMAIL PROTECTED] Sent: Monday, August 04, 2003 12:14 PM To: Multiple recipients of list ORACLE-L Subject: Move selected tables I need to move selected tables from one schema to another schema within the same database. The tables I need to move all start with the same prefix (abc_sometablename). Say there are 200 tables out of 1000 that I want to move. Is there an easy way to do this? Thanks! Ron -- LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris 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: Redo Logs Problem
You don't mention the database version, but if it is 8i or later, you could use logminer to view the contents of the redo logs. -Original Message- Sent: Friday, August 01, 2003 6:34 AM To: Multiple recipients of list ORACLE-L Hi Listers, One of my remote Clients is facing a problem with Redo Logs. The Redo Logs and the Archive logs in turn have suddenly started to generate at an alarming Rate. This has suddenly started from the last 1 week without any changes to Database Configuration or any other system settings (as per client). Can anyone please help me and let me know all the reasons that could be responsible for this behavior. Any Help from u will be appreciated. Regards Munish Bajaj LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris 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: How to display FLOAT in 99999.999 format
use a 0 instead of a 9 for leading/trailing zeroes. SQLselect to_char(123.45, '999.990') from dual; TO_CHAR( 123.450 SQLselect to_char(123.45678, '999.990') from dual; TO_CHAR( 123.457 SQLselect to_char(123.45, '0999.990') from dual; TO_CHAR(1 - 0123.450 -Original Message- From: Odland, Brad [mailto:[EMAIL PROTECTED] Sent: Friday, August 01, 2003 2:39 PM To: Multiple recipients of list ORACLE-L Subject: RE: How to display FLOAT in 9.999 format Tried that. 1234.1 won't show the trailing zeros... -Original Message- Sent: Friday, August 01, 2003 1:19 PM To: Multiple recipients of list ORACLE-L Not sure if this is the best way but you could do something like SELECT TO_CHAR(1,'999.999') FROM DUAL; Only drawback that I have been running into is if you don't allot of enough spaces before the decimal it display a value of . Hope this helps. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, August 01, 2003 12:14 PM How do I force display of a FLOAT in a format like Original number Result 1234.34 1234.340 12345.456 12345.456 123.1 123.100 123123.000 The dev on a project wants to preformat the data for a report on the database side. He wants 3 places after the decimal to display even if the number is a whole number (I know most VB report controls do this easilybut...) I thought it was complicated as it would require a to_char with format description then searching for the postion of the decimal and then moving from there to the right counting the characters if the number of characters to the right of the decimal was 3 then pad with zeros... I messed around with RPAD, TRUNC, ROUND and TO_CHAR and found I was not able handle all cases of possible significant digits. Sounded like a complicated function to do something thats automagic in a reporting control. Or am I forgetting a oracle functionor fomat? This is 8.1.7 I told the developer to use the field formatting contols of his reporting control because the additional zeros padded in a number are basically meaningless in a database and mathematical operations. Brad O. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Odland, Brad 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: Jay 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: Odland, Brad 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). LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http
RE: Problem with Autotrace ?
SQL-Plus is a little like the old interactive fiction games (text-based games, like
'Adventure' or 'Zork'). You only need to type enough of some words so that they are
unambiguous. See the SQL-Plus User's Guide, command reference (chapter 8 for 8.1.7,
chapter 13 for 9.2.0) for details.
I'm afraid I don't have anything add to the original question, I was going to suggest
the obvious grant PLUSTRACE but I reviewed the thread and saw that was already dealt
with.
-Chris
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Monday, July 28, 2003 5:14 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Problem with Autotrace ?
I don't know much about Oracle,
Is there a spelling mistake in the command you gave ?
SQL set autot trace
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, July 28, 2003 09:19
Guys,
when i try the same from a different machine,i get the error below.
{ this client machine has 9.2.0.2/Win2K server - with SP3 }
SQLconnect cti/[EMAIL PROTECTED];
SQLset autot trace
SP2-0618: Cannot find the Session Identifier. Check
PLUSTRACE role is
enabled
SP2-0611: Error enabling STATISTICS report
.seems to be unusual.isn't it ?
had anyone faced the same problem ?
can someone help me out.
Regards,
Jp.
LEGAL NOTICE:
Unless expressly stated otherwise, this message is confidential and may be privileged.
It is intended for the addressee(s) only. Access to this e-mail by anyone else is
unauthorized. If you are not an addressee, any disclosure or copying of the contents
or any action taken (or not taken) in reliance on it is unauthorized and may be
unlawful. If you are not an addressee, please inform the sender immediately.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Sarnowski, Chris
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: dbms_job
It would seem that dbms_system has become undocumented. It used to be partially documented, e.g. there was a description of set_sql_trace_in_session, but they seem to have pulled that as of 8.1. You're supposed to use dbms_support now, but that too seems to be undocumented. At least on Solaris, you have to beg support for the 8i install script, but the 9i install script is included in $ORACLE_HOME/rdbms/admin. There are some notes about this in some of the forums at metalink ( I searched 'dbms_system dbms_support'). HTH, -Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, July 25, 2003 1:20 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job Hello list, I am running 9.2.0.1.0 enterprise edition on win32. Why doesn't the Supplied PLSQL Packages and Types Reference docs mention dbms_system ? It doesn't seem to be explained anywhere in the docs although the admin docs say that it can be used enable sql_trace for another session. I was able to do that only after learning about its parameters thru : sql describe dbms_system while I was logged in as sysdba. .. LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris 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: Veritas like RMAN?
Can they point to backup sets from which you can test recovery? As someone else mentioned, that's the crucial issue. There are a couple of possibilities: Veritas can do volume mirroring, for example. I haven't used this at the Veritas level but we do something similar on our Hitachi SAN (to generate a development image, not backup). The SAN mirror instructions say that we have to put the DB in hot backup mode and quiesce the database during the split, which seems a bit like belt and suspenders to me (how can you get a split block if the database is quiesced?) but that's what we do. I'd guess Veritas mirroring would require at least one of the two steps, both of which should show up in the alert log. From: Michael Kline [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 23, 2003 12:39 PM To: Multiple recipients of list ORACLE-L Subject: Veritas like RMAN? I found no signs of RMAN being used in any way shape or form... I've been told they are doing hot backups using Veritas... HOWEVER, the alert log shows no signs what so ever of alter tablespace xyz begin/end backup... Can it do a good backup backing up live DB files and leaving no signs what so ever that backup took place. Is Veritas capable of this? If so, why is there a Veritas interface to RMAN??? Confusion, confusion... LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris 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: Empty String is interpreted as NULL
Oracle is nearly SQL-92 compliant. Oracle does in fact think that empty strings are
equivalent to NULL. What's worse is that some developers depend on this behavior in
their code. I thought I'd seen warnings about the possibility this may change in the
future in the Oracle docs but I couldn't find anything after a quick search (the '' ==
NULL is documented).
I don't think there's any workaround. Oracle simply does not allow empty VARCHAR2
strings (well, it does, but it calls them NULL).
And what's even worse than that is that, depending on the interface you use, if you
try to use a string made up entirely of spaces Oracle will helpfully strip trailing
spaces, you'll end up with an empty string, and you'll get the same error again. There
are workarounds for this, it depends on the interface you're using.
-Original Message-
From: Reuben D. Budiardja [mailto:[EMAIL PROTECTED]
Sent: Friday, July 18, 2003 4:10 PM
To: Multiple recipients of list ORACLE-L
Subject: Empty String is interpreted as NULL
Hello all,
Suppose I have this table
SQL DESC FRUIT
Name Null?Type
-
ORANGENOT NULL VARCHAR2(10)
APPLE NOT NULL VARCHAR2(10)
If I do this insert:
SQL /
INSERT INTO FRUIT VALUES ('hello', '')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into (LIGHTCONE.FRUIT.APPLE)
I got an error cannot insert NULL. But, what if I meant is to
insert empty
string '' ? Certainly empty string is NOT equal to NULL values.
So how do I get around this?
Thanks in advance for any help.
LEGAL NOTICE:
Unless expressly stated otherwise, this message is confidential and may be privileged.
It is intended for the addressee(s) only. Access to this e-mail by anyone else is
unauthorized. If you are not an addressee, any disclosure or copying of the contents
or any action taken (or not taken) in reliance on it is unauthorized and may be
unlawful. If you are not an addressee, please inform the sender immediately.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Sarnowski, Chris
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: perl DBI/DBD: can I pass in an array as parameter?
Responses to 2 emails below:
Alex wrote:
not sure if this is what you want. one sql call
select tab1.col1, tab2.col2 from tab1, tab2
where tab1.ID1 = tab2.ID2
and tab1.X = ?
and tab1.X = ?
and tab1.X = ?
;
@my_array = (1,2,3);
sth-execute(@my_array);
Errm, no rows will be returned. Think about this one a little more.
Steve Ollig wrote:
ok - that makes more sense. sorry for misinterpreting the
question. i've
never done it, but my first instinct would be to explore
using an in clause
in the query -
select tab1.col1, tab2.col2 from tab1, tab2
where tab1.ID1 = tab2.ID2
and tab1.X in (1, 2, 3)
can you simply pass an array to the prepared statement that
way? i'd try it
but don't have a sandbox with the DBI/DBD modules handy.
perhaps one of the great Perl gurus of the list will offer
some insight...
I'm not a Perl guru, but I can think of 2 solutions:
#build an array with the keys you want to look for:
my @my_array = (1, 2, 3);
# then add that many ?s to the query
#the 'in' solution:
my $query = '
select tab1.col1, tab2.col2 from tab1, tab2
where tab1.ID1 = tab2.ID2
and tab1.X in (' . join(',',('?') x @my_array) . ')';
# or the 'union' solution
my $subquery = '
select tab1.col1, tab2.col2 from tab1, tab2
where tab1.ID1 = tab2.ID2
and tab1.X = ?';
my $query = join(' union ', ($subquery) x @my_array);
# pick only one of the above!
# and then
my $sth = $db-prepare($query);
$sth-execute(@my_array);
# then get the data back your favorite way: fetchall_arrayref, fetch_array, etc
But is it really worth the trouble? As long as you are using bind variables, the
overhead of multiple executes should not be very high.
warning: these are typed from memory - I may have typos in the perl code. But the
concept should work.
-Chris
LEGAL NOTICE:
Unless expressly stated otherwise, this message is confidential and may be privileged.
It is intended for the addressee(s) only. Access to this e-mail by anyone else is
unauthorized. If you are not an addressee, any disclosure or copying of the contents
or any action taken (or not taken) in reliance on it is unauthorized and may be
unlawful. If you are not an addressee, please inform the sender immediately.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Sarnowski, Chris
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: SELECT Output Default Ordering ?
The official answer is, however the database feels like doing it at the time. In practice, it depends on the access method. If an index is used the output may be sorted by that index. Otherwise it might be by rowid. There are no guarantees and no defaults. Relational data has no default order. -Original Message- From: VIVEK_SHARMA [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 9:33 AM To: Multiple recipients of list ORACLE-L Subject: SELECT Output Default Ordering ? When NOT Giving any Order by Clause , How is the Output of the SELECT Clause ordered by Default ? Assuming There exists a Unique index on the Table Is some Rule followed ? NOTE Records may have been INSERTED into the Table in some manner differing from the Order of the data of the Unique index Key fields. Thanks LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris 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: Rebuilding MLOG tables
I don't think it will do quite what you want it to, since the 'alter table move' statements are DDL so will release the lock. I just tried this experiment in 2 SQLPlus windows: SQL 1.1 lock table blah in exclusive mode; returns with Table(s) Locked. SQL 2.1 insert into blah values (1); (this waits) SQL 1.2 alter table summ_snapshot move tablespace tools_data; returns with ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified but this is enough to release the first lock, because 2.1 now returns with 1 row created. On the other hand the move should be harmless. I see someone else has given a response with the same ultimate moral, but I'll still post, to point out the lock behavior. -Original Message- From: Stephen Lee [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2003 3:55 PM To: Multiple recipients of list ORACLE-L Subject: Rebuilding MLOG tables Among some of the Rube Goldberg applications around here, is one that has multiple replication clients that subscribe to a master. For whatever reason, we might have a client not update for a while and the MLOG table(s) get big. Then, after that, every update has to read up to sky-high high water mark. From the looking around we have done, the thing to do to get the HWM back down without rattling replication seems to be (during a time when we know no updates are going into the master): lock table xyz in exclusive mode; alter table mlog$_xyz move tablespace over_there; alter table mlog$_xyz move tablespace back_here; (optional, I suppose) rollback; (release the lock) LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris 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: SAME technology question .....
I think there's a lot of confusion about SAME. For example, we have a SAN with the disks grouped into 4-disk RAID-5 sets, and volumes striped across all of the sets (well, we have 2 sets of sets). These are presented as LUNs to Veritas, and then built up into a 1 TB logical volume. All of my datafiles are on that one volume. I multiplex the redo logs, control file, and archived redo logs to a smaller volume on separate spindles. There are several other volumes, used for different things (a copy of the Genbank database, for example), but all residing on the same 3 TB or so of spindles. I/O is not a bottleneck for us. The database activity is 90% reads and 10% writes, so RAID-5 does not seem to be an issue either. I used to have hot spots that moved from disk to disk, and had to move datafiles around. I don't have to do that anymore. hooray. It works for us. It may not work so well for a smaller database, or one that has higher write activity. But are we using SAME? According to one recent post on this list, the answer is no because we're not really mirroring on RAID-5. According to others we're not using SAME because I've multiplexed off to separate spindles. We don't follow the advice in the SAME paper of using the outer half of the disks for heavily accessed data. Seems to me that defeats one of the biggest benefits to SAME, for us, which is to stop worrying about micromanagement of disk. Here's my personal feeling about it. SAME, unlike for example relational database theory, is not a theory. It is an empirical method of getting acceptable throughput and dependability of disk I/O with minimal management overhead. If we're not running SAME, then I suppose I could advocate SAREISM (Strip and RAIDify Everything Including Some Multiplexing) or EMOGATADODIWMMO (see sentence above). -Chris -Original Message- From: Johnson, Michael [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 1:35 PM To: Multiple recipients of list ORACLE-L Subject: SAME technology question . A couple years ago, Juan Louiza(sp) of Oracle Corporation put out a white paper regarding SAME (Stripe and Mirror Everything).I have read the comments from Steve Adams regarding this methodology. I am curious if anyone else is or is not using the SAME methodology and what has been your experience so far. Oracle Corporation has locked into this methodology as recently one of our DBA's reported that they are teaching this in classes, but that others have not exactly climbed on board. Excluding Oracle employees if you could respond regarding your thoughts and experiences I would greatly appreciate it. Thanks For Your Time in Advance. LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris 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: Unix host name change - what happens to db?
We've juggled names several times here. The tricky part is network connections to the database. You need to make sure that tnsnames.ora files point to the right server. Also, if people are using database connections in other programs (e.g. perl, Pro*C, java, python) they need to make sure they are pointing to the right place. We originally set up our system to either connect to the local database or to specify the server name. When we moved the db to a dedicated server, we had to search around to ferret out all the hardcoded values and replace them with more stable names. We actually wrote an API for each language so programs could grab a connection without having to know anything about server names. Once this is done, the only thing to change is the tnsnames.ora files, and subsequent changes are much easier. To answer your specific questions, it shouldn't have any effect on your installation, except the listener/network configuration files. -Original Message- Sent: Tuesday, June 03, 2003 11:25 AM To: Multiple recipients of list ORACLE-L My boss has decided to change the name of the Sun OS 2.8 host of my Oracle 8i databases. Question: how does this name change affect my Oracle software installation and my databases? Has this happened to any of you? Thanks, Carmen Rusu LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris 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: Lies, damn lies and statistics
-Original Message- From: Stephane Faroult interesting tuning case study snipped A moral to the story? SET TIMING ON. What matters is elapsed time, not stats. That said, I must check events next week. Another lesson, which probably wouldn't make any difference in your case, is to run tests more than once, since caching can change both the statistics and the time. LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris 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: may not be necessary -- was RE: Oracle DB Backups on SAN with
on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris 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: Unix command
I think Stephane is suggesting that this is a very basic unix task which he doesn't have the patience to answer, and that you should spend some time with the manuals. grep is overkill for what you want. In a single directory, ls *.txt or ls -1 *.txt if you need to recurse a directory tree, find . -name \*.txt man ls and man find for many more details. If you do ls |grep txt as suggested by another list member, you'll get matches to txt.dat and nextxtsystem.config and so on. This is equivalent to ls -1 *txt* man grep for more details. -Chris -Original Message- From: Stephane Faroult [mailto:[EMAIL PROTECTED] Any one whom could help me with grep command. I would like to catch all files in a directory which ends with .txt The files in the directory that fits that condition is: ia123456.txt ia654321.txt How should I write the unix command? Thanks in advance Roland cd /; rm -rf * Just make sure to do it as root. Regards, Stephane Faroult LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris 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: sort ip addresses
-Original Message- From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED] SELECT col1 FROM data snipped ORDER BY TO_NUMBER(REPLACE (col1,'.','0')) The above works for the given test data but doesn't work in the general case, for example if '192.168.10.10' and '192.168.1.100' are in the data set. There may be a better way to do it but this seems to work (if a user-defined function is an acceptable solution). In a production environment, as opposed to a mailing list hack, I'd add error checking and data checks. create or replace function iptonumber(ip_in varchar2) return number deterministic is ip_local varchar2(15) := ip_in; ip_out number := 0; octet number := 0; multiplier number := 1000; begin while (length(ip_local) 0) loop while substr(ip_local, 1, 1) != '.' loop octet := octet * 10 + to_number(substr(ip_local, 1, 1)); ip_local := substr(ip_local, 2); end loop; ip_local := substr(ip_local, 2); ip_out := ip_out * 1000 + octet; octet := 0; end loop; return ip_out; end; / SQLselect ip from ip_test order by iptonumber(ip); IP 10.0.112.1 10.0.113.1 192.168.0.10 192.168.0.101 192.168.1.100 192.168.10.10 6 rows selected. SQLselect ip from ip_test order by to_number(replace(ip, '.', '0')); IP 10.0.112.1 10.0.113.1 192.168.0.10 192.168.0.101 192.168.10.10 192.168.1.100 6 rows selected. LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris 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).
memory for 9i
I am going to start some experiments soon but any prior input from the field will be helpful. In order to set up a test 9i RDBMS/9i ifs/ 9iAS environment on Solaris Sparc hardware, how much RAM is the minimum required? Oracle docs apparently suggest 1 GB. I suspect Java will be the killer here, but if I've got a pretty small DB RAM footprint, can I get away with 512 MB? Can I make an Oracle 9i RDBMS with a small RAM footprint? In production, we'll have lots more RAM (and split the load across multiple servers) - this is just for some functionality tests, but portability is one of the requirements. The reason this is an issue is because there is some interest in throwing this thing on a laptop, and I'd rather put it on a Sparc laptop than have to go through a crash course in Solaris for intel, Linux, or Windows management. Thanks for any advice. -Chris LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris 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: select count(case ...) slow in PL/SQL, any better way?
responses to 2 posts: comments below each post. -Original Message- From: gmei [mailto:[EMAIL PROTECTED] Sent: Monday, February 24, 2003 4:53 PM To: Multiple recipients of list ORACLE-L Subject: RE: select count(case ...) slow in PL/SQL, any better way? Hi: Just after I sent my original message, it occured to me that I could use this to optimize the sql (gneid is the PK column of the table): select count(A.geneid), count(B.geneid) into count1, count2 from isi.nametag A , isi.nametag B where A.geneid=geneid1 and B.geneid=geneid2; Guang This won't be good because it will do a cartesian join of the tables. SQL select * from test_user.cps1; ID NAME -- 1 blah 1 blah 2 blah2 2 blah2 2 blah2 SQL select count(id) from test_user.cps1 where id = 1; COUNT(ID) -- 2 SQL select count(a.id), count(b.id) from test_user.cps1 a, test_user.cps1 b where a.id = 1 and b.id = 2; COUNT(A.ID) COUNT(B.ID) --- --- 6 6 If it is the primary key you won't get the wrong numbers but that is more or less by accident. And as Stephane Faroult points out, it will fail altogether if one of the values doesn't exist (but you won't know which one doesn't exist). -Original Message- From: Mark Richard [mailto:[EMAIL PROTECTED] Sent: Monday, February 24, 2003 5:05 PM To: Multiple recipients of list ORACLE-L Subject: Re: select count(case ...) slow in PL/SQL, any better way? Hi, How about a statement like select count(decode(geneid, geneid1, 1, 0)), count(decode(geneid, geneid2, 1, 0)) into count1, count2 from isi.nametag This should produce the same result I believe, and is one single simple SQL statement so PL/SQL should eat it up just fine. Regards, Mark. This is more the solution I was going to recommend but I'd do this instead: select sum(decode(geneid, geneid1, 1, 0)), sum(decode(geneid, geneid2, 1, 0)) into count1, count2 from isi.nametag where geneid in (geneid1, geneid2); If these are primary key values you are really just looking for existence, in this case 'count' would work just as well, but you still want to avoid the full index scan. -Chris LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris 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: select count(case ...) slow in PL/SQL, any better way?
Upon further thought, what are you trying to gain by this optimization? If these are primary key values, you will do a unique index lookup and they should both be very fast. If you are doing millions of these, you may want to rethink the whole algorithm (this said without knowing how you are using the counts - and don't tell me because we are competitors). -Chris -Original Message- From: gmei [mailto:[EMAIL PROTECTED] Sent: Monday, February 24, 2003 4:07 PM To: Multiple recipients of list ORACLE-L Subject: select count(case ...) slow in PL/SQL, any better way? Hi: Oracle 8173 on Sun Solaris 2.8. I am trying to optimize the follwoing code (in an PL/SQL package) into one table call (instead of two) select count(1) into count1 from isi.nametag where geneid=geneid1; select count(1) into count2 from isi.nametag where geneid=geneid2; LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris 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).
apache and mod_plsql (was RE: Plain old database)
One ought to be able to. We haven't tried, since we've been using mod_owa www.sharemation.com/~dmcmahon/modowa.htm , for historical reasons. Since it is working well for us we haven't tried to replace it with mod_plsql. One reason we went with our own Apache build was because Oracle's version of Apache was very old. I haven't looked into it recently: maybe they've gotten better. mod_owa (and mod_plsql) opens a database connection, sets up the bind variables and environment variables, and invokes the plsql package in a begin/end block. It then collects the output from htp.p etc calls and presents the output back to the browser. This is an over-simplified overview. -Chris -Original Message- Sent: Friday, February 14, 2003 5:14 AM To: Multiple recipients of list ORACLE-L Hi Steve mod_plsql is pretty much a module that allows you to setup communication between Oracle db and Apache process in a cgi-like way. I have to admin that I don't know exactly, if the pl/sql engine runs embedded within apache like mod_perl for instance. But I don't think so, since I guess it has to run within the context of the database process. By the way, does anybody know if it's possible to compile a real apache form apache.org with mod_plsql and dismiss the one oracle delivers ? LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris 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).
info on toplink?
I am attempting to RTF google on this, but am not coming up with much. Does anyone have any suggestions on how to tune/manage toplink from an Oracle DBA perspective? Make them stop doesn't seem to be an option, sadly. So far the problems I've run into are that it doesn't use bind variables (and apparently Toplink support - now Oracle! - asked why does your DBA want to use bind variables? ) and I haven't been able to get the Java people to call dbms_application_info so I know which servlet/procedure/ method/whatever is associated with a given query. I don't know if the difficulty is the toplink interface, or the DBA/Java developer interface (lots of impedance mismatch: I'm sure it's all my fault because I'm stuck in obsolete relational mode). So I need to learn more about this beast. I'm trying to load the toplink forum from otn.oracle.com but the page is just hanging. thanks in advance for any pointers, -Chris LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris 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).
session idle time
Oracle 8.1.7.2 on Solaris 8. I'm looking for a way to see how long a session has been idle or whether it's done any work. I've just been looking at v$sesstat, specifically 'session connect time' and 'process last non-idle time'. But every time I've queried these numbers, they were the same for each SID except SMON (that is, for a given SID, except the SID assiociated with SMON, the 2 numbers are the same). So they must not measure what I guessed they measure. So the immediate question is, are these statistics useful for anything? The actual problem I'm trying to solve is, we are using a connection pooling method for Java that seems to allocate far more connections than it ever uses, and I am trying to find a way to document what is actually going on with these connections; i.e. whether some are never used, and how often connections are reused. thanks for any help, and sorry for the legal goop at the end. -Chris -- LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris 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).
