RE: SQL query
At SQL*Plus it is the same command Describe or desc and name of the table You also need to be connected to the database HTH Aleem -Original Message- Sent: Saturday, July 13, 2002 11:53 AM To: Multiple recipients of list ORACLE-L Subject:SQL query I am using an Oracle database running in Linux. I would like to view the description of a table. For ex., if there is a table called 'person'. I would like to see the names of the columns in this table, their datatypes and such other details. In other dbmss like mysql a command 'describe tablename' gives the information. What is the equivalent in Oracle? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vandana INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL query
I am sorry for not being precise in my question. I would like to view the primary key, foreign key and other 'constraints' information of my table. While this could be viewed with 'desc' in other dbmss, this information is not provided with the 'desc' in oracle. With what command can I view it in Oracle? On Fri, 12 Jul 2002, Abdul Aleem wrote: At SQL*Plus it is the same command Describe or desc and name of the table You also need to be connected to the database HTH Aleem -Original Message- Sent: Saturday, July 13, 2002 11:53 AM To: Multiple recipients of list ORACLE-L Subject: SQL query I am using an Oracle database running in Linux. I would like to view the description of a table. For ex., if there is a table called 'person'. I would like to see the names of the columns in this table, their datatypes and such other details. In other dbmss like mysql a command 'describe tablename' gives the information. What is the equivalent in Oracle? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vandana INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL query
Title: RE: SQL query Same Desc TableName Arslan Zaheer Dar [EMAIL PROTECTED] Database Administrator Shaukat Khanum Memorial Cancer Hospital Research Centre www.shaukatkhanum.org.pk + 92 (042) 5180725 - 34 Ext: 2323 -Original Message- From: Vandana [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 13, 2002 11:53 AM To: Multiple recipients of list ORACLE-L Subject: SQL query I am using an Oracle database running in Linux. I would like to view the description of a table. For ex., if there is a table called 'person'. I would like to see the names of the columns in this table, their datatypes and such other details. In other dbmss like mysql a command 'describe tablename' gives the information. What is the equivalent in Oracle?
Re: Re: Help me tuning the log file sync wait event
Bunyamin Karadeniz£¬
ÔÚ 2002-07-12 01:48:00 You wrote:
Hi ,
You encounter log file sync and log file parallel write events .
This can be because of a slow device on which redo log files are on . OR
your redo log sizing is bad.
hi, you said the redo log size have something to do with this log file sync wait
event? I do not understand it, can you explain it to me?
Maybe my log buffer is too big, now it is 2M in size. But when it was 1M, i see much
wait time for log buffer space:(, so wondering whether downsize the log buffer.
Thanks for your suggestions.
Good luck!
chaos
[EMAIL PROTECTED]
zhu chao
DBA of Eachnet.com
86-021-32174588-667
Bunyamin Karadeniz
Oracle DBA
Havelsan A.S. Eskisehir yolu 7.km
Ankara / Turkey
Tel : +90 535 3357729
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, July 12, 2002 6:33 AM
hi, dbas:
One of the database i manage face a serious wait event, log file sync.
This is a big and busy oltp system, and using disk array of Sun T3 with
raid-5. We are using Veritas QuickIO for datafile and redo log files.
The pressure on the database is growing fast, and more and more the redo
log becomes the bottleneck of the database.
Here is some data showing the fact:
2002/05/03
Top 5 Wait Events
~ Wait %
Total
Event Waits Time (cs) Wt
Time
---
log file sync 82,244 92,442
33.68
db file sequential read 298,301 80,719
29.41
log file parallel write81,849 36,989
13.48
db file parallel write 5,427 33,615
12.25
control file parallel write 4,6736,104
2.22
2002/05/07
Top 5 Wait Events
~ Wait %
Total
Event Waits Time (cs) Wt
Time
---
log file sync 6,352,383 15,785,313
40.09
db file sequential read26,862,699 12,538,922
31.85
log file parallel write 5,971,2293,990,066
10.13
db file parallel write290,4793,164,391
8.04
db file scattered read 1,749,137 814,981
2.07
2002/05/21
Top 5 Wait Events
~ Wait %
Total
Event Waits Time (cs) Wt
Time
---
log file sync 2,207,6096,688,751
59.47
log file parallel write 2,044,9771,385,379
12.32
db file parallel write103,1551,203,077
10.70
db file sequential read 8,772,9081,088,922
9.68
log buffer space3,284 222,604
1.98
2002/05/28
Top 5 Wait Events
~ Wait %
Total
Event Waits Time (cs) Wt
Time
---
log file sync 2,247,585 20,529,779
63.71
db file parallel write441,0524,377,899
13.59
log file parallel write 1,724,0893,806,535
11.81
db file sequential read 8,854,5252,044,020
6.34
enqueue78,759 592,411
1.84
2002/07/04
Top 5 Wait Events
~ Wait %
Total
Event Waits Time (cs) Wt
Time
---
log file sync 3,838,694 13,158,371
63.28
db file sequential read 2,189,8632,401,275
11.55
log file parallel write 3,401,0352,098,018
10.09
db file parallel write 97,0861,503,608
7.23
enqueue71,251 432,706
2.08
2002/07/11
Top 5 Wait Events
~ Wait %
Total
Event Waits Time (cs) Wt
Time
---
log file sync 453,8629,679,513
68.11
db file sequential read
Re: SQL query
select * from dba_cons_columns; On Saturday 13 July 2002 01:08, Vandana wrote: I am sorry for not being precise in my question. I would like to view the primary key, foreign key and other 'constraints' information of my table. While this could be viewed with 'desc' in other dbmss, this information is not provided with the 'desc' in oracle. With what command can I view it in Oracle? On Fri, 12 Jul 2002, Abdul Aleem wrote: At SQL*Plus it is the same command Describe or desc and name of the table You also need to be connected to the database HTH Aleem -Original Message- Sent: Saturday, July 13, 2002 11:53 AM To: Multiple recipients of list ORACLE-L Subject:SQL query I am using an Oracle database running in Linux. I would like to view the description of a table. For ex., if there is a table called 'person'. I would like to see the names of the columns in this table, their datatypes and such other details. In other dbmss like mysql a command 'describe tablename' gives the information. What is the equivalent in Oracle? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: Help me tuning the log file sync wait event
Connor McDonald£¬ ÔÚ 2002-07-12 02:33:00 You wrote: Some suggestions a) Check your commit frequency - if you're committing like mad then this is a great way to over stress redo logging operations. Yes, i check the statspack report and find there is about 20 commits/second in may, and now there is 40 commits/second these days. I do not know whether this is too much. THis is a busy oltp system,and the trend is that there will be more and more transactions per second, painful b) Check the size of the average redo write. If they're small, you might get some benefit by allocation write cache in the T3 to the redo logs (if thats possible) to batch up the writes The average redo size per transaction is quite small, as i can see it from the statspack, ~Per Second Per Transaction --- --- Redo size: 82,547.28 2,247.16 per transaction is 2kbytes in size. I will try to modify the T3 cache policy. and see if this helps.It can be modified when it is running, as the document said, but my sa do not dare to modify it as i had requested.:(, maybe we have to try it. oracle@main-db1$sar -d 2 2 SunOS main-db1 5.7 Generic_106541-14 sun4u07/13/02 22:07:33 device%busy avque r+w/s blks/s avwait avserv 22:07:35 ssd0,e 10020.7 1993189 0.0 104.4 This is the most busy time in the day for the system, and this device ssd0,e is for the redo log volumn, 3189blocks/200=16blocks/second, that means 8KBytes per write.This is some small write, but still much greate than the 2k per transaction. why? other wasted log because of the too large log_buffer_size? (there should be no read for redo log, right, unless it is being archived, so all write). c) Look at means at reducing the amount of redo log your're generating eg transaction auditing etc This seems hard, we do not have much other information other than the transaction data. Thanks so much for your help, and your site:) Good luck! chaos [EMAIL PROTECTED] zhu chao DBA of Eachnet.com 86-021-32174588-667 hth connor --- chaos [EMAIL PROTECTED] wrote: hi, dbas: One of the database i manage face a serious wait event, log file sync. This is a big and busy oltp system, and using disk array of Sun T3 with raid-5. We are using Veritas QuickIO for datafile and redo log files. The pressure on the database is growing fast, and more and more the redo log becomes the bottleneck of the database. Here is some data showing the fact: 2002/05/03 Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- log file sync 82,244 92,442 33.68 db file sequential read 298,301 80,719 29.41 log file parallel write 81,849 36,989 13.48 db file parallel write 5,427 33,615 12.25 control file parallel write 4,6736,1042.22 2002/05/07 Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- log file sync 6,352,383 15,785,313 40.09 db file sequential read 26,862,699 12,538,922 31.85 log file parallel write 5,971,2293,990,066 10.13 db file parallel write 290,4793,164,3918.04 db file scattered read 1,749,137 814,9812.07 2002/05/21 Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- log file sync 2,207,6096,688,751 59.47 log file parallel write 2,044,9771,385,379 12.32 db file parallel write 103,1551,203,077 10.70 db file sequential read 8,772,9081,088,9229.68 log buffer space 3,284 222,6041.98 2002/05/28 Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- log file sync 2,247,585 20,529,779 63.71 db file parallel write 441,0524,377,899 13.59 log file parallel write 1,724,0893,806,535 11.81 db file sequential read 8,854,5252,044,0206.34 enqueue 78,759 592,4111.84 2002/07/04 Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- log file sync 3,838,694 13,158,371 63.28 db file sequential read 2,189,8632,401,275 11.55 log file parallel write 3,401,0352,098,018 10.09 db file parallel write 97,0861,503,6087.23 enqueue 71,251 432,7062.08 2002/07/11 Top 5 Wait Events ~ Wait
RE: Re: Help me tuning the log file sync wait event
Definitely there is a bottleneck in disks or the response time is very high. Since you are using Raid-5, I would suggest as a test to make your redo groups one member only or to move the other members to another raid while keeping the log_buffer at 2M. Regards, Waleed -Original Message- To: Multiple recipients of list ORACLE-L Sent: 7/13/02 11:03 AM Bunyamin Karadeniz, ? 2002-07-12 01:48:00 You wrote: Hi , You encounter log file sync and log file parallel write events . This can be because of a slow device on which redo log files are on . OR your redo log sizing is bad. hi, you said the redo log size have something to do with this log file sync wait event? I do not understand it, can you explain it to me? Maybe my log buffer is too big, now it is 2M in size. But when it was 1M, i see much wait time for log buffer space:(, so wondering whether downsize the log buffer. Thanks for your suggestions. Good luck! chaos [EMAIL PROTECTED] zhu chao DBA of Eachnet.com 86-021-32174588-667 Bunyamin Karadeniz Oracle DBA Havelsan A.S. Eskisehir yolu 7.km Ankara / Turkey Tel : +90 535 3357729 - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, July 12, 2002 6:33 AM hi, dbas: One of the database i manage face a serious wait event, log file sync. This is a big and busy oltp system, and using disk array of Sun T3 with raid-5. We are using Veritas QuickIO for datafile and redo log files. The pressure on the database is growing fast, and more and more the redo log becomes the bottleneck of the database. Here is some data showing the fact: 2002/05/03 Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- log file sync 82,244 92,442 33.68 db file sequential read 298,301 80,719 29.41 log file parallel write81,849 36,989 13.48 db file parallel write 5,427 33,615 12.25 control file parallel write 4,673 6,104 2.22 2002/05/07 Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- log file sync 6,352,383 15,785,313 40.09 db file sequential read26,862,699 12,538,922 31.85 log file parallel write 5,971,229 3,990,066 10.13 db file parallel write290,479 3,164,391 8.04 db file scattered read 1,749,137 814,981 2.07 2002/05/21 Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- log file sync 2,207,609 6,688,751 59.47 log file parallel write 2,044,977 1,385,379 12.32 db file parallel write103,155 1,203,077 10.70 db file sequential read 8,772,908 1,088,922 9.68 log buffer space3,284 222,604 1.98 2002/05/28 Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- log file sync 2,247,585 20,529,779 63.71 db file parallel write441,052 4,377,899 13.59 log file parallel write 1,724,089 3,806,535 11.81 db file sequential read 8,854,525 2,044,020 6.34 enqueue78,759 592,411 1.84 2002/07/04 Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- log file sync 3,838,694 13,158,371 63.28 db file sequential read 2,189,863 2,401,275 11.55 log file parallel write 3,401,035 2,098,018 10.09 db file parallel write 97,086 1,503,608 7.23 enqueue71,251 432,706 2.08 2002/07/11 Top 5 Wait Events ~ Wait % Total Event
Re: SQL query
select object_name from all_objects where lower(object_name) like '%cons%'; will give you all the data dictionary tables/views that deals with constraints ltiu On Saturday 13 July 2002 01:08, Vandana wrote: I am sorry for not being precise in my question. I would like to view the primary key, foreign key and other 'constraints' information of my table. While this could be viewed with 'desc' in other dbmss, this information is not provided with the 'desc' in oracle. With what command can I view it in Oracle? On Fri, 12 Jul 2002, Abdul Aleem wrote: At SQL*Plus it is the same command Describe or desc and name of the table You also need to be connected to the database HTH Aleem -Original Message- Sent: Saturday, July 13, 2002 11:53 AM To: Multiple recipients of list ORACLE-L Subject:SQL query I am using an Oracle database running in Linux. I would like to view the description of a table. For ex., if there is a table called 'person'. I would like to see the names of the columns in this table, their datatypes and such other details. In other dbmss like mysql a command 'describe tablename' gives the information. What is the equivalent in Oracle? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL query
Vandana,
Put this into a script, save to hard drive, and run
from sqlplus using @path\script:
SET ECHO OFF
accept table_name prompt Enter the name of the Table
:
set heading on
set verify on
set newpage 0
ttitle 'Table Description - Space Definition'
spool tfstbdsc.lst
btitle off
column nline newline
set pagesize 54
set linesize 78
set heading off
set embedded off
set verify off
accept report_comment char prompt 'Enter a comment to
identify system: '
select 'Date - '||to_char(sysdate,'Day Ddth Month
HH24:MI:SS'),
'At- '||'report_comment' nline,
'Username - '||USER nline
from sys.dual
/
prompt
set embedded on
set heading on
set verify off
column ts format a30
column ta format a30
column clu format a30
column pcf format 90
column pcu format 90
column int format 99,999,999,990
column mat format 99,999,999,990
column inx format 99,999,999,990
column nxt format 99,999,999,990
column mix format 99,999,999,990
column max format 99,999,999,990
column pci format 90
column num format 99,999,999,990
column blo format 99,999,999,990
column emp format 99,999,999,990
column avg format 99,999,999,990
column cha format 99,999,999,990
column rln format 99,999,999,990
column hdg format a30 newline
set heading off
select 'Table Name' hdg, TABLE_NAME ta,
'Tablespace Name' hdg, TABLESPACE_NAME ts,
'Cluster Name' hdg, CLUSTER_NAMEclu,
'% Free' hdg, PCT_FREEpcf,
'% Used' hdg, PCT_USEDpcu,
'Ini Trans' hdg,INI_TRANS int,
'Max Trans' hdg,MAX_TRANS mat,
'Initial Extent (K)' hdg, INITIAL_EXTENT/1024 inx,
'Next Extent (K)' hdg, NEXT_EXTENT/1024nxt,
'Min Extents' hdg, MIN_EXTENTS mix,
'Max Extents' hdg, MAX_EXTENTS max,
'% Increase' hdg, PCT_INCREASEpci,
'Number of Rows' hdg, NUM_ROWSnum,
'Number of Blocks' hdg, BLOCKS blo,
'Number of Empty Blocks' hdg, EMPTY_BLOCKSemp,
'Average Space' hdg,AVG_SPACE avg,
'Chain Count' hdg, CHAIN_CNT cha,
'Average Row Length' hdg, AVG_ROW_LEN rln
from all_tables
where TABLE_NAME=UPPER('table_name')
/
set heading on
set embedded off
column cn format a30 heading 'Column Name'
column fo format a15 heading 'Type'
column nu format a8 heading 'Null'
column nds format 99,999,999 heading 'No Distinct'
column dfl format heading 'Dflt Len'
column dfv format a40 heading 'Default Value'
ttitle 'Table Description - Column Definition'
select COLUMN_NAME cn,
DATA_TYPE ||
decode(DATA_TYPE,
'NUMBER',
'('||to_char(DATA_PRECISION)||
decode(DATA_SCALE,0,'',','||to_char(DATA_SCALE))||')',
'VARCHAR2',
'('||to_char(DATA_LENGTH)||')',
'DATE','',
'Error') fo,
decode(NULLABLE,'Y','','NOT NULL') nu,
NUM_DISTINCT nds,
DEFAULT_LENGTH dfl,
DATA_DEFAULT dfv
FROM all_tab_columns
where TABLE_NAME=UPPER('table_name')
order by COLUMN_ID
/
ttitle 'Table Constraints'
set heading on
set verify off
column cn format a30 heading 'Primary Key'
column cln format a45 heading 'Table.Column Name'
column ct format a7 heading 'Type'
column st format a7 heading 'Status'
column ro format a30 heading 'Ref Owner|Constraint
Name'
column se format a70 heading 'Criteria ' newline
break on cn on st
set embedded on
prompt Primary Key
prompt
select cns.CONSTRAINT_NAME cn,
cns.TABLE_NAME||'.'||cls.COLUMN_NAME cln,
initcap(cns.STATUS) st
fromall_constraints cns,
all_cons_columns cls
where cns.table_name=upper('table_name')
and cns.owner=user
and cns.CONSTRAINT_TYPE='P'
and cns.constraint_name=cls.constraint_name
order by cls.position
/
prompt Unique Key
prompt
column cn format a30 heading 'Unique Key'
select cns.CONSTRAINT_NAME cn,
cns.TABLE_NAME||'.'||cls.COLUMN_NAME cln,
initcap(cns.STATUS) st
fromall_constraints cns,
all_cons_columns cls
where cns.table_name=upper('table_name')
and cns.owner=user
and cns.CONSTRAINT_TYPE='U'
and cns.constraint_name=cls.constraint_name
order by cls.position
/
column cln format a38 heading 'Foreign Key' newline
column clfn format a38 heading 'Parent Key'
break on cn on st skip 1
prompt Foreign Keys
prompt
select cns.CONSTRAINT_NAME cn,
initcap(STATUS) st,
Re: Oracle on Linux ... Production Strength ???
We are considering both Red Hat and Suse distributions. We have discovered that regardless of the Linux distribution support is generally expensive. That is not a particularly 'deal breaker' determining factor .. BUT .. I question the quality of support, the expediency of response and the 'sense of urgency' experienced in the event of a critical application being down. I am familiar with limited Oracle-Linux implementations but not to the 'industrial strength' degree that has been proposed (but already implemented) by our requesting user community. The prices for 7x24 support are roughly the same for linux and Solaris, HP-UX, or AIX. One other option is to buy the linux from IBM, with support. IBM seriously wants linux to succeed on thier platforms and has good support. Linux Care and Cygnus (now part of Red Hat) have been dealing with mission-critical systems for some time and are capable of fixing things. Obviously, setting up the system in a supportable fashion (e.g., supported hardware, up to date drivers) will get you better response times. Is there a preferred distribution Sure: Red Hat preferrs that you buy theirs, SuSE wants your money also. Beyond that the kernel -- a.k.a., linux -- is something you download from the net and compile locally. It has relatively little effect on the difference between distributions. The real distinctions are in SysAdmin tools and the installation. Most of it is a purely religious issue, all you can really do is set up a few machines and try them. So far as I know HP and IBM are both going with Red Hat as their base distro's and Oracle only supports Red Hat so that is probably what you'll end up with. Eyeball the available support contracts to be sure. The net result will depend heavily on the hardware you're running. For serious databases X86 platforms don't work well because of hardware limitations. You are probably better off looking at the hardware first and then finding out which software vendor is supported (probably Red Hat for HP or IBM systems). My initial implementation is Suse 7.2 Enterprise on an IBM NetFinity, 4 cpu, 2 Gbyte (memory) server using a Net Appliance Filer. There are six instances currently up and running. Thus far there have been no occurrences of swapping or i/o bottlenecks but then the system has yet to be fully 'stressed' and there are scalability concerns. The USER also wants to put Oracle 9iAS on the same box - I have managed to delay that for now, pending further research. I have had a couple of worrying episodes where a file system 'filled up' (on the Filer) that completely 'hung' the system requiring a full system re-boot. Incidentally the aforementioned NetFinity implementation is 'a given' as the six instances have already been migrated from an aged and de-commissioned HP system. I have inherited the results and there is no going back at this juncture. What file system are you running? Are you using LVM? devfs? A full system lockup in this case seems suspicious. -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
(Fwd) ORACLE HAS ONCE AGAIN ROYALLY F*ED UP / Re: Looking for Forms 5
Assuming that you can somehow find the patch number (which is the same as the directory name), this is the new patch download facility: ftp updates.oracle.com cd 1647241 binary get p1647241_210_WINNT.zip ---log file--- 230- 230- Welcome to the Oracle Patch Download FTP Server 230- 230- Access 230- -- 230- Access to this system is limited to authorized users of Oracle 230- Metalink. Unauthorized access to or use of this system is prohibited 230- and may subject you to civil and criminal prosecution. Use of this 230- system may be monitored for the purpose of maintaining system 230- security, and system information may be accessed or disclosed under 230- limited circumstances. 230- 230- All transmissions of Oracle software, documentation, source code, 230- technical data or technology must comply with Oracle's Export 230- Compliance Corporate Policy. For more information, refer to Oracle's 230- Internal Export Control Compliance Manual or contact your division's 230- Export Control Officer. 230- 230- Usage 230- - 230- To download a patch, you must know the patch number. At any time you 230- can cd patch number and then ls to find a listing of patches 230- with that number. Enter quote site help to get this welcome banner 230- and additional helpful instructions. 230- 230- Caveats 230- --- 230- - You may not list files or directories from the root directory. 230- - You must select binary transfers for this service to work. 230 ftp --- dir PORT 192,168,0,2,13,85 200 PORT command OK. --- LIST 150 Opening data connection for file listing. total 1 226-Listing complete. Data connection has been closed. 226-It is the policy of this server to deny all 226-requests to list from the root directory. 226-Only diretories with names which match 226 bug numbers exist and there are too many to be listed. ftp: 9 bytes received in 0.00Seconds 9000.00Kbytes/sec. ftp --- quit QUIT 221 Goodbye. Service closing connection. ---end log file--- --- Forwarded message follows --- Looking for Forms 5 patches To: O D T U G - D E V 2 K - L @ f a t c i t y . c o m Go into Metalink and click on the Patches button on the left: Notice: All critical patches have been moved to this new advanced Patch Download screen. As of June 17, 2002, the old patch download area has been decommissioned. Patch Download Screen, Decommission Information --- http://metalink.oracle.com/metalink/plsql/ml2_documents.showNO T?p_id=199805.1 excerpt: As of June 17, 2002, this portion of the MetaLink patch download area has been decommissioned. For your convenience, all critical patches have been moved from this area to the main patch download area. You can access the main patch download area by clicking on the Patches button in the MetaLink navigation menu. In the unlikely event that you are unable to retrieve a critical patch, please create a Metalink iTar, with details of the required patch, for Oracle Support to resolve. Do not use the MetaLink feedback button to request a patch, as this will only delay the resolution. Thank you! The MetaLink team. --- View the latest Applications Release 11i Packs ( http://ap103aru.us.oracle.com/quicklinks/apps.html ), the latest Oracle Server / Tools Patchsets ( http://ap103aru.us.oracle.com/quicklinks/server.html ), or enter your selection criteria below. Patch Download To view a list of available patches, select a product and platform combination from the drop-down lists. Patch Number: Product Family: select a product family ... Limit Search to: select an entry ... Includes File: ... Order by: Release date Patch Number Copyright (c) 1995, 2001 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use. Of course the profoundly sick b*stards that designed the convoluted select criteria list made it impossible to find something simple like a list of patches for forms 5, you have to rummage around amongst various options/suboptions for Developer, Developer Suite, etc. Another victim of Oracle's byzantine product naming/numbering scheme. Note that a simple search for forms 5 patch returned a vast number of entries that do not appear to be relevant. CAN SOMEBODY PLEASE TELL THE DECISION MAKERS AT ORACLE THAT METALINK SUCKS?!?! Moving right along, I look in Top Tech Docs. Internet Developer Tools seems the closest approximation??? (of course forms 5 may, or may not, be used for Internet, but nevermind.) Ah yes, click Oracle Forms. Under Patches, there is almost nothing useful!!! AMAZING! CAN SOMEBODY PLEASE TELL THE DECISION MAKERS AT ORACLE THAT METALINK SUCKS?!?! Ah, but in 64630.1, there is a link to 74145.1 (Developer Version Number Matrix). hm... : Forms 5.0.5 = Developer 2.0 Forms 5.0.6 = Developer 2.1 So, the
TOra: Toolkit for Oracle
I've seen some chatter here about Tora, but when I go to http://www.globecom.se/tora/ or http://www.globecom.net/tora/, all I get is a page with user comments and screen shots. Where do I go to download the product? Thx. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: TOra: Toolkit for Oracle
Go to the web site you mentioned below. There is a link saying it's available from sourceforge. Click on it and download. Dennis Don wrote: I've seen some chatter here about Tora, but when I go to http://www.globecom.se/tora/ or http://www.globecom.net/tora/, all I get is a page with user comments and screen shots. Where do I go to download the product? Thx. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dennis M. Heisler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
TEST
Hi. This is a test mail to see if my subscription was successful. Sorry to bother. Cesar Aracena CE / MCSE+I Neuquen, Argentina +54.299.6356688 +54.299.4466621
RE: Where is Oracle 9.2 init.ora?
All Right, Larry. Since we have the test servers and databases; and my Company still pays for 'doing Oracle' the 'scary' way, here is another 'scary thing' I did with SPFILE :) (9iR1 on HP) SQL conn / as sysdba Connected to an idle instance. SQL startup using spfile ORACLE instance started. Total System Global Area 72273416 bytes Fixed Size 437768 bytes Variable Size 37748736 bytes Database Buffers 33554432 bytes Redo Buffers 532480 bytes Database mounted. Database opened. SQL show parameter db_cache_size NAME TYPEVALUE --- -- db_cache_sizebig integer 33554432 SQL !mv spfileKED9.ora spfileKED9.ora.bak -- hide the spfile SQL !ls -l *.ora -rw-r--r-- 1 oracle dba 12920 May 10 2001 initdw.ora SQL alter system set db_cache_size=10M scope=both; -- try to set a new value System altered. --- No problem? SQL show parameter db_cache_size NAME TYPEVALUE --- -- db_cache_sizebig integer 12582912 -- New value in effect. SQL !ls -l *.ora -rw-r--r-- 1 oracle dba 12920 May 10 2001 initdw.ora -- Still no SFILE -- Now, why would not Oracle tell us that there was no spfile to process SCOPE=BOTH ? SQL c/both/spfile 1* alter system set db_cache_size=10M scope=spfile SQL / alter system set db_cache_size=10M scope=spfile * ERROR at line 1: ORA-27037: unable to obtain file status HP-UX Error: 2: No such file or directory Additional information: 3 --This is what should have happened with SCOPE=BOTH as well, or at least a warning that SCOPE=BOTH was processed as SCOPE=MEMORY since there was no SPFILE available. I would not have objected if Oracle re-recreated SPFILE in the default location and told me so! If anyone has seen any mention of this particular behaviour of SCOPE=BOTH, I would like to know the source of that information. I have searched Metalink, Google but have not come across any. I have created an iTar with OWS. Thanks. As I said before, SPFILE has some things that need to be made fool proof. This time I did not drink prior to doing this 'scary' stuff !!;-) Regards, - Kirti -Original Message- From: Larry Elkins [SMTP:[EMAIL PROTECTED]] Sent: Friday, July 12, 2002 9:03 PM To: Multiple recipients of list ORACLE-L Subject: RE: Where is Oracle 9.2 init.ora? Man, it scares the heck out of me too that Jared and Kirti are actually doing Oracle -- I can't believe companies actually pay them ;-) And you two guys, and I'm talking to you Kirti and Jared, probably dig in and do things you shouldn't on test boxes just to see how things work and to learn. FWIW, I've heard rumors about other people doing similar things. You've probably even intentionally crashed a DB or pulled the plug just to see if you could recover. Shame on you two. You should both be banished from the list for doing such unconventional things ;-) And neither of you will ever be allowed close to a DB I deal with -- I'll call ltiu from now on ;-) Larry -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of [EMAIL PROTECTED] Sent: Friday, July 12, 2002 8:08 PM To: Multiple recipients of list ORACLE-L Subject: RE: Where is Oracle 9.2 init.ora? Some of us have been around the block a few times. :) Editing binary files is no big deal. You neophytes are all the same. Jared ltiu [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/12/2002 04:28 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Where is Oracle 9.2 init.ora? You DBA's must be drunk. Spfiles are in binary format and if you open it in a text editor, all you see are weird characters. Man. You guys are actually doing Oracle? Scares me. ltiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: Buffer busy waits are 10.96% of non-idle waits
One more detail: buffer busy waits is not an indication of competition for a block on disk. It indicates competition for a block IN MEMORY. More memory won't help, faster disks won't help. The cure is to make the competing processes not compete. The problem is usually a result of applications that have been parallelized by running several similar jobs simultaneously, without designing the jobs to work on different subsets of the data at the same time. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Jul 23-25 Chicago - Miracle Database Forum, Sep 20-22 Middlefart Denmark - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas -Original Message- Sent: Friday, July 12, 2002 4:14 PM To: Multiple recipients of list ORACLE-L CLASS COUNT TIME -- -- -- data block 131525173 225446798 will not -- using bind variables instead of literals -- seperating tables and indexes to diferent tabelspace solve my problem ? No and no. You have a very specific problem: Two or more processes are trying to access the exact same data block at the same time. You are part way through diagnosing your problem but you haven't finished yet. Your job now is to figure out some very specific things: which data block the processes are waiting for, which table or index the block belongs to, which SQL statement keeps accessing that block so often, and why. Diagnosis complete, you'll fix the problem. But you can't do that until you know what the problem is. Until you know which SQL statement is causing the problem and specifically what the nature of the problem is, you're not going to be able to fix it. To figure out the next steps to take, which v$ views to query, what to look for, what the common problems are and how to fix them, do a Google search for: buffer busy waits data block -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Help me tuning the log file sync wait event
Also, as Anjo taught me a couple years ago, log file sync is particularly susceptible to long latencies if you simply have long CPU runqueues. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Jul 23-25 Chicago - Miracle Database Forum, Sep 20-22 Middlefart Denmark - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas -Original Message- McDonald Sent: Friday, July 12, 2002 5:33 AM To: Multiple recipients of list ORACLE-L Some suggestions a) Check your commit frequency - if you're committing like mad then this is a great way to over stress redo logging operations. b) Check the size of the average redo write. If they're small, you might get some benefit by allocation write cache in the T3 to the redo logs (if thats possible) to batch up the writes c) Look at means at reducing the amount of redo log your're generating eg transaction auditing etc hth connor --- chaos [EMAIL PROTECTED] wrote: hi, dbas: One of the database i manage face a serious wait event, log file sync. This is a big and busy oltp system, and using disk array of Sun T3 with raid-5. We are using Veritas QuickIO for datafile and redo log files. The pressure on the database is growing fast, and more and more the redo log becomes the bottleneck of the database. Here is some data showing the fact: 2002/05/03 Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- log file sync 82,244 92,442 33.68 db file sequential read 298,301 80,719 29.41 log file parallel write 81,849 36,989 13.48 db file parallel write 5,427 33,615 12.25 control file parallel write 4,6736,1042.22 2002/05/07 Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- log file sync 6,352,383 15,785,313 40.09 db file sequential read 26,862,699 12,538,922 31.85 log file parallel write 5,971,2293,990,066 10.13 db file parallel write 290,4793,164,3918.04 db file scattered read 1,749,137 814,9812.07 2002/05/21 Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- log file sync 2,207,6096,688,751 59.47 log file parallel write 2,044,9771,385,379 12.32 db file parallel write 103,1551,203,077 10.70 db file sequential read 8,772,9081,088,9229.68 log buffer space 3,284 222,6041.98 2002/05/28 Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- log file sync 2,247,585 20,529,779 63.71 db file parallel write 441,0524,377,899 13.59 log file parallel write 1,724,0893,806,535 11.81 db file sequential read 8,854,5252,044,0206.34 enqueue 78,759 592,4111.84 2002/07/04 Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- log file sync 3,838,694 13,158,371 63.28 db file sequential read 2,189,8632,401,275 11.55 log file parallel write 3,401,0352,098,018 10.09 db file parallel write 97,0861,503,6087.23 enqueue 71,251 432,7062.08 2002/07/11 Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time
Re: Oracle on Linux ... Production Strength ???
Christopher Royce wrote: Need Input: I would like to solicit real life experiences, educated opinions, accolades and criticisms from those of you who have implemented or are considering implementing Oracle on Linux in a business critical production environment. We are considering both Red Hat and Suse distributions. We have discovered that regardless of the Linux distribution support is generally expensive. That is not a particularly 'deal breaker' determining factor .. BUT .. I question the quality of support, the expediency of response and the 'sense of urgency' experienced in the event of a critical application being down. I am familiar with limited Oracle-Linux implementations but not to the 'industrial strength' degree that has been proposed (but already implemented) by our requesting user community. Is there a preferred distribution We already have Red Hat and Suse implementations and will choose one of them as the standard 'should we chose to accept this mission'. I believe that both claim to be the preferred distribution by Oracle and that they are 'tier one ports' As for Linux distributions? While Linux is Linux (mostly), you will probably have fewer problems listening to Oracle's recommendation than trying to go it alone using another distro. The reason I say that is, when Oracle tells you that Oracle 8.X or 9.X is certified on RedHat 7.X they mean that, for the most part, if you install *THAT* distro of Linux, you should be able to successfully install Oracle on it. You shouldn't have to worry about tracking down a specific Kernel version to patch your particular favorite distro. And, you shouldn't have to worry about *which* version of the glibc libraries you have to track down and install. Obviously, newer versions may require you to upgrade those things to be certified, but, you shouldn't need to worry so much about them out the door. By thw way, my preferred Distro is Mandrake. (Bear in mind that RedHat was [and may still be] compiled to run on an 80386. Most modern CPU's have additional features that you have to compile your software to use. Mandrake, on the other hand, is pre-compiled for a Pentium.) [NOTE: you can always re-compile the operating system binaries to run on a different type of CPU with either Distro.] My initial implementation is Suse 7.2 Enterprise on an IBM NetFinity, 4 cpu, 2 Gbyte (memory) server using a Net Appliance Filer. There are six instances currently up and running. Thus far there have been no occurrences of swapping or i/o bottlenecks but then the system has yet to be fully 'stressed' and there are scalability concerns. The USER also wants to put Oracle 9iAS on the same box - I have managed to delay that for now, pending further research. I have had a couple of worrying episodes where a file system 'filled up' (on the Filer) that completely 'hung' the system requiring a full system re-boot. Incidentally the aforementioned NetFinity implementation is 'a given' as the six instances have already been migrated from an aged and de-commissioned HP system. I have inherited the results and there is no going back at this juncture. Bear in mind that if you are using a NetApp Filer, you are actually using NFS to access your filesystems. While Oracle's stance on this has changed somewhat (used to be a never, but never, do that, now [thanks to much goading by NetApp and others] it is a 'compatible' solution). In my understanding, the previous position was taken because not all NFS servers start nfslockd and nfsstatd by default. Namely, some version of HP/UX. (They were there, and could be started, but you had to know that you needed them). Personally, accessing my datafiles (or any other Oracle component) via NFS is sub-optimal in my book. NFS is a hideously inefficient protocol and an Oracle Database can create quite a bit of NFS traffic (in the regular world, this would be just disk I/O). Additionally NFS doesn't necessarily handle some things quite as gently as a local filesystem would. (As you noticed, the unavailability of the filesystem can cause the machine to hang). I would recommend investigating several things: 1. Look into your NFS mount options (and any other options you can set with your NFS client). You may be able to change the NFS client's behavior, somewhat, when conditions like a full filesystem occur. 2. Turn off tablespace autoextend. This way, with the exception of archivelogs, your chances of filling up a filesystem are dramatically decreased. You (as a DBA) will actually have to *manage* the growth. (In case you haven't guessed, tablespace autoextend is a bad feature, in my book). 3. Consider disabling the NetApp's snapshot feature. I believe that this is on by default. (And, I'm told, allows some nice point-in-time recoverability. However, I'm not sure how well this works
RE: My TAR button disappeared from MetaLink
Hi,
Your - TAR read and create priv has been removed, that is why you are not able to see
TAR button in left pane.
You contact with your local support and tell them your problem and they will grant you
this privs. I am having administrator right on my company CSI, and I have done this
thing many times. If I don't want a user to give privs of TAR creation or reading,
then I simply remove this privs, and that user not able to see TAR section.
Hope this helps.
Regards,
Sandeep.
-Original Message-
From: Boivin, Patrice J [mailto:[EMAIL PROTECTED]]
Sent: Fri 7/12/2002 8:24 PM
To: Multiple recipients of list ORACLE-L
Cc:
Subject: My TAR button disappeared from MetaLink
I clicked on a URL link in an e-mail from Oracle support, and got an error
message telling me I have no privs to view the document.
I logged into Metalink, and noticed that there is no TAR section in the
right pane, as I usually have.
There also is no TAR button in the left pane, I remember there being one
there but my memory may be wrong...
???
I submitted MetaLink feedback.
Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Boivin, Patrice J
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
W±ë zØ^¡÷âr¥9,BÅm¶ÿà (§Ú©Êëa¢´×¤««, ÑI©ÝyêZjË'{erV
+r5ëp¢¹z»âqëçÎwó9ÔPó9ßÎtçT8'è®xæåÂ'µêçzÖqë,üÆ¢)à.+-±:Ã*.®Ç¥}úèØb²f¢)à+-±éÝjq
+ j)fzËëh.+-êî}«\Ü¢ièµá $ì¥éex(|¸¬´k«¹©Ý{azg¬±¨ àØw%¹×)Þríj)â
+I@ND º+¶§jg¨~f¢)à+-Ê°j{m¡·«zj/y×ë¢f(ºf²j[(±éݶ³Ü¢i×è®az¸§~æjبX¤z˱Êân)à
