Hi List,
I have got a shell script for taking full export backup which I am
attaching with this mail as .sql file as I cannot send .sh file as file
attachments. I am getting the following error when I run the script in
full_exp_20030321_1309_job.log:
Hemant
In case nobody has yet replied I believe your error is that you cannot use a
bind variable in an execute immediate for the db link name as you are trying
to do with this statement:
exec_string := 'select sum(bytes)/1048576 from dba_data_files@:b1';
Changing it to this might work
On Thursday 20 March 2003 18:19, Hamid Alavi wrote:
Dear List,
Hi,
I have a question regarding Table size, I want to put the small size of
tables in a small TBS,But don't know the size of table do I have to
calculate the size for which period, Assume we want to keep the data for 5
years so
Hi,
Looks like one or more environment issues. First, make sure that mknod is in your
path or go ahead and enter the full path to it in the script ( /usr/sbin/mknod on my
local HP system, looks like you might be on Sun, though ).
Next, make sure your export command is all on one line.
Instead
Thanks Tim !
That works !
now I just have to resolve the ORA-02020: too many database links in use error !
Thanks again.
Hemant
--- Tim Onions [EMAIL PROTECTED] wrote:
Hemant
In case nobody has yet replied I believe your error is that you
cannot use a
bind variable in an execute
Hi Ranganath,
I think you must be executing this script from oracle user. It seems you
do not have execute permissions on mknod command. Ask your sysadmin to give
access to that command.
or
run this script from root user by exporting the ORACLE_SID ORACLE_HOME.
rukmini
The mknod command isn't in the path. Either
setup the path in the script or explicitly
specify the path.
I don't think that the ORACLE_HOME is getting set
correctly in your derive_oracle_paths function.
Check the oratab file.
Hemant
--- Krishnaswamy, Ranganath [EMAIL PROTECTED] wrote:
Hi
I've fixed the open links issue as well.
Thanks
tti 'Database Sizes (excluding TEMPFILEs) ' center
spool DB_Sizes
set serveroutput on size 5;
declare
cursor c1 is select db_link from user_db_links;
remote_DB varchar2(128);
db_size number;
exec_string varchar2(255);
begin
Hi all,
is there any way how to start password protected
listener from certain
.bat file.
I am using Windows NT and I don't know how to pass a
password to lsnrctl
-utility when I am doing like this lsnrctl reload
PASSWORD.
All tips are welcome...
Rgds,
Kalle
What would be the overhead of setting audit_trail=db in init.ora
on the overall database performance ?
NOTE - This is a heavily loaded Hybrid Database having 4000 concurrent users
What optional settings may be better ?
Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
Hemant
I faced the same problem when setting up a centralized monitoring utility
(very much similar to what you are trying to accomplish here, I think)
For the Ora-2020 - either increase the distributed_transactions parameter or
use dbms_sql instead of execute immediate where you can explicitly
Currently we are use ORACLE developer 6i and plan to
upgrade to IDS 9i. I
heard ORACLE Forms/reports 9i take out client/server
support and use Web browser. my question are:
1. On installation for user's PC does their has way
ONLY install forms/report run time engine and
OC4J?
2. We don't need
Kalle,
I do something similiar by creating a command file with lsnrctl commands and
then executing them like this:
@rem
@rem First set the log file name via lsnrctl so we can rename it in dos
@rem
@if exist %oracle_home%\network\log\%listenerlog%_temp.log del
Hi list
Is there any way to remove a killed session from v$session. Is it even
necessary to do that ?
I ran this:
select spid, status, osuser, s.program from
v$process p, v$session s where p.addr=s.paddr
To check for the killed processes' spid in order to remove the thread (it's
9.2 on win2k)
I think I need to read more on the MVs. Right now I'm
very unclear how can they add something to a prebuild
table. I thought they could replace an aggregate.
Thanks for the advice, everyone (Paula, Beth, Dennis,
Darrel, hope I didn't miss anyone). I might revive
this thread when I get some more
Hi.
I'm trying to get a feel of the number of the queries
executed at the same time against a database. I have
been using the following script (actually this is a
part of a script)
Select a.type, count(1) col1
from v$session a,
v$process b
where
a.paddr=b.addr
and a.status like 'ACTIVE%'
Title: Good to be back
5 days
without oracle-l, boy that means a lot of time to do the other work
! ;-)
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]On Behalf Of Jamadagni,
RajendraSent: Thursday, March 20, 2003 3:04 PMTo:
Multiple recipients of
You must use the direct path insert in order to use the nologging option.
use sqlldr with direct=y
Stephane
-Original Message-
McClure
Sent: Thursday, March 20, 2003 6:14 PM
To: Multiple recipients of list ORACLE-L
Allright I have a developer that is loading a few gig of historical
Title: Re: lsnrctl -utility, passing password in a command file
a) You don't need the password to start the listener
b) In NT you can start/stop the listener by starting/stopping the service
(net start/stop OraclexxxTNSListener) without using lsnrctl . Of course you
need the authority to
It depends.
I would think that it largely depends on what you intend to audit and how
often those audits occur. For example, on a production system, auditing
failed logins may be OK, but auditing both failed and successful logins may
put some load on the DB.
You may also want to consider moving
Jack,
I happen to know that in 8.1.7 there were some bugs with lost messages
that can cause problems, like sessions waiting on down grades or
upgrades to complete. So the wait should be short (less than a second).
Anjo.
Jack van Zanen wrote:
Hi
This client has Oracle 8.1.7 OPS with 2
Stefan,
The session stays in the KILLED status because of a lot of reasons - the
primary being the rollback being performed for all the changes the session
did. At some point the session rollback will be complete and the session
will be removed. Don't worry about the session being there.
HTH.
Babu,
Closing the cursor doesn't close the connection AFAIK.
You can can use dbms_session.close_database_link
or ALTER SESSION CLOSE DATABASE LINK name
to do that.
Jared
On Friday 21 March 2003 04:28, Babu Nagarajan wrote:
Hemant
I faced the same problem when setting up a centralized
It should disappear eventually, it sometimes takes a while.
Try the immediate clause next time, they don't usually hang around.
alter system kill session 'sid,serial' immediate;
-Original Message-
From: Stefan Jahnke [mailto:[EMAIL PROTECTED]
Sent: Friday, March 21, 2003 8:54 AM
To:
Hi Gene,
The following where clause will work:
a.paddr=b.addr
and a.status = 'ACTIVE'
and a.username is not null
-Original Message-
Sent: Friday, March 21, 2003 09:24
To: Multiple recipients of list ORACLE-L
Hi.
I'm trying to get a feel of the number of the queries
executed at the
The default maximum number of characters per line for UTL_FILE.FOPEN is
1023.
When using repeatedly UTL_FILE.PUTS, it appends characters to the
current output line, Oracle counts the the total number of characters
per line written, and it seems, your program exceeded this maximum.
Either issue
Setting AUDIT_TRAIL=DB just allows you to enable audit. It, by itself, has
no load.
I have AUDIT_TRAIL=DB and AUDIT SESSION with about 100 connections
a minute and haven't noticed any performance impact.
But, then, I haven't set an STATEMENT or OBJECT audit options.
I have a daily SQL job which
This is probably pretty basic, so please keep in mind that Im a developer and Im
trying to pick up more of the DBA side.
I am assuming that the preferred way to create a tablespace in 8i, 9i is as
follows(this is out of OTN docs)
CREATE TABLESPACE lmtbsb DATAFILE
I do not think that you could record the number of concurrent queries.
On the other hand, you can record the number of concurrent transactions.
That is what your query will do.
Another way is to use statspack.
We're running statspack on our 22 production instances at each hour.
We're keeping 2
One question and an answer from a previous message of yours.
1) What OS are you using? There is a pcscfg.cfg file on most operating
systems that I know of where you can set that stuff up. Although anything added
to the gui or command line will override.
2) errtype is for when your
Depends on what you audit. If what you are auditing
occurs with high frequency then naturally there is
some overhead
Connor
--- VIVEK_SHARMA [EMAIL PROTECTED] wrote:
What would be the overhead of setting audit_trail=db
in init.ora
on the overall database performance ?
NOTE - This is a
I presume that the mknod does NOT report an error and actually does
create a pipe device ?
Let's see the other errors
LRM-00112 multiple values not allowed for parameter 'string'
Cause: An attempt was made to specify multiple values for a parameter which
can take only one value.
Action: Do not
Use orakill first, rather than using 'alter session kill', it's more
reliable. You'll have to wait for Oracle to clean up the session.
On occasion the only way to clean it up is bounce the database,
which is why I always use kill -9 or orakill to start with.
Jared
On Friday 21 March 2003
-Original Message-
It depends.
I would think that it largely depends on what you intend to
audit
You may also want to consider moving the AUD$ table out of the SYSTEM
tablespace. If you've got Metalink, see article 1019377.6.
-
PCTINCREASE is not relevant if you are using locally managed
tablespaces.
The DBA did tell you correctly, but he/she meant for dictionary managed
tablespaces, not LMT. In 8i, the SYSTEM tablespace still has to be
dictionary managed, while in 9i the default is now LMT
--- [EMAIL PROTECTED]
Just moments ago I ran the query below got 40 rows returned.
I'm at a loss to explain how an SQL statement can have ZERO parse_calls
and ZERO executions while recording actual work against the DB
select FIRST_LOAD_TIME, DISK_READS , BUFFER_GETS , ROWS_PROCESSED,
sql_text
from v$sql
where
RG
Here is a Web page that explains it pretty well.
http://www.samoratech.com/TopicOfInterest/swLMT.htm
You need to decide whether you want your data file to autoextend. I can't
recall if that is the default or not.
You can't set pctincrease for the tablespace and if you think about
Stephane:
Why do you think if would records the number of
concurrent transactions, but not the queries? what is
the difference from the v$session standpoint/
--- Stephane Paquette
[EMAIL PROTECTED] wrote:
I do not think that you could record the number of
concurrent queries.
On the other
Please exlain the difference between local and global statistics.
Is this statisitics on a partition?
Brad Odland
-Original Message-
Sent: Thursday, March 20, 2003 3:29 PM
To: Multiple recipients of list ORACLE-L
Brad, you're absolutely correct. Explain plan is what is intended, but
prakash,
thanks.
--- BALA,PRAKASH (HP-USA,ex1) [EMAIL PROTECTED]
wrote:
Hi Gene,
The following where clause will work:
a.paddr=b.addr
and a.status = 'ACTIVE'
and a.username is not null
-Original Message-
Sent: Friday, March 21, 2003 09:24
To: Multiple recipients of list
Once you set to uniform extents, pctincrease will default to 0.
Most Oracle gurus advise to just use uniform extents for all situations.
-Original Message-
Sent: Friday, March 21, 2003 10:59
To: Multiple recipients of list ORACLE-L
This is probably pretty basic, so please keep in mind
Try this,
set linesize 132
column logon_time format a18 heading Logon Time
set pagesize 24
set feedback on
select
SADDR,
SID,
SERIAL#,
substr(USERNAME,1,8) USER,
STATUS ,
SCHEMA#,
OSUSER,
PROCESS,
to_char(logon_time, 'DD-MON-YY HH24:MI:SS') LOGON_TIME,
-- substr(action,1,5) action,
All,
I've got an application that does not use bind variables. The code is written
in Microsoft Visual C++. I have no background with C++ and need some help in
telling the developers how to use bind variables in their code (they don't
know and aren't sure how to find out). I pulled the
Yes, local statistics are the partition specific statitics. Global stats are
on the partitioned object as a whole. Global stats are vitally important
when you are accessing more than one partition.
If you are accessing 1 partition and you do not have global stats, then
the optimizer will use the
Hi Kevin,
Long time no talk or see. Hope things are well with
you. Going forward it will nice for us to quantify
any performance differences that we observe,
preferably with data supporting the claim. Don't get
me wrong, I am not trying to beat you up on this, but
trying to bring some clarity to
thanks in case I happen to work on a 7.3 database
what kind of pctincrease should I set? What about the other settings? Just curious.
Ryan
From: BALA,PRAKASH (HP-USA,ex1) [EMAIL PROTECTED]
Date: 2003/03/21 Fri PM 12:54:41 EST
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Anyone have a
"simple" script to scan an alert
log for errors and
email a report if found?
Customer wants
something to run "often", but only
email when an error
is found. I've got something
that does a bit of
that and runs twice a day, but
always sends
out.
Thanks.
Maks.
Title: RE: Explain Plan vs Actual Execution Plan
I'm open to abuse if I'm wrong, but I think that the intended distinction was:
let's assume table T with partitions P1 and P2
analyze table t compute statistics ; --- global statistics
analyze table t partition (p1) compute statistics ; ---
Yes, that is specific for a partitioned table.
Using dbms_stats.gather_table_stats, there is a parameter called granularity with
which you can specify to gather stats for your table at different levels:
DEFAULT: Gather global- and partition-level statistics.
SUBPARTITION: Gather
Title: RE: RE: Locally Managed Tablespaces
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
thanks in case I happen to work on a 7.3 database
what kind of pctincrease should I set? What about the other
settings? Just curious.
The current thinking
Gaja,
You're correct. I should have quantified what I meant by significant. As
well as given more detail on what I was doing. That said, here is what I
remember of what I was doing
Specifically, At the request of management, I was testing the performance
and extent allocation of locally
pctincrease=0 and set the storage parameters at the tablespace level
and do NOT put storage parameters on the individual objects.
you can fake the workings (without the bitmap!) of an LMT by doing
that. Next extent=initial extent, pctincrease=0 will effectively
allocate extents of equal sizes
Here's a scenario -
Connections are load balanced (server-side) over both the instances of the
RAC. Temp Space is 10GB.
User A (non-expert) connects to instance 2 from a GUI tool and clicks on
the data-tab of a 50-million row table and chooses to order by some huge
column. This results in temp
Karen,
I'm far from a pro at C++ also, haven't found a good reason to use it anyway
as anything you want to do in C++ is also just as easily (if not more so) in
plain old C. I characterize C++ as a language for old Cobol programers who are
sorry the verboseness of that language is dying.
Here's what I use. Pretty basic (crappy) but may be a starting point for
you. You'll have to go through and change where you alert files are stored
and to whom to send the email. I schedule them to run about every 15
minutes. blat is a mail utility you can downloaded from the internet.
v$session.status indicates that a sql statement is going on.
I do not know any indicator on query.
But you do have indicators on transactions.
The total transactions are the sum of user commits and user rollbacks.
Check those 2 statistics.
Stephane
-Original Message-
Sent: Friday,
We got around it by dropping the natural and failover nodes from OEM,
modifying the oratab files to contain only the databases active the
node, bounce the dbsnmp agents, rediscover the nodes and change the
oratab files back to their original. The trick is in the discovery of
the services on that
Check Tim Gorman's web site, http://www.evdbt.com/tools.htm. He has a script
(chk_oerr.sh) to monitor alert.log for new ORA- errors and e-mail.
You can see how it is done and come up with your own version for your own
requirements.
- Kirti
-Original Message-
Sent: Friday, March 21,
In 8.1.7, is rebuilding the appropriate way to move
an index to a different tablespace?
Thanks,
Peter Schauss
Northrop Grumman Corporation
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Schauss, Peter
INET: [EMAIL PROTECTED]
Fat City Network Services--
Mogens wrote
There are many things I don't get in this life.
I doubt it very much...
One of them is the
statements about disk storage being an admin nightmare and way too
expensive. Aren't disks very cheap these days?!
Other than ignorance I see no way in which storage admin can be a
Lyndon writes
It doesn't mean they can think through a production
database
crash recovery, deal with users, etc.
No amount of training, exam taking or even experience can prepare you
with any of this.
Both training *and* experience can help with this. The latter more than
the former.
You can try a korn shell script called chkdberr.ksh at
http://www.appsdba.com/scripts/chkdberr.ksh, it's already got the email
built in.
At 10:39 AM 3/21/2003 -0800, you wrote:
Anyone have a simple script to scan an alert
log for errors and email a report if found?
Customer wants something to
Jonathan wrote
I'd test what happens if the algorithm says 'I need a 64m extent'
and
there is (say) 24m free space left. IIRC you do get an 'unable to
allocate extent blah' error but I can't swear to it, and it wouldn't
at
all surprise me if the behaviour changed in a later release.
I'm trying to setup the sendmail feature ldap_routing using
Oracle Internet Directory (OID)for the LDAP server.
There is a matching rule in the sendmail schema called
caseExactIA5SubstringsMatch not doesn't exist in OID.
Has anyone found a way to resolve this problem?
I opened a tar with Oracle
This is a test, I have not received any messages in the last couple of days.
-Scott
**
Scott Stefick
Oracle Certified DBA
Wm. Rainey Harper College
847.925.6130
**
--
Please see the official ORACLE-L FAQ:
Here a fast little script I wrote that can
be ran every minute
Emails only when it finds a new message
And pages if it finds a new message
between 6pm and 5am
FPATH=/prod/dba/scr
autoload f_xmail
logfile=/prod/dba/logs/`cat
/prod/dba/sys_data/newpath`/misc_logs/chk_alerts.log
There is an easier way to handle character strings in PRO*C:
EXEC SQL BEGIN DECLARE SECTION;
char stime[12];
EXEC SQL VAR stime is STRING(12);
EXEC SQL END DECLARE SECTION;
Now you can handle stime as a standard C, null terminated character
string.
HTH,
Peter Schauss
Northrop
Yes, you can use online and parallel both to make it fairly transparent
and reasonably fast.
For a 6 CPU server I generally use
Alter index my_index rebuild
Tablespace new_ts
Online
Parallel 4
Allan
-Original Message-
Sent: Friday, March 21, 2003 2:09 PM
To: Multiple recipients of list
Title: Re: Moving an index
Yes
At 12:08 PM 3/21/2003 -0800, you wrote:
In 8.1.7, is rebuilding the appropriate way to move
an index to a different tablespace?
Thanks,
Peter Schauss
Northrop Grumman Corporation
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
Andy:
This is a very cool script.
Thanks very much!!
Barb
--- Andy Rivenes [EMAIL PROTECTED] wrote:
You can try a korn shell script called chkdberr.ksh
at
http://www.appsdba.com/scripts/chkdberr.ksh, it's
already got the email
built in.
At 10:39 AM 3/21/2003 -0800, you wrote:
Anyone
So Oracle doesn't support it (go figure), what about adding the schema
yourself? That can't void Oracle Support can it?
I think you're looking for RFC2307. I can't find the doc at Sun, but there
is a PDF at
http://www.netsys.com/library/papers/sun_ldap_setup.pdf
You may need to rework the
Peter,
Other than dropping it. Rebuilding also has the advantage of not requiring
down time.
Dick Goulet
Reply Separator
Author: Schauss; Peter [EMAIL PROTECTED]
Date: 3/21/2003 12:08 PM
In 8.1.7, is rebuilding the appropriate way to move
an
Well now, It's said you learn something new every day. Thanks Peter!!
Dick Goulet
Reply Separator
Author: Schauss; Peter [EMAIL PROTECTED]
Date: 3/21/2003 1:49 PM
There is an easier way to handle character strings in PRO*C:
EXEC SQL BEGIN DECLARE
Rich,
I am trying to import the sendmail.schema file from
sendmail.org. The problem is that it contains a matching rule called
caseIgnoreIA5SubstringsMatch which OID doesn't support. There is a
caseIgnoreSubstringsMatch rule. I don't know if the IA5 part is important
to sendmail.
FWIW I've come to think of autoextend as a valuable ally in certain cases.
When loading data it's nice to enable autoextend when you don't know
how large you really need to have the database files. Create several
and set autoextend on, being sure that if all were to fill up, it won't
fill up
For 7.3 it is also important to set MINIMUM EXTENT
for the tablespace to match the initial and next - then
every extent has to be at worst a multiple of the minimum
extent size whatever a rogue user does.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
For one-day tutorials:
(see
Title: RE: RE: Locally Managed Tablespaces
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
I think my 'reasonable' # of extents must be higher than
yours Jacques. :)
I agree that having a datafile autoextend is better than having a job die. On the other
so for normal business you should not use autoextend? You should monitor it
yourself? What are some tips for monitoring the database to see if you need
to extend your tablespace manually? Do you use DBMS_ALERT and read the v$
views and then broadcast a message if you need to extend a tablespace?
Title: RE: RE: Locally Managed Tablespaces
I think minimum extent was a new parameter in 8.0.
-Original Message-
From: Jonathan Lewis [mailto:[EMAIL PROTECTED]]
For 7.3 it is also important to set MINIMUM EXTENT
for the tablespace to match the initial and next - then
every
I knew I'd forget something :) it's been a while since I worked on
7.3
thanks!
--- Jonathan Lewis [EMAIL PROTECTED] wrote:
For 7.3 it is also important to set MINIMUM EXTENT
for the tablespace to match the initial and next - then
every extent has to be at worst a multiple of the minimum
Title: RE: RE: Locally Managed Tablespaces
Well, my first suggestion would be to buy a software package from a reputable software company that lets you predict object growth and an estimate of when your tablespace will be full. Contact me for more details. :)
But seriously, you can write a
81 matches
Mail list logo
