create table as select
List , I am using Oracle 9.2.0.1.0 enterprise edn on windows. I have a table with a long datatype column. Is there any way of duplicating this table using the create table ... as select command ? or do I have to use the copy command ? Is the copy command present in 10g ? desc s_warehouse Name Null?Type -- -- .. . ADDRESSLONG . create table test as select * from s_warehouse ; create table test as select * from s_warehouse * ERROR at line 1: ORA-00997: illegal use of LONG datatype -- 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: create table as select
Bulbultyagi My recollection is that with a LONG datatype you cannot use a create table as select, but must use the COPY command. I cannot confirm whether COPY is available in 10g. Sorry. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Sunday, December 07, 2003 8:04 AM To: Multiple recipients of list ORACLE-L List , I am using Oracle 9.2.0.1.0 enterprise edn on windows. I have a table with a long datatype column. Is there any way of duplicating this table using the create table ... as select command ? or do I have to use the copy command ? Is the copy command present in 10g ? desc s_warehouse Name Null?Type -- -- . ADDRESSLONG create table test as select * from s_warehouse ; create table test as select * from s_warehouse * ERROR at line 1: ORA-00997: illegal use of LONG datatype -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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: create table as select
Bulbultyagi, You can also consider to use a tool called OraExp from http://www.agileinfosoftware.com It uses OCI to read long, blob, clob etc and generate insert statements as a SQL script and then you can run this script to load it to your target database, export is pretty fast. I tried the other day and it worked great. Eric - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, December 07, 2003 12:04 Bulbultyagi My recollection is that with a LONG datatype you cannot use a create table as select, but must use the COPY command. I cannot confirm whether COPY is available in 10g. Sorry. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Sunday, December 07, 2003 8:04 AM To: Multiple recipients of list ORACLE-L List , I am using Oracle 9.2.0.1.0 enterprise edn on windows. I have a table with a long datatype column. Is there any way of duplicating this table using the create table ... as select command ? or do I have to use the copy command ? Is the copy command present in 10g ? desc s_warehouse Name Null?Type -- -- . ADDRESSLONG create table test as select * from s_warehouse ; create table test as select * from s_warehouse * ERROR at line 1: ORA-00997: illegal use of LONG datatype -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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: Eric King INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: select via dblink does not use index
Here are all the details: Source database 9.2.0.4 (upgrade from 8.1.6.3.4). Target database 8.1.6.3.4. View definition: create view my_view as select * from [EMAIL PROTECTED] Sql: select * from local_table , my_view where local_table.branch = 1 and my_view.customer = 200 + local_table.branch * 1 + local_table.customer; All tables are analyzed. There are about 300 records in local_table and 1M records in remote_table. My_view.customer is primary key of target_table. Where branch =1 is a set of 65 records. Optimizer_mode=choose in both databases. Explain plan: Hash join between FTS on local table and remote (in/out = serial). Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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).
Re: select via dblink does not use index
Solved. It was a hash join with the smaller table first but it pulled the whole 1M records for this. There was a: where local_table.branch = 1 in the query. I changed it into: where remote_table.branch = 1 (there is an index on remote_table.branch) and it came down to 2 seconds. Thanks all. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 9:49 PM I did a select like: select name from local_table , remote_table where local_table.account = remote_table.account. Remote table is a view with dblink. I select about 100 records out of about 1M records at the remote db. I found out that oracle does full table scan at the remote site. I will welcome ideas how to make oracle use the index on the remote side. Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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.net -- 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).
RE: select via dblink does not use index
Yechial, It's been a couple of years since I worked on tuning queries with db links, but a couple of issues come to mind:is the correct table being used for the inner table of the join, is too much data being sent over the network. -is the correct table being used for the inner table: I remember in earlier versions of Oracle, the local table was always the driving table. I don't know if that is stll the case, but it would be clear from the explain plan. The smaller table (local_table) should be the inner table of your hash join. Of course if this is the case, the full 1M records of the remote_table are being pulled across and compared to the hash table. ( a 10046 trace should help show if this is where the time is going) -is too much data being sent over the network: Assuming your result set is much smaller than the number of records in your remote table, you can run the query on the remote side and then bring back the result set. On the Target database create a view (create view my_remote_view as select * from target_table, [EMAIL PROTECTED] where ...). Kick off the query from your source database using (select * from [EMAIL PROTECTED]). The query is run on the remote side with only the result set passed back. Of course you now have to check if the correct table is being used as the inner table for the hash join (see the first point). If not, a different execution plan might be necessary. There also might be some newer features provided for distributed queries which I haven't had the chance to use yet. Henry -Original Message- Adar Sent: Thursday, December 04, 2003 8:29 AM To: Multiple recipients of list ORACLE-L Here are all the details: Source database 9.2.0.4 (upgrade from 8.1.6.3.4). Target database 8.1.6.3.4. View definition: create view my_view as select * from [EMAIL PROTECTED] Sql: select * from local_table , my_view where local_table.branch = 1 and my_view.customer = 200 + local_table.branch * 1 + local_table.customer; All tables are analyzed. There are about 300 records in local_table and 1M records in remote_table. My_view.customer is primary key of target_table. Where branch =1 is a set of 65 records. Optimizer_mode=choose in both databases. Explain plan: Hash join between FTS on local table and remote (in/out = serial). Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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.net -- Author: Poras, Henry R. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: select via dblink does not use index
What are the Oracle versions, settings for optimizer_mode and full execution plans. Can you clarify Remote table is a view with dblink. Do you mean your query references a local view which is a select from a remote table; or does your query reference a view at a remote site which is a simple select from a table at that site. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 7:49 PM I did a select like: select name from local_table , remote_table where local_table.account = remote_table.account. Remote table is a view with dblink. I select about 100 records out of about 1M records at the remote db. I found out that oracle does full table scan at the remote site. I will welcome ideas how to make oracle use the index on the remote side. Yechiel Adar Mehish -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: granting SELECT privilege on SYS.X$ TABLES
Check out Jacques e-mail address - he *works* for Quest ;) -Original Message- Wolfgang Breitling Sent: 15 November 2003 07:04 To: Multiple recipients of list ORACLE-L Someone must have created sys.x_$ views on some of the sys.x$ tables. Installing statspack does that for example for X$KCBFWAIT, X$KSPPSV, X$KSPPI, and X$KSQST. Do you have quest? I believe it does it for some of the x$ tables as well. I routinely do it for all x$ tables in my test databases and grant select to the select_catalog_role. Then I can access the x$tables without having to log on as sys. In my test databases I always At 07:14 PM 11/14/2003, you wrote: P.S. I forgot to mention that in all the databases (including the 8.1.7 databases) in which I tried this, init parameter O7_DICTIONARY_ACCESSIBILITY was set to FALSE. I always thought that one could not grant SELECT privilege on the SYS.X$ tables, and to make them accessible to another user one would have to create a view on the table (as mentioned on Steve Adams' ixora website: http://www.ixora.com.au/scripts/prereq.htm create_xviews.sql) However someone told me recently that you could grant SELECT on sys.X_$... When I tried this, I saw results that confused me. In Database A, Oracle 8.1.7.4.1, Windows 2000 server, I was able to 1- grant select on SYS.X_$KTFBFE to another_user ; 2- grant select on SYS.X_$KTFBHC to another_user ; 3- grant select on SYS.X_$KTFBUE to another_user ; BUT 4- grant select on SYS.X_$KDXST to another_user ; returns ORA-00942 table or view does not exist. In database B, using the same ORACLE_HOME as database A (i.e. identical Oracle version and OS) even the first three grant statements returned ORA-00942 When I tried it on more recent Oracle databases on Windows / SunOS servers, it worked intermittently: Oracle 9.0 (SunOS): all GRANTS failed Oracle 9.2 (SunOS): GRANTS 1-3 were successful, GRANT 4 failed Oracle 10.1 beta (Windows 2000): all GRANTS failed Does anyone know the reason for this strange behaviour? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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). Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.537 / Virus Database: 332 - Release Date: 06/11/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.537 / Virus Database: 332 - Release Date: 06/11/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith 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: granting SELECT privilege on SYS.X$ TABLES
Yet another case of the right hand not knowing what the left hand is doing. Now I'm curious to find out which team is creating those x_$ views. -Original Message- Mark Leith Check out Jacques e-mail address - he *works* for Quest ;) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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: granting SELECT privilege on SYS.X$ TABLES
Boy do I feel stupid! Thank you Mr. Khedr and Mr. Breitling. I know the views are there, but at first I thought that maybe there were being created as part of the database creation process in some cases. In the first database I looked at, the X_$ views had the same creation date as the database creation date, but then one of the programmers here might have used a Quest tool on the database on the same day it was created. Now I see that in the other databases that have those views, the views were created later. I'll have to find out which product does that, most of the quest products create objects staring with QUEST (e.g. QUEST_XXX_X$KTFBUE) - actually AFAIK it's the rule for any object created in the database - but it does seem like there's one that doesn't use that naming convention. -Original Message- Wolfgang Breitling Someone must have created sys.x_$ views on some of the sys.x$ tables. Installing statspack does that for example for X$KCBFWAIT, X$KSPPSV, X$KSPPI, and X$KSQST. Do you have quest? I believe it does it for some of the x$ tables as well. I routinely do it for all x$ tables in my test databases and grant select to the select_catalog_role. Then I can access the x$tables without having to log on as sys. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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: granting SELECT privilege on SYS.X$ TABLES
Someone must have created sys.x_$ views on some of the sys.x$ tables. Installing statspack does that for example for X$KCBFWAIT, X$KSPPSV, X$KSPPI, and X$KSQST. Do you have quest? I believe it does it for some of the x$ tables as well. I routinely do it for all x$ tables in my test databases and grant select to the select_catalog_role. Then I can access the x$tables without having to log on as sys. In my test databases I always At 07:14 PM 11/14/2003, you wrote: P.S. I forgot to mention that in all the databases (including the 8.1.7 databases) in which I tried this, init parameter O7_DICTIONARY_ACCESSIBILITY was set to FALSE. I always thought that one could not grant SELECT privilege on the SYS.X$ tables, and to make them accessible to another user one would have to create a view on the table (as mentioned on Steve Adams' ixora website: http://www.ixora.com.au/scripts/prereq.htm create_xviews.sql) However someone told me recently that you could grant SELECT on sys.X_$... When I tried this, I saw results that confused me. In Database A, Oracle 8.1.7.4.1, Windows 2000 server, I was able to 1- grant select on SYS.X_$KTFBFE to another_user ; 2- grant select on SYS.X_$KTFBHC to another_user ; 3- grant select on SYS.X_$KTFBUE to another_user ; BUT 4- grant select on SYS.X_$KDXST to another_user ; returns ORA-00942 table or view does not exist. In database B, using the same ORACLE_HOME as database A (i.e. identical Oracle version and OS) even the first three grant statements returned ORA-00942 When I tried it on more recent Oracle databases on Windows / SunOS servers, it worked intermittently: Oracle 9.0 (SunOS): all GRANTS failed Oracle 9.2 (SunOS): GRANTS 1-3 were successful, GRANT 4 failed Oracle 10.1 beta (Windows 2000): all GRANTS failed Does anyone know the reason for this strange behaviour? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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). Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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).
granting SELECT privilege on SYS.X$ TABLES
I always thought that one could not grant SELECT privilege on the SYS.X$ tables, and to make them accessible to another user one would have to create a view on the table (as mentioned on Steve Adams' ixora website: http://www.ixora.com.au/scripts/prereq.htm create_xviews.sql) However someone told me recently that you could grant SELECT on sys.X_$... When I tried this, I saw results that confused me. In Database A, Oracle 8.1.7.4.1, Windows 2000 server, I was able to 1- grant select on SYS.X_$KTFBFE to another_user ; 2- grant select on SYS.X_$KTFBHC to another_user ; 3- grant select on SYS.X_$KTFBUE to another_user ; BUT 4- grant select on SYS.X_$KDXST to another_user ; returns ORA-00942 table or view does not exist. In database B, using the same ORACLE_HOME as database A (i.e. identical Oracle version and OS) even the first three grant statements returned ORA-00942 When I tried it on more recent Oracle databases on Windows / SunOS servers, it worked intermittently: Oracle 9.0 (SunOS): all GRANTS failed Oracle 9.2 (SunOS): GRANTS 1-3 were successful, GRANT 4 failed Oracle 10.1 beta (Windows 2000): all GRANTS failed Does anyone know the reason for this strange behaviour? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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: granting SELECT privilege on SYS.X$ TABLES
You need to check if the SYS.X_$tables are views or tables. I believe they are views for the X$Tables Waleed -Original Message- Sent: Friday, November 14, 2003 8:54 PM To: Multiple recipients of list ORACLE-L I always thought that one could not grant SELECT privilege on the SYS.X$ tables, and to make them accessible to another user one would have to create a view on the table (as mentioned on Steve Adams' ixora website: http://www.ixora.com.au/scripts/prereq.htm create_xviews.sql) However someone told me recently that you could grant SELECT on sys.X_$... When I tried this, I saw results that confused me. In Database A, Oracle 8.1.7.4.1, Windows 2000 server, I was able to 1- grant select on SYS.X_$KTFBFE to another_user ; 2- grant select on SYS.X_$KTFBHC to another_user ; 3- grant select on SYS.X_$KTFBUE to another_user ; BUT 4- grant select on SYS.X_$KDXST to another_user ; returns ORA-00942 table or view does not exist. In database B, using the same ORACLE_HOME as database A (i.e. identical Oracle version and OS) even the first three grant statements returned ORA-00942 When I tried it on more recent Oracle databases on Windows / SunOS servers, it worked intermittently: Oracle 9.0 (SunOS): all GRANTS failed Oracle 9.2 (SunOS): GRANTS 1-3 were successful, GRANT 4 failed Oracle 10.1 beta (Windows 2000): all GRANTS failed Does anyone know the reason for this strange behaviour? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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: 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).
RE: granting SELECT privilege on SYS.X$ TABLES
P.S. I forgot to mention that in all the databases (including the 8.1.7 databases) in which I tried this, init parameter O7_DICTIONARY_ACCESSIBILITY was set to FALSE. I always thought that one could not grant SELECT privilege on the SYS.X$ tables, and to make them accessible to another user one would have to create a view on the table (as mentioned on Steve Adams' ixora website: http://www.ixora.com.au/scripts/prereq.htm create_xviews.sql) However someone told me recently that you could grant SELECT on sys.X_$... When I tried this, I saw results that confused me. In Database A, Oracle 8.1.7.4.1, Windows 2000 server, I was able to 1- grant select on SYS.X_$KTFBFE to another_user ; 2- grant select on SYS.X_$KTFBHC to another_user ; 3- grant select on SYS.X_$KTFBUE to another_user ; BUT 4- grant select on SYS.X_$KDXST to another_user ; returns ORA-00942 table or view does not exist. In database B, using the same ORACLE_HOME as database A (i.e. identical Oracle version and OS) even the first three grant statements returned ORA-00942 When I tried it on more recent Oracle databases on Windows / SunOS servers, it worked intermittently: Oracle 9.0 (SunOS): all GRANTS failed Oracle 9.2 (SunOS): GRANTS 1-3 were successful, GRANT 4 failed Oracle 10.1 beta (Windows 2000): all GRANTS failed Does anyone know the reason for this strange behaviour? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Select ?
When I used query SELECT id FROM test_table WHERE LENGTH(testcolu) = 0; I received error like ORA-00932: inconsistent datatypes I'm on 8163. Let me know if you have any thoughts. I want to findout those columns which are updated thru empty_clob() functions or those columns which are showing NULL during select but those columns were updated either thru EMPTY_CLOB() or ' '.? thx- Seema From: Vladimir Begun [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Select ? Date: Mon, 10 Nov 2003 10:19:25 -0800 SELECT id FROM test_table WHERE LENGTH(testcolu) = 0; -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Seema Singh wrote: What SQL I have to use.Is there any way can i know what are those columns were updated thru EMPTY_CLOB() function? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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). _ MSN Shopping upgraded for the holidays! Snappier product search... http://shopping.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Select ?
I would suggest to read the documentation. You changed the original question now you want to see something else. SQL CREATE TABLE test_table (id NUMBER, testcolu CLOB); Table created. SQL INSERT INTO test_table VALUES(1, 'test'); 1 row created. SQL INSERT INTO test_table VALUES(2, ' '); 1 row created. SQL INSERT INTO test_table VALUES(3, NULL); 1 row created. SQL INSERT INTO test_table VALUES(4, EMPTY_CLOB()); 1 row created. SQL COMMIT; 1 SELECT id, testcolu 2FROM test_table 3 WHERE dbms_lob.getlength(testcolu) = 0 4* OR (dbms_lob.getlength(testcolu) = 1 AND dbms_lob.instr(testcolu, ' ') = 1) SQL / ID TESTCOLU - -- 2 4 If the columns updated to ' ' it can not show NULL during select. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Seema Singh wrote: When I used query SELECT id FROM test_table WHERE LENGTH(testcolu) = 0; I received error like ORA-00932: inconsistent datatypes I'm on 8163. Let me know if you have any thoughts. I want to findout those columns which are updated thru empty_clob() functions or those columns which are showing NULL during select but those columns were updated either thru EMPTY_CLOB() or ' '.? thx- Seema From: Vladimir Begun [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Select ? Date: Mon, 10 Nov 2003 10:19:25 -0800 SELECT id FROM test_table WHERE LENGTH(testcolu) = 0; -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Seema Singh wrote: What SQL I have to use.Is there any way can i know what are those columns were updated thru EMPTY_CLOB() function? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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). _ MSN Shopping upgraded for the holidays! Snappier product search... http://shopping.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Select ?
thanks a lot Vladimir. From: Vladimir Begun [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Select ? Date: Thu, 13 Nov 2003 10:49:25 -0800 I would suggest to read the documentation. You changed the original question now you want to see something else. SQL CREATE TABLE test_table (id NUMBER, testcolu CLOB); Table created. SQL INSERT INTO test_table VALUES(1, 'test'); 1 row created. SQL INSERT INTO test_table VALUES(2, ' '); 1 row created. SQL INSERT INTO test_table VALUES(3, NULL); 1 row created. SQL INSERT INTO test_table VALUES(4, EMPTY_CLOB()); 1 row created. SQL COMMIT; 1 SELECT id, testcolu 2FROM test_table 3 WHERE dbms_lob.getlength(testcolu) = 0 4* OR (dbms_lob.getlength(testcolu) = 1 AND dbms_lob.instr(testcolu, ' ') = 1) SQL / ID TESTCOLU - -- 2 4 If the columns updated to ' ' it can not show NULL during select. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Seema Singh wrote: When I used query SELECT id FROM test_table WHERE LENGTH(testcolu) = 0; I received error like ORA-00932: inconsistent datatypes I'm on 8163. Let me know if you have any thoughts. I want to findout those columns which are updated thru empty_clob() functions or those columns which are showing NULL during select but those columns were updated either thru EMPTY_CLOB() or ' '.? thx- Seema From: Vladimir Begun [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Select ? Date: Mon, 10 Nov 2003 10:19:25 -0800 SELECT id FROM test_table WHERE LENGTH(testcolu) = 0; -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Seema Singh wrote: What SQL I have to use.Is there any way can i know what are those columns were updated thru EMPTY_CLOB() function? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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). _ MSN Shopping upgraded for the holidays! Snappier product search... http://shopping.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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). _ Crave some Miles Davis or Grateful Dead? Your old favorites are always playing on MSN Radio Plus. Trial month free! http://join.msn.com/?page=offers/premiumradio -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Select ?
SELECT id FROM test_table WHERE LENGTH(testcolu) = 0; -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Seema Singh wrote: What SQL I have to use.Is there any way can i know what are those columns were updated thru EMPTY_CLOB() function? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Select ?
What SQL I have to use.Is there any way can i know what are those columns were updated thru EMPTY_CLOB() function? Thx -Seema From: Vladimir Begun [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Select ? Date: Sat, 08 Nov 2003 22:09:25 -0800 get length of each of those you will see which one you need. length of #4 should be 0. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Seema Singh wrote: Hi, If we have table with clob column and want to findout whcih clob column rows has been updated/inilialised thru empty_clob() functions? How to do that? Like table with 2 columns ID and testcolu .ID is desc test_table Name Null? Type - ID NUMBER(16) TESTCOLU CLOB Having rows like ID TESTCOLU -- 1 2 3 4 99 Out of these 5 rows id# 4 were inilialised thru EMPTY_CLOB() function.Wondering which sql statement would pickup only those rows having id value 4 . The ID 3 was inilialised thru NULL and 99 was with ' '. thanks in advance. -Seema -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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). _ Is your computer infected with a virus? Find out with a FREE computer virus scan from McAfee. Take the FreeScan now! http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh 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).
Select ?
Hi, If we have table with clob column and want to findout whcih clob column rows has been updated/inilialised thru empty_clob() functions? How to do that? Like table with 2 columns ID and testcolu .ID is desc test_table Name Null? Type - ID NUMBER(16) TESTCOLU CLOB Having rows like ID TESTCOLU -- 1 2 3 4 99 Out of these 5 rows id# 4 were inilialised thru EMPTY_CLOB() function.Wondering which sql statement would pickup only those rows having id value 4 . The ID 3 was inilialised thru NULL and 99 was with ' '. thanks in advance. -Seema _ Compare high-speed Internet plans, starting at $26.95. https://broadband.msn.com (Prices may vary by service area.) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Select ?
get length of each of those you will see which one you need. length of #4 should be 0. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Seema Singh wrote: Hi, If we have table with clob column and want to findout whcih clob column rows has been updated/inilialised thru empty_clob() functions? How to do that? Like table with 2 columns ID and testcolu .ID is desc test_table Name Null?Type - ID NUMBER(16) TESTCOLU CLOB Having rows like ID TESTCOLU -- 1 2 3 4 99 Out of these 5 rows id# 4 were inilialised thru EMPTY_CLOB() function.Wondering which sql statement would pickup only those rows having id value 4 . The ID 3 was inilialised thru NULL and 99 was with ' '. thanks in advance. -Seema -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: performance issue on select count(*)
Tim, Thanks for your reply. The select count(*) is doing an index range scan on the column tid. No table access in the execution plan. The query you provided returned the following result: NUM_ROWS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY LAST_ANAL - --- --- - 2326064.1 161201 1 3 28-OCT-03 I have tried local partition index on tid but the execution time was still around 10secs for the initial execution and 1sec subsequently. The table is partitioned on a date field. I would be interested to know if there is a way to speed up the initial execution or how to diagnose what the delay was. It does not seems right that there is such a big difference in elapsed time between the initial and subsequent execution. I monitored the wait events during both executions. They were all pretty low. It does not appear to be I/O bound either. tnsping from my PC to the database took about 30msec. Any other suggestions what I could check? Thanks. linda select * from v$session_event where sid=98; Initial run: SID EVENTTOTAL TOTAL TIME AVERAGE MAX WAITS TIMEOUTS WAITED WAIT WAIT --- - -- 98 latch free 115 681 .008695652 1 98 control file sequential read 300 0 0 98 refresh controlfile command 100 0 0 98 buffer busy waits 100 0 0 98 log file sync 101 1 1 98 db file sequential read 1968 0 827 .42022357710 98 file open 502 .4 1 98 SQL*Net message to client 305 00 0 0 98 SQL*Net message from client 3040 31819 104.667763 29911 Subsequent run: - SID EVENT TOTAL TOTAL TIME AVERAGE MAX WAITS TIMEOUTS WAITED WAIT WAIT --- - -- 99 latch free 162 93 3 .018518519 2 99 control file sequential read 3 0 0 0 0 99 refresh controlfile command1 0 0 0 0 99 buffer busy waits 1 00 0 0 99 log file sync 1 00 0 0 99 file open 3 01 .3 1 99 SQL*Net message to client 54 00 0 0 99 SQL*Net message from client53 02893 54.5849057 2698 From: Tim Gorman [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: performance issue on select count(*) Date: Mon, 27 Oct 2003 10:34:59 -0800 Linda, I am guessing that since your table is partitioned on an unspecified date column, that the index on TID is either LOCAL or non-partitioned (i.e. GLOBAL). If it is LOCAL (you would have had to specify the keyword, as it is not the default), then you will be performing indexed RANGE scans on each of the partitions in the index. Naturally, the more partitions there are, the longer this may take, but probably not a great deal longer than if the index was a GLOBAL non-partitioned index. But regardless of the number of RANGE scans and the type of index it is, the main question is whether TID is a good index to use in the first place. This is a matter of data, purely the nature of the data. You can diagnose this better using results from the following query: SELECT NUM_ROWS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, LAST_ANALYZED FROMDBA_INDEXES WHERE INDEX_NAME = 'name-of-index'; Of particular interest are the values in AVG_LEAF_BLOCKS_PER_KEY and AVG_DATA_BLOCKS_PER_KEY, as the cost-based optimizer uses these to calculate the cost of an index RANGE scan (assuming that column-level statistics or histograms have not been gathered). If the values of these two columns are high, then the CBO will be hesitant to use
Re: performance issue on select count(*)
The symptom suggests caching is a big factor here - most likely block-buffers. Contrary to ?current? popular beliefs, BCHR is still a very relevant performance indicator - either being very high, or being too low - both of which gives a good indication of something that needs to be looked at. I would be interested to know if there is a way to speed up the initial execution or how to diagnose what the delay was. It does not seems right that there is such a big difference in elapsed time between the initial and subsequent execution. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Binley Lim 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: performance issue on select count(*)
So, what exactly is indicated by a high or low hit rate? What, exactly, is high and what do you consider low? What HR are you talking about? This would be the infamous BCHR: select 'bc_hit_ratio' ratio,( sum(decode(name, 'consistent gets',value,0)) + sum(decode(name,'db block gets', value,0)) - sum(decode(name,'physical reads', value,0))) / ( sum(decode(name, 'consistent gets',value,0)) + sum(decode(name,'db block gets', value,0)) ) * 100 from v$sysstat What exactly should the number returned by this query tell me? On 10/28/2003 10:59:25 AM, Binley Lim wrote: The symptom suggests caching is a big factor here - most likely block-buffers. Contrary to ?current? popular beliefs, BCHR is still a very relevant performance indicator - either being very high, or being too low - both of which gives a good indication of something that needs to be looked at. I would be interested to know if there is a way to speed up the initial execution or how to diagnose what the delay was. It does not seems right that there is such a big difference in elapsed time between the initial and subsequent execution. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Binley Lim 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). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: Re: performance issue on select count(*)
yes bchr is only useful at extremes, but its based on interpretation. if you have a very high BCHR, you probably have alot of very bad sql. if you have a very low one AND are in a type of application where you should(namely OLTP) you may want to consider increasing your buffer cache. mladen is right. there is no 'exact' very high and very low. you have to interpret it. that is about it. Anyone who uses it for anymore than that is wrong. From: Mladen Gogala [EMAIL PROTECTED] Date: 2003/10/28 Tue PM 12:09:34 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: performance issue on select count(*) So, what exactly is indicated by a high or low hit rate? What, exactly, is high and what do you consider low? What HR are you talking about? This would be the infamous BCHR: select 'bc_hit_ratio' ratio,( sum(decode(name, 'consistent gets',value,0)) + sum(decode(name,'db block gets', value,0)) - sum(decode(name,'physical reads', value,0))) / ( sum(decode(name, 'consistent gets',value,0)) + sum(decode(name,'db block gets', value,0)) ) * 100 from v$sysstat What exactly should the number returned by this query tell me? On 10/28/2003 10:59:25 AM, Binley Lim wrote: The symptom suggests caching is a big factor here - most likely block-buffers. Contrary to ?current? popular beliefs, BCHR is still a very relevant performance indicator - either being very high, or being too low - both of which gives a good indication of something that needs to be looked at. I would be interested to know if there is a way to speed up the initial execution or how to diagnose what the delay was. It does not seems right that there is such a big difference in elapsed time between the initial and subsequent execution. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Binley Lim 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). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: performance issue on select count(*)
An unusually high BCHR could be an indicator that your database is running Connor McDonald's choose_a_hit_ratio procedure. http://www.oracledba.co.uk/tips/choose.htm Jared Mladen Gogala [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/28/2003 09:09 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: performance issue on select count(*) So, what exactly is indicated by a high or low hit rate? What, exactly, is high and what do you consider low? What HR are you talking about? This would be the infamous BCHR: select 'bc_hit_ratio' ratio,( sum(decode(name, 'consistent gets',value,0)) + sum(decode(name,'db block gets', value,0)) - sum(decode(name,'physical reads', value,0))) / ( sum(decode(name, 'consistent gets',value,0)) + sum(decode(name,'db block gets', value,0)) ) * 100 from v$sysstat What exactly should the number returned by this query tell me? On 10/28/2003 10:59:25 AM, Binley Lim wrote: The symptom suggests caching is a big factor here - most likely block-buffers. Contrary to ?current? popular beliefs, BCHR is still a very relevant performance indicator - either being very high, or being too low - both of which gives a good indication of something that needs to be looked at. I would be interested to know if there is a way to speed up the initial execution or how to diagnose what the delay was. It does not seems right that there is such a big difference in elapsed time between the initial and subsequent execution. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Binley Lim INET: [EMAIL PROTECTED]
Re: Re: performance issue on select count(*)
Exactly my point! ;-) You cannot use it for anymore than that, and neither should you ignore it completely. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 6:39 AM yes bchr is only useful at extremes, but its based on interpretation. if you have a very high BCHR, you probably have alot of very bad sql. if you have a very low one AND are in a type of application where you should(namely OLTP) you may want to consider increasing your buffer cache. mladen is right. there is no 'exact' very high and very low. you have to interpret it. that is about it. Anyone who uses it for anymore than that is wrong. From: Mladen Gogala [EMAIL PROTECTED] Date: 2003/10/28 Tue PM 12:09:34 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: performance issue on select count(*) So, what exactly is indicated by a high or low hit rate? What, exactly, is high and what do you consider low? What HR are you talking about? This would be the infamous BCHR: select 'bc_hit_ratio' ratio,( sum(decode(name, 'consistent gets',value,0)) + sum(decode(name,'db block gets', value,0)) - sum(decode(name,'physical reads', value,0))) / ( sum(decode(name, 'consistent gets',value,0)) + sum(decode(name,'db block gets', value,0)) ) * 100 from v$sysstat What exactly should the number returned by this query tell me? On 10/28/2003 10:59:25 AM, Binley Lim wrote: The symptom suggests caching is a big factor here - most likely block-buffers. Contrary to ?current? popular beliefs, BCHR is still a very relevant performance indicator - either being very high, or being too low - both of which gives a good indication of something that needs to be looked at. I would be interested to know if there is a way to speed up the initial execution or how to diagnose what the delay was. It does not seems right that there is such a big difference in elapsed time between the initial and subsequent execution. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Binley Lim 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). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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
performance issue on select count(*)
Hi, I have an online application that does a 'select count(*)' on a few tables. The 'select counts' always runs slow (about 10secs) for the first time and then fast again ( 1sec) after subsequent accesses. The query runs slow again when the data is flushed out of the buffer cache. 10046 trace shows that the query takes a long time whenever there are disk accesses to fetch the data (about 1000 8K) into db cache. It should not take that long to fetch 1000 8K blocks into the cache and I/O does not appear to be the problem. Anyone has any idea what the problem may be or how I can speed up my query? DB: 8.1.7.4 query: select count(*) from tickets where tid='value1'; where tickets has about 2 million records partition on a date field. and tid is indexed. thanks. linda _ Enjoy MSN 8 patented spam control and more with MSN 8 Dial-up Internet Service. Try it FREE for one month! http://join.msn.com/?page=dept/dialup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Linda Wang 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: performance issue on select count(*)
Linda, I guess that the key word is 'partition'. This type of query should not require to access the table if (hopefully) tid is indexed. If the index on tid is also partitioned, all index partitions have to be searched. My feeling is that in such a case what should run faster is some parallel fast full scan. Does your execution plan show this type of process or something wildly different ? SF - --- Original Message --- - From: Linda Wang [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Mon, 27 Oct 2003 05:24:32 Hi, I have an online application that does a 'select count(*)' on a few tables. The 'select counts' always runs slow (about 10secs) for the first time and then fast again ( 1sec) after subsequent accesses. The query runs slow again when the data is flushed out of the buffer cache. 10046 trace shows that the query takes a long time whenever there are disk accesses to fetch the data (about 1000 8K) into db cache. It should not take that long to fetch 1000 8K blocks into the cache and I/O does not appear to be the problem. Anyone has any idea what the problem may be or how I can speed up my query? DB: 8.1.7.4 query: select count(*) from tickets where tid='value1'; where tickets has about 2 million records partition on a date field. and tid is indexed. thanks. linda -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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: performance issue on select count(*)
Stephane, the execution plan for the statement is an index range scan on tid. It did not access the table. index is not partitioned. I will testpartitioning the index and with the parallel fast full scan. Anyone else has any other suggestions? Thanks. linda From: Stephane Faroult [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: performance issue on select count(*) Date: Mon, 27 Oct 2003 05:49:24 -0800 Linda, I guess that the key word is 'partition'. This type of query should not require to access the table if (hopefully) tid is indexed. If the index on tid is also partitioned, all index partitions have to be searched. My feeling is that in such a case what should run faster is some parallel fast full scan. Does your execution plan show this type of process or something wildly different ? SF - --- Original Message --- - From: Linda Wang [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Mon, 27 Oct 2003 05:24:32 Hi, I have an online application that does a 'select count(*)' on a few tables. The 'select counts' always runs slow (about 10secs) for the first time and then fast again ( 1sec) after subsequent accesses. The query runs slow again when the data is flushed out of the buffer cache. 10046 trace shows that the query takes a long time whenever there are disk accesses to fetch the data (about 1000 8K) into db cache. It should not take that long to fetch 1000 8K blocks into the cache and I/O does not appear to be the problem. Anyone has any idea what the problem may be or how I can speed up my query? DB: 8.1.7.4 query: select count(*) from tickets where tid='value1'; where tickets has about 2 million records partition on a date field. and tid is indexed. thanks. linda -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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). _ See when your friends are online with MSN Messenger 6.0. Download it now FREE! http://msnmessenger-download.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Linda Wang 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: performance issue on select count(*)
Linda, I am guessing that since your table is partitioned on an unspecified date column, that the index on TID is either LOCAL or non-partitioned (i.e. GLOBAL). If it is LOCAL (you would have had to specify the keyword, as it is not the default), then you will be performing indexed RANGE scans on each of the partitions in the index. Naturally, the more partitions there are, the longer this may take, but probably not a great deal longer than if the index was a GLOBAL non-partitioned index. But regardless of the number of RANGE scans and the type of index it is, the main question is whether TID is a good index to use in the first place. This is a matter of data, purely the nature of the data. You can diagnose this better using results from the following query: SELECT NUM_ROWS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, LAST_ANALYZED FROMDBA_INDEXES WHERE INDEX_NAME = 'name-of-index'; Of particular interest are the values in AVG_LEAF_BLOCKS_PER_KEY and AVG_DATA_BLOCKS_PER_KEY, as the cost-based optimizer uses these to calculate the cost of an index RANGE scan (assuming that column-level statistics or histograms have not been gathered). If the values of these two columns are high, then the CBO will be hesitant to use the index, and with good reason. Thus, with the use of the index rejected as an option, you'll of course have a FULL table scan on your hands. There is probably more to it, but this should be a start. Feel free to post the results of the query above to the list, if you wish... Hope this helps... -Tim on 10/27/03 6:24 AM, Linda Wang at [EMAIL PROTECTED] wrote: Hi, I have an online application that does a 'select count(*)' on a few tables. The 'select counts' always runs slow (about 10secs) for the first time and then fast again ( 1sec) after subsequent accesses. The query runs slow again when the data is flushed out of the buffer cache. 10046 trace shows that the query takes a long time whenever there are disk accesses to fetch the data (about 1000 8K) into db cache. It should not take that long to fetch 1000 8K blocks into the cache and I/O does not appear to be the problem. Anyone has any idea what the problem may be or how I can speed up my query? DB: 8.1.7.4 query: select count(*) from tickets where tid='value1'; where tickets has about 2 million records partition on a date field. and tid is indexed. thanks. linda _ Enjoy MSN 8 patented spam control and more with MSN 8 Dial-up Internet Service. Try it FREE for one month! http://join.msn.com/?page=dept/dialup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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).
Sql query : select max timestamp value from table
I have multiple timestamps values for single ip in a table, I need the max(timestamp)for each ip I select out. example: timestamp ip 2003-09-29 13:20:23 68.209.182.42003-09-29 13:20:44 68.209.182.42003-10-02 12:53:38 68.209.182.42003-10-02 12:35:06 68.75.94.1582003-10-02 12:52:03 68.97.33.69 Thus select distinct ip, max(timestamp) from table group by ip, timestamp; returns every timestamp value per ip. Any ideas on how to get only the max(timestamp) for each ip?
Re: Sql query : select max timestamp value from table
Johan, First, you don't need the distinct. The proper query will return 1 row per ip. Second, take the max(timestamp) out of the group by. That is causing the problem. Daniel Johan Muller wrote: I have multiple timestamps values for single ip in a table, I need the max(timestamp) for each ip I select out. example: timestamp ip 2003-09-29 13:20:2368.209.182.4 2003-09-29 13:20:4468.209.182.4 2003-10-02 12:53:3868.209.182.4 2003-10-02 12:35:0668.75.94.158 2003-10-02 12:52:0368.97.33.69 Thus select distinct ip, max(timestamp) from table group by ip, timestamp; returns every timestamp value per ip. Any ideas on how to get only the max(timestamp) for each ip? begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard
RE: Sql query : select max timestamp value from table
select ip, max(timestamp) from table group by ip; -Original Message-From: Johan Muller [mailto:[EMAIL PROTECTED]Sent: Thursday, October 02, 2003 10:45 AMTo: Multiple recipients of list ORACLE-LSubject: Sql query : select max timestamp value from table I have multiple timestamps values for single ip in a table, I need the max(timestamp)for each ip I select out. example: timestamp ip 2003-09-29 13:20:23 68.209.182.42003-09-29 13:20:44 68.209.182.42003-10-02 12:53:38 68.209.182.42003-10-02 12:35:06 68.75.94.1582003-10-02 12:52:03 68.97.33.69 Thus select distinct ip, max(timestamp) from table group by ip, timestamp; returns every timestamp value per ip. Any ideas on how to get only the max(timestamp) for each ip?
RE: Sql query : select max timestamp value from table
Title: RE: Sql query : select max timestamp value from table select ip, max(timestamp) from table group by ip; Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Johan Muller [SMTP:[EMAIL PROTECTED] I have multiple timestamps values  for single ip in a table, I need the max(timestamp)  for each ip I select out. example: timestamp ip 2003-09-29 13:20:23   68.209.182.4 2003-09-29 13:20:44   68.209.182.4 2003-10-02 12:53:38   68.209.182.4 2003-10-02 12:35:06   68.75.94.158 2003-10-02 12:52:03   68.97.33.69 Thus select distinct ip, max(timestamp) from table group by ip, timestamp; returns every timestamp value per ip. Any ideas on how to get only the max(timestamp) for each ip?
RE: Sql query : select max timestamp value from table
Johann, Take the timestamp out of your group by. Cheers, Melanie *** Melanie Caffrey Proximo Consulting Services, Inc. [EMAIL PROTECTED] (212) 686-6004 Ext. 32 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Johan Muller Sent: Thursday, October 02, 2003 10:45 AM To: Multiple recipients of list ORACLE-L Subject: Sql query : select max timestamp value from table I have multiple timestamps values for single ip in a table, I need the max(timestamp)for each ip I select out. example: timestamp ip 2003-09-29 13:20:23 68.209.182.4 2003-09-29 13:20:44 68.209.182.4 2003-10-02 12:53:38 68.209.182.4 2003-10-02 12:35:06 68.75.94.158 2003-10-02 12:52:03 68.97.33.69 Thus select distinct ip, max(timestamp) from table group by ip, timestamp; returns every timestamp value per ip. Any ideas on how to get only the max(timestamp) for each ip?
RE: Sql query : select max timestamp value from table
Title: Message select ip, max(timestamp) from table group by ip; -Original Message-From: Johan Muller [mailto:[EMAIL PROTECTED] Sent: Thursday, October 02, 2003 10:45 AMTo: Multiple recipients of list ORACLE-LSubject: Sql query : select max timestamp value from table I have multiple timestamps values for single ip in a table, I need the max(timestamp)for each ip I select out. example: timestamp ip 2003-09-29 13:20:23 68.209.182.42003-09-29 13:20:44 68.209.182.42003-10-02 12:53:38 68.209.182.42003-10-02 12:35:06 68.75.94.1582003-10-02 12:52:03 68.97.33.69 Thus select distinct ip, max(timestamp) from table group by ip, timestamp; returns every timestamp value per ip. Any ideas on how to get only the max(timestamp) for each ip?
RE: RE: Create Table..As Select: Number formats
I'm perhaps a little late replying, but have you looked at using alter table xxx modify (column number(1,2)); for example to modify the number format after creating the partition. I'm expecting there to be limits but it sounds like you are only trying to increase the number type size not decrease it. Also, you might wear some table scans while it inspects data depending on the change but might be acceptable depending on volumes and timeframes. [EMAIL PROTECTED] isys.co.uk To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: RE: Create Table..As Select: Number formats .com 02/10/2003 03:09 Please respond to ORACLE-L Thanks to everyone who responded to this thread - Option A is now to persuade the designers to remove the Number formatting from the parttioned table, Option B is to pre-create the working table and populate it with Truncate and Insert /* Append */ Option B will be slower, I think, due to the extra redo/undo generated despite my best efforts to persuade it otherwise. I've done enough tests with changing the format of the number columns to convince myself that that the calculated Number columns are the only remaining issue. Cheers Simon Anderson I'm trying to create a table using 'Create Table...As Select...' ... Are you sure that it comes from the NUMBER() columns? Reminds me of the problem when you have a NULL in a UNION, which must be explicitly cast with a to_number(), to_date() or to_char(). Might it come from some NULL in your CREATE TABLE AS SELECT ... ? Regards, Stephane Faroult Oriole -- 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). 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. 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 a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999. Please advise
Create Table..As Select: Number formats
I'm trying to create a table using 'Create Table...As Select...' The contents will then be exchanged into a partitioned table, so they need to have the same names and datatypes. Some of the columns in the created table are populated with zeroes will be updated after the partition exchange, one of the columns is populated with a decode that returns a single digit number. The problem is that I need particular number formats in these columns - the one-digit column should be a number(1,0) and the others should be number(12,4). I can't specify column types in create table...as select, so how else can I force the columns to a particular format? Any ideas much appreciated. Cheers Simon Anderson -- 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).
RE: Create Table..As Select: Number formats
I'm trying to create a table using 'Create Table...As Select...' The contents will then be exchanged into a partitioned table, so they need to have the same names and datatypes. Some of the columns in the created table are populated with zeroes will be updated after the partition exchange, one of the columns is populated with a decode that returns a single digit number. The problem is that I need particular number formats in these columns - the one-digit column should be a number(1,0) and the others should be number(12,4). I can't specify column types in create table...as select, so how else can I force the columns to a particular format? Any ideas much appreciated. Cheers Simon Anderson Simon, I don't think that there is any problem here. Specifying the number of digits is largely cosmetic - consider it as a default mask. It doesn't affect how data is stored inside the tables AFAIK. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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: Create Table..As Select: Number formats
Hi! I'm trying to create a table using 'Create Table...As Select...' The contents will then be exchanged into a partitioned table, so they need to have the same names and datatypes. Actually they do not have to have same column names, only the datatypes and column order has to be the same (at least in 9.2). Some of the columns in the created table are populated with zeroes will be updated after the partition exchange, one of the columns is populated with a decode that returns a single digit number. The problem is that I need particular number formats in these columns - the one-digit column should be a number(1,0) and the others should be number(12,4). I can't specify column types in create table...as select, so how else can I force the columns to a particular format? I tried to use CAST function, it didn't give an error, but column remained just normal number, so it didn't help. I suggest you to create the table structure first and then use insert APPEND to populate the table (you can also use nologging if you like). Tanel. Any ideas much appreciated. Cheers Simon Anderson -- 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: Tanel Poder 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: Create Table..As Select: Number formats
I'm trying to create a table using 'Create Table...As Select...' ... I don't think that there is any problem here. Specifying the number of digits is largely cosmetic - consider it asa default mask. It doesn't affect how data is stored inside the tables AFAIK. Regards, Stephane Faroult Oriole The problem is when I try to exchange the newly created table into the partitioned table - the designers (in their infinite wisdom) have specified number formats for that table. alter table daily_total exchange partition jun_02 with table dt_temp including indexes gives me the error: * ERROR at line 1: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION Unless I get the column types to match exectly - I can't ask for a change in table design to remove the awkward formatting until I've at least tried to get the format to work in the 'Create Table...As Select..' Cheers Simon Anderson -- 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).
RE: RE: Create Table..As Select: Number formats
:31 I'm trying to create a table using 'Create Table...As Select...' ... I don't think that there is any problem here. Specifying the number of digits is largely cosmetic - consider it asa default mask. It doesn't affect how data is stored inside the tables AFAIK. Regards, Stephane Faroult Oriole The problem is when I try to exchange the newly created table into the partitioned table - the designers (in their infinite wisdom) have specified number formats for that table. alter table daily_total exchange partition jun_02 with table dt_temp including indexes gives me the error: * ERROR at line 1: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION Unless I get the column types to match exectly - I can't ask for a change in table design to remove the awkward formatting until I've at least tried to get the format to work in the 'Create Table...As Select..' Cheers Simon Anderson Simon, Are you sure that it comes from the NUMBER() columns? Reminds me of the problem when you have a NULL in a UNION, which must be explicitly cast with a to_number(), to_date() or to_char(). Might it come from some NULL in your CREATE TABLE AS SELECT ... ? Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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: RE: Create Table..As Select: Number formats
Thanks to everyone who responded to this thread - Option A is now to persuade the designers to remove the Number formatting from the parttioned table, Option B is to pre-create the working table and populate it with Truncate and Insert /* Append */ Option B will be slower, I think, due to the extra redo/undo generated despite my best efforts to persuade it otherwise. I've done enough tests with changing the format of the number columns to convince myself that that the calculated Number columns are the only remaining issue. Cheers Simon Anderson I'm trying to create a table using 'Create Table...As Select...' ... Are you sure that it comes from the NUMBER() columns? Reminds me of the problem when you have a NULL in a UNION, which must be explicitly cast with a to_number(), to_date() or to_char(). Might it come from some NULL in your CREATE TABLE AS SELECT ... ? Regards, Stephane Faroult Oriole -- 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).
Re: RE: Create Table..As Select: Number formats
Thanks to everyone who responded to this thread - Option A is now to persuade the designers to remove the Number formatting from the parttioned table, Option B is to pre-create the working table and populate it with Truncate and Insert /* Append */ Option B will be slower, I think, due to the extra redo/undo generated despite my best efforts to persuade it otherwise. I've done enough tests with changing the format of the number columns to convince myself that that the calculated Number columns are the only remaining issue. No, option B is as fast as CTAS (as long as you don't have any indexes on the table). Just make sure that your append hint works... Tanel. Cheers Simon Anderson I'm trying to create a table using 'Create Table...As Select...' ... Are you sure that it comes from the NUMBER() columns? Reminds me of the problem when you have a NULL in a UNION, which must be explicitly cast with a to_number(), to_date() or to_char(). Might it come from some NULL in your CREATE TABLE AS SELECT ... ? Regards, Stephane Faroult Oriole -- 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: Tanel Poder 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: RE: Create Table..As Select: Number formats
No, option B is as fast as CTAS (as long as you don't have any indexes on the table). Just make sure that your append hint works... Also you have to specify NOLOGGING on table or tablespace level when doing insert /*+ APPEND */ or use NOLOGGING hint if you're on 9i. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: select distinct values
Whoa there! See below: -Original Message- From: elain he [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 9:40 PM To: Multiple recipients of list ORACLE-L Subject: select distinct values Hi, Could someone shed some light on this. I'm trying to formulate a query to return distinct value on a column - testid. select * from testing; TESTID NAME STATUS -- --- 1 MIKE Y 1 JOE Y 1 JIMY 2 AMY Y The output I'm expecting is TESTID NAME STATUS -- --- 1 MIKE Y 2 AMY Y ** This sentance contains a fundamental error of understanding of a relational db. The query should display the first occurence of the testid and ignore records with the same testid. The ORDER in which rows are retrieved from a table is not, never, ever will be significant. By definition. So if you can find a script which appears to give you the correct result for this example, then sure as eggs is eggs, it will fail on another data set. There is NOTHING in your example data set which enables a logical predicate to establish precedence of any one row with Testid=1 over another. peter edinburgh thanks! elain _ Instant message in style with MSN Messenger 6.0. Download it now FREE! http://msnmessenger-download.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: elain he 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). * This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. .http://www.bgs.ac.uk * -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robson, Peter 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).
Antw: RE: select distinct values
Hi Rich, same for me. Just one diference: my table is named surrogate_brain ;-) Greetings, Guido [EMAIL PROTECTED] 24.09.2003 00.29 Uhr Man, every time I think I have a handle on the analytic functions, someone bowls me over with it's power and simplicity. I was trying to do this using the FIRST function of 9i. Oh well. INSERT INTO saved_messages ... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke 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).
[Q] need help on SQLplus select distinct !!
I plan to select distinct data from table. anyone has suggestion? Thanks. select distinct employee from (select employee_id, lname, fname, mi, hiredate , as employee from emp) SQL SQL / from (select employee_id, lname, fname, mi, hiredate , as employee from emp) * ERROR at line 2: ORA-00936: missing expression __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mike mon 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: [Q] need help on SQLplus select distinct !!
Title: RE: [Q] need help on SQLplus select distinct !! shouldn't that be select distinct employee_id from emp / ?? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: mike mon [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 23, 2003 11:00 AM To: Multiple recipients of list ORACLE-L Subject: [Q] need help on SQLplus select distinct !! I plan to select distinct data from table. anyone has suggestion? Thanks. select distinct employee from (select employee_id, lname, fname, mi, hiredate , as employee from emp) SQL SQL / from (select employee_id, lname, fname, mi, hiredate , as employee from emp) * ERROR at line 2: ORA-00936: missing _expression_ __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mike mon 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). 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
Re: [Q] need help on SQLplus select distinct !!
You got an extra comma in your statement. Btw, check your statement construction, you probably want the subquery named as employee not hiredate column.. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 5:59 PM I plan to select distinct data from table. anyone has suggestion? Thanks. select distinct employee from (select employee_id, lname, fname, mi, hiredate , as employee from emp) SQL SQL / from (select employee_id, lname, fname, mi, hiredate , as employee from emp) * ERROR at line 2: ORA-00936: missing expression __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mike mon 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: Tanel Poder 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: [Q] need help on SQLplus select distinct !!
Mike, You really don't need to in-line view for this. I'm also not sure what exactly you are trying to do. You could: select distinct employee_id, lname, fname, mi, hiredate from emp or select distinct employee_id from emp what exactly are your trying to get? Distinct employee_id's? Lname's? Fname's? or a combination of all items? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, September 23, 2003 11:00 AM To: Multiple recipients of list ORACLE-L I plan to select distinct data from table. anyone has suggestion? Thanks. select distinct employee from (select employee_id, lname, fname, mi, hiredate , as employee from emp) SQL SQL / from (select employee_id, lname, fname, mi, hiredate , as employee from emp) * ERROR at line 2: ORA-00936: missing expression __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mike mon 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: Mercadante, Thomas F 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: [Q] need help on SQLplus select distinct !!
Are you doing homework...? Shame on you -Original Message- Sent: Tuesday, September 23, 2003 10:00 AM To: Multiple recipients of list ORACLE-L I plan to select distinct data from table. anyone has suggestion? Thanks. select distinct employee from (select employee_id, lname, fname, mi, hiredate , as employee from emp) SQL SQL / from (select employee_id, lname, fname, mi, hiredate , as employee from emp) * ERROR at line 2: ORA-00936: missing expression __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mike mon INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Odland, Brad INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: [Q] need help on SQLplus select distinct !!
Select distinct employee_id||lname|fname|mi||hiredate as employee from emp -Original Message- Sent: Tuesday, September 23, 2003 5:00 PM To: Multiple recipients of list ORACLE-L I plan to select distinct data from table. anyone has suggestion? Thanks. select distinct employee from (select employee_id, lname, fname, mi, hiredate , as employee from emp) SQL SQL / from (select employee_id, lname, fname, mi, hiredate , as employee from emp) * ERROR at line 2: ORA-00936: missing expression __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mike mon 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: Jack van Zanen 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).
select distinct values
Hi, Could someone shed some light on this. I'm trying to formulate a query to return distinct value on a column - testid. select * from testing; TESTID NAME STATUS -- --- 1 MIKE Y 1 JOE Y 1 JIMY 2 AMY Y The output I'm expecting is TESTID NAME STATUS -- --- 1 MIKE Y 2 AMY Y The query should display the first occurence of the testid and ignore records with the same testid. thanks! elain _ Instant message in style with MSN Messenger 6.0. Download it now FREE! http://msnmessenger-download.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: elain he INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: select distinct values
Elain, The query is returning the correct data. DISTINCT applies to the row as a whole. Since the NAME adds a new element, DISTINCT finds that 1MIKEY is different than 1JOEY. How do you determine the 'first' row of the testid? Depending upon the access path (table/index), this could be a different row that what you expect. Do you only care about 1 row for each TESTID and STATUS combination? Daniel elain he wrote: Hi, Could someone shed some light on this. I'm trying to formulate a query to return distinct value on a column - testid. select * from testing; TESTID NAME STATUS -- --- 1 MIKE Y 1 JOE Y 1 JIMY 2 AMY Y The output I'm expecting is TESTID NAME STATUS -- --- 1 MIKE Y 2 AMY Y The query should display the first occurence of the testid and ignore records with the same testid. thanks! elain _ Instant message in style with MSN Messenger 6.0. Download it now FREE! http://msnmessenger-download.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: elain he 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). begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard
RE: select distinct values
Try this (in 8i or above): select * from ( select testid, name, status, rank() over( partition by testid order by rownum ) dup_rank from testing ) where dup_rank = 1 -Original Message- Sent: Tuesday, September 23, 2003 1:40 PM To: Multiple recipients of list ORACLE-L Hi, Could someone shed some light on this. I'm trying to formulate a query to return distinct value on a column - testid. select * from testing; TESTID NAME STATUS -- --- 1 MIKE Y 1 JOE Y 1 JIMY 2 AMY Y The output I'm expecting is TESTID NAME STATUS -- --- 1 MIKE Y 2 AMY Y The query should display the first occurence of the testid and ignore records with the same testid. thanks! elain _ Instant message in style with MSN Messenger 6.0. Download it now FREE! http://msnmessenger-download.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: elain he 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: Alan Gano INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: select distinct values
SELECT DISTINCT testid , FIRST_VALUE(name) OVER (PARTITION BY testid ORDER BY ROWID) name , FIRST_VALUE(status) OVER (PARTITION BY testid ORDER BY ROWID) status FROM testing / -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. elain he wrote: Hi, Could someone shed some light on this. I'm trying to formulate a query to return distinct value on a column - testid. select * from testing; TESTID NAME STATUS -- --- 1 MIKE Y 1 JOE Y 1 JIMY 2 AMY Y The output I'm expecting is TESTID NAME STATUS -- --- 1 MIKE Y 2 AMY Y The query should display the first occurence of the testid and ignore records with the same testid. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: select distinct values
Daniel, I need only 1 row for each testid, name,status combination. I can do a distinct on testid but I need the name and status to be displayed as well. thanks. elain From: Daniel Fink [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: select distinct values Date: Tue, 23 Sep 2003 12:54:50 -0800 Elain, The query is returning the correct data. DISTINCT applies to the row as a whole. Since the NAME adds a new element, DISTINCT finds that 1MIKEY is different than 1JOEY. How do you determine the 'first' row of the testid? Depending upon the access path (table/index), this could be a different row that what you expect. Do you only care about 1 row for each TESTID and STATUS combination? Daniel elain he wrote: Hi, Could someone shed some light on this. I'm trying to formulate a query to return distinct value on a column - testid. select * from testing; TESTID NAME STATUS -- --- 1 MIKE Y 1 JOE Y 1 JIMY 2 AMY Y The output I'm expecting is TESTID NAME STATUS -- --- 1 MIKE Y 2 AMY Y The query should display the first occurence of the testid and ignore records with the same testid. thanks! elain _ Instant message in style with MSN Messenger 6.0. Download it now FREE! http://msnmessenger-download.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: elain he 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). daniel.fink.vcf _ Share your photos without swamping your Inbox. Get Hotmail Extra Storage today! http://join.msn.com/?PAGE=features/es -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: elain he INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: select distinct values
Alan, Thanks! that works. elain From: Alan Gano [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: select distinct values Date: Tue, 23 Sep 2003 13:04:47 -0800 Try this (in 8i or above): select * from ( select testid, name, status, rank() over( partition by testid order by rownum ) dup_rank from testing ) where dup_rank = 1 -Original Message- Sent: Tuesday, September 23, 2003 1:40 PM To: Multiple recipients of list ORACLE-L Hi, Could someone shed some light on this. I'm trying to formulate a query to return distinct value on a column - testid. select * from testing; TESTID NAME STATUS -- --- 1 MIKE Y 1 JOE Y 1 JIMY 2 AMY Y The output I'm expecting is TESTID NAME STATUS -- --- 1 MIKE Y 2 AMY Y The query should display the first occurence of the testid and ignore records with the same testid. thanks! elain _ Instant message in style with MSN Messenger 6.0. Download it now FREE! http://msnmessenger-download.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: elain he 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: Alan Gano 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). _ Add MSN 8 Internet Software to your existing Internet access and enjoy patented spam protection and more. Sign up now! http://join.msn.com/?page=dept/byoa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: elain he 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: [Q] need help on SQLplus select distinct !!
sorry about that, but if you don't want answer, you can easy to ignore. You don't need insult other people. You may good, but everyone sometime still need start from fresh. "Odland, Brad" [EMAIL PROTECTED] wrote: Are you doing homework...?Shame on you-Original Message-Sent: Tuesday, September 23, 2003 10:00 AMTo: Multiple recipients of list ORACLE-LI plan to select distinct data from table. anyone hassuggestion?Thanks.select distinct employeefrom (select employee_id, lname, fname, mi, hiredate, as employee from emp)SQL SQL /from (select employee_id, lname, fname, mi, hiredate ,as employee from emp)*ERROR at line 2:ORA-00936: missing _expression___Do you Yahoo!?Yahoo! SiteBuilder - Free, easy-to-use web site design softwarehttp://sitebuilder.yahoo.com-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: mike monINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Odland, BradINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software
RE: select distinct values
Man, every time I think I have a handle on the analytic functions, someone bowls me over with it's power and simplicity. I was trying to do this using the FIRST function of 9i. Oh well. INSERT INTO saved_messages ... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: elain he [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 5:00 PM To: Multiple recipients of list ORACLE-L Subject: RE: select distinct values Alan, Thanks! that works. elain From: Alan Gano [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: select distinct values Date: Tue, 23 Sep 2003 13:04:47 -0800 Try this (in 8i or above): select * from ( select testid, name, status, rank() over( partition by testid order by rownum ) dup_rank from testing ) where dup_rank = 1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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: [Q] need help on SQLplus select distinct !!
but just ignoring message is NOT what this list is all about. Joe mike mon wrote: sorry about that, but if you don't want answer, you can easy to ignore. You don't need insult other people. You may good, but everyone sometime still need start from fresh. */Odland, Brad [EMAIL PROTECTED]/* wrote: Are you doing homework...? Shame on you -Original Message- Sent: Tuesday, September 23, 2003 10:00 AM To: Multiple recipients of list ORACLE-L I plan to select distinct data from table. anyone has suggestion? Thanks. select distinct employee from (select employee_id, lname, fname, mi, hiredate , as employee from emp) SQL SQL / from (select employee_id, lname, fname, mi, hiredate , as employee from emp) * ERROR at line 2: ORA-00936: missing expression __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mike mon INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Odland, Brad INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Do you Yahoo!? Yahoo! SiteBuilder http://us.rd.yahoo.com/evt=10469/*http://sitebuilder.yahoo.com - Free, easy-to-use web site design software -- Joseph S Testa Chief Technology Officer Data Management Consulting 614-791-9000 It's all about the CACHE -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: select distinct values
elain he wrote: Hi, Could someone shed some light on this. I'm trying to formulate a query to return distinct value on a column - testid. select * from testing; TESTID NAME STATUS -- --- 1 MIKE Y 1 JOE Y 1 JIMY 2 AMY Y The output I'm expecting is TESTID NAME STATUS -- --- 1 MIKE Y 2 AMY Y The query should display the first occurence of the testid and ignore records with the same testid. thanks! elain Elain, Check the SQL Reference, row_number() function. The examples will take you where you want to go ... -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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: [Q] need help on SQLplus select distinct !!
select distinct column1, columnn2 , ... from table; Stephane -Original Message- mike mon Sent: Tuesday, September 23, 2003 11:00 AM To: Multiple recipients of list ORACLE-L I plan to select distinct data from table. anyone has suggestion? Thanks. select distinct employee from (select employee_id, lname, fname, mi, hiredate , as employee from emp) SQL SQL / from (select employee_id, lname, fname, mi, hiredate , as employee from emp) * ERROR at line 2: ORA-00936: missing expression __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mike mon 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: Stephane Paquette 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).
restriction on the number of columns in select statement.
Can someone tell me the restriction on the # of columns in the select clause of the statement.. We are running into sort key too long - ORA-01467. I am told there is no order by or group by in the query. I need to look @ the query to make sure there are no references in the statement that generates a implicit sort. I tried to search on the restriction and could not comeup with anything. Thanks for your help Murali. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Murali_Pavuloori/[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: restriction on the number of columns in select statement.
Title: RE: restriction on the number of columns in select statement. it means total length of column sizes in group/order by cannot exceed your block size. See http://tinyurl.com/nk93 Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Murali_Pavuloori/[EMAIL PROTECTED] [mailto:Murali_Pavuloori/[EMAIL PROTECTED]] Sent: Tuesday, September 16, 2003 1:30 PM To: Multiple recipients of list ORACLE-L Subject: restriction on the number of columns in select statement. Can someone tell me the restriction on the # of columns in the select clause of the statement.. We are running into sort key too long - ORA-01467. I am told there is no order by or group by in the query. I need to look @ the query to make sure there are no references in the statement that generates a implicit sort. I tried to search on the restriction and could not comeup with anything. Thanks for your help Murali. 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.*2
[Q] SQL Help!! How to make select left justify and fill space??
Can anyone tell me on select how can make it left justify and fill space? example: IDnumber(8) output: 12345 Emplyeename ^ |--- fill space Thanks. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mike mon 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: [Q] SQL Help!! How to make select left justify and fill space
Title: RE: [Q] SQL Help!! How to make select left justify and fill space?? select rpad(to_char(id),8,' ') from your_table / Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: mike mon [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 27, 2003 11:09 AM To: Multiple recipients of list ORACLE-L Subject: [Q] SQL Help!! How to make select left justify and fill space?? Can anyone tell me on select how can make it left justify and fill space? example: ID number(8) output: 12345 Emplyeename ^ |--- fill space Thanks. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mike mon 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). *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
RE: [Q] SQL Help!! How to make select left justify and fill space??
hi Mike, use ltrim to left justify and then rpad to pad it out with spaces, so something like this rpad(ltrim(id), '', 15) id N. :--Original Message- :-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] :-Behalf Of :-mike mon :-Sent: 27 August 2003 16:09 :-To: Multiple recipients of list ORACLE-L :-Subject: [Q] SQL Help!! How to make select left justify and :-fill space?? :- :- :-Can anyone tell me on select how can make it left :-justify and fill space? :- :-example: :- :- IDnumber(8) :- :-output: :- :-12345 Emplyeename :- ^ :- |--- fill space :- :- :- :-Thanks. :- :- :-__ :-Do you Yahoo!? :-Yahoo! SiteBuilder - Free, easy-to-use web site design software :-http://sitebuilder.yahoo.com :--- :-Please see the official ORACLE-L FAQ: http://www.orafaq.net :--- :-Author: mike mon :- 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: Nuala Cullen 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: [Q] SQL Help!! How to make select left justify and fill space??
Use something like FORMAT STDOUT= @ @ $id, $empname
RE: [Q] SQL Help!! How to make select left justify and fill space??
LOL! Mladen Gogala [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/27/2003 10:04 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: [Q] SQL Help!! How to make select left justify and fill space?? Use something like FORMAT STDOUT= @ @ $id,$empname
RE: [Q] SQL Help!! How to make select left justify and fill space??
Title: Message
Well,
people are trying to use SQL*Plus as if it was a report generating tool, which
it isn't.
To
tell the truth, I started using perl because of its formatting capabilities (I
hope you still
do
remember the old ora_login stuff from perl4?) because my report looked so
much nicer
then
with sqlplus. I don't have running sums, but they're rarely needed. On the other
hand,
I can
do left and right justification, top of the page format, multiline fields
(impossible to do
with
sqlplus) and a lot of other good stuff, like conditional printing (that's why
perl has
"if"
statement). The ideal thing for generating pretty reports out of any database is
called
perl.
It's cheap, it's well documented ("Learning Perl", "Perl Programming", "Perl
Cookbook",
"Perl
for Oracle DBA", "Advanced Perl Programming", "Perl for Sys Admins", "Perl
Objects,
References Modules","Learning Perl
Tk", "Object Oriented Perl Programming", I have a
whole
shelf devoted to perl.I have a hunch that when Cary's book gets into
the circulation,
there
will be another book on that shelf. If that is not a well documented
language, then I
don't
know what is) and is ideally suited for the purpose. Yet, people are still using
sqlplus.
Why?
It beats me.
--Mladen GogalaOracle DBA
-Original Message-From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
[EMAIL PROTECTED]Sent: Wednesday, August 27, 2003 3:00
PMTo: Multiple recipients of list ORACLE-LSubject: RE:
[Q] SQL Help!! How to make select left justify and fill
space??LOL!
"Mladen Gogala"
[EMAIL PROTECTED] Sent by: [EMAIL PROTECTED]
08/27/2003 10:04 AM
Please respond to ORACLE-L
To:
Multiple recipients of list ORACLE-L
[EMAIL PROTECTED] cc:
Subject:RE: [Q] SQL Help!! How to
make select left justify and fill
space??Use something like FORMAT
STDOUT=@
@$id,
$empname
Note:
This message is for the named person's use only. It may contain
confidential, proprietary or legally privileged information. No
confidentiality or privilege is waived or lost by any mistransmission. If
you receive this message in error,please immediately delete it and all
copies of it from your system, destroy any hard copies of it and notify the
sender. You must not, directly or indirectly, use, disclose, distribute,
print, or copy any part of this message if you are not the intended
recipient.Wang Trading
LLCand any of its subsidiaries each reserve the right to
monitor all e-mail communications through its networks. Any views
expressed in this message are those of the individual sender, except where the
message states otherwise and the sender is authorized to state them to be the
views of any such entity.
RE: [Q] SQL Help!! How to make select left justify and fill space??
Mladen, Even I use sqlplus for some reporting. There's even a perl script in Perl for Oracle DBA's that is a driver for sqlplus. Why? For the things that are missing in Perl that I needed for some reporting. Mostly the column breaks. There is no convenient module yet for doing that in Perl. I started one for Perl, but never finished it. Not enough oop experience for the complexity of what I was trying to do ( breaks and sums ). Have a working prototype, but far from finished. Just ran out of time for it. Sqlplus does still have it's place. Jared Mladen Gogala [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/27/2003 12:59 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: [Q] SQL Help!! How to make select left justify and fill space?? Well, people are trying to use SQL*Plus as if it was a report generating tool, which it isn't. To tell the truth, I started using perl because of its formatting capabilities (I hope you still do remember the old ora_login stuff from perl4?) because my report looked so much nicer then with sqlplus. I don't have running sums, but they're rarely needed. On the other hand, I can do left and right justification, top of the page format, multiline fields (impossible to do with sqlplus) and a lot of other good stuff, like conditional printing (that's why perl has if statement). The ideal thing for generating pretty reports out of any database is called perl. It's cheap, it's well documented (Learning Perl, Perl Programming, Perl Cookbook, Perl for Oracle DBA, Advanced Perl Programming, Perl for Sys Admins, Perl Objects, References Modules,Learning Perl Tk, Object Oriented Perl Programming, I have a whole shelf devoted to perl.I have a hunch that when Cary's book gets into the circulation, there will be another book on that shelf. If that is not a well documented language, then I don't know what is) and is ideally suited for the purpose. Yet, people are still using sqlplus. Why? It beats me. -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Wednesday, August 27, 2003 3:00 PM To: Multiple recipients of list ORACLE-L Subject: RE: [Q] SQL Help!! How to make select left justify and fill space?? LOL! Mladen Gogala [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/27/2003 10:04 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: [Q] SQL Help!! How to make select left justify and fill space?? Use something like FORMAT STDOUT= @ @ $id,$empname Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.
Re: 9iR2, grant select on a column (without using views) using RL
Rahul,
I'm not sure if this is too late; but here
is a strategy you could follow to achieve what you want. True, VPD does not have
a mechanism to suppress columns; and using a view for each user is impractical.
Someday, I hope, VPD will have that capability; but until then you could try the
following.
Suppose you have a table called SAVINGS,
for savings account holders as follows:
ACCTNO
NUMBERCLEARED_BALANCE
NUMBERUNCLEARED_BALANCE
NUMBER
The records in the table are as
follows:
ACCTNO CLEARED_BALANCE
UNCLEARED_BALANCE-- ---
-
1
1000
1100
2
1100
1200
3
1300
1500
Cleared balance is the amount the the
customer can draw from the bank. If there are checks outstanding, the balance is
shown in uncleared. Let's start with a simple example - you have users who are
allowed to see the uncleared balance of the customers and the others are not.
Intead of hiding the column completely, which how VPD operates, you would want
to show then as zero, if not authorized to see that; otherwise the actual value
is shown.
You would create a context as
follows:
create context sec_ctx using
sec_ctx_pkg;
The trusted function can be created
as:
create procedure
sec_ctx_pkg( p_attribute_name in
varchar2, p_attribute_value in varchar2)
isbegin
dbms_session.set_context(
'sec_ctx',
p_attribute_name,
p_attribute_value);end;/
Inthe after-login trigger, you would
set the context value automatically for user using
set_Ctx_pkg ('cleared', 'yes'); or
set_Ctx_pkg ('cleared', 'no'); depending on whether the user is cleared to see
the balance or not. In real life, you may have a table that lists all users and
whether or not they are cleared. The after-logon trigger could read that table
and set the context attribute properly.
Next, you would craete a view.
create or replace view
vw_savingsasselect acctno,
cleared_balance,decode(sys_context('sec_ctx','cleared'),'yes',uncleared_balance,
0) uncleared_balancefrom savings
/
Note: there is only ONE view, not one per
user. Regardless of how many users you have, there will be only one
view.
Now to test the setup. Assume user RAHUL is
allowed to see the uncleared_balance. The after-logon trigger will set the
context attribute "cleared" to "yes" when the user logs in. When the user
selects:
select * from vw_savings;
He sees:
ACCTNO CLEARED_BALANCE
UNCLEARED_BALANCE- ---
-
1
1000
1100
2
1100
1200
3
1300
1500
Which is the correct value. Now, user ARUP
logs in, who does not have the authority to see the uncleared balance. The logon
trigger will set the attribute to "no" and the same select will now
produce:
ACCTNO CLEARED_BALANCE
UNCLEARED_BALANCE-- ---
-
1
1000
0
2
1100
0
3
1300
0
Note: How the uncleared balance is 0.
This model can be extended to any column
and any number of valuesfor theattribute "cleared". You could even
specify levels of users who are allowed to see the balances under certain
amount; not above that. In case of character values; it's even simpler; just
mask it by some value such as "", or "NOT CLEARED TO SEE".
All the users are granted select privileges
on the view, not the table. The context setting procedure is owned by a secured
user; SYS would do, but you should have a separate username, say, SECUSER, for
it. In doing so, you prevent the user from setting the context
directly.
This is not VPD and not supposed to be; but
I think it will work nice for your purpose. Please let us know the devlopment at
your side.
Hope this helps.
Arup Nanda
www.proligence.com
- Original Message -
From: "rahul" [EMAIL PROTECTED]
To: "Multiple recipients of list ORACLE-L"
[EMAIL PROTECTED]
Sent: Sunday, August 24, 2003 4:34 AM
Subject: RE: 9iR2, grant select on a column
(without using views) using RL
how would i write a policy which retuns selected columns if the user
has issued select * from tab ??? using views for each
user would work, but then.. i would end up with so many views in the
main schema !!! ;-( On Sat, 23 Aug 2003 12:24:39
-0800, "Jamadagni, Rajendra" [EMAIL PROTECTED] wrote
: This message is in MIME format. Since your mail reader
does not understand this format, some or all of this message may
not be legible. Use RLS ..
Raj
--
-- Rajendra dot Jamadagni at nospamespn dot
com All Views expressed in this email are strictly
personal. QOTD: Any clod can have facts, having an opinion is an
art ! -Original
Message- Sent: Saturday, August 23, 2003 2:34 AM
To: Multiple recipients of list ORACLE-L
list, i'm ikn the process of designing security for a highly sensitive
schema for a bank, plan:
have multiple oracle users, an
Re: 9iR2, grant select on a column (without using views) using RL
Tell me about it. :) Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Tanel Poder wrote: Hi! The views are small part. There are over 15 objects in whole database, of which over 22000 are packages. System TS is about 4GB. (source$ table is 1.2GB, total of IDL_ tables is also about 1.2G). Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: 9iR2, grant select on a column (without using views) using RL
rahul
You can use the standard technique for that -- hide your sensitive
columns under a view, something like
...
SELECT pkey
, DECODE(SYS_CONTEXT('CTX$SEC', 'ROLE')
, 'CEO', col1
, 'MANAGER', col1
NULL
) col1
...
Where ctx$sec role is a application role based security context. You
can define whatever context you like. Using this approach you can use
one view that covers different user application roles.
Regards,
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
rahul wrote:
how would i write a policy which retuns selected columns if the user has
issued select * from tab ???
using views for each user would work, but then.. i would end up with
so many views in the main schema !!! ;-(
On Sat, 23 Aug 2003 12:24:39 -0800, Jamadagni, Rajendra
[EMAIL PROTECTED] wrote :
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
Use RLS ...
Raj
--
--
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
-Original Message-
Sent: Saturday, August 23, 2003 2:34 AM
To: Multiple recipients of list ORACLE-L
list, i'm ikn the process of designing security for a highly sensitive
schema for a bank,
plan:
have multiple oracle users, and use roles, and grant minimum required
privs, all the user/role/privs management coded in the application (with
in
turn would create the db role and user etc)
probolem:
i cannot do a grant select(col1)on tabname to role1, as select grant on
a
column level is not supported, to workaround this i must
1) use views and include all the columns granted seleted privs for a
user,
then give grant select on this view to user.
2) somehow use RLS ??
TIA
-Rahul
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
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: 9iR2, grant select on a column (without using views) using RL
Hi, Unrelated question : If the system tablespace is so big would it not hurt performance for queries to all_tables, v$session, dba_segments etc. In such a case : can tables like source$be moved out of system tablespace and would it make sense. Thank YouVladimir Begun [EMAIL PROTECTED] wrote: Tell me about it. :)Regards,-- Vladimir BegunThe statements and opinions expressed here are my own anddo not necessarily represent those of Oracle Corporation.Tanel Poder wrote: Hi! The views are small part. There are over 15 objects in whole database, of which over 22000 are packages. System TS is about 4GB. (source$ table is 1.2GB, total of IDL_ tables is also about 1.2G). Tanel.-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Vladimir BegunINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-! Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software
Re: 9iR2, grant select on a column (without using views) using RL
NO! Never move a data dictionary table out of the SYSTEM tablespace; or do any kind of operation on them. The only tables that can be operated on - particularly DELETEs and TRUNCATEs, are AUD$ and FGA_LOG$ (in 9i). Besides, how would moving SOURCE$ table from SYSTEM tablespace help? The probelm is not a smaller tablespace size. Most of the data dictionary views are well indexed, anyway; so performance impact may be low. Something I have monkeyed around earlier is to place my own indexes on these tables, if needed. Mind you, these are not supported by Oracle; so you are on your own if something happens. In most cases, however, that may not be necessary. Hope this helps. Arup - Original Message - From: A Joshi To: Multiple recipients of list ORACLE-L Sent: Monday, August 25, 2003 2:14 PM Subject: Re: 9iR2, grant select on a column (without using views) using RL Hi, Unrelated question : If the system tablespace is so big would it not hurt performance for queries to all_tables, v$session, dba_segments etc. In such a case : can tables like source$be moved out of system tablespace and would it make sense. Thank YouVladimir Begun [EMAIL PROTECTED] wrote: Tell me about it. :)Regards,-- Vladimir BegunThe statements and opinions expressed here are my own anddo not necessarily represent those of Oracle Corporation.Tanel Poder wrote: Hi! The views are small part. There are over 15 objects in whole database, of which over 22000 are packages. System TS is about 4GB. (source$ table is 1.2GB, total of IDL_ tables is also about 1.2G). Tanel.-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Vladimir BegunINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-! Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!?Yahoo! SiteBuilder - Free, easy-to-use web site design software
Re: 9iR2, grant select on a column (without using views) using RL
A Joshi, Big/huge segments do not hurt performance, they only consume space. Some queries (operations) against big segments can lead to performance problems. So, I do not think that one should consider segment's size as an immediate performance problem. v$session is not a segment you should worry about. sys.source$ -- no way, it must live in system tablespace. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. A Joshi wrote: Hi, Unrelated question : If the system tablespace is so big would it not hurt performance for queries to all_tables, v$session, dba_segments etc. In such a case : can tables like source$ be moved out of system tablespace and would it make sense. Thank You -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: 9iR2, grant select on a column (without using views) using RL
how would i write a policy which retuns selected columns if the user has issued select * from tab ??? using views for each user would work, but then.. i would end up with so many views in the main schema !!! ;-( On Sat, 23 Aug 2003 12:24:39 -0800, Jamadagni, Rajendra [EMAIL PROTECTED] wrote : This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. Use RLS ... Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Saturday, August 23, 2003 2:34 AM To: Multiple recipients of list ORACLE-L list, i'm ikn the process of designing security for a highly sensitive schema for a bank, plan: have multiple oracle users, and use roles, and grant minimum required privs, all the user/role/privs management coded in the application (with in turn would create the db role and user etc) probolem: i cannot do a grant select(col1)on tabname to role1, as select grant on a column level is not supported, to workaround this i must 1) use views and include all the columns granted seleted privs for a user, then give grant select on this view to user. 2) somehow use RLS ?? TIA -Rahul -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: rahul 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: rahul 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: 9iR2, grant select on a column (without using views) using RL
how would i write a policy which retuns selected columns if the user has issued select * from tab ??? using views for each user would work, but then.. i would end up with so many views in the main schema !!! ;-( On Sat, 23 Aug 2003 12:24:39 -0800, Jamadagni, Rajendra [EMAIL PROTECTED] wrote : This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. Use RLS ... Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Saturday, August 23, 2003 2:34 AM To: Multiple recipients of list ORACLE-L list, i'm ikn the process of designing security for a highly sensitive schema for a bank, plan: have multiple oracle users, and use roles, and grant minimum required privs, all the user/role/privs management coded in the application (with in turn would create the db role and user etc) probolem: i cannot do a grant select(col1)on tabname to role1, as select grant on a column level is not supported, to workaround this i must 1) use views and include all the columns granted seleted privs for a user, then give grant select on this view to user. 2) somehow use RLS ?? TIA -Rahul -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: rahul 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: rahul 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: 9iR2, grant select on a column (without using views) using RL
Hi! how would i write a policy which retuns selected columns if the user has issued select * from tab ??? You can't. Because in describe phase of query, sys.col$ is queried to get column names and datatypes. And this always returns all columns that physically exist in a given table (except columns set as unused). So, unless you implement some kind of RLS on sys.col$ table, which I doubt is gonna ever work, you'll always see all the fields of a row of a given table. Your options are either code the column viewing security to application, or create views - not for every user, but for every security profile. There's probably lot less security profiles than users. Then make a grant and a private synonym for appropriate view to every user's schema (if you are using Oracle authentication mechanism). That way your application can always access synonym X which points to view1, view2... etc.. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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: 9iR2, grant select on a column (without using views) using RLS
Have you thought about encrypting those sensitive columns? The user will need select decrypt(balance) to see the content. Then you grant execute on decrypt only to privileged users. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, August 23, 2003 8:34 AM list, i'm ikn the process of designing security for a highly sensitive schema for a bank, plan: have multiple oracle users, and use roles, and grant minimum required privs, all the user/role/privs management coded in the application (with in turn would create the db role and user etc) probolem: i cannot do a grant select(col1)on tabname to role1, as select grant on a column level is not supported, to workaround this i must 1) use views and include all the columns granted seleted privs for a user, then give grant select on this view to user. 2) somehow use RLS ?? TIA -Rahul -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: rahul 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: 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).
Re: 9iR2, grant select on a column (without using views) using RL
using views for each user would work, but then.. i would end up with so many views in the main schema !!! ;-( SQL select owner, count(*) from dba_views group by owner having count(*) 100 order by 2 desc; OWNERCOUNT(*) -- -- APPS_AF 15899 SYS 1410 Is this too many views for you? ;) This is a regular Oracle Applications 11.5.7 installation... Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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: 9iR2, grant select on a column (without using views) using RL
You would better count how much space those views' definitions consume in your system tablespace. :) Tanel Poder wrote: using views for each user would work, but then.. i would end up with so many views in the main schema !!! ;-( SQL select owner, count(*) from dba_views group by owner having count(*) 100 order by 2 desc; OWNERCOUNT(*) -- -- APPS_AF 15899 SYS 1410 Is this too many views for you? ;) This is a regular Oracle Applications 11.5.7 installation... -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: 9iR2, grant select on a column (without using views) using RL
Hi! The views are small part. There are over 15 objects in whole database, of which over 22000 are packages. System TS is about 4GB. (source$ table is 1.2GB, total of IDL_ tables is also about 1.2G). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, August 24, 2003 10:54 PM You would better count how much space those views' definitions consume in your system tablespace. :) Tanel Poder wrote: using views for each user would work, but then.. i would end up with so many views in the main schema !!! ;-( SQL select owner, count(*) from dba_views group by owner having count(*) 100 order by 2 desc; OWNERCOUNT(*) -- -- APPS_AF 15899 SYS 1410 Is this too many views for you? ;) This is a regular Oracle Applications 11.5.7 installation... -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Tanel Poder 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: 9iR2, grant select on a column (without using views) using RL
Title: RE: 9iR2, grant select on a column (without using views) using RLS Use RLS ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: rahul [mailto:[EMAIL PROTECTED]] Sent: Saturday, August 23, 2003 2:34 AM To: Multiple recipients of list ORACLE-L Subject: 9iR2, grant select on a column (without using views) using RLS list, i'm ikn the process of designing security for a highly sensitive schema for a bank, plan: have multiple oracle users, and use roles, and grant minimum required privs, all the user/role/privs management coded in the application (with in turn would create the db role and user etc) probolem: i cannot do a grant select(col1)on tabname to role1, as select grant on a column level is not supported, to workaround this i must 1) use views and include all the columns granted seleted privs for a user, then give grant select on this view to user. 2) somehow use RLS ?? TIA -Rahul -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: rahul 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). 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.*2
Re: 9iR2, grant select on a column (without using views) using RL
Title: RE: 9iR2, grant select on a column (without using views) using RLS Hm, I think you can't use RLS to restrict access to columns of returned rows. You only can control which entire rows are returned (based on values of some columns). You have to use views or application logic to control read access to specific columns. Tanel. - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Saturday, August 23, 2003 11:24 PM Subject: RE: 9iR2, grant select on a column (without using views) using RL Use RLS ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: rahul [mailto:[EMAIL PROTECTED]] Sent: Saturday, August 23, 2003 2:34 AM To: Multiple recipients of list ORACLE-L Subject: 9iR2, grant select on a column (without using views) using RLS list, i'm ikn the process of designing security for a highly sensitive schema for a bank, plan: have multiple oracle users, and use roles, and grant minimum required privs, all the user/role/privs management coded in the application (with in turn would create the db role and user etc) probolem: i cannot do a "grant select(col1)on tabname to role1", as select grant on a column level is not supported, to workaround this i must 1) use views and include all the columns granted seleted privs for a user, then give grant select on this view to user. 2) somehow use RLS ?? TIA -Rahul -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: rahul 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).
9iR2, grant select on a column (without using views) using RLS
list, i'm ikn the process of designing security for a highly sensitive schema for a bank, plan: have multiple oracle users, and use roles, and grant minimum required privs, all the user/role/privs management coded in the application (with in turn would create the db role and user etc) probolem: i cannot do a grant select(col1)on tabname to role1, as select grant on a column level is not supported, to workaround this i must 1) use views and include all the columns granted seleted privs for a user, then give grant select on this view to user. 2) somehow use RLS ?? TIA -Rahul -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: rahul 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).
select strings with '_' in it
Hello, I have this data 'v_lan' and also 'vclan'. I would like to select 'v_lan' but not 'vclan'. Is it possible to do that? I tried set escape on select col1 from table where col1 like 'v\_lan' / but it doesn't work. thank you, benny __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Benny Pei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: select strings with '_' in it
select col1 from table where col1 like 'v\_lan' escape '\' ; -Original Message- From: Benny Pei [mailto:[EMAIL PROTECTED] I have this data 'v_lan' and also 'vclan'. I would like to select 'v_lan' but not 'vclan'. Is it possible to do that? I tried set escape on select col1 from table where col1 like 'v\_lan' / but it doesn't work. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: select strings with '_' in it
Title: RE: select strings with '_' in it Remember, '_' matches any 1 character, so change what you're looking for. Try: Select col1 from table where translate(col1,'_','#') like 'v#lan%' / Regards, Alan Martin Defense Logistics Info Service Battle Creek, MI -Original Message- From: Benny Pei [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 19, 2003 6:19 PM To: Multiple recipients of list ORACLE-L Subject: select strings with '_' in it Hello, I have this data 'v_lan' and also 'vclan'. I would like to select 'v_lan' but not 'vclan'. Is it possible to do that? I tried set escape on select col1 from table where col1 like 'v\_lan' / but it doesn't work. thank you, benny __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Benny Pei INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: select strings with '_' in it
Title: RE: select strings with '_' in it Hi! I think translate is overkill here. Use: select col from tab where col like 'v\_lan' escape '\'; Tanel. - Original Message - From: Martin, Alan (Contractor) (DLIS) To: Multiple recipients of list ORACLE-L Sent: Wednesday, August 20, 2003 1:39 AM Subject: RE: select strings with '_' in it Remember, '_' matches any 1 character, so change what you're looking for. Try: Select col1 from table where translate(col1,'_','#') like 'v#lan%' / Regards, Alan Martin Defense Logistics Info Service Battle Creek, MI -Original Message- From: Benny Pei [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 19, 2003 6:19 PM To: Multiple recipients of list ORACLE-L Subject: select strings with '_' in it Hello, I have this data 'v_lan' and also 'vclan'. I would like to select 'v_lan' but not 'vclan'. Is it possible to do that? I tried set escape on select col1 from table where col1 like 'v\_lan' / but it doesn't work. thank you, benny __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Benny Pei INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: select strings with '_' in it
Tanel Poder wrote: Hi! I think translate is overkill here. Use: select col from tab where col like 'v\_lan' escape '\'; vs Select col1 from table where translate(col1,'_','#') like 'v#lan%' / Not only is the escape method cleaner, it's much more efficient. Unless you have an index on translate(col1,'_','#') (unlikely), that query will always result in a sequential scan. Ugh. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Scott Lamb 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: RAID select
Hi! Definitely do NOT put your rollback segment tablespaces to non-raid disks, if you don't want your database to be down in case of media failure. Also, if your system has to be highly available, I wouldn't put anything except maybe multiplexed online logs, archive logs and possibly some backups and such stuff on non-raid. Even temp shouldn't be on non-redundant disk if you don't want to lose your on disk sorting ability in case of media failure.. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, July 21, 2003 4:39 PM Assuming that you have 3 physical devices (NON-RAID, RAID-1, and RAID-0+1) my quick-and-dirty take on this is: NON-RAID redo logs - mirrored by Oracle control logs - mirrored by Oracle ORACLE_HOME RBS01 RBS02 TEMP STAT(For PERFSTAT) RAID-1 SYSTEM RWEBCONFIG(Less Accessed) RTRADE(Less Accessed) Archive destination - move to tape Backups - copy to tape RAID-0+1 USERS01(Heavily Accessd) INDEX01(Heavily Accessd) INDEX02(Heavily Accessd) I wouldn't worry about putting the indexes on the same device with the tables. It is a multi-platter device and each datafile will be stripped across multiple platters. You may want to look into the stripe size depending on the number of users who will be concurrently accessing the database. Basic rule-of-thumb, the more concurrent users the larger the stripe size. bhabani s pradhan To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] bhabaniindiacc: @rediffmail.com Subject: RAID select Sent by: ml-errors 07/20/2003 12:49 PM Please respond to ORACLE-L Hi I have a DB server with RAID-1 and RAID-0+1 (There is no RAID-0). Also the NON-RAID diska are available. I have the tablespaces as: SYSTEM USERS01(Heavily Accessd) RWEBCONFIG(Less Accessed) RTRADE(Less Accessed) INDEX01(Heavily Accessd) INDEX02(Heavily Accessd) RBS01 RBS02 TEMP STAT(For PERFSTAT) What could be a suitable configuration for the datafiles under these tablespaces across the available RAIDs. Also where the backups and archive_dest should be. Can anybody give me some tips on this Thanks and Regards ___ Download the hottest happening ringtones here! OR SMS: Top tone to 7333 Click here now: http://sms.rediff.com/cgi-bin/ringtone/ringhome.pl -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: bhabani s pradhan 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: Thomas Day 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: Tanel Poder 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: RAID select
Assuming that you have 3 physical devices (NON-RAID, RAID-1, and RAID-0+1) my quick-and-dirty take on this is: NON-RAID redo logs - mirrored by Oracle control logs - mirrored by Oracle ORACLE_HOME RBS01 RBS02 TEMP STAT(For PERFSTAT) RAID-1 SYSTEM RWEBCONFIG(Less Accessed) RTRADE(Less Accessed) Archive destination - move to tape Backups - copy to tape RAID-0+1 USERS01(Heavily Accessd) INDEX01(Heavily Accessd) INDEX02(Heavily Accessd) I wouldn't worry about putting the indexes on the same device with the tables. It is a multi-platter device and each datafile will be stripped across multiple platters. You may want to look into the stripe size depending on the number of users who will be concurrently accessing the database. Basic rule-of-thumb, the more concurrent users the larger the stripe size. bhabani s pradhan To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] bhabaniindiacc: @rediffmail.com Subject: RAID select Sent by: ml-errors 07/20/2003 12:49 PM Please respond to ORACLE-L Hi I have a DB server with RAID-1 and RAID-0+1 (There is no RAID-0). Also the NON-RAID diska are available. I have the tablespaces as: SYSTEM USERS01(Heavily Accessd) RWEBCONFIG(Less Accessed) RTRADE(Less Accessed) INDEX01(Heavily Accessd) INDEX02(Heavily Accessd) RBS01 RBS02 TEMP STAT(For PERFSTAT) What could be a suitable configuration for the datafiles under these tablespaces across the available RAIDs. Also where the backups and archive_dest should be. Can anybody give me some tips on this Thanks and Regards ___ Download the hottest happening ringtones here! OR SMS: Top tone to 7333 Click here now: http://sms.rediff.com/cgi-bin/ringtone/ringhome.pl -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: bhabani s pradhan 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: Thomas Day 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: Re: RAID select
Thanks a Lot === On Mon, 21 Jul 2003 Thomas Day wrote : Assuming that you have 3 physical devices (NON-RAID, RAID-1, and RAID-0+1) my quick-and-dirty take on this is: NON-RAID redo logs - mirrored by Oracle control logs - mirrored by Oracle ORACLE_HOME RBS01 RBS02 TEMP STAT(For PERFSTAT) RAID-1 SYSTEM RWEBCONFIG(Less Accessed) RTRADE(Less Accessed) Archive destination - move to tape Backups - copy to tape RAID-0+1 USERS01(Heavily Accessd) INDEX01(Heavily Accessd) INDEX02(Heavily Accessd) I wouldn't worry about putting the indexes on the same device with the tables. It is a multi-platter device and each datafile will be stripped across multiple platters. You may want to look into the stripe size depending on the number of users who will be concurrently accessing the database. Basic rule-of-thumb, the more concurrent users the larger the stripe size. bhabani s pradhan To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] bhabaniindiacc: @rediffmail.com Subject: RAID select Sent by: ml-errors 07/20/2003 12:49 PM Please respond to ORACLE-L Hi I have a DB server with RAID-1 and RAID-0+1 (There is no RAID-0). Also the NON-RAID diska are available. I have the tablespaces as: SYSTEM USERS01(Heavily Accessd) RWEBCONFIG(Less Accessed) RTRADE(Less Accessed) INDEX01(Heavily Accessd) INDEX02(Heavily Accessd) RBS01 RBS02 TEMP STAT(For PERFSTAT) What could be a suitable configuration for the datafiles under these tablespaces across the available RAIDs. Also where the backups and archive_dest should be. Can anybody give me some tips on this Thanks and Regards ___ Download the hottest happening ringtones here! OR SMS: Top tone to 7333 Click here now: http://sms.rediff.com/cgi-bin/ringtone/ringhome.pl -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: bhabani s pradhan 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: Thomas Day 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). ___ Download the hottest happening ringtones here! OR SMS: Top tone to 7333 Click here now: http://sms.rediff.com/cgi-bin/ringtone/ringhome.pl -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: bhabani s pradhan 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).
RAID select
Hi I have a DB server with RAID-1 and RAID-0+1 (There is no RAID-0). Also the NON-RAID diska are available. I have the tablespaces as: SYSTEM USERS01(Heavily Accessd) RWEBCONFIG(Less Accessed) RTRADE(Less Accessed) INDEX01(Heavily Accessd) INDEX02(Heavily Accessd) RBS01 RBS02 TEMP STAT(For PERFSTAT) What could be a suitable configuration for the datafiles under these tablespaces across the available RAIDs. Also where the backups and archive_dest should be. Can anybody give me some tips on this Thanks and Regards ___ Download the hottest happening ringtones here! OR SMS: Top tone to 7333 Click here now: http://sms.rediff.com/cgi-bin/ringtone/ringhome.pl -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: bhabani s pradhan 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: DECODE AND SELECT
- Original Message - I am in urgent need of backporting oracle9i pl/sql to oracle8i.I have encountered some case satements like this CASE when x0 then (select y from deptno) else (select Z from emp) end i need to convert them to decode statements.Can anybody tell me how to write a select statement within decode. You already got some suggestions. But if you're backporting from 9i to 8i, don't bother: 8i supports CASE happily. At least in SQL... Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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).
