At 10:34 AM 1/14/2003, Ron Rogers wrote:
List,
We use TOAD as one of our database tools and I was viewing some of the
SQL statements that were processed and I can't explain the why the
statement is the way it is.
The developer wrote Select to_char(sysdate,'mm-dd-') from dual;
in a VB
v$instance changed a lot. Otherwise Jared is right about #, which was mostly appended
to (number) columns named 'TYPE' (became a reserved word in 8.x, thanks to the
$£ùàç?%* object option) (in sys.obj$, sys.user$ most notably, possibly sys.seg$ too -
perhaps sys.con$ or sys.cdef$ as well, can't
Thanks,
this is what I'm looking for.
regards
Volker Schoen
E-Mail: mailto:[EMAIL PROTECTED]
http://www.inplan.de
-Ursprüngliche Nachricht-
Von: Reardon, Bruce (CALBBAY) [mailto:[EMAIL PROTECTED]]
Gesendet: Mittwoch, 15. Januar 2003 02:54
An: Multiple recipients of list ORACLE-L
I installed IDS with a user called netrangr (the IDS manual shows it)! All
Oracle variables are set in the profile of this user! If I connected in the
Solaris with this user and call the sqlplus, I connect to the database
allright!!!
Thanks
Flávio Reis
-Mensagem original-
De: Richard Ji
tony ynot,
Hi, what is your physical memory and sga? If set mincache=direct,
enlarge your sga to a higher value(maybe half of physical memory according to your
connections and parameters like sort_).
Since enlarge the db_block_buffer does help, why set it back
Hi,
Has anybody yet upgraded from Oracle 7.2 clients to 9.2 clients and get
their delphi/BDE apps to work.
If so could you please tell me more or less how, so I can pretend to be
smart with the application people :-)
Does a 7.2 client communicate with 9.2 database anyway?? According to
Title: RE: slowish query causing problems...
Hello List,
Thanks Mark, the rownum = 1 did the trick, it chopped a full six seconds off most of the trials.
Thank You to very one who contributed, the optimizer still insists to run use FULL rather than the indexes, even after analyzing the
we have ORACLE 8.1.7 on NT server and this erver only run ORACLE
application. Sometime the server will have 100% CPU usage form 20 minutes
to couple hours. In that time No one can login or do anything. We guess
the problem come from SMON, but all of the tablespaces pct_increase is 0
(except
Hi,
Has anybody yet upgraded from Oracle 7.2 clients
to 9.2 clients and get
their delphi/BDE apps to work.
If so could you please tell me more or less how, so
I can pretend to be
smart with the application people :-)
Does a 7.2 client communicate with 9.2 database
anyway?? According to
Hi Dear List,
Many many thanks to all who replied to this thread. Your information is
very valuable for us. On my own research, I found majority higher
education institute chose Brio over other products. Let's see, we will
arrange a demo to our site.
Joan
--
Please see the official ORACLE-L
I don't know how big your Indexes are but ...
I drop the Index and create with NOPOPULATE and then do a CTX_DDL.SYNC_INDEX
which can safely run in the background [I do this for the lone Context Index
in 9iFS].
Hemant
At 09:29 AM 14-01-03 -0800, you wrote:
Hey all,
Spent last night (til the
I am using substitution variables in SQLPlus, but don't want to be prompted
for their values. I want to set them at the start of the script. Kind of
like a preprocessor directive. Is this possible?
Erik
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Erik Williams
thanks a lot 4 your replies.
/*i followed the way dick and mark suggested.*/
daniel
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Daniel Wisser
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California
Another good one is DBA_TAB_COLUMNS. Check the tables listed in it with the
tables listed in DBA_TABLES.
When faced with a similar situation, do a little detective work. Since these
are views, you can examine the query that generates the view. This
information is in DBA_VIEWS. Compare the
Paula - I'm no expert in this area, but since I didn't notice a reply to
your question . . .
Are relational constraints defined on these tables? If yes, then you have a
simple task. If no, it is a bit more challenging. Recently someone here used
Sybase Powerdesigner on a 100-table schema that
Greg,
Did you get any reply on this ?
I've got a developer who also received a similar error message in one of
her Pro*C programs
when we were testing Failover [SHUTDOWN ABORT one instance].
Hemant
At 01:29 PM 09-01-03 -0800, you wrote:
Have any
of you all seen this error-and really understand
Interesting idea, Hemant! It's not perfect, but it's a better alternative
than either the ORA-29863 or populating the index on the rebuild.
Thanks! :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
You can explicitly assign the values in the script or you can use parameters
and pass in the values. Make sure you use the and not .
Dan Fink
-Original Message-
Sent: Wednesday, January 15, 2003 8:04 AM
To: Multiple recipients of list ORACLE-L
I am using substitution variables in
Hi All,
I cannot seem to find DD view on global temp tables. I would think Oracle
stores it somewhere. Also is there a way to change from delete rows to
preserve rows via some alter command.
Thanks
Rick
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL
SET SCAN OFF
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, January 15, 2003 3:04 PM
I am using substitution variables in SQLPlus, but don't want to be
prompted
for their values. I want to set them at the start of the script. Kind of
Dennis,
That's one of the nuances of MV replication. If you alter the master table,
you have to drop and recreate the MV on the snapshot site. This is as per
Oracle's internal documentation.
However, there is a trick. If you have created the MV using a prebuilt
table, then you can have a
Use instead of and you should not get prompted if the variable has a
value set.
-Original Message-
Sent: 15 January 2003 15:04
To: Multiple recipients of list ORACLE-L
I am using substitution variables in SQLPlus, but don't want to be prompted
for their values. I want to set them at
Title: Can't start Apache for OEM web interface
Hi!
I am trying to configure the Enterprise Manager Console to be accessed from a web browser. Does anybody have any experience with this?
My client is XP and IE6. The database (9.2.0.2) and Apache web server are running on Solaris
When doing an update that fails to update any rows, does a Rollback or a
Commit need to be done at the end?
We have a problem with some code where an update is attempted, but after
the SQL command has run SQL%ROWCOUNT = 0 so no rows were updated. The
program does not then perform a Commit
Title: RE: Database/system Crashing
I'm not sure about savecore but I do know that the disks
are internal. No SAN... They're about to yank SUN's chain because they are
trying to resolve this remotely and its taking too long. Hopefully we'll have
something solid from SUN next week. I am going
Stephane - If you ever take the OCP 9i upgrade exam, you'll want to be very
clear on 07_DICTIONARY_ACCESSIBILITY. Here is the definition:
Setting this TRUE (default before 9i) means users with SELECT ANY can read
any objects owned by SYS (data dictionary.
So if your users have scripts that read
Title: RE: Where to get info on global temp tables
select *
from dba_tables
where temporary = 'YES'
/
I believe the answer is NO to your second question.
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any
Title: RE: Question about a session error with 9iRAC
fact: Oracle Server - Enterprise Edition
fact: Transparent Application Failover (TAF)
symptom: Application fails
symptom: ORA-25408: can not safely replay call
cause: This is one of the session state related errors you can get with
Title: RE: Win2k/8.1.7/SQL Question
I looked at our copy of the 9i Docs, and did some hunting around on
the
web, and this code definitely looks like 9i SQL. However, the
Siebel
instance is 8.1.7.3 (or 4...I don't exactly remember). How can they
be
running this SQL?
Thanks,
Mike
Another thing that works is using a connect string to a non existent host. Takes
forever to return an error. A timeout of 5 seconds in the perl script works.
Thanks for your help, Dave
On Tue, Jan 14, 2003 at 12:16:27PM -0800, [EMAIL PROTECTED] wrote:
Try killing the processes for an Oracle test
Title: Message
All, I would like to
track the performance of my production databases by runningthesame
SQL statementagainst each database every 5 minutes or so and
recording the results. For example:
sql set timing
on;
sql select
count(*) from dba_tables;
That was I would
know if they
My guess is that your test data contains two dates with the year 1902.
09:18:49 SQL
09:18:49 SQLcreate table d( d date );
Table created.
09:18:49 SQL
09:18:49 SQLalter session set nls_date_format = 'mm/dd/yy';
Session altered.
09:18:49 SQL
09:18:49 SQLinsert into d values('12/10/1902');
1
That's not quite what he asked for...
Erik, you're looking for the DEFINE command, and you don't really need to
use '' either (unless you want to reuse the script without the DEFINE),
e.g.:
define datestamp = sysdate - 1
select count(*) from logtab2003
where datestamp = to_char(datestamp,
Yes.
SQL define tname = t1
SQL select * from tname where rownum 2;
old 1: select * from tname where rownum 2
new 1: select * from t1 where rownum 2
EMPNUM SEQNUM LAST_BID
-- -- --
100 1 0
SQL undefine tname
SQL /
Enter value
define myvar='whats up, doc?'
select 'myvar' from dba_objects;
Erik Williams [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
01/15/2003 07:04 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:SQLPlus
You can use the SQL*Plus commands DEFINE to provide values for variables
and UNDEFINE to reset them.
Karen
Original Message:
-
Date: Wed, 15 Jan 2003 07:04:18 -0800
To: [EMAIL PROTECTED]
I am using substitution variables in SQLPlus, but don't want to be prompted
for their
Title: Message
Statspack ??
Raj
__
Rajendra
Jamadagni
MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot
com
Any opinion expressed here is
personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but
having an
Something like this, perhaps.
accept var1 char prompt 'What grantee? ';
select *
from dba_sys_privs
where grantee = upper('var1');
-Original Message-
From: Erik Williams [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 15, 2003 10:04 AM
To: Multiple recipients of list ORACLE-L
John - What SQL query was used to select these rows. Was it SELECT FOR
UPDATE?
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, January 15, 2003 10:29 AM
To: Multiple recipients of list ORACLE-L
When doing an update that fails to
I'm not sure this is what you wnat, but here is a short example of the use
of DEFINE / UNDEFINE and (Accept once but use many times inside script
without re-prompt)
--
define testtyp = Repair
/
select * from buy_type where buy_typ =
Larry,
Thanks for taking the time to explain this. You are really patient when it
comes to writing!
I see now how Oracle does the refresh. I also remember now seeing a post in
this list some time ago asking about the possibility of tuning a similar
sql. I think it was you :)
The way Oracle is
Title: RE: oracle locking question
Yes ... because you are doing a transaction. Row count 0 is the result, but at that time transaction is still in uncommitted state. Please issue a commit or a rollback to complete the transaction ..
Raj
__
Title: Message
Tom,
Not a good idea. Since new tables can be added/dropped and
the queries from the data dictionary views can be poor performers, you really
don't get a good idea of the actual speed of operation. The execution of this
query once every 5 minutes will have a negative impact
How does the IDS gets run? Do you also login as netrangr and run it
or is it started during system startup through rc.d scripts?
-Original Message-
Sent: Wednesday, January 15, 2003 6:44 AM
To: Multiple recipients of list ORACLE-L
Syst
I installed IDS with a user called netrangr (the
Tom - I'll provide an example of what we do and maybe it will give you some
ideas. On one database, the users have identified a certain process that has
marginal performance at best, and when anything gets out of whack it gets
bad real fast. So the developers have added a logging feature in the
A more comprehensive solution would be statspack.
A simpler solution would be to get the sum of wait time (not counting the
idle ones) . it could provide you with some measure of database
performance... You need to arrive at a baseline wait time as being normal
for your database and any
If you pass arguments to your script, they would get reflected in the
substitution variables defined in the script.
test1.sql:
*
Disclaimer
This message (including any attachments) contains
confidential information intended for a specific
Title: Message
I
guess I would also like to test out the network response time. If I run
the same SQL from one UNIX box to the production databases at other sites (via
sqlnet),I can record total run time and sql statement run time (I assume
the difference would be network response time?).
John,
I just ran across this the other day.
The answer is, you must perform a commit or rollback to release the locks.
You can see this by doing something as simple as
update table
set column_name='a value'
where pk_column=0;== make sure there is no record for this value.
then, look for
Hi all!
I need to know if is possible to limit Oracle to use only one processor, in
a machine that has 2 processors?!
And if is possible to limit RAM too? Can I limit Oracle usage of RAM memory?
Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
Title: RE: Date data type
Because in the second select, you are using the wrong mask. Try 'mmyy'.
-Original Message-
From: Sony kristanto [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 14, 2003 8:49 PM
To: Multiple recipients of list ORACLE-L
Subject: Date data type
Hi
Ok, thanks
-Original Message-
Sent: Wednesday, January 15, 2003 2:10 PM
To: Multiple recipients of list ORACLE-L
Tom - I'll provide an example of what we do and maybe it will give you some
ideas. On one database, the users have identified a certain process that has
marginal performance
DENNIS WILLIAMS wrote:
Stephane - If you ever take the OCP 9i upgrade exam, you'll want to be very
clear on 07_DICTIONARY_ACCESSIBILITY. Here is the definition:
Setting this TRUE (default before 9i) means users with SELECT ANY can read
any objects owned by SYS (data dictionary.
So if
Title: Message
Tom,
Hate
to say it, but what you really need is a robot tool that can mimic what your
application is doing and to keep the results for reviewing
later.
any
query againstDBA views are notoriously slow - mostly because the tables
that these views are based on are not really
Ah! There's the rub...
RMAN isn't free unless you are only using device-type=DISK. If you are
going to use device-type=SBT-TAPE, then some media-management vendor like
Veritas or Legato or whomever is going to charge you a *bundle* to
integrate.
Oracle purposely did this so they would not have
Flavio - You control the amount of RAM by your init.ora settings.
Limiting processor usage is probably up to your O.S., and you didn't mention
which O.S. you are on. This is probably not a good idea. Modern operating
systems do a pretty good job of prioritizing the work, and limiting by CPU
is a
List,
i have access to a server that has 1 GIG memory and I will be loading
Linux on as the OS. I would like to load 9iAS and allow the developers
the ability to start learning to use Portal to create a browser based
database access method.
Is there a list that will cross reference from If you
Title: Message
Yes I
understand your point. Thanks
-Original Message-From: Mercadante,
Thomas F [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January
15, 2003 3:11 PMTo: '[EMAIL PROTECTED]'Cc:
'[EMAIL PROTECTED]'Subject: RE: Database
tracking
Tom,
Hate
to say
Title: RE: Database tracking
Tom,
This is what we do ...
We have a schema called heartbeat ... owns a table called rhythm (one column time_stamp data type date);
1. Using a perl program, we log on to instance, insert a row in the table with value sysdate.
2. Use dbms_lock.sleep to sleep
Flavio,
I agree with what Dennis said about the limiting of ram usage by
Oracle but,
The init.ora setting will establish what you would like to use for the
oracle settings and it is best if it is contigious memory also. On the
OS OpenVMS there is a OS setting that will reserve memory in a
Almost true. If you are going to use a local attached tape device you can
use the Legatto Single Server version of Legato that comes with 9i and
backup to that tape device. Of course this dosen't support networked backups
or robotic tape devices.
RF
-Original Message-
Sent: Wednesday,
Hello All,
I have found some of the tables are heavily chained in one of the database .
I want to fix them by exp and imp, but before that I would like to have a
formula or some better method to identify the new PCTFREE and PCTUSED for
each individual table.
Many of you have might have done this
FWIW, our habit has been to install 'everything but the kitchen sink'.
IIRC, you get four options on 'what' to install in 9iAS, and only the 4th
and most complete option is functional from a dev POV. Again, for example,
Portal is available by default in the 4th and most complete install option.
I believe the lock was obtained on the table prior to updating the rows.
So a commit would signal an end to the work and release any objects that
were locked.
Either that or the session has some kind of separation or withdrawal
problem.:)
RWB
--
Please see the official ORACLE-L FAQ:
-- worlds_smallest_perfmon
--
-- Monitors wait time and logs information to database alert logs.
--
-- p_interval = # of minutes to wait between checks
-- p_alert = # of seconds per minute spent in wait that triggers alert
--
-- This code is completely untested, use at your own risk. Run this for
Madhu
Here is a good article that discusses the various aspects:
http://www.dba-oracle.com/art_pctfree.htm
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, January 15, 2003 3:06 PM
To: Multiple recipients of list ORACLE-L
Hello All,
I'm looking for a
way of checking which sessions on the database have trace turned
on.
I can set event in
another session, but I'd like to know whether the event (10046 in this case)
is set in some
sessions.
Mladen
Gogala
Oracle
DBA
Oxford Health
Plans
www.oxhp.com
How about this:
run a 10046 level 4 trace
run the trace file through tkprof so you can
easily pick out a few queries based on number
of executions, consistent gets or whatever.
The level 4 allows you to get valid values
for any bind variables in the WHERE clause
create SQL and/or PL/SQL
The worlds smallest perfmon could be 11 bytes smaller
if you changed 'while true loop' to 'loop'
Jared
Post, Ethan [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
01/15/2003 01:26 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
ha ha, done.
-Original Message-
Sent: Wednesday, January 15, 2003 3:46 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Importance: High
The worlds smallest perfmon could be 11 bytes smaller
if you changed 'while true loop' to 'loop'
Jared
--
Please see the official ORACLE-L FAQ:
it is best if it is contigious memory also
sniff, sniff...
I smell a questionable 'de facto' truth here. :)
Jared
Ron Rogers [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
01/15/2003 12:44 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL
I am starting to plan my RMAN/Legato Networker implementation.
I purchased Robert's book and have read Appendix C 8i Differences... I am
now persuaded to use RMAN 9i. However, the da'management wants RMAN
Legato Oracle module implemented ASAP. I looked at the compatibility
matrix and it
I think the new signal handling in perl 5.8 causes problems here.
The signal is recorded by the *C* signal handler when it happens
but calling the specified *perl* handler is deferred until it's
safe to do so - generally the next perl statement boundary.
If the application is stuck in some OS
Mladen,
Form 2 previous list postings:
-Original Message-
Sent: Wednesday, 30 January 2002 5:51 AM
To: Multiple recipients of list ORACLE-L
Here you go...
1) Find the OSPID for the suspected user (other than pmon, smon and their
famiy)
select s.username, p.spid
from v$session s,
Title: RE: Database tracking
Wow!
Thatdefinitely is one simple and elegant
solution, Raj.
I wonder what you call when the alarm goes off,
"heart-broken"?:)
Arup
- Original Message -
From:
Jamadagni, Rajendra
To: Multiple recipients of list ORACLE-L
Sent:
To expand on your description of define, I thought Erik may be looking
for a way to pass the variables in. Maybe this will be of some use.
sqlplus id/pwd @test 'a b c' 'd e f'
Where test.sql is:
define field1 = '1'
define field2 = '2'
select 'field1', 'field2' from dual;
HTH,
John
[EMAIL
Is it a way to compare two tables to see the differences? We have problem
with our database, someone input new data into database that causes problem,
we want to find and remove those entries from that table.
Thanks,
David
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
As far as I know, when you do 'update', you lock the record as exclusive so
anyone else can't update the record that you're updating. Thus, commit or
rollback need to be done to release the records although your updated is
fail.
-Original Message-
From: John Dunn [SMTP:[EMAIL
Larry,
Thanks for taking the time to explain this. You are really patient when it
comes to writing!
I see now how Oracle does the refresh. I also remember now seeing
a post in
this list some time ago asking about the possibility of tuning a similar
sql. I think it was you :)
It was me. I
Does your database contains only two tables ? and if I may know what's
really problem with your database ?
Rgrds,
Sony
-Original Message-
From: Nguyen, David M [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, January 16, 2003 8:04 AM
To: Multiple recipients of list ORACLE-L
Subject:
Thank you very much for all your respond.
It will be helpful, but Jared, I'm sure that my data ain't contains another
'02' year except 2002.
Rgrds,
Sony
-Original Message-
From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, January 16, 2003 12:14 AM
To: [EMAIL
Hi All
For those sites with either a standby, DR or failover database,
the following information is very important to you. You could be in
breach of Oracle's Licensing agreement and could cost you $100,000s
if not millions $$
(Read the summary at the end if you want to skip the
details)
In the
I have enjoyed reading the books from the authors who participate on this
list. I hope to read books in the future from others on this list that
haven't written a book yet. If you have considered writing an Oracle book,
take a look at this article:
http://www.devx.com/devx/editorial/10491
David - If your tables aren't too large, you can do something like:
delete from table1
where col1 not in (select col1 from table2);
If you have many rows, you may want to use a variation on this to reduce the
transaction size.
Like:
delete from table1
where col1 not in (select col1 from
Like I said previously, did you try this:
select * from test where dt between to_date('01-DEC-02 00:00:00','DD-MON-YY
HH24:MI:SS') and to_date('31-DEC-02 23:59:59','DD-MON-YY HH24:MI:SS');
Richard
-Original Message-
Sent: Wednesday, January 15, 2003 9:34 PM
To: Multiple recipients of
On Wednesday 15 January 2003 18:34, Sony kristanto wrote:
Thank you very much for all your respond.
It will be helpful, but Jared, I'm sure that my data ain't contains another
'02' year except 2002.
Maybe not, but I suggest you look up RR in the fine manual anyway. :)
Jared
Rgrds,
Hi Jared
I have a reply from someone who does not want to be identified.
This is his case.
His company tried reasoning and discussing it with Oracle
and even tried a compromised (which I would not be happy with)
He company put forward to Oracle to pay for the full licence
on the production
Thanks Tony.
Looks like Larry E is trying to boost revenues in a down
economy by any means necessary.
You're right, this doesn't seem right.
Jared
On Wednesday 15 January 2003 19:08, [EMAIL PROTECTED] wrote:
Hi All
For those sites with either a standby, DR or failover database,
the
David
You might want to look at
select *
from t1
minus
select *
from t2
/
t
-Original Message-
Sent: Thursday, 16 January 2003 9:04 am
To: Multiple recipients of list ORACLE-L
Is it a way to compare two tables to see the differences? We have problem
with our database, someone input new
Thanks for the pointer, it was a good script.
Venkat
--
On Wed, 15 Jan 2003 02:09:15
v.schoen wrote:
Thanks,
this is what I'm looking for.
regards
Volker Schoen
E-Mail: mailto:[EMAIL PROTECTED]
http://www.inplan.de
-Ursprüngliche Nachricht-
Von: Reardon, Bruce (CALBBAY)
90 matches
Mail list logo
