Oracle white papers

[John.Hallas]

Useful white papers and articles on  www.nyoug.org under presentation link.

John
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Snapshot Too Old Error on Export !!!

[John.Hallas]

Jackson,
As you have discovered , the issue is that other transactions are
overwriting your read consistent view of the tables.
Options could include
1) Running the export at a quite time (sounds like you have tried that but
with only partial suucess)
2) Taking a direct export which reduces the time very considerably
(especially if you do it at weekend)
3) Take subsets of the data using either a parameter file with export with a
list of tables you are interested in and multiple exports (different
parameter files of course)
4) Using the query option of export to again take subsets of data from the
biggest tables

Using options 3+ 4 means you need to put in some sort of means of capturing
changes ( a IUD trigger to capture rowid's) but it will be very difficult to
ensure integrity.

Your last sentence is very worrying, I hope you are not using export as a
means of backing up the database because from what you have said that is
totally unreliable.
Out of the above options only 1) will give you any sort of consistent view
of the tables.

John

 
-Original Message-
Sent: 21 February 2003 10:00
To: Multiple recipients of list ORACLE-L


Hi all

I have a problem when doing an export in one of ourt production
databases. The export fails with ORA-01555, snapshot too old error.

I have increased the number of rollback segments and their sizes on
the database. Also I have went to an extent of specifying the
parameter constent=n on my script but backups fails. The worst part is
this export runs for a long time and then fails, more than 24 hours.

The only time that this export succeed is over the weekend, because
most of the time few people are working or not at all. Now I have
tried to start it after hours but as I said it still takes long and
end up failing the next day.

Could somebody help me here, this is very critical to be running
production without proper backups .!

Thanx

___
 http://www.webmail.co.za the South-African free email service

  NetWiseGurus.Com Portal - Your Own Internet Business Today!

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jackson Dumas
  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: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Breaking down values in a large table

[John.Hallas]

Thanks Waleed, that is exactly what I wanted.
And thanks to all the others who responded

John

-Original Message-
Sent: 11 February 2003 22:54
To: Multiple recipients of list ORACLE-L


I hope this helps:

--
drop table test_bal ;
--
-- create a sample table
--
create table test_bal( my_pk number);
--
-- Insert sample data
--
begin
for i in 100..21000 loop
  insert into test_bal values (i);
end loop;
end;

--
select  b.rows_cnt as table_rows_cnt, 
c.mrownum  as acc_rows_cnt, 
c.my_pkas bucket_end_inclusive, 
ceil(c.mrownum * 4/ b.rows_cnt) as bucket_id 
 from (select a.*,rownum mrownum 
 from (select my_pk 
from test_bal 
order by 1) a) c, 
  (select count(*) rows_cnt from test_bal) b
where ceil((c.mrownum + 1)* 4/ b.rows_cnt) > ceil( c.mrownum * 4/
b.rows_cnt);

--

Regards,

Waleed


-Original Message-
Sent: Tuesday, February 11, 2003 9:19 AM
To: Multiple recipients of list ORACLE-L


Listers,
I have a table of 125M rows (not partitioned) which I am exporting. I want
to break the export into 4 dmp files using the query command on the pk
column.
 
I am looking at how the best way of finding the values of the PK (number)
which are at 25%, 50% and 75% ish for the table so that I can get 4 evenly
sized exports
 
My query line in the parameter file will be along the lines of where 
1)   log_no < xx
2)   log_no >= xx and < yy
3)   log_no >= yy and < zz
4)   log_no >= zz
 
I am thinking of a sql something like the following
 
Select /*+  index ffs(table_name index_name) */
Log_no , floor(log_no / 4), count(*)
>From table_name group by floor(log_no / 4), log_no
 
Version is 8.1.7.1
 
Can anybody help please
 
Thanks
 
John
 
 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  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: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Skipping a table on import

[John.Hallas]

Jack,
I always knew there was a limit on the size of a parfile which I had assumed
was a maximum no of tables allowed. However I recently found out that it is
a byte limit on the overall file parameter. (It was 8K in 7.x )
Do you not hit this limit with several thousand table names (presumably not,
otherwise you would not do it ) ??

John


-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: 07 February 2003 13:04
To: Multiple recipients of list ORACLE-L



Craig,

I had to smile when you stated you had 400 tables and were reluctant to
list them in the export or import parfile.  Our 3rd party Student
Information system has over 47,000 tables.  I frequently do exports and/or
imports with parfiles listing several thousand tables.  It's not a problem
at all.

Spool the following to a text file and incorporate it (with minor editing
to add the parens and remove the first comma) into your parfile...
Select ',' || Table_Name
>From   User_Tables
Where Table_Name <> ''
;

Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]



 

  "Craig Healey"

  <[EMAIL PROTECTED]To:   Multiple recipients
of list ORACLE-L  
  om>   <[EMAIL PROTECTED]>

  Sent by: cc:

  [EMAIL PROTECTED] Subject:  Skipping a table on
import
 

 

  02/07/2003 04:04

  AM

  Please respond to

  ORACLE-L

 

 





Hi,
I'm trying to import the live schema into a test schema, but 1 or 2
tables have a lot of data and it's taking hours. Is there a way to skip
certain tables during an import?
We're using 8.1.7
We have 400 tables, so I'd like to avoid having to list all the tables
in the export statement.
I have to pre-create the tables to put them in the correct tablespace,
so could I alter the table definitions to force import to skip
particular ones?
Any help is appreciated.

Craig Healey
--
Author: Craig Healey
  INET: [EMAIL PROTECTED]





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: RMAN question

[John.Hallas]

Helmut,
This thread came up a couple of days ago 
The answers were to use the commands
change datafilecopy delete  or change backuppiece delete 
 
Also have a look at the script $ORACLE_HOME/rdbms/demo/rman1.sh to automate
the process from the output of report obsolete
 
HTH 
 
John

-Original Message-
Sent: 07 February 2003 11:39
To: Multiple recipients of list ORACLE-L



Hi! 

How do I make RMAN delete all obsolete backups? We are using a recovery
catalog. 

Example: 

RMAN> report obsolete; 

RMAN-03022: compiling command: report 
Report of obsolete backups and copies 
Type KeyCompletion TimeFilename/Handle 
 -- --  
Backup Set   523573 29012003-16:19:33 
Backup Piece 523575 29012003-16:19:33  03ee44q5_1_1 
Backup Set   525544 31012003-03:47:24 
Backup Piece 525546 31012003-03:47:24  06ee80te_1_1 
Backup Set   525545 31012003-08:04:25 
Backup Piece 525547 31012003-08:04:25  07ee8d2m_1_1 
Backup Set   526282 01022003-03:34:14 
Backup Piece 526284 01022003-03:34:14  0aeealdg_1_1 
Backup Set   526283 01022003-07:55:02 
Backup Piece 526285 01022003-07:55:02  0beeb0m0_1_1 
Backup Set   527884 04022003-03:52:28 
Backup Piece 527886 04022003-03:52:28  0eeeiiig_1_1 
Backup Set   527885 04022003-08:12:52 
Backup Piece 527887 04022003-08:12:52  0feeius4_1_1 
Backup Set   528851 05022003-03:51:41 
Backup Piece 528853 05022003-03:51:41  0ieel6sk_1_1 
Backup Set   528852 05022003-08:13:00 
Backup Piece 528854 05022003-08:13:00  0jeelj6h_1_1 
Backup Set   529486 06022003-03:43:12 
Backup Piece 529488 06022003-03:43:12  0meenrds_1_1 
Backup Set   529487 06022003-08:00:57 
Backup Piece 529489 06022003-08:00:57  0neeo72i_1_1 

Now I want to delete those obsolete backups... 

RMAN>  delete obsolete; 

RMAN-00571: === 
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS === 
RMAN-00571: === 
RMAN-00558: error encountered while parsing input commands 
RMAN-01005: syntax error: found "obsolete": expecting one of: "expired,
script" 
RMAN-01007: at line 1 column 9 file: standard input 

What would be the correct syntax? 

This is 8.1.7 on solaris. 

Thanks, 
Helmut 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Skipping a table on import

[John.Hallas]

Craig,
Assuming you don't need the tables that you don't want  to import the best
option is to not export the tables by using a parameter file. 
(This speeds up the export, reduces the size of the dmp file, saves the
backup/ftp time to copy the file to the target system and saves reading
through the dmp file until it passes data it is not interested in)
Next best bet is to use a parameter file on the import.

A solution for you could be to create the 'not wanted' tables (just create
but no data in them) on the target database and put an ignore = N line in
the import parameter file / interactive command
The import would then go through the import file creating and populating all
the tables  until he gets to the ones you don't want and then it will throw
an error message saying 'table already exists' and move onto the next table
in the import.

HTH 

John



-Original Message-
Sent: 07 February 2003 10:04
To: Multiple recipients of list ORACLE-L


Hi,
I'm trying to import the live schema into a test schema, but 1 or 2
tables have a lot of data and it's taking hours. Is there a way to skip
certain tables during an import?
We're using 8.1.7
We have 400 tables, so I'd like to avoid having to list all the tables
in the export statement.
I have to pre-create the tables to put them in the correct tablespace,
so could I alter the table definitions to force import to skip
particular ones?
Any help is appreciated.
 
Craig Healey



**

This email and any files transmitted with it are confidential and intended
solely
for the use of the individual or entity to whom they are addressed and may
contain
confidential and/or privileged material.  Any review, retransmission,
dissemination
or other use of, or taking of any action in reliance upon, this information
by 
persons or entities other than the intended recipient is prohibited.
Statements
and opinions expressed in this e-mail may not represent those of the
company.
  
If you have received this email in error please notify
[EMAIL PROTECTED] 
 
This footnote also confirms that this email message has been swept by
MIMEsweeper
for the presence of computer viruses (www.mimesweeper.com)


***

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Craig Healey
  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: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Question on "set autotrace on statistics"

[John.Hallas]

SET AUTOTRACE only reports on DML (select, update,insert , delete)
statements.
The commands you ran were DDL commands 
If you notice at the end you did a count of the plan_table and that reported
statistics

John

-Original Message-
Sent: 05 February 2003 15:59
To: Multiple recipients of list ORACLE-L


Hi:

I am trying to understand why sqlplus did not display stats when I run
"analyze table ..." and "create index ..."?

TIA.

Guang

--

SQL> set autotrace on statistics
SQL> analyze table gene_upr compute statistics;

Table analyzed.

SQL> desc gene_upr;
 Name  Null?Type
 -  

 ID NUMBER
 NAME   VARCHAR2(128)
 NAME_UPR   VARCHAR2(128)
 SPECIESID  NUMBER

SQL> CREATE index gene_upr_id_index
  2  ON gene_upr(id)
  3  TABLESPACE INDEXES  nologging
  4  STORAGE (INITIAL 1500 NEXT 1500 pctincrease 0);

Index created.

SQL> select count(*) from plan_table;

  COUNT(*)
--
 0


Statistics
--
  0  recursive calls
  4  db block gets
  1  consistent gets
  0  physical reads
  0  redo size
187  bytes sent via SQL*Net to client
311  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: gmei
  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: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: RMAN - delete obsolete?

[John.Hallas]

Alex,
Oracle produce a shell script to do exactly this. 
It is called  $ORACLE_HOME/rdbms/demo/rman1.sh 

HTH 


John

-Original Message-
Sent: 05 February 2003 11:49
To: Multiple recipients of list ORACLE-L


Hello List,

I think my question is very simple...

How to execute RMAN command DELETE OBSOLETE from script without confirmation
question to remove obsolete backups
automatically?

Many thanks for your help.
-- 
Best regards,
Alex  mailto:[EMAIL PROTECTED]

__
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Alex Andriyashchenko
  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: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Cron not working

[John.Hallas]

Valdimir,
Is the cron daemon running ? (ps -ef|grep for cron and you should see a line
something like /usr/sbin/cron running as root.)
I don't know about the output of crontab -l on Linux but what  you have
printed is nothing like I have seen on a standard flavour of unix. 
A sample crontab entry might look as follows
00 06 * * * $HOME/scripts/arch.sh> $HOME/logbook/arch.log 2>&1

This will run a shell script at 06:00 every day

John

-Original Message-
Sent: 04 February 2003 10:59
To: Multiple recipients of list ORACLE-L


Hello to everyone...

Red Hat 6.2

How do I find out what is wrong with cron? From January 15th until today
cron is not working... My (oracle's) crontab file HAS not changed...

So I went to the /var/log/cron file. Last activity was on January 15th.
Since then no activity has been recorderd.

If I issue "crontab " and afterwards "crontab -l", there
are lines

oracle (02/04-11:37:14-7686) REPLACE (oracle)
oracle (02/04-11:37:17-7690) LIST (oracle)

My job was scheduled on 11:40, however job wasn't executed.

How do I see whether cron is running or not?

Thanks

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Vladimir Barac
  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: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: OT: Cron not working

[John.Hallas]

Vladimir,
I have seen 2 replies to questions today that were less than tactful, yours
and one regarding awk & ksh
If you post a message asking for help and someone tries to help then it is
bad manners to be critical of that help.

I am replying to this because I also replied to your post with some basic
info on the use of cron, the same as Robert did.
If we both read your mail and thought you had minimal knowledge of cron then
perhaps your post was at fault , not those who offer help.

Glad to hear you have sorted the problem anyway

John


-Original Message-
Sent: 04 February 2003 13:19
To: Multiple recipients of list ORACLE-L


Oh, really? I'm not beginner...

Anyway, thanks... Solution was weird...

When I deleted /var/log/cron file, then touched /var/log/cron and then
started cron - everything went
back to normal.

Why the f&(#$^%* it wasn't working, I don't understabd... /var/log/cron was
small (~30k), "df -k /var/log" gave me about 1 Gig of free space... Yet,
cron was not working...


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, February 04, 2003 13:23


> $ps xea | grep crond
>
> Will show you if the cron process is running or not
>
> -rje
>
>
> VB> Hello to everyone...
>
> VB> Red Hat 6.2
>
> VB> How do I find out what is wrong with cron? From January 15th until
today
> VB> cron is not working... My (oracle's) crontab file HAS not changed...
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Moving tables from one tablespace to another tablespace

[John.Hallas]

As I have found to my cost when you have a database with long columns forget
about 90% of Oracle newer functionality.
Sqlplus copy is the best workaround (other than exp/imp)


Here is a example script

set long 4000
set array 5000
copy from user/password@ to user/password@ - 
insert complex_20m_nopk using select * from complex_nopk;
commit;

HTH

John



-Original Message-
Sent: 17 January 2003 07:34
To: Multiple recipients of list ORACLE-L


Hi

I need to move some tables from one tablespace to another. I started doing
it using 

alter table tablename move tablespace new_tablespace;

this is working fine!!. My problem is it's not working with the table which
are having long columns.

I know work arround for this  exp.. drop and create .. then imp.

There is any other way round for this..

thanks in advance

Banarasi Babu
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: BanarasiBabu Tippa
  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: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: HELP..URGENT....NOT

[John.Hallas]

Dave,
Is this the one that you were looking for. I don't recall where I picked it
up from but it was probably this list

http://www.orakle.com/

John

-Original Message-
Sent: 16 January 2003 10:29
To: Multiple recipients of list ORACLE-L


A while back I posted a link to a website that has hundreds of Oracle links.
It's a site that is good for Unix and Windoze bigots, pick your flavor.
Well, I lost my hard drive and my internet favorites.  If anyone has that
link that I posted I would greatly appreciate it if you can now post it cuz
I cannot remember the name of it.

Thanks

Dave
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Farnsworth, Dave
  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: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Automatic backup on Oracle 9i -- For Jared

[John.Hallas]

I have never known what lol stood for , I appreciated  the general meaning
by looking at the context it was used in but 
know I actually know.
I knew that if I perservered long enough with this list long enough I would
find something of interest (lol)

John

-Original Message-
Sent: 02 January 2003 15:30
To: Multiple recipients of list ORACLE-L


I have philosophical trouble with it. I dislike the abbreviations. I
will use abbreviations to condense phrases (lol for lots of laughs) but
I really dislike seeing "you" written as "u". It's not that hard to
type the extra two letters.

--- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote:
> As far as we know there is no bandwidth limit on this list and
> problems can
> be understood better if you state them in a lucid and clear language.
> I had
> to really read twice (sorry haven't had my "Great One" yet) to
> understand
> the abbreviations.
> 
> Am I the only one or is there anyone else who has trouble with such
> language?
> TIA
> Raj
> __
> Rajendra JamadagniMIS, 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 opinion is an art!
> 
> 
> -Original Message-
> Sent: Tuesday, December 31, 2002 12:34 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Jared what exactly U use Veritas Netbackup fr in Ur backup strategy.
> I use
> rman to take backup on disk. Please describe the role of veritas NB
> in
> detail and
> if u can send me example script to perform what tasks, that would be
> gr8.
> OraCop
> >
*This
> e-mail message is confidential, intended only for the named
> recipient(s) above and may contain information that is privileged,
> attorney work product or exempt from disclosure under applicable law.
> If you have received this message in error, or are not the named
> recipient(s), please immediately notify corporate MIS at (860)
> 766-2000 and delete this e-mail message from your computer, Thank
>
you.*1
> 


__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Indentifying Redundant Indexes

[John.Hallas]

Cary,
I assume that using stored outlines will achieve No 2 in your list. Would
that not be an easier approach?
If you altered the system to have CREATE_STORED_OUTLINES=true and ran for  a
period when  all scripts are likely to be run, say a month so that all
month-end processing was completed, and then check the
OUTLN.OL$HINTS.HINT_TEXT column with a query such as the following.

  1  select hint_text from outln.ol$hints
  2* where substr(hint_text,1,5) = 'INDEX'
SQL> /

HINT_TEXT


INDEX(SIMPLE_PK_5M SIMPLE_PK)

HTH

John

-Original Message-
Sent: 23 December 2002 05:59
To: Multiple recipients of list ORACLE-L


Mark,

The way we do it is by what you might call extreme brute force:

1. Capture all SQL from the applications (not just the stuff you've run
in the past month, but the stuff you'll run in the future too, like
period-end close processes, and so on).

2. Generate execution plans in production for all this SQL. Store the
plans.

3. In a "structural replica" system (that is, a full-schema test
instance; you don't need real application data, but you do need schema
and db statistics imported from the production system), drop the index.

4. Generate execution plans on the replica system for all the SQL. Store
the plans.

5. Compare the two sets of plans from steps 2 and 4.

6. Decide whether the different in 4 that are different from the plans
in 2 are better or worse than the plans in 2.

Like I said, it's a "big hammer" method, but it has its reliability
advantages, and the only step that we haven't automated is #6. (I'm
assuming that you already have a valid test system as described in #3.)
The tool we use that does steps 2, 4, and 5 is called Project Laredo
(www.hotsos.com/products/laredo).


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic 101, Jan 7-9 Knoxville
- Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen
- 2003 Hotsos Symposium, Feb 9-12 Dallas


-Original Message-
Richard
Sent: Sunday, December 22, 2002 10:54 PM
To: Multiple recipients of list ORACLE-L

Dear List,

Firstly - Merry Christmas to those who have not already departed for the
holiday season.

I'm currently doing some investigation based around indexes and would
like
everyone's opinion:  What is everyone's preferred approach to identify
redundant (as in "never used by Oracle") indexes?  I believe Oracle 9
might
have a feature to set a flag on objects and then check back later to see
if
they have been accessed however we're still stuck on 8.1.7.4 (Solaris).
Some of my thoughts include:

* Can query for physical disk i/o at a tablespace level easily, however
more difficult to go to an object level.

* Could create a trace file and then inspect explain plans for existence
of
index accesses, however trace file probably not practical to capture for
a
long period of time.

* Ideal statistic would be something along the lines of "index x used y
times in last 24 hours", however a simple "index x was used in the last
24
hours" would be ok.

Obviously we are searching for indexes to remove and identifying those
which aren't queried over a set period of time would be good candidates
for
a starting point.  Any advice you might have would be greatly
appreciated.

Regards,
 Mark.

<<>>

   Privileged/Confidential information may be contained in this message.
  If you are not the addressee indicated in this message
   (or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the
sender
   by reply e-mail or by telephone on (61 3) 9612-6999.
   Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
  that do not relate to the official business of
 Transurban City Link Ltd
 shall be understood as neither given nor endorsed by it.
<<<>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mark Richard
  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 

RE: Footprint of 9i

[John.Hallas]

Nearly 4Gb on Solaris 2.8. Pretty much a full selection installed

John

-Original Message-
Sent: 19 December 2002 15:00
To: Multiple recipients of list ORACLE-L



On Tru64 5.1

/oracle/app/oracle/product> du -sk 9.2.0
4601900 9.2.0

There are multiple DBA's here that have a hand in this, so I don't know how
much fiddling with the Apache stuff has been done (probably not much).  At
this time, there is only one 9.2 database on the box, and it is
experimental, so I don't think there are a bunch of network logs that have
piled up.  I think this installation "everything including the kitchen
sink", so I would think this size would be near the upper limit.

> -Original Message-
> From: Bob Metelsky [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, December 19, 2002 7:49 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Footprint of 9i
> 
> 
> All
> 
> I'm about to consider rolling out 9i here and I'm wondering 
> what type of
> footprint most people are seeing. In other words how large of 
> an install
> is a database administrators (enterprise edition)?
> 
> I've installed 9i at home on WINNT server, Win XP Pro and Suse Linux 8
> all were about 2G. I also noticed that even if I tried to remove some
> components I did not think I needed that the installation 
> size/footprint
> was not really affected
> 
> Also a worthwhile note is that my current (enterprise edition) install
> of 8 17 on win2k server is about 1G 
> 
> This issue is we all run the same size drives here and really cant
> afford to loose 2G 1G maybe 
> 
> So is it true that overall installing the "Enterprise" edition of 9i
> that one could not expect to be much below 2G??
> 
> If not, we may have to add a drive for the dba's which is not going to
> go over big.
> 
> thanks
> Bob
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Bob Metelsky
>   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: Stephen Lee
  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: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: career questions

[John.Hallas]

Good question Maria and something a lot of us on the list either do, have
done or have considered.
I have no intention of questioning your abilities but being the sole DBA on
a site it is quite easy to be seen as a good dba because the users and your
management have nothing to compare against.
It is also most likely that you will have a set way of doing things and do
not try different methods or ideas that often.
I know I have been there and done that.

I had a very good job in a large company where I was the main dba and was
seen as good at doing my job. However I had the same concerns as yourself
and felt that I had a lot more to learn. I left and started working for
myself but the first place I went to was a large site and they had 30 DBAs
working just on specific project. That was a real learning curve for me. It
was an immediate chance to compare myself against a whole variety of DBAs
and realise where my strengths and weaknesses were and what I had to compare
myself against. It was quickly obvious to me that there were 3 groups of
DBAs, 
1) Those who were adequate, wanted to make money and did not bother
themselves with niceties like doing a good job
2) Those who did  good job , wanted to learn more but were not of the
highest calibre
3) A small number who were exceptionally good

Well I think I fitted into group 2, maybe below the half way mark in order
of ranking. But what I had, and you sound the same from your post, was the
desire to learn more. I will never get to be in group 3 but I certainly feel
I have moved up the ranking list of group 2 by taking in all the experience
I have gained by working with other DBAs and moving around between sites. 

I think what I am trying to say  is that perhaps a better move would be to a
larger company with a number of DBAs who you could work with and get a
better understanding of your weaknesses and strengths

I do not know about the job market in your area but in the UK and probably
the USA now is not a great time for stepping out on your own, especially if
you do not have a track record of doing so.

Perhaps another option would be to work for a consultancy where you could
gain the experience of working on a number of sites and still retain a
stable job

Hope all that helps

John


-Original Message-
Sent: 13 December 2002 05:24
To: Multiple recipients of list ORACLE-L


Gurus,

I have been an Oracle DBA for almost 5 years and would like very
much to venture into independent Oracle consulting (part-time). As i
have spent most of these 5 years as a sole DBA for different companies,
studying and learning the ropes of Oracle Administration through books,
magazines, test machines and this list, I feel confident enough to
handle most Oracle issues. At the same time, I still have that feeling
of inadequacy. I am not sure if I am good enough to offer my services to
local companies.
That said, I'd like to ask every one who are into consulting (on
their own or as a part-time gig) what are the technical and soft skills
necessary before I even think about consulting. How would you know that
you're "technically" ready for it. And how do I get started. Tips are
welcome too!

Thanks everyone.

=)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Maria Aurora VT de la Vega
  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.com
-- 
Author: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: sqllldr, long datatype and carriage returns - an ugly combin

[John.Hallas]

Thanks for that Peter and Yechiel, whilst both posts were useful I don't
think they really answered my questions.
I know I could replace the CR characters but that involves a vast amount of
pre-processing (these are very large tables and there are hundreds of them
and they are in constant use). It may work on a varchar2 field but I am not
sure about long (easy enough to test I know). I know there is also an issue
with PL/SQL having a 32K limit on an variable so I could not assign it
within a pl/sql loop.

There is also an issue of data integrity, this is part of a database
migration and we need to do it in the fastest manner possible (30 minute
downtime would be nice). The combination of moving o/s, Oracle versions and
having a lot of longs reduces our options quite significantly.
We have looked at a 3rd party ETL tool which does what we want and moves the
data very very quickly (it does not write to disk and therefore does not
need to perform any file i/o processing). However it stops dead when it hits
a CR (it uses sqlldr as it's main means of loading data). 
Changing data would not endear us to the users and gain any confidence in
the process

Thanks anyway

John

-Original Message-
Sent: 12 December 2002 10:40
To: Multiple recipients of list ORACLE-L
combinatio


You can use
select translate(field1||chr(126)||field2||chr(126),chr(015),' ')
in the program that creates the sequential file.
this will replace every chr(015) with blank.

Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Thursday, December 12, 2002 11:23 AM


> Listers,
> I have got a number of tables with long columns in them. The text is
> free-format and contains carriage returns. Some long columns may be empty
> and others will have varying lengths of data (typical call-centre operator
> input scenario) there is no  end of record marker that is common between
> columns.
> I need to extract each record and load it via sqlldr into another system.
I
> could extract using field1||chr(126)||field2||chr(126) etc 126 being the ~
> character but that does not get around the chr(015) CR issue.
>
> I understand sqlldr has issues with CR and I have tried various ways of
> resolving this problem. I cannot pre-process the records and remove the CR
> character because of the volume of data and the lack of time involved.
>
> I have looked at the stream record format option in my control  file and I
> fail to get this tro work as it seems to hang the session every time I
call
> it. I also suspect that it defines a common end of record marker and as I
do
> not have one I would have to massage the data which is not a route I can
go
> down. PS This is Tru64 and 8.1.7 but I think it is a general issue and not
> specific to any version of Oracle or o/s
>
> Furthermore it appears that using stream record format parameter reduces
> performance which is a key requirement for us.
>
> The questions are
>
> 1) How have other people managed with carriage returns in long columns
> (I suspect a CR in a varchar2 field will be a problem as well)?
> 2) Is the stream record format the way I want to go (I suspect not)
> 3) Has anybody any better ideas
>
> TIA
>
> John
>
>
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: <[EMAIL PROTECTED]
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Yechiel Adar
  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.com
-- 
Author: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTE

sqllldr, long datatype and carriage returns - an ugly combinatio

[John.Hallas]

Listers,
I have got a number of tables with long columns in them. The text is
free-format and contains carriage returns. Some long columns may be empty
and others will have varying lengths of data (typical call-centre operator
input scenario) there is no  end of record marker that is common between
columns.
I need to extract each record and load it via sqlldr into another system. I
could extract using field1||chr(126)||field2||chr(126) etc 126 being the ~
character but that does not get around the chr(015) CR issue.

I understand sqlldr has issues with CR and I have tried various ways of
resolving this problem. I cannot pre-process the records and remove the CR
character because of the volume of data and the lack of time involved.

I have looked at the stream record format option in my control  file and I
fail to get this tro work as it seems to hang the session every time I call
it. I also suspect that it defines a common end of record marker and as I do
not have one I would have to massage the data which is not a route I can go
down. PS This is Tru64 and 8.1.7 but I think it is a general issue and not
specific to any version of Oracle or o/s

Furthermore it appears that using stream record format parameter reduces
performance which is a key requirement for us.

The questions are

1)  How have other people managed with carriage returns in long columns
(I suspect a CR in a varchar2 field will be a problem as well)?
2)  Is the stream record format the way I want to go (I suspect not)
3)  Has anybody any better ideas

TIA

John






-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: ora 3237

[John.Hallas]

Are you using LMT for the tablespace and if so what size extents?
I have had that problem when I have defaulted the LMT extent size to 64K (by
accident) and I have tried to create extents at 1M

HTH

John

-Original Message-
Sent: 10 December 2002 14:14
To: Multiple recipients of list ORACLE-L


Hi all:

I'm trying to create an 9.2 database. I have created a
rollback tablespace (not using UNDO TS yet) size 500M.
Then I try to create a rollback segment via:

create rollback segment r01_01 tablespace rbs01
storage (initial 1M next 1M minextents 16 maxextents
1017 optimal 16M)

This gives me an error 3237 - initial extent can not
be allocated. Reduce the number of free list groups. 
What is the problem here? Isn't the default number of
free list groups 1?

thanks
Gene

__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gurelei
  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.com
-- 
Author: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Oracle 8i Certification Will Terminate When???

[John.Hallas]

Peter,
I don't think a date has been announced yet. 
When they do state a date they give 6 months notice though

John

-Original Message-
Sent: 04 December 2002 15:59
To: Multiple recipients of list ORACLE-L


I have been looking at the Oracle web site for the
termination date of Oracle 8i certification tests. 
There is nothing there.  Only Oracle 8.  Rumor has had
it that there is a termination date. 

Does anyone have an authoritative answer?

=
Pete Barnett
Lead Database Administrator
The Regence Group
[EMAIL PROTECTED]

__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Peter Barnett
  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.com
-- 
Author: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

OT: Toasters

[John.Hallas]

As a bit of light relief
John



Carry on til the end for the SAP toaster
If IBM made toasters ... They would want one big toaster where people bring
bread to be submitted for overnight toasting. IBM would claim a worldwide
market for five, maybe six toasters. 
If Xerox made toasters ... You could toast one-sided or double-sided.
Successive slices would get lighter and lighter. The toaster would jam your
bread for you. 
If Radio Shack made toasters ... The staff would sell you a toaster, but not
know anything about it. Or you could buy all the parts to build your own
toaster. 
If Oracle made toasters ... They'd claim their toaster was compatible with
all brands and styles of bread, but when you got it home you'd discover the
Bagel Engine was still in development, the Croissant Extension was three
years away, and that indeed the whole appliance was just blowing smoke. 
If Sun made toasters ... The toast would burn often, but you could get a
really good cuppa Java. 
Does DEC still make toasters?... They made good toasters in the '80s, didn't
they? 
If Hewlett-Packard made toasters ... They would market the Reverse Toaster,
which takes in toast and gives you regular bread. 
If Tandem made toasters ... You could make toast 24 hours a day, and if a
piece got burned the toaster would automatically toast you a new one. 
If Thinking Machines made toasters ... You would be able to toast 64,000
pieces of bread at the same time. 
If Cray made toasters ... They would cost $16 million but would be faster
than any other single-slice toaster in the world. 
If the NSA made toasters ... Your toaster would have a secret trap door that
only the NSA could access in case they needed to get at your toast for
reasons of national security. 
If Sony made toasters ... The ToastMan, which would be barely larger than
the single piece of bread it is meant to toast, can be conveniently attached
to your belt. 
If Timex made toasters ... They would be cheap and small quartz-crystal
wrist toasters that take a licking and keep on toasting. 
If Fisher Price made toasters ... "Baby's First Toaster" would have a
hand-crank that you turn to toast the bread that pops up like a
Jack-in-the-box. 
And, of course: If Microsoft made toasters ... Every time you bought a loaf
of bread, you would have to buy a toaster. You wouldn't have to take the
toaster, but you'd still have to pay for it anyway. Toaster'95 would weigh
15000 pounds (hence requiring a reinforced steel countertop), draw enough
electricity to power a small city, take up 95% of the space in your kitchen,
would claim to be the first toaster that lets you control how light or dark
you want your toast to be, and would secretly interrogate your other
appliances to find out who made them. Everyone would hate Microsoft
toasters, but nonetheless would buy them since most of the good bread only
works with their toasters. 
If Apple made toasters ... It would do everything the Microsoft toaster
does, but 5 years earlier. 
If SAP made toasters, the manual to run the toaster would be approximately
10,000 pages long. The toaster would come with 2,500 switches which would
all have to be set in an exact pattern and in a precise sequence in order to
toast specific kinds of bread. Each pattern would be established by SAP's
experts as the "Best Practices" method of toasting that kind of bread. It
would take a team of basis and functional contractors about 1 year to
configure the toaster in the best manner, and then another 6 months to test
it. In the mean time, your entire family would need to attend extensive
training classes on how to use the new toaster. In order to support end
users and consultants, MIT would establish a list-serv for people to post
questions and answers regarding toaster set-up and operation. Of course, the
online help would randomly pop up in German. But once it was running, you'd
get the best toast in the world.



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: OMF question

[John.Hallas]

There is a OMF related parameter called db_create_online_log_dest_n.
This defines where the online redo logs are created. The value n can vary
from 1 to 5 and it allows for upto 5 multiplexed copies opf each redo log
group member.
 
HTH
 
John 

-Original Message-
Sent: 03 December 2002 11:29
To: Multiple recipients of list ORACLE-L


Hi !
 
I 've a question regarding OMF realted parameter CREATE_DB_ONLINE_LOG_N. Now
y is'nt this parameter mentioned in the list of parameters in the Oracle
Documenttion cd of 9i. Is it obsolete or oracle does not recommend it's use.
 
regards
Tc
 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: process memory utilization

[John.Hallas]

I posted a similar question recently and whilst I received some answers I
never did discover an exact way of matching oracle use of memory with unix
use of memory.
One issue is that when oracle releases memory the unix process does not
automatically do the same until the memory is required. Therefore it is
likely that there will always be a difference.
Tim Gorman pointed me to a script oramem.sh (www.evdbt.com/tools.htm) which
does what you are doing with a pmap function which is quite useful
The discussion was around 4/10/02 and the thread title was "How much memory
is an oracle shadow process using"

John

-Original Message-
Sent: 03 December 2002 11:29
To: Multiple recipients of list ORACLE-L


chao_ping,
It seems i am posting a too long question, so nobody is
interested.
I post the answer to my second question, to check the memory
utilization in linux operation system.
It is in  /proc/spid/status file.





Regards
zhu chao
Eachnet DBA
86-21-32174588-667
[EMAIL PROTECTED]
www.cnoug.org(Chinese Oracle User Group)

=== 2002-12-02 08:34:00 ,you wrote£º===

>Hi,dba friends:
>   I am thinking of measure how much memory per connection used, from
the os viewpoint and oracle viewpoint. And this is my result from my
production server, and i have some questions below.
>
>   
>23:56:28 SQL>  select sum(value),sum(value)/count(distinct sid) average
from v$sesstat where statistic#=15; --uga
>
>  SUM(VALUE)  AVERAGE
> 
>  69,098,528  145,777
> 
>23:58:09 SQL>select sum(value),sum(value)/count(distinct sid),max(value)
from v$sesstat where statistic#=20  --pga
>
>  SUM(VALUE) SUM(VALUE)/COUNT(DISTINCTSID)   MAX(VALUE)
> - 
> 265,290,648559,684.9117,510,184
>
>   There is about my production server(oracle817+solaris7), and I also
used pmap to trace some process and it look like:
>oracle@main-db1$pmap 11443   #some process id i which is choosed randomly
via /usr/ucb/ps -aux.
>11443:  oraclebiddb (LOCAL=NO)
>0001  29440K read/exec
/export/home/oracle/app/product/8.1.7/bin/oracle
>000101DBE000464K read/write/exec
/export/home/oracle/app/product/8.1.7/bin/oracle
>000101E32000   1440K read/write/exec [ heap ]
>00038000 5685720K read/write/exec/shared  [ shmid=0x65 ]
>7D80 16K read/exec /usr/lib/sparcv9/libmp.so.2
>7D902000  8K read/write/exec   /usr/lib/sparcv9/libmp.so.2
>7DA0 88K read/exec /usr/lib/sparcv9/libm.so.1
>7DB14000 16K read/write/exec   /usr/lib/sparcv9/libm.so.1
>7DC0  8K read/exec /usr/lib/sparcv9/libkstat.so.1
>7DD0  8K read/write/exec   /usr/lib/sparcv9/libkstat.so.1
>7DE0 32K read/exec /usr/lib/sparcv9/librt.so.1
>7DF06000  8K read/write/exec   /usr/lib/sparcv9/librt.so.1
>7E00 24K read/exec /usr/lib/sparcv9/libaio.so.1
>7E104000 16K read/write/exec   /usr/lib/sparcv9/libaio.so.1
>7E20704K read/exec /usr/lib/sparcv9/libc.so.1
>7E3AE000 64K read/write/exec   /usr/lib/sparcv9/libc.so.1
>7E3BE000  8K read/write/exec [ anon ]
>7E40  8K read/exec /usr/lib/sparcv9/libsched.so.1
>7E50  8K read/write/exec   /usr/lib/sparcv9/libsched.so.1
>7E60 32K read/exec /usr/lib/sparcv9/libgen.so.1
>7E706000  8K read/write/exec   /usr/lib/sparcv9/libgen.so.1
>7E80 40K read/exec /usr/lib/sparcv9/libsocket.so.1
>7E908000 16K read/write/exec   /usr/lib/sparcv9/libsocket.so.1
>7EA0624K read/exec /usr/lib/sparcv9/libnsl.so.1
>7EB9A000 64K read/write/exec   /usr/lib/sparcv9/libnsl.so.1
>7EBAA000 32K read/write/exec [ anon ]
>7EC0   3896K read/exec
/export/home/oracle/app/product/8.1.7/lib/libjox8.so
>7F0CC000192K read/write/exec
/export/home/oracle/app/product/8.1.7/lib/libjox8.so
>7F0FC000  8K read/write/exec [ anon ]
>7F30 40K read/exec
/export/home/oracle/app/product/8.1.7/lib64/libdsbtsh8.so
>7F408000  8K read/write/exec
/export/home/oracle/app/product/8.1.7/lib64/libdsbtsh8.so
>7F40A000  8K read/write/exec [ anon ]
>7F50  8K read/exec
/export/home/oracle/app/product/8.1.7/lib64/libskgxp8.so
>7F60  8K read/write/exec
/export/home/oracle/app/product/8.1.7/lib64/libskgxp8.so
>7F68128K read/exec /usr/lib/sparcv9/ld.so.1
>7F79 16K read/exec
/usr/platform/sun4u/lib/sparcv9/libc_psr.so.1
>7F79E000  8K read/write/exec   /usr/lib/sparcv9/ld.so.1
>7F7A  8K read/write/exec [ anon ]
>7F7B  8K read/write/exec [ anon ]
>7

RE: patches

[John.Hallas]

I think I veer towards Yechiel's view here Patrice. I am not sure I have
ever heard anyone suggest applying all outstanding relevant patches to a
database however much you test first. (To be honest you are not advocating
that, just raising the question).
Normal policy wherever I have worked is not to patch unless absolutely
necessary.
 
Another point is that 1 patch can provide a new code piece of binary and
patches are not cumulative. Therefore applying patch a then patch b could
well mean that patch a is overwritten and yet your documentation indicates
that it has been installed.
 
HTH
 
John

-Original Message-
Sent: 02 December 2002 12:54
To: Multiple recipients of list ORACLE-L


Yechiel,
 
If that's your philosophy, and there are known unpatched bugs in the server
software, how can you be 100 percent sure it will stay up?
 
Granted, many of these bugs are esoteric, but not all.
 
I am curious what Oracle Support consultants do when they have a 24x7
contract, with all these patches.  Surely they have a list.
 
Regards,
Patrice Boivin 
Systems Analyst (Oracle Certified DBA) 

[Boivin, Patrice J]  -Original Message-
Sent: Friday, November 29, 2002 3:49 AM
To: Multiple recipients of list ORACLE-L



We only apply patches as needed and where needed.
For example: I had a problem with export taking a long time on one system.
I installed a patch for this problem (after testing in test environment of
course) only on that database.
 
My motto is: If it ain't broken do not fix it.
 
I have seen too many follow up fixes to install something I do not need.
 
Yechiel Adar
Mehish

- Original Message - 
To: Multiple   recipients of list ORACLE-L 
Sent: Wednesday, November 27, 2002 9:05 PM

I am wondering how your sites handle patching production servers.
 
I just did a search in MetaLink, since 8174 was released there have been 48
patches (if I just select RDBMS).
 
If I select other items in my search, I get upwards of 70 additional bug
fixes.
 
How do high reliability sites handle patching?  I assume they would rather
fix potential problems (testing the patches on a testbed of course) rather
than just apply bug fixes as problems are encountered on production servers.
 
regards,
Patrice Boivin 
Systems Analyst (Oracle Certified DBA) 
 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).