installation of portal3.0 of 9iAS on linux
Hi Everybody, I'm installing portal3.0 of oracle9iAS on redhat linux 6.1 It's giving some strange things. When I run it using ./ winstall at Cshell It gives error invalid SYS password. while SYS password of database is change_on_install. And when I run it in bourne shell it gives error ORACLE_HOME: Undefined variable While on giving command echo $ORACLE_HOME it shows it correctly I want help immediately. Thanks in advance. Tarun Sharma -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: TARUN SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Import/Export performance
Hi Roy, Planning, planning and planning is required to make this a piece-of-cake.. This one is a rather long one.. Sorry.. Prepare scripts to build tables, indexes and constraints etc.. Prebuild the tables in the target database. I am assuming that a database is already created with all the tablespaces etc.. Improving Export performance: 1) Use direct=y. That will make the export process significantly faster. 2) Along with (1), setting 'recordlength' to multiples of db_block_size or to its max value (65535) will help 'squeeze out' some more performance gain. Although, this parameter is to be used when exporting/importing on different OS where it has different default values, I use it for added performance gain. You may want to give it a trial run to see if that would help. 3) I do not export indexes. Improving Import Performance: 1) Keep database in no-archive log mode, if it is not already so. 2) Remember to use ignore=y since tables are already present. 3) Use commit=y to control rollback segment usage. 4) Do not import indexes by setting indexes=n (just to be sure). 5) Set buffer= to a high value, 5-10 MB should work fine (there is no proportional gain performance in raising this value too high). 6) Set analyze=n to suppress automatic estimation of table statistics. Analyze tables using your procedures after indexes etc are built. 7) For primary key constraint indexes, I keep the quota on the target tablespace to 0 to make it fail during import. (something I just find easier to remember). 8) Set log= to some log file name to capture all (good and bad) messages from the import process. 9) After the import is completed, set sort_area_size, sort_area_retained_size to a higher value (whatever is adequate and possible) to speed up index build process. Also, consider TEMPORARY type temp tablespace with properly configured initial and next (multiples of sort_area_size) extents. Make sure temp tablespace has ample room, should index build processes perform disk sorts. Also, make sure quota is okay on tablespaces for primary key constraint indexes. 10) Run all the index build scripts. Use nologging attribute and consider building indexes in parallel, if resources are available to do so. 11) Enable all the constraints etc. 12) After all indexes are successfully built, make sure the sort parameters are adjusted back to what they should be for running the db normally. Spot check and make sure everything looks fine and okay. 13) Do not forget the SQL*Net thingy.. Make necessary changes to global_name, TNSNAMES.ora and LISTENER.ora file. Bounce the listener. 14) Run your own procedures to analyze tables and indexes. 15) Take a cold back up. 16) Startup mount and change to archive log (if required). Open the db for users. 17) Time to hit the door.. Hope this helps... - Kirti Deshpande Verizon Information Services http://www.superpages.com > -Original Message- > From: Roy Ferguson [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, April 18, 2001 2:31 PM > To: Multiple recipients of list ORACLE-L > Subject: Import/Export performance > > I will be re-building a database using export/import and would like to > know what > specific things I can look at or do prior to doing the export and the > import to > increase the time it takes as well as to limit the number of invalid > objects. > > The database is 8.0.5.2.1 on Sun Sparc Solaris 2.6 and has 35,000 objects. > > I will be importing into an 8.1.6 database on the same O/S. > > Thanks in advance. Roy > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Problem with DBMS_SQL
Hi Steve/Dick, Thanks for your clarifications. You are right. I was relying on the grant via role and once I granted the privilege directly it worked fine. Regards, - Bhat -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: row count in table ?
Or if your tables are all analyzed and you want a rough answer (faster but less accurate) select table_name , num_rows from dba_tables where owner='&schema'; You might want to round the num_rows. Again, only as good as the last analyze - when it was done and estimate vs compute. Regards, Bruce -Original Message- Sent: Thursday, 19 April 2001 12:30 To: Multiple recipients of list ORACLE-L set heading stuff off spool tblcount.sql select 'select count(*) from ' || tablename || ' ;' from data_dictionary_table where schema = 'the one you want'; spool off @tblcount.sql On 18 Apr 2001, at 17:30, Andrea Oracle wrote: > Is there an easy way to find out how many rows are > there is each table for a schema? Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: row count in table ?
Analyze the tables, then look at num_rows in dba_tables. Andrea Oracle wrote: > All, > > Is there an easy way to find out how many rows are > there is each table for a schema? Thank you. > > Andrea > > __ > Do You Yahoo!? > Yahoo! Auctions - buy the things you want at great prices > http://auctions.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Andrea Oracle > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dennis M. Heisler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: row count in table ?
set heading stuff off spool tblcount.sql select 'select count(*) from ' || tablename || ' ;' from data_dictionary_table where schema = 'the one you want'; spool off @tblcount.sql On 18 Apr 2001, at 17:30, Andrea Oracle wrote: > Is there an easy way to find out how many rows are > there is each table for a schema? Thank you. http://www.polarworld.com/images/feces.jpg -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Direct Vs. SQL*net
Title: RE: Direct Vs. SQL*net
see answer below
> -Original Message-
> From: Reardon, Bruce (CALBBAY)
> [mailto:[EMAIL PROTECTED]]
>
> Unfortunately this doesn't work for NT.
>
> Under NT using 817, the program field from v$process always
> seems to show
> Oracle.exe.
> Using v$session.program you can determine if a user is using
> svrmgrl but I
> do not know how to
> Also, the terminal and machine fields show the same values.
>
> Is there a solution that will work under NT?
> >
> > Reply Separator
> > Author: "Charlie Mengler" <[EMAIL PROTECTED]>
> > Date: 4/18/2001 9:50 AM
> >
> > How can one determine via code (PL/SQL)
> > whether or not the current session is connected
> > "directly" (via the Bequeath protocol) or via
> > SQL*Net?
> >
> > The solution needs to work for both V7 & V8.
I'm not even going to try to write a query that would work under different versions of Oracle on different servers. I doubt that you could find a single query for all versions (unless maybe you use x$ tables.)
Here is the rough idea for a query for Oracle 8.1.7 on Windows NT. It would probably need to be enhanced.
select
a.sid,
a.serial#,
a.username,
decode (rtrim (substr (b.network_service_banner, 1, 18)),
'Windows NT TCP/IP', 'SQL*Net',
'Oracle Bequeath NT', 'Bequeath',
'Other?'
) as connection_method
from
v$session a,
v$session_connect_info b
where
a.sid = b.sid
and rtrim (substr (b.network_service_banner, 1, 18))
in ('Windows NT TCP/IP', 'Oracle Bequeath NT') ;
--
Jacques R. Kilchoer
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com
row count in table ?
All, Is there an easy way to find out how many rows are there is each table for a schema? Thank you. Andrea __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrea Oracle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Direct Vs. SQL*net
Just off the top of my head... If you're on a Unix platform, you can get the process id of the session from v$process (joined to v$session), then grep for that pid from the output of the Unix 'ps' command, then grep again for 'LOCAL=NO'. -wk -Original Message- Sent: Wednesday, April 18, 2001 3:31 PM To: Multiple recipients of list ORACLE-L I already thought of your suggestion but know that it won't be foolproof; at least for my evironment. I have a DBLINK that allows my developers to use the SQL*PLUS "COPY" to reference tables "on the local machine". In other words, I'd like a way to discern the difference between SQL> connect username/password SQL> connect username/password@PROD when in the first case I'm logged onto the host where "PROD" resides and ORACLE_SID=PROD = [EMAIL PROTECTED] wrote: > > Charlie, > > Now that's a good question. The best answer I can come up with is to check > the machine column in V$SESSION. if it matches the server machine name then > they should be using the beq protocol, I'd think. Otherwise they must be using > SQL*Net. Wonder if anyone else has a suggestion, Hey Jared, your the guru here > aren't you?? :-) > > Dick Goulet > > Reply Separator > Subject:Direct Vs. SQL*net > Author: "Charlie Mengler" <[EMAIL PROTECTED]> > Date: 4/18/2001 9:50 AM > > How can one determine via code (PL/SQL) > whether or not the current session is connected > "directly" (via the Bequeath protocol) or via > SQL*Net? > > The solution needs to work for both V7 & V8. > > -- > Charlie Mengler Maintenance Warehouse > [EMAIL PROTECTED] 10641 Scripps Summit Ct > 858-831-2229 San Diego, CA 92131 > The future is here. It is just not evenly distributed. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Charlie Mengler > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Charlie Mengler Maintenance Warehouse [EMAIL PROTECTED] 10641 Scripps Summit Ct 858-831-2229 San Diego, CA 92131 The future is here. It is just not evenly distributed. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Warren Kassel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Monitoring Archived Logs
Lindsay, Here are some points to ponder to help you develop a strategy for maintaining archive logs. If you use 3rd party backup software, does it make provision for maintaining archive logs? This will influence your decisions. Do you have a SLA? ( service level agreement ) If so, this will help you determine how many archive logs you need to keep online to ensure speedy recoveries should they be needed. Never delete archive logs that may be needed for a future recovery and have not yet been backed up on tape ( or media of your choice ) Using other peoples scripts is fine, as long as you understand how they work and what actions they will take under various circumstances such as the archive log disk area filling up. Don't trust the recoverability of your databases to scripts or software that you don't understand or are unsure of. Test *everything*. The point of the exercise is to help you understand what your requirements really are, then put together the tools that make it happen. When you can answer all of this, there are a number of script around that you can use or modify for your own use. Jared On Wednesday 18 April 2001 14:26, Lindsay Stoddard wrote: > Hi, > > Does anyone have a monitoring script and/or strategy for maintaining > current archived redo log files and deleting old ones? > > Thanks, > > Lindsay > ACS Governement Services -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle Financials DBAs Needed in Charlotte, NC..
Charlotte, North Carolina area manufacturing company is looking for several Oracle Financials DBAs to join its' I.T. team. These are full-time staff positions, so no contractors or third parties please. *JOB DESCRIPTION: Provide database administration for an Oracle ERP environment. Technical responsibilities include Database and Application installations, upgrades and patches; database backup and recovery; security; monitoring; performance tuning; capacity planning; analysis and design; automating administration functions. Requires customer support including analysis, recommendations and solutions for creating, upgrading, integrating and managing database systems in a distributed environment. Participate as part of a team providing 24 x 7 DBA support (with on-call rotations). Responsible for other duties assigned by the Manager Oracle Database Administrators Ability to multi-task and perform in an interrupt driven environment. Must be able to work independently *SKILLS REQUIRED: -Bachelor or technical degree required. -Must have at least five to seven years of relevant work experience, which should include at least four years of experience administering Oracle RDBMS plus, third party applications and tools running in a UNIX environment including the maintenance and support of Oracle Applications 10.7 NCA or higher. -Strong Oracle 7 and 8 background required, as well as a strong Oracle Applications knowledge. -8i, SQL, PL/SQL, Developer2000 and 6i, Express Server, Sales Analyzer and Discoverer Unix shell scripting -Strong Knowledge of Internet Technology (n-tier architecture, HTML, Java, Browsers, etc.) This position offers: * Opportunity to become a key member of the team * Compensation D.O.E... but competitive * Challenge * Relocation Assistance NO sub contracting positions available. PLEASE do not send your resume if you are not in the United States. For immediate consideration, please send your resume as an attachment to: Bill Law, Oracle Placement Specialist OraStaff, Inc. Ph: 1-800-549-8502-Please do not call if you need sponsorship Email: [EMAIL PROTECTED] Please use job code: One/Charlotte/Fin. DBA/Williams Note: This is only one of the many opportunities that we have available across the U.S. for candidates with Oracle skills who are U.S. citizens or permanent residents. So if this one is not a match for you, we invite you to send us your resume- as we quite possibly have the opportunity that you are seeking. We pay referral fees. So please contact me if you know of anyone who would be qualified/interested in the posiition described above- if it is not a match for you. Thanks, Bill Law -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: OraStaff INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Direct Vs. SQL*net
Hi, Unfortunately this doesn't work for NT. Under NT using 817, the program field from v$process always seems to show Oracle.exe. Using v$session.program you can determine if a user is using svrmgrl but I do not know how to Also, the terminal and machine fields show the same values. Is there a solution that will work under NT? Regards, Bruce -Original Message- Sent: Thursday, 19 April 2001 5:46 Dick, Try this: SQL> l 1 select p.spid OSPROCESS, 2 substr(p.program,1,25) OSPROG, 3 substr(s.osuser,1,12) OSUSER, 4 status, 7 substr(s.machine,1,12) MACHINE, 8 s.terminal 10 from v$session s, 11 v$process p 12 where s.paddr = p.addr 14 andosuser = rholman 13* order by osuser, s.sid SQL> RESULTS from internal connection: $. oraenv $sqlplus sa OSPROCESS OSPROG OSUSER STATUS MACHINE TERMINAL - --- --- -- 22446 oracle@cass (TNS V1-V3) rholman ACTIVE casspts/8 1 row selected. RESULTS from net8 connection: $. oraenv $sqlplus sa@cl85dev OSPROCESS OSPROG OSUSER STATUS MACHINE TERMINAL - --- --- -- 10876 oracle@cass (S001) rholman ACTIVE casspts/8 1 row selected. If you look at the program from v$process (OSPROG) you can see the difference between the two connections. Also a ps -ef|grep oracle will show protocol information on the internal connection. The machine column for both of these is the same since I ran them from the console of the same machine. -- Rodd Holman Oracle DBA (605) 988-1373 [EMAIL PROTECTED] Comments made are my own opinions and views. They do not represent views, policies, or procedures of LodgeNet Entertainment Corporation On Wednesday 18 April 2001 13:21, [EMAIL PROTECTED] wrote: > Charlie, > > Now that's a good question. The best answer I can come up with is to > check the machine column in V$SESSION. if it matches the server machine > name then they should be using the beq protocol, I'd think. Otherwise they > must be using SQL*Net. Wonder if anyone else has a suggestion, Hey Jared, > your the guru here aren't you?? :-) > > Dick Goulet > > Reply Separator > Author: "Charlie Mengler" <[EMAIL PROTECTED]> > Date: 4/18/2001 9:50 AM > > How can one determine via code (PL/SQL) > whether or not the current session is connected > "directly" (via the Bequeath protocol) or via > SQL*Net? > > The solution needs to work for both V7 & V8. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Bitmap Indexes sizes
Hi Darren, Yes, this sort of space blow-out is to be expected for a bitmap index on a volatile table. It is not so bad with more recent versions, and the MINIMIZE RECORDS PER BLOCK syntax can help. If the snapshot is manually refreshed, you will do better to drop this index prior to each refresh. Otherwise, a simple (aka B*-tree) index should be considered. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Thursday, 19 April 2001 8:15 To: Multiple recipients of list ORACLE-L I have (had) an index that according to index_stat (after analyze index) and dba_extents that reported to be approximately 53 Mb and took up 11 extents. The percentage increase is 50. I dropped the index and recreated it, after I adjusted the initial extents, it turns out the final size is less then 500k. This seems like a large amount for it to drop. The index is on a snapshot within our data warehouse. Has anybody else seen this ?? Is this a problem with bitmap indexes. ?? Thanks Darren Darren Browett P.EngThis message was transmitted Systems Admin/DBA using 100% recycled electrons Information and Communications Technology. City of Coquitlam P:(604) 927 - 3614 E:[EMAIL PROTECTED] - -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle 9iAS and Portal 3.0
I have installed Oracle 9iAS and am having problems with the Portal 3.0 install. The 9iAS server is Solaris 8 with 9iAS 1.0.2.1. The database server is Solaris 8 with Oracle 8.1.7. I am very new to Java and Apache so I hope someone can help steer me in the right direction. I am using the metalink document 132444.1 "Common Installation Problems with Portal 3.0 (9IAS)" to help me troubleshoot. My first problem was that the Java Virtual Machine was not properly installed in the database. I ran initjvm.sql and then reinstalled Portal 3.0. That took care of the first set of problems. When I try to connect to the "Portal Page", "Login Server Page" or the "Gateway Settings Page" I get a "Page cannot be Displayed" message. So ... Now I am trying to verify that the Apache Listener and Jserver are set up correctly. I can access http://machine_ip_address: . This page has the "HTTP Components" listed. I can access everything on this page but: "Jserver Demo" http://machine_ip_address:/servlet/IsItWorking/ and "mod_plsql" http://machine_ip_address:/pls/portal30_sso/portal30_sso.wwsso_app_admin .ls_login The document says that if you cannot access the Jserver Demo to verify that JDK 1.2.2 is installed. In my case it was not, so I downloaded the JDK 1.2.2_07 self extracting binary from Sun and installed it. I changed the jserv.properties file to point wrapper.bin to the location where JDK 1.2.2 was installed and added 2 wrapper.classpath lines for the dt.jar and tools.jar. I also modified the PATH variable to have the JDK 1.2.2 location as the first directory in the list. Then I stopped and started the Apache listener. I also verified that I can connect from the 9iAS server to the Database server using sqlplus which was successful. I still cannot access the Jserv Demo. I really don't know what to look at next. Any ideas? Thanks, Nancy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nancy McCormick INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Bitmap Indexes sizes
I have (had) an index that according to index_stat (after analyze index) and dba_extents that reported to be approximately 53 Mb and took up 11 extents. The percentage increase is 50. I dropped the index and recreated it, after I adjusted the initial extents, it turns out the final size is less then 500k. This seems like a large amount for it to drop. The index is on a snapshot within our data warehouse. Has anybody else seen this ?? Is this a problem with bitmap indexes. ?? Thanks Darren Darren Browett P.EngThis message was transmitted Systems Admin/DBA using 100% recycled electrons Information and Communications Technology. City of Coquitlam P:(604) 927 - 3614 E:[EMAIL PROTECTED] - -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Peoplesoft (sqr)
I'm trying to understand what is going on with our sqr install when migrating Oracle from 7.3.4 to 8.1.6 (on Sun 2.6). After changing the Oracle version we tried relinking the sqr executable in order to change the Oracle version (sqrmake prompts for version information). The relink failed because we don't have a c compiler on the server. What I don't understand is why the initial install doesn't ask for a c compiler but the relink does? Any ideas? Thanks. Henry -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henry Poras INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: block_size
The max db_block_size depends on your OS platform. 8K size is better for OLTP and 16-32K is better for Datawarehouse applications. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Wed, 18 Apr 2001 11:25:53 -0800 I'm in the process of upgrade to 8.1.6 from 8.0.5 and will be changing the block_size by re-building the database via exp/imp. This is on a Sun Sparc Solaris 2.6 server. I noticed that the db block_size can have a maximum value of 16k. I was wondering if anyone has experience in determining the block size that works best in their environment and if you could share how you determined the value. Thanks in advance. Roy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Meeting at IOUG
Don't go!!! Didn't you ever see Pinocchio? Brian Norrell Manager, MPI Development QuadraMed 511 E John Carpenter Frwy, Su 500 Irving, TX 75062 (972) 831-6600 -Original Message- Sent: Tuesday, April 17, 2001 2:28 PM To: Multiple recipients of list ORACLE-L Anybody knows what is a Pleasure Island event? Alex Hillman -Original Message- Sent: Tuesday, April 17, 2001 2:54 PM To: Multiple recipients of list ORACLE-L Glad to see so many people wanting to get together. I would like to propose a time and place... Tuesday night is the "big bash", and Wednesday night is a "Pleasure Island" event. There is a Monday night welcome reception in the exhibit hall until 7:45. So, I think the best time is Monday at 8pm, somewhere in the Swan and Dolphin complex (not knowing the Orlando area personally). http://www.swandolphin.com/restaur.htm lists the 15 restaurants (with 3D views). My vote is Juan & Only's - "A perfect place to enjoy moderately priced southwestern cuisine. Stop by the bar for the best margaritas in town! Open for dinner." for its bar and not super-loud setting so we can talk. - Ari Kaplan On Tue, 17 Apr 2001, Stephen Andert wrote: > I'll be there and would like to meet as many of you as possible. If anyone who knows the ropes can suggest a time/place, that would be great. As this is my first IOUG event, I don't have any ideas on where/when, so it's up to all you "veterans". > > Keep this thread going. > > > Stephen Andert > > > >>> [EMAIL PROTECTED] 04/16 8:15 PM >>> > don't know that any meeting has been scheduled, I do know that a number of > the members of the list are presenting and I intend to be at most of those > sessions :) > > on the other hand, Gaja's sessions tend to draw EVERYONE so you can be sure > of meeting others on the list there > > > >From: "Murali Vallath" <[EMAIL PROTECTED]> > >Reply-To: [EMAIL PROTECTED] > >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > >Subject: Meeting at IOUG > >Date: Mon, 16 Apr 2001 18:30:43 -0800 > > > > > >Are any of the floks from the list meeting during the IOUG-A live in > >Florida. > > > >I am attending and would like to join the meeting. > > > >Murali Vallath > >_ > >Get your FREE download of MSN Explorer at http://explorer.msn.com > > > >-- > >Please see the official ORACLE-L FAQ: http://www.orafaq.com > >-- > >Author: Murali Vallath > > INET: [EMAIL PROTECTED] > > > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > >San Diego, California-- Public Internet access / Mailing Lists > > > >To REMOVE yourself from this mailing list, send an E-Mail message > >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > >the message BODY, include a line containing: UNSUB ORACLE-L > >(or the name of mailing list you want to be removed from). You may > >also send the HELP command for other information (like subscribing). > > _ > Get your FREE download of MSN Explorer at http://explorer.msn.com > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Rachel Carmichael > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Stephen Andert > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ari D Kaplan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spe
Re: tnsnames problem
They will use the same tnsnames only if they are running the same orauser. Denise Gwinn [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Monitoring Archived Logs
Hi, Does anyone have a monitoring script and/or strategy for maintaining current archived redo log files and deleting old ones? Thanks, Lindsay ACS Governement Services -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lindsay Stoddard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: move schema away from system table space
You can move tables on 8i , but not indexes, you have to rebuild indexes in order to move them to other TS. - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Wednesday, April 18, 2001 4:20 PM > If you are on 8i you can move tables and indexes to different tablespaces > (very easy to do it with EZSQL for example) and then change default > tablespacefrom system to new one. > > Alex Hillman > > -Original Message- > Sent: Wednesday, April 18, 2001 8:55 AM > To: Multiple recipients of list ORACLE-L > > > Another option: > > 1. Export the users table data. > 2. alter user USERNAME default tablespace NEW_TABLESPACE temporary > tablespace TEMP_TABLESPACE; > 3. duplicate the schema with temp table names - tablename_temp > 4. import the data > 5. drop the old tables from the SYSTEM tablespace > 6. alter table TABLENAME_TEMP rename to TABLENAME; > > Or: > > 1. alter user USERNAME default tablespace NEW_TABLESPACE temporary > tablespace TEMP_TABLESPACE; > 2. create table TABLENAME_TEMP as (select * from TABLENAME); > 3. drop existing SYSTEM tables for the user > 4. alter table TABLENAME_TEMP rename to TABLENAME; > > With both of these methods you will not need to shutdown the system at all. > The second option should be done at a quite time, as you *could* (depending > on the amount of data within these tables) be doing a huge select, and you > don't want users updating to the table in SYSTEM, as you will loose data in > the new tables. If you do have users that are connected 24*7, this could be > difficult, but, if you are prepared to make them wait a while, you could > lock the table in exclusive mode whilst doing the switch and drop. > > HTH > > Mark > > > -Original Message- > Ashe > Sent: Wednesday, April 18, 2001 12:36 > To: Multiple recipients of list ORACLE-L > > > Best way is to use export/import utilities. This can be done with server on > line and you will get all table data if done at quite time when table is not > being updated. See Oracle Backup and Recovery Guide for info on exp/imp. > PA > -Original Message- > Sent: Tuesday, April 17, 2001 3:50 PM > To: Multiple recipients of list ORACLE-L > > > Hi All, > I need to move a user/schema away from the system > tablespace to a newly created user tablespace. Could > somebody point me to the references on how to do the > job? Since a production server is running off the > schema, is it possible to do the move without having > to shut down the server? > Thanks a lot. > WL > > __ > Do You Yahoo!? > Yahoo! Auctions - buy the things you want at great prices > http://auctions.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: lwm > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Patricia Ashe > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Mark Leith > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Hillman, Alex > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > ---
RE: Lawrence To's article on standby databases
Hi Mundo, Be careful if you are on V8.1.6.0 as I have had problems with implementing an architecture where the Standby DB is in Read Only mode during business hours and then manually recovered outside business hours. The problems were occuring whilst the Standby was shutdown and then restarted or indeed closed for recovery. The Arch Redo files were NOT being sent after the Standby DB was put into Read Only again In the end I developed a FTP solution which is fine for the purpose in question. Martin -Original Message- Sent: 18 April 2001 16:25 To: Multiple recipients of list ORACLE-L Hi Everyone, Where can I get Lawrence To's article on standby databases as mentioned in a recent standby discussion? Thanks...Mundo -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edmundo Vasquez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Martin Kendall INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: tnsnames problem
If they're installed into different ORACLE_HOMEs, they'll look in different default locations for the file. You can probably override this using the TNS_ADMIN environment variable. > -Original Message- > From: Eric.Chesebro [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, April 18, 2001 2:40 PM > To: ORACLE-L; Eric.Chesebro > Subject: tnsnames problem > > > When I open sqlworksheet I have no problem getting into the > database but > when I try getting into the db using sqlplus I get the > following error: > > ERROR: ORA-12154: TNS:could not resolve service name > > > Don't they both use the same tnsnames file? > > Thanks, > Eric > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Chesebro, Eric > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). >
RE: tnsnames problem
Was worksheet and sqlplus installed from the same version of the IMS. It could be possible that one is an older version and may be looking for sqlnet 2.3.x. Just a guess. Rick -Original Message- Sent: Wednesday, April 18, 2001 3:40 PM To: Multiple recipients of list ORACLE-L When I open sqlworksheet I have no problem getting into the database but when I try getting into the db using sqlplus I get the following error: ERROR: ORA-12154: TNS:could not resolve service name Don't they both use the same tnsnames file? Thanks, Eric -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chesebro, Eric INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locally Managed Tablespaces
I remember ssing somebody suggestion to create normal file in advance and then create temporary tablespace with reuse. Alex Hillman -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 18, 2001 2:56 PM To: Multiple recipients of list ORACLE-L I'm using LMTs with the UNIFORM EXTENT option on 8.1.6[.2], with great results. Two caveats I've noticed: 1) If you want to use LMTs for rollback segments, you MUST create at least one rollback segment in a dictionary managed tablespace first (in addition to the ever-present "system" RBS). This is bug#1176609, which you can lookup on Metalink... assuming that the palm pilot they're using as a webserver is up, of course. (-: 2) If you want to make your TEMP tablespace locally managed (i.e. created using TEMPFILE instead of DATAFILE), it will be created sparse on Unix systems. This means that (for example) you *won't* get an error if you inadvertently attempt to create a file larger than it's host filesystem... not up front, anyway. If that non-existent tempfile space is ever needed, you'll certainly get errors at that point (especially nasty if one of your control files is in the same filesystem). So make absolutely certain that the file size is entered correctly. According to Oracle Support, #2 is a documentation error which will be corrected in 9i. I've been meaning to submit an enhancement request, asking that file > filesystem be treated as an error, but haven't actually done it yet. Cheers! > -Original Message- > From: pashe [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, April 17, 2001 6:36 PM > To: ORACLE-L > Cc: pashe > Subject: Locally Managed Tablespaces > > > > I am interested in some statistics on Oracle locally managed > tablespaces. I > have been looking for any bugs or negative info about them. > Are they in use > at alot of sites? Seems like all the information I have come > across is > positive. Which is great! But maybe they aren't being used at alot of > sites. Can I hear about experiences from others on this > list? how many > sites are actually using them? > I have several databases that I am getting ready to go > production soon and > would like to create the tablespaces as locally managed, but need more > statistics. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:Direct Vs. SQL*net
Dick, Try this: SQL> l 1 select p.spid OSPROCESS, 2 substr(p.program,1,25) OSPROG, 3 substr(s.osuser,1,12) OSUSER, 4 status, 7 substr(s.machine,1,12) MACHINE, 8 s.terminal 10 from v$session s, 11 v$process p 12 where s.paddr = p.addr 14 andosuser = rholman 13* order by osuser, s.sid SQL> RESULTS from internal connection: $. oraenv $sqlplus sa OSPROCESS OSPROG OSUSER STATUS MACHINE TERMINAL - --- --- -- 22446 oracle@cass (TNS V1-V3) rholman ACTIVE casspts/8 1 row selected. RESULTS from net8 connection: $. oraenv $sqlplus sa@cl85dev OSPROCESS OSPROG OSUSER STATUS MACHINE TERMINAL - --- --- -- 10876 oracle@cass (S001) rholman ACTIVE casspts/8 1 row selected. If you look at the program from v$process (OSPROG) you can see the difference between the two connections. Also a ps -ef|grep oracle will show protocol information on the internal connection. The machine column for both of these is the same since I ran them from the console of the same machine. -- Rodd Holman Oracle DBA (605) 988-1373 [EMAIL PROTECTED] Comments made are my own opinions and views. They do not represent views, policies, or procedures of LodgeNet Entertainment Corporation On Wednesday 18 April 2001 13:21, [EMAIL PROTECTED] wrote: > Charlie, > > Now that's a good question. The best answer I can come up with is to > check the machine column in V$SESSION. if it matches the server machine > name then they should be using the beq protocol, I'd think. Otherwise they > must be using SQL*Net. Wonder if anyone else has a suggestion, Hey Jared, > your the guru here aren't you?? :-) > > Dick Goulet > > Reply Separator > Author: "Charlie Mengler" <[EMAIL PROTECTED]> > Date: 4/18/2001 9:50 AM > > How can one determine via code (PL/SQL) > whether or not the current session is connected > "directly" (via the Bequeath protocol) or via > SQL*Net? > > The solution needs to work for both V7 & V8. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rodd Holman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Insert Cardinalities into the data dictionary directly
One of interesting uses of this feature is creation of statistics for temporary tables which cannot be done otherwise. It is Tom Kite's idea. Alex Hillman -Original Message- Sent: Wednesday, April 18, 2001 1:15 PM To: Multiple recipients of list ORACLE-L Take a look at dbms_stats.set_column_stats and dbms_stats.set_table_stats. The latter allows you to set the number of rows, the size of the table and such ; the first allows you to set the number of distinct values for a column the number of nulls, etc. I'm not sure what you are trying to accomplish with this. Is simply changing the cardinalities of a the columns enough? What about the distribution of those values? If the changes result in a different query path, and your queries run more slowly, does that mean they will do so when the statistics truly reflect the database. Perhaps they are running more slowly because you lied to the optimizer. "I am aware that if this is was an environment with good cardinalities, these values could be exported and imported into another environment." Cardinality reflects the ratio of distinct values to the total number of values in the database. I can think of scenarios where cardinality might change significantly for a time. But I would think in most cases it would remain fairly constant. FYI, if statements which assert something contrary to fact are subjunctive not conditional. "If this WERE an environment with good cardinalities" Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Wednesday, April 18, 2001 8:06 AM To: Multiple recipients of list ORACLE-L I am trying again -- To obtain a good analysis of a SQL statement especially in a new development environment, based on the environment that it is to be deployed on, it would good to sometime reflect/simulate the production volume. Is there a way to input table cardinalities directly into the data dictionary so that the Optimizer could be made to act like in production. I am aware that if this is was an environment with good cardinalities, these values could be exported and imported into another environment. My question is this possible from scratch? Regards, Murali Vallath _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Murali Vallath INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Import/Export performance
I will be re-building a database using export/import and would like to know what specific things I can look at or do prior to doing the export and the import to increase the time it takes as well as to limit the number of invalid objects. The database is 8.0.5.2.1 on Sun Sparc Solaris 2.6 and has 35,000 objects. I will be importing into an 8.1.6 database on the same O/S. Thanks in advance. Roy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
block_size
I'm in the process of upgrade to 8.1.6 from 8.0.5 and will be changing the block_size by re-building the database via exp/imp. This is on a Sun Sparc Solaris 2.6 server. I noticed that the db block_size can have a maximum value of 16k. I was wondering if anyone has experience in determining the block size that works best in their environment and if you could share how you determined the value. Thanks in advance. Roy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Direct Vs. SQL*net
I already thought of your suggestion but know that it won't be foolproof; at least for my evironment. I have a DBLINK that allows my developers to use the SQL*PLUS "COPY" to reference tables "on the local machine". In other words, I'd like a way to discern the difference between SQL> connect username/password SQL> connect username/password@PROD when in the first case I'm logged onto the host where "PROD" resides and ORACLE_SID=PROD = [EMAIL PROTECTED] wrote: > > Charlie, > > Now that's a good question. The best answer I can come up with is to check > the machine column in V$SESSION. if it matches the server machine name then > they should be using the beq protocol, I'd think. Otherwise they must be using > SQL*Net. Wonder if anyone else has a suggestion, Hey Jared, your the guru here > aren't you?? :-) > > Dick Goulet > > Reply Separator > Subject:Direct Vs. SQL*net > Author: "Charlie Mengler" <[EMAIL PROTECTED]> > Date: 4/18/2001 9:50 AM > > How can one determine via code (PL/SQL) > whether or not the current session is connected > "directly" (via the Bequeath protocol) or via > SQL*Net? > > The solution needs to work for both V7 & V8. > > -- > Charlie Mengler Maintenance Warehouse > [EMAIL PROTECTED] 10641 Scripps Summit Ct > 858-831-2229 San Diego, CA 92131 > The future is here. It is just not evenly distributed. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Charlie Mengler > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Charlie Mengler Maintenance Warehouse [EMAIL PROTECTED] 10641 Scripps Summit Ct 858-831-2229 San Diego, CA 92131 The future is here. It is just not evenly distributed. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Direct Vs. SQL*net
And for getting machine name one can use sys_context('USERENV', 'HOST').
Alex Hillman
-Original Message-
Sent: Wednesday, April 18, 2001 2:22 PM
To: Multiple recipients of list ORACLE-L
Charlie,
Now that's a good question. The best answer I can come up with is to
check
the machine column in V$SESSION. if it matches the server machine name then
they should be using the beq protocol, I'd think. Otherwise they must be
using
SQL*Net. Wonder if anyone else has a suggestion, Hey Jared, your the guru
here
aren't you?? :-)
Dick Goulet
Reply Separator
Author: "Charlie Mengler" <[EMAIL PROTECTED]>
Date: 4/18/2001 9:50 AM
How can one determine via code (PL/SQL)
whether or not the current session is connected
"directly" (via the Bequeath protocol) or via
SQL*Net?
The solution needs to work for both V7 & V8.
--
Charlie Mengler Maintenance Warehouse
[EMAIL PROTECTED] 10641 Scripps Summit Ct
858-831-2229 San Diego, CA 92131
The future is here. It is just not evenly distributed.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Charlie Mengler
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Hillman, Alex
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Index Block Dumps
Hi list, Oracle 7 // HP-UX How can I generate an Index Dump? And how can I read this data? TIA ___ Do You Yahoo!? Envía mensajes instantáneos y recibe alertas de correo con Yahoo! Messenger - http://messenger.yahoo.es -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Pablo=20ksksksk?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Ability for non DBA user to kill session.
Native dinamic SQL is execute immediate. Alex Hillman -Original Message- Sent: Wednesday, April 18, 2001 12:46 PM To: Multiple recipients of list ORACLE-L Are you sure that "execute immediate" doesn't support "alter system" command? Maybe it's a bug? Which version, which OS? -Original Message- Sent: Tuesday, April 17, 2001 7:10 PM To: Multiple recipients of list ORACLE-L Hi All, Further to that, it is necessary to use DBMS_SQL or native dynamic SQL for this as PL/SQL does not support ALTER SYSTEM directly. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Wednesday, 18 April 2001 3:37 To: Multiple recipients of list ORACLE-L Create a procedure as SYS (or someone else powerful) which does the 'alter system' and then grant just the proc to the user hth connor --- lerobe - Lee Robertson <[EMAIL PROTECTED]> wrote: > All, > > Is there a method for allowing a non DBA user to > kill their own (and only > their own) session. I have had a trawl through > Metalink and have seen > various methods (using procedures) of doing it but > all of these appear to > rely on granting the alter system role to the user. > > Oracle 8.0.5.0.0 > Compaq Tru64 4.0f > > Regards > > Lee > > Lee Robertson > Acxiom > Tel:0191 525 7344 > Fax:0191 525 7007 > Email: [EMAIL PROTECTED] > > > > > The information contained in this communication is > confidential, is intended only for the use of the > recipient > named above, and may be legally privileged. If the > reader > of this message is not the intended recipient, you > are > hereby notified that any dissemination, distribution > or > copying of this communication is strictly > prohibited. > If you have received this communication in error, > please > re-send this communication to the sender and delete > the > original message or any copy of it from your > computer > system. > = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) "Some days you're the pigeon, some days you're the statue" Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Direct Vs. SQL*net
Look at the terminal column of v$process. It's value will be "UNKNOWN" for user sql*net connections and pts/? for those which are not. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Wednesday, April 18, 2001 11:22 AM To: Multiple recipients of list ORACLE-L Charlie, Now that's a good question. The best answer I can come up with is to check the machine column in V$SESSION. if it matches the server machine name then they should be using the beq protocol, I'd think. Otherwise they must be using SQL*Net. Wonder if anyone else has a suggestion, Hey Jared, your the guru here aren't you?? :-) Dick Goulet Reply Separator Author: "Charlie Mengler" <[EMAIL PROTECTED]> Date: 4/18/2001 9:50 AM How can one determine via code (PL/SQL) whether or not the current session is connected "directly" (via the Bequeath protocol) or via SQL*Net? The solution needs to work for both V7 & V8. -- Charlie Mengler Maintenance Warehouse [EMAIL PROTECTED] 10641 Scripps Summit Ct 858-831-2229 San Diego, CA 92131 The future is here. It is just not evenly distributed. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Insert Cardinalities into the data dictionary directly
Thanks for the feedback, I am coming from the Oracle Rdb world, where these row counts/cardinialities could be inserted into the data dictionary to simulate the optimizer behaviour similar to a production environment. I see your point, this could be a negative impact to the optimizer. Thanks for the input.] Murali Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Wed, 18 Apr 2001 09:15:23 -0800 Take a look at dbms_stats.set_column_stats and dbms_stats.set_table_stats. The latter allows you to set the number of rows, the size of the table and such ; the first allows you to set the number of distinct values for a column the number of nulls, etc. I'm not sure what you are trying to accomplish with this. Is simply changing the cardinalities of a the columns enough? What about the distribution of those values? If the changes result in a different query path, and your queries run more slowly, does that mean they will do so when the statistics truly reflect the database. Perhaps they are running more slowly because you lied to the optimizer. "I am aware that if this is was an environment with good cardinalities, these values could be exported and imported into another environment." Cardinality reflects the ratio of distinct values to the total number of values in the database. I can think of scenarios where cardinality might change significantly for a time. But I would think in most cases it would remain fairly constant. FYI, if statements which assert something contrary to fact are subjunctive not conditional. "If this WERE an environment with good cardinalities" Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Wednesday, April 18, 2001 8:06 AM To: Multiple recipients of list ORACLE-L I am trying again -- To obtain a good analysis of a SQL statement especially in a new development environment, based on the environment that it is to be deployed on, it would good to sometime reflect/simulate the production volume. Is there a way to input table cardinalities directly into the data dictionary so that the Optimizer could be made to act like in production. I am aware that if this is was an environment with good cardinalities, these values could be exported and imported into another environment. My question is this possible from scratch? Regards, Murali Vallath _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Murali Vallath INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Murali Vallath INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
NT/Oracle defrag: just say no / RE: server sizing [NT: need XEON?]
Lerone, (It appears you wanted to also reply to the list, but didn't get the list address into your response, so I'm including your entire message below in this reply to the list. ) Glad you found the info useful. I hope you are going to be able to get a box that can be upgraded from 2 cpus to at least 4 (or that your SLA states that additional scaling might require whole replacement with a 4way or 6way or 8way box later). We had a discussion of NT/Oracle defrag recently. As you probably know, the "conventional wisdom" for NT SysAdmins is to use one of the better defrag products, and frequently. http://www.ultratech-llc.com/Personal/Files/?File=Defragger.TXT However, Oracle says that *IF* you do things "right", you should NOT want-to/need-to/have-to defrag. Something along the lines of: NTFS is such that when the Oracle db files are pre-allocated, they are basically "pre-defragged", and since they are maintained as large files, there are *no* conditions under which they are going to fragment (at the OS level) after being created. All defragging should be internal to Oracle (imp/exp, or whatever...) Having repeatedly read the "conventional wisdom" about the need to defrag NT constantly, I was initially amazed at Oracle's assertion, but in subsequent discussion with Oracle tech support, as well as the gurus in this list and the NT list, it turns out Oracle is right. Of course for a non-db server (eg, a file server) that has a lot of small files that change a lot, defragging is needed. I noticed that you haven't mentioned backup issues. We are currently struggling to decide between getting the Oracle modules from either Legato or Veritas (hot backup), or just staying with NT4, and doing manual/scripted cold backups only. On another, semi-related topic, when I used to be subscribed to a couple of Netware SysAdmin lists, I noticed that there was a pretty high percentage of people subscribed with a *lot* of "old school" type engineering and hardware Intel/Drive-config/Networking expertise. You might consider subscribing to one of those and slip a few hardware questions in. (archives: http://lsv.syr.edu/archives/novell.html, that list's super-guru: Joe Doupnik <[EMAIL PROTECTED]>) Of course beware that SysAmins, especially Netwareistas & other old timers, usually tend to hate dbas, and refer to Oracle as "the beast". :) regards, ep On 18 Apr 2001, at 10:04, Streeter, Lerone A LBX <[EMAIL PROTECTED]> wrote: To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> Date sent: Wed, 18 Apr 2001 10:04:06 -0500 > thank you all, such an incredible amount of information and some much > desired real world testimony. just off the top of my head we'll more than > likely be going with compaq and there are versions of windows 2000 that > support 8GB of RAM so that should suffice. a lot of my information is > gestimates based on what we have or know. i consider us small-to-medium and > the 300 users and such are worst case guesses based on one time peaks. > > as i remember we were looking at dual 1GHz processor boxii and being that > our existing, insufficient, storage is about 200G we'll be looking at > external rack mount chassis. we have to archive/purge our database at > regular intervals to manage space and the archive information needs to be > readily accessible for six years. right now we've got a production database > and at least four archive databases which consume about 90G. > > I think we'll be ok with the mixed bag of drive configurations, raid5 and > raid1; no raid0... failed drive will kill the raid0 set, better to use a > more recoverable raid; which brings up another question. Defragmentation. > they talked a lot on defragmentation so now i'm wondering how may of you > NT'ers use an O/S defragmenter or do you use the export/drop/build/import > method? not good with millions of rows i'd assume from my own personal > experiences with migrating from server to server. i'm working on a > spec/tech document and i'll post it when completed, with acknowledgements of > course... thanks. > > > > === > Lerone Streeter > System Analyst > Abbott LBG > [EMAIL PROTECTED] > === ... > On 17 Apr 2001, at 12:11, Streeter, Lerone A LBX wrote: > > Date sent:Tue, 17 Apr 2001 12:11:22 -0800 > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > From: "Streeter, Lerone A LBX" > <[EMAIL PROTECTED]> > Subject: RE: server sizing > > ... > > > why NT? familiarity and comfort. we've asked and everyone doted on > > oracle's ability to run on NT just as well as *nix and being that we have > 0 > > *nix boxes mgmt of course wanted NT. we looked for support in having > oracle > > on *nix but found none and accepted the offering. > > ... > > >>...right now we have about 100 users and a
RE: move schema away from system table space
If you are on 8i you can move tables and indexes to different tablespaces (very easy to do it with EZSQL for example) and then change default tablespacefrom system to new one. Alex Hillman -Original Message- Sent: Wednesday, April 18, 2001 8:55 AM To: Multiple recipients of list ORACLE-L Another option: 1. Export the users table data. 2. alter user USERNAME default tablespace NEW_TABLESPACE temporary tablespace TEMP_TABLESPACE; 3. duplicate the schema with temp table names - tablename_temp 4. import the data 5. drop the old tables from the SYSTEM tablespace 6. alter table TABLENAME_TEMP rename to TABLENAME; Or: 1. alter user USERNAME default tablespace NEW_TABLESPACE temporary tablespace TEMP_TABLESPACE; 2. create table TABLENAME_TEMP as (select * from TABLENAME); 3. drop existing SYSTEM tables for the user 4. alter table TABLENAME_TEMP rename to TABLENAME; With both of these methods you will not need to shutdown the system at all. The second option should be done at a quite time, as you *could* (depending on the amount of data within these tables) be doing a huge select, and you don't want users updating to the table in SYSTEM, as you will loose data in the new tables. If you do have users that are connected 24*7, this could be difficult, but, if you are prepared to make them wait a while, you could lock the table in exclusive mode whilst doing the switch and drop. HTH Mark -Original Message- Ashe Sent: Wednesday, April 18, 2001 12:36 To: Multiple recipients of list ORACLE-L Best way is to use export/import utilities. This can be done with server on line and you will get all table data if done at quite time when table is not being updated. See Oracle Backup and Recovery Guide for info on exp/imp. PA -Original Message- Sent: Tuesday, April 17, 2001 3:50 PM To: Multiple recipients of list ORACLE-L Hi All, I need to move a user/schema away from the system tablespace to a newly created user tablespace. Could somebody point me to the references on how to do the job? Since a production server is running off the schema, is it possible to do the move without having to shut down the server? Thanks a lot. WL __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: lwm INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Patricia Ashe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
tnsnames problem
When I open sqlworksheet I have no problem getting into the database but when I try getting into the db using sqlplus I get the following error: ERROR: ORA-12154: TNS:could not resolve service name Don't they both use the same tnsnames file? Thanks, Eric -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chesebro, Eric INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Temporary Tablespace Design
Temporary tablespace also used after sorting in memory finished (for sorts small enough to not need intermediate sorts and merging) and sort_area_retained_size less then sort_area_size. Alex Hillman -Original Message- Sent: Wednesday, April 18, 2001 12:26 PM To: Multiple recipients of list ORACLE-L Well, not quite. SORT_AREA_SIZE specifies the maximum amount, in bytes, of memory to use for a sort. After the sort is complete and all that remains to do is to return the rows, the memory is released down to the size specified by SORT_AREA_RETAINED_SIZE. After the last row is returned, all memory is freed. Temporary disk is used for the intermediate sort runs only if the SORT_AREA_SIZE isn't large enough to handle the sort in memory. The SORT_AREA_RETAINED_SIZE controls the size of the read buffer which is used to maintain a portion of the sort in memory. Myself, I use 2048000 for SORT_AREA_SIZE, and 512000 for SORT_AREA_RETAINED_SIZE. >>> [EMAIL PROTECTED] 04/18/01 11:36AM >>> I think you are not correct. First - Oracle allocates memory for sorting as needed by 8K chunks up to sort_area_size. Second if your sort_area_size is large enough to do sort in memory and your sort_area_retained_size < sort_area_size oracle will dump sorted data into temporary tablespace and then read from this tablespace. So it is a tradeoff between late memory release and temporary tablespace I/O. Alex Hillman -Original Message- Sent: Wednesday, April 18, 2001 10:40 AM To: Multiple recipients of list ORACLE-L Hi John, As you mentioned, it depends on a number of factors. I believe I replied to a question about improving performance of index building after an import. Anyway, here is the reasoning behind why I said that When Oracle begins sorting, it starts with the memory equal to sort_area_retained_size value and may eventually acquire memory equal to sort_area_size. This involves at OS level, malloc (and may be some other) memory mgmt routine(s). After the sort phase is complete, Oracle will trigger memory de-allocation and the OS will go at work again. If there are servile sessions performing sorts, there can be an increased OS level memory mgmt activity (while attaining sort_area_size and releasing it back to sort_area_retained_size) . But if the server has *enough* memory keeping sort_area_retained_size equal to sort_area_size may actually help. I also assume that these days most installations have ample memory. And that the advice of using 50% (which I had heard of) of sort area size for sort area retained size should always be viewed in light of one's requirements and resource availability. But I can't stop wondering... how come it was 50% and not 10% or 20%.. so it's all relative. I have been using what I suggested, for a number of years now with no problems or complaints. Cheers ! - Kirti Deshpande Verizon Information Services http://www.superpages.com > -Original Message- > From: Hallas, John [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, April 18, 2001 6:21 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Temporary Tablespace Design > > FOR YOUR INFORMATION > > ESIS and EPFAL are now part of Logica. The Internet email addresses of the > staff has changed to the following - [EMAIL PROTECTED] eg > [EMAIL PROTECTED] Emails using the old format will continue to be > delivered until 30th June 2001. > > > Kirti Deshpande writes > > "Also, keep SORT_AREA_RETAINED_SIZE the same as SORT_AREA_SIZE. As the > former will be used while fetching data after sort phase is completed." > > I thought best advice was to make the retained size smaller (50%?) of > sort_area_size to allow initial sort memory to be released after the first > part of the sort is managed and only the merge phase of a disk sort is > left > to do. > > I appreciate that a lot depends on the amount of memory available and the > number of concurrent processes that may be performing sorts but surely the > aim is to free memory up as soon as possible down to the > sort_area_retained_size > > Thanks > > John > > (PS I do apologise for the rubbish at the top of this mail but it is > inserted after I have sent the mail into our mail gateway) > > > > > > This e-mail and any files transmitted with it, are confidential to Logica > and are intended solely for the use of the individual or entity to whom > they are addressed. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Hallas, John > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you wa
OT: eBusiness article
Interesting business article from the CEO of Abbott, Miles D. White, "Old Line Online: Transforming Traditional Companies Through E-Business": https://abbott.com/corporate/corporate_center.html# -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RMAN doubts
Never mind, i could fix it. I had to configure Legato to use device /dev/rmt/0hbn instead of /dev/rmt/0 Thank you - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Wednesday, April 18, 2001 1:31 PM > What is your problem? > > I use 816. > There is problem with sbttest in 816. But backups and recovery work fine. > You need to relink the libobk.so file. > > I you provide details, I would try to answer. > > -Rahul > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Monday, April 16, 2001 2:15 PM > > > > Sorry to bother you, i was using RMAN to backup to disk for over 1 year, > > but we need to backup to > > tape, so, i had to install Legato. > > I'm having some troubles, and i only want to know if you also experienced > > the same troubles. > > I can do backup to sbt_tape, but i can't read the saved files. > > I created a TAR in Metalink, and checked Legato Site out.but they > still > > didn't anwer me back.. > > so, i'll appreciate any feed backup. > > Thank you. > > > > > > - Original Message - > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > Sent: Thursday, April 12, 2001 6:40 PM > > > > > > > The database 'may' be in NOARCHIVELOG mode > > > 1. Its a 300 GB database and RMAN picks up only the USED blocks to might > > > save > > >time and disk space for disk backup > > > 2. We back up directly to tape. So if done through RMAN, we need not > keep > > >inventory of tapes as RMAN/Legato would maintain it internally > > > 3. I understand that with RMAN, even if database is in NOARCHIVELOG mode > > >still it would allow incremental backups which would mean saving of > > time. > > > -Rahul > > > > > > > > > - Original Message - > > > To: "LazyDBA mailing list" <[EMAIL PROTECTED]> > > > Sent: Wednesday, April 11, 2001 5:19 PM > > > > > > > > > > If you are in NOARCHIVELOG mode you have to shutdown the Db (either > > within > > > > or out of RMAN) before a backup. There's no real benefit using RMAN > for > > > cold > > > > backups. > > > > > > _ > > > > Get Your Private, Free E-mail from MSN Hotmail at > > http://www.hotmail.com. > > > > > > > > > > > > > > > > Think you know someone who can answer the above question? Forward it > to > > > them! > > > > to unsubscribe, send a blank email to > [EMAIL PROTECTED] > > > > to subscribe send a blank email to [EMAIL PROTECTED] > > > > Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl > > > > Tell yer mates about http://www.farAwayJobs.com > > > > > > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > -- > > > Author: Rahul Dandekar > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > > San Diego, California-- Public Internet access / Mailing Lists > > > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You may > > > also send the HELP command for other information (like subscribing). > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Claudio Roca > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing Lists > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Rahul Dandekar > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Claudio Roca INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Die
RE: Locally Managed Tablespaces
I'm using LMTs with the UNIFORM EXTENT option on 8.1.6[.2], with great results. Two caveats I've noticed: 1) If you want to use LMTs for rollback segments, you MUST create at least one rollback segment in a dictionary managed tablespace first (in addition to the ever-present "system" RBS). This is bug#1176609, which you can lookup on Metalink... assuming that the palm pilot they're using as a webserver is up, of course. (-: 2) If you want to make your TEMP tablespace locally managed (i.e. created using TEMPFILE instead of DATAFILE), it will be created sparse on Unix systems. This means that (for example) you *won't* get an error if you inadvertently attempt to create a file larger than it's host filesystem... not up front, anyway. If that non-existent tempfile space is ever needed, you'll certainly get errors at that point (especially nasty if one of your control files is in the same filesystem). So make absolutely certain that the file size is entered correctly. According to Oracle Support, #2 is a documentation error which will be corrected in 9i. I've been meaning to submit an enhancement request, asking that file > filesystem be treated as an error, but haven't actually done it yet. Cheers! > -Original Message- > From: pashe [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, April 17, 2001 6:36 PM > To: ORACLE-L > Cc: pashe > Subject: Locally Managed Tablespaces > > > > I am interested in some statistics on Oracle locally managed > tablespaces. I > have been looking for any bugs or negative info about them. > Are they in use > at alot of sites? Seems like all the information I have come > across is > positive. Which is great! But maybe they aren't being used at alot of > sites. Can I hear about experiences from others on this > list? how many > sites are actually using them? > I have several databases that I am getting ready to go > production soon and > would like to create the tablespaces as locally managed, but need more > statistics.
Problems with 8.1.6.3.4 patch on NT/2000
Hi there! I have just installed the 8.1.6.3.4 patch on Win2k (i.e. upgraded from 8.1.6.3.0 to 8.1.6.3.4) and now the db is nor starting anymore. The Windows service starts, but it does not start the database. When trying to use oradim to start the db, I get some kernl32.dll error message etc. Weird things. Anybody else experiencing difficulties with the 8.1.6.3.4 patch? Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Helmut Daiminger INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Weird link problem
Good question. I just tried it and it retuns 782 instantly - which is correct. So what is the problem with the star (*) ??? ..tom > -Original Message- > From: Tim Sawmiller [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, April 18, 2001 10:40 AM > To: Multiple recipients of list ORACLE-L > Subject: Re: Weird link problem > > What happens if you do select count(name) from tab1.table? > > >>> [EMAIL PROTECTED] 04/18/01 09:55AM >>> > AIX 4.3 > RDBMS 8.1.6.2 > > When I select through a database link I have having a strange problem. If I > select a field or fields it works. If I select a count it hangs. Any ideas? > > select count(*) from tab1.table; **hangs** > > select name, address from tab1.table; **works fine** > > Help!!! > > ..tom > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Tim Sawmiller > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Temporary Tablespace Design
Hi Alex, Yes, Thanks for catching it. I got mixed up in explaining the memory allocation properly and left out some things. Sorry. What you say, is what happens in the very first sort as far as allocating memory in chunks (until sort_area_size is reached) is concerned. When the very first sort operation completes, 'sort area' is left in PGA (UGA for MTS) with the size equal to sort_area_retained_size (after deallocating additional memory, whenever the free() call is executed). Subsequent sort operation will start with 'sort area' of the size equal to sort_area_retained_size and start allocating memory when needed till it reaches the max allowed (sort_area_size). Keeping these two areas of the same value avoids this memory allocation/deallocation process, provided there is enough memory available to do so. Also, the sort_area_retained_size is used in the 'fetch' phase of a sort opeation. So, if the sort had to use disk, then it will help reduce the number of I/Os. This is my understanding of how sort is handled. Anyone knowing more 'internal' workings of sort, please let me (and us) know so I can get this straight. Thanks. - Kirti Deshpande Verizon Information Services http://www.superpages.com > -Original Message- > From: Hillman, Alex [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, April 18, 2001 10:37 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Temporary Tablespace Design > > I think you are not correct. First - Oracle allocates memory for sorting > as > needed by 8K chunks up to sort_area_size. Second if your sort_area_size is > large enough to do sort in memory and your sort_area_retained_size < > sort_area_size oracle will dump sorted data into temporary tablespace and > then read from this tablespace. So it is a tradeoff between late memory > release and temporary tablespace I/O. > > Alex Hillman > > -Original Message- > Sent: Wednesday, April 18, 2001 10:40 AM > To: Multiple recipients of list ORACLE-L > > > Hi John, > As you mentioned, it depends on a number of factors. I believe I replied > to > a question about improving performance of index building after an import. > Anyway, here is the reasoning behind why I said that When Oracle > begins > sorting, it starts with the memory equal to sort_area_retained_size value > and may eventually acquire memory equal to sort_area_size. This involves > at > OS level, malloc (and may be some other) memory mgmt routine(s). After the > sort phase is complete, Oracle will trigger memory de-allocation and the > OS > will go at work again. If there are servile sessions performing sorts, > there > can be an increased OS level memory mgmt activity (while attaining > sort_area_size and releasing it back to sort_area_retained_size) . But if > the server has *enough* memory keeping sort_area_retained_size equal to > sort_area_size may actually help. I also assume that these days most > installations have ample memory. And that the advice of using 50% (which I > had heard of) of sort area size for sort area retained size should always > be > viewed in light of one's requirements and resource availability. But I > can't > stop wondering... how come it was 50% and not 10% or 20%.. so it's all > relative. I have been using what I suggested, for a number of years now > with no problems or complaints. > > Cheers ! > > - Kirti Deshpande > Verizon Information Services >http://www.superpages.com > > > -Original Message- > > From: Hallas, John [SMTP:[EMAIL PROTECTED]] > > Sent: Wednesday, April 18, 2001 6:21 AM > > To: Multiple recipients of list ORACLE-L > > Subject:RE: Temporary Tablespace Design > > > > FOR YOUR INFORMATION > > > > ESIS and EPFAL are now part of Logica. The Internet email addresses of > the > > staff has changed to the following - [EMAIL PROTECTED] eg > > [EMAIL PROTECTED] Emails using the old format will continue to be > > delivered until 30th June 2001. > > > > > > Kirti Deshpande writes > > > > "Also, keep SORT_AREA_RETAINED_SIZE the same as SORT_AREA_SIZE. As the > > former will be used while fetching data after sort phase is completed." > > > > I thought best advice was to make the retained size smaller (50%?) of > > sort_area_size to allow initial sort memory to be released after the > first > > part of the sort is managed and only the merge phase of a disk sort is > > left > > to do. > > > > I appreciate that a lot depends on the amount of memory available and > the > > number of concurrent processes that may be performing sorts but surely > the > > aim is to free memory up as soon as possible down to the > > sort_area_retained_size > > > > Thanks > > > > John > > > > (PS I do apologise for the rubbish at the top of this mail but it is > > inserted after I have sent the mail into our mail gateway) > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Ser
Re:Direct Vs. SQL*net
Charlie, Now that's a good question. The best answer I can come up with is to check the machine column in V$SESSION. if it matches the server machine name then they should be using the beq protocol, I'd think. Otherwise they must be using SQL*Net. Wonder if anyone else has a suggestion, Hey Jared, your the guru here aren't you?? :-) Dick Goulet Reply Separator Author: "Charlie Mengler" <[EMAIL PROTECTED]> Date: 4/18/2001 9:50 AM How can one determine via code (PL/SQL) whether or not the current session is connected "directly" (via the Bequeath protocol) or via SQL*Net? The solution needs to work for both V7 & V8. -- Charlie Mengler Maintenance Warehouse [EMAIL PROTECTED] 10641 Scripps Summit Ct 858-831-2229 San Diego, CA 92131 The future is here. It is just not evenly distributed. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: authentication externally on WinNT
I was just recently playing around with this and found a couple of tips to fix my problem: There is a registry setting called: OSAUTH_PREFIX_DOMAIN that is now defaulted to TRUE for 8.1 databases. To get my machine to login without a username/password I had to create an oracle user name like "MYDOMAIN\USERABC". ( not sure if it matters but the notes also said to put everything in uppercase). You can set this to false and then i believe the domain name can be left off the oracle username. There is an init.ora parameter = REMOTE_OS_AUTHENT that must be set to TRUE ( I do believe this causes some security risks so you may want to investigate those issues to.) There is also an init.ora parameter OS_AUTHENT_PREFIX that needs to be set. I believe the default is now "" (no prefix required for the oracle user account). I used these keywords and was able to locate some helpful documents in Metalink. HTH, Jim References Metalink: Note:102665.1 -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Wednesday, April 18, 2001 2:28 AM >Hi! > >Oracle8.1.7 on WinNT. >I created database user with external authentication with whom I'm logged on >that WinNT server. >When I start exp there is error > invalid username/password logon denied. >What am I missing? It works just fine on UNIX! > >TIA, >Sonja >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: =?iso-8859-2?Q?Sonja_=A9ehovi=E6?= > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jim Walski INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Direct Vs. SQL*net
How can one determine via code (PL/SQL) whether or not the current session is connected "directly" (via the Bequeath protocol) or via SQL*Net? The solution needs to work for both V7 & V8. -- Charlie Mengler Maintenance Warehouse [EMAIL PROTECTED] 10641 Scripps Summit Ct 858-831-2229 San Diego, CA 92131 The future is here. It is just not evenly distributed. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Insert Cardinalities into the data dictionary directly
Take a look at dbms_stats.set_column_stats and dbms_stats.set_table_stats. The latter allows you to set the number of rows, the size of the table and such ; the first allows you to set the number of distinct values for a column the number of nulls, etc. I'm not sure what you are trying to accomplish with this. Is simply changing the cardinalities of a the columns enough? What about the distribution of those values? If the changes result in a different query path, and your queries run more slowly, does that mean they will do so when the statistics truly reflect the database. Perhaps they are running more slowly because you lied to the optimizer. "I am aware that if this is was an environment with good cardinalities, these values could be exported and imported into another environment." Cardinality reflects the ratio of distinct values to the total number of values in the database. I can think of scenarios where cardinality might change significantly for a time. But I would think in most cases it would remain fairly constant. FYI, if statements which assert something contrary to fact are subjunctive not conditional. "If this WERE an environment with good cardinalities" Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Wednesday, April 18, 2001 8:06 AM To: Multiple recipients of list ORACLE-L I am trying again -- To obtain a good analysis of a SQL statement especially in a new development environment, based on the environment that it is to be deployed on, it would good to sometime reflect/simulate the production volume. Is there a way to input table cardinalities directly into the data dictionary so that the Optimizer could be made to act like in production. I am aware that if this is was an environment with good cardinalities, these values could be exported and imported into another environment. My question is this possible from scratch? Regards, Murali Vallath _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Murali Vallath INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: filesystem buffer ON/OFF ?
Hi li, You could limit the bufferpages to a certain value but turning them off is defenitely not a good idea as there are more files accessed than just oracle data and binaries. cheers alex > > Hi, all > I have a database running oracle8.1.6 on HP-UX 11 (64 bit). We enable > file system buffer on OS level. > I think bypassing the filesystem buffer will improve performance because > right now, both oracle and the filesystem are performing this operation. > > If I am right, should I turn off file system buffer only for data, index > segments, how about system,rollback segs. and temporary > > segs. ? > Any suggestion are very welcomed, and any website talking about this? > because I have to convince my SA guys. > > thanks in advance. > li Content-Type: text/html; name="Anhang: 1" Content-Transfer-Encoding: quoted-printable Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alex Apostolopoulos INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: authentication externally on WinNT
Are you suggesting using "ops$" accounts over sql*net?. Is your database inaccessible usesr with administrator/root privileges on any of their machines? OPS$ authentication is notoriously easy to spoof. Ian MacGregor Stanford Linear Accelerator Center ian@slac,stanford.edu -Original Message- Sent: Wednesday, April 18, 2001 9:46 AM To: Multiple recipients of list ORACLE-L You need an oracle username with authent_prefix added to the NT username, which means that if NT username is "scott", oracle username should be "ops$scott", in case that you will be using the default authentication prefix of "ops$". You can always choose something else, like 'mg$', (my initials, combined with my favorite currency) -Original Message- Sent: Wednesday, April 18, 2001 5:40 AM To: Multiple recipients of list ORACLE-L Hi! Oracle8.1.7 on WinNT. I created database user with external authentication with whom I'm logged on that WinNT server. When I start exp there is error invalid username/password logon denied. What am I missing? It works just fine on UNIX! TIA, Sonja -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-2?Q?Sonja_=A9ehovi=E6?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: authentication externally on WinNT
You need an oracle username with authent_prefix added to the NT username, which means that if NT username is "scott", oracle username should be "ops$scott", in case that you will be using the default authentication prefix of "ops$". You can always choose something else, like 'mg$', (my initials, combined with my favorite currency) -Original Message- Sent: Wednesday, April 18, 2001 5:40 AM To: Multiple recipients of list ORACLE-L Hi! Oracle8.1.7 on WinNT. I created database user with external authentication with whom I'm logged on that WinNT server. When I start exp there is error invalid username/password logon denied. What am I missing? It works just fine on UNIX! TIA, Sonja -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-2?Q?Sonja_=A9ehovi=E6?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Temporary Tablespace Design
FOR YOUR INFORMATION ESIS and EPFAL are now part of Logica. The Internet email addresses of the staff has changed to the following - [EMAIL PROTECTED] eg [EMAIL PROTECTED] Emails using the old format will continue to be delivered until 30th June 2001. Tim, You are confirming my original understanding. However Kirti stated that based on your figures if a sort was used then malloc would be called 4 times to get from 512000 to 2048000. I do not know the answer for certain but Kirti's view seems logical because sort_area_size is the MAX size and it is very likely that only the retained is allocated initially and then increased in increments up to sort_area_retained_size. Any bigger and the sort is done to the temporary tablespace. John -Original Message- From: Tim Sawmiller [mailto:[EMAIL PROTECTED]] Sent: 18 April 2001 17:26 To: Multiple recipients of list ORACLE-L Subject:RE: Temporary Tablespace Design Well, not quite. SORT_AREA_SIZE specifies the maximum amount, in bytes, of memory to use for a sort. After the sort is complete and all that remains to do is to return the rows, the memory is released down to the size specified by SORT_AREA_RETAINED_SIZE. After the last row is returned, all memory is freed. Temporary disk is used for the intermediate sort runs only if the SORT_AREA_SIZE isn't large enough to handle the sort in memory. The SORT_AREA_RETAINED_SIZE controls the size of the read buffer which is used to maintain a portion of the sort in memory. Myself, I use 2048000 for SORT_AREA_SIZE, and 512000 for SORT_AREA_RETAINED_SIZE. >>> [EMAIL PROTECTED] 04/18/01 11:36AM >>> I think you are not correct. First - Oracle allocates memory for sorting as needed by 8K chunks up to sort_area_size. Second if your sort_area_size is large enough to do sort in memory and your sort_area_retained_size < sort_area_size oracle will dump sorted data into temporary tablespace and then read from this tablespace. So it is a tradeoff between late memory release and temporary tablespace I/O. Alex Hillman -Original Message- Sent: Wednesday, April 18, 2001 10:40 AM To: Multiple recipients of list ORACLE-L Hi John, As you mentioned, it depends on a number of factors. I believe I replied to a question about improving performance of index building after an import. Anyway, here is the reasoning behind why I said that When Oracle begins sorting, it starts with the memory equal to sort_area_retained_size value and may eventually acquire memory equal to sort_area_size. This involves at OS level, malloc (and may be some other) memory mgmt routine(s). After the sort phase is complete, Oracle will trigger memory de-allocation and the OS will go at work again. If there are servile sessions performing sorts, there can be an increased OS level memory mgmt activity (while attaining sort_area_size and releasing it back to sort_area_retained_size) . But if the server has *enough* memory keeping sort_area_retained_size equal to sort_area_size may actually help. I also assume that these days most installations have ample memory. And that the advice of using 50% (which I had heard of) of sort area size for sort area retained size should always be viewed in light of one's requirements and resource availability. But I can't stop wondering... how come it was 50% and not 10% or 20%.. so it's all relative. I have been using what I suggested, for a number of years now with no problems or complaints. Cheers ! - Kirti Deshpande Verizon Information Services http://www.superpages.com > -Original Message- > From: Hallas, John [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, April 18, 2001 6:21 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Temporary Tablespace Design > > > Kirti Deshpande writes > > "Also, keep SORT_AREA_RETAINED_SIZE the same as SORT_AREA_SIZE. As the > former will be used while fetching data after sort phase is completed." > > I thought best advice was to make the retained size smaller (50%?) of > sort_area_size to allow initial sort memory to be released after the first > part
RE: Priv to view package bodies created by other users
Debbie, You could try grant select on dba_source view. Cheers Iain Nicoll -Original Message- Sent: 18 April 2001 16:45 To: Multiple recipients of list ORACLE-L I am a relatively new DBA and looking for advise. I have a request from our developers for the ability to view package body information which has been created by other users. We are currently using Oracle8i Enterprise Edition Release 8.1.6.1.0. I have been advised that in order to view package bodies created by other users you need the "create any procedure" system privilege. To view type bodies created by other users you need the "create any type" system privilege. Are there any object level privileges that would accomplish this request? TIA for your help, Debbie James -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Debbie James INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ORA - 600 errors reported
Dear DBA Gurus, I am running Oracle 8.1.6 on Sun Solaris 2.8. One of the developers issued a delete command on a table without a where clause which contained 80,000 records which caused the oracle session to hang. He forcefully closed the session. Then he tried to issue the truncate command on the same table. Then he got the error Ora-00054 (resource busy and acquire with nowait specified). Then he called me. I identified the process which was locking the object by querying v$locked_object. Then I killed the session through alter system kill session command after identifying the sid and serial# from the process. Then I received the message the process is marked for kill. I again tried to issue the truncate command on the same table. I got the same error Ora-00054. Then I forcefully closed the session and issued shutdown immediate and restarted the database. Then when I issued select count(*) from tablename or truncate table tablename I received the below error: ORA - 00600: internal error code, arguments: [kcbgcur_3], [4], [12], [54525964], [11281], [11294], [0], [ ] I checked the alert log file which said that errors in file $ORACLE_HOME/admin/udump/mercedes_ora_43798.trc file. When I checked that file it had given the same error along with some block addresses from I couldn't decipher anything. Any idea as to why are these errors generated? Any help in this regard will be appreciated. TIA and regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Ability for non DBA user to kill session.
Are you sure that "execute immediate" doesn't support "alter system" command? Maybe it's a bug? Which version, which OS? -Original Message- Sent: Tuesday, April 17, 2001 7:10 PM To: Multiple recipients of list ORACLE-L Hi All, Further to that, it is necessary to use DBMS_SQL or native dynamic SQL for this as PL/SQL does not support ALTER SYSTEM directly. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Wednesday, 18 April 2001 3:37 To: Multiple recipients of list ORACLE-L Create a procedure as SYS (or someone else powerful) which does the 'alter system' and then grant just the proc to the user hth connor --- lerobe - Lee Robertson <[EMAIL PROTECTED]> wrote: > All, > > Is there a method for allowing a non DBA user to > kill their own (and only > their own) session. I have had a trawl through > Metalink and have seen > various methods (using procedures) of doing it but > all of these appear to > rely on granting the alter system role to the user. > > Oracle 8.0.5.0.0 > Compaq Tru64 4.0f > > Regards > > Lee > > Lee Robertson > Acxiom > Tel:0191 525 7344 > Fax:0191 525 7007 > Email: [EMAIL PROTECTED] > > > > > The information contained in this communication is > confidential, is intended only for the use of the > recipient > named above, and may be legally privileged. If the > reader > of this message is not the intended recipient, you > are > hereby notified that any dissemination, distribution > or > copying of this communication is strictly > prohibited. > If you have received this communication in error, > please > re-send this communication to the sender and delete > the > original message or any copy of it from your > computer > system. > = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) "Some days you're the pigeon, some days you're the statue" Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Lawrence To's article on standby databases
it's out on metalink >From: "Edmundo Vasquez" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: Lawrence To's article on standby databases >Date: Wed, 18 Apr 2001 07:25:26 -0800 > >Hi Everyone, > >Where can I get Lawrence To's article on standby databases >as mentioned in a recent standby discussion? > >Thanks...Mundo > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Edmundo Vasquez > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RMAN doubts
What is your problem? I use 816. There is problem with sbttest in 816. But backups and recovery work fine. You need to relink the libobk.so file. I you provide details, I would try to answer. -Rahul - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, April 16, 2001 2:15 PM > Sorry to bother you, i was using RMAN to backup to disk for over 1 year, > but we need to backup to > tape, so, i had to install Legato. > I'm having some troubles, and i only want to know if you also experienced > the same troubles. > I can do backup to sbt_tape, but i can't read the saved files. > I created a TAR in Metalink, and checked Legato Site out.but they still > didn't anwer me back.. > so, i'll appreciate any feed backup. > Thank you. > > > - Original Message - > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Sent: Thursday, April 12, 2001 6:40 PM > > > > The database 'may' be in NOARCHIVELOG mode > > 1. Its a 300 GB database and RMAN picks up only the USED blocks to might > > save > >time and disk space for disk backup > > 2. We back up directly to tape. So if done through RMAN, we need not keep > >inventory of tapes as RMAN/Legato would maintain it internally > > 3. I understand that with RMAN, even if database is in NOARCHIVELOG mode > >still it would allow incremental backups which would mean saving of > time. > > -Rahul > > > > > > - Original Message - > > To: "LazyDBA mailing list" <[EMAIL PROTECTED]> > > Sent: Wednesday, April 11, 2001 5:19 PM > > > > > > > If you are in NOARCHIVELOG mode you have to shutdown the Db (either > within > > > or out of RMAN) before a backup. There's no real benefit using RMAN for > > cold > > > backups. > > > > _ > > > Get Your Private, Free E-mail from MSN Hotmail at > http://www.hotmail.com. > > > > > > > > > > > > Think you know someone who can answer the above question? Forward it to > > them! > > > to unsubscribe, send a blank email to [EMAIL PROTECTED] > > > to subscribe send a blank email to [EMAIL PROTECTED] > > > Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl > > > Tell yer mates about http://www.farAwayJobs.com > > > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Rahul Dandekar > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing Lists > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Claudio Roca > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul Dandekar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: server sizing [NT: need XEON?]
yes, I made a mistake then... I'll see what I can do to collect metrics on logical/physical I/Os. my information is based on NT/MSSQL, we are moving to oracle so some things you mentioned were never a concern; ultimately I want to have the infrastructure as sound, functional, and scalable as possible. we've already been bitten a few times, long stories that I won't go into; regardless they've resulted in complete rebuilds and redesigns. I dread spending another 50hr holiday weekend watching lights flash hoping I can get production backup before the users get in. I have seen CPU utilization jump in certain scenarios so that disk I/O may be an issue but with load distributed across multiple controller channels... I don't know; maybe good, maybe not. I'll take note of the drive specs and their levels of performance. I hadn't even considered not having multiple writers, I was sure we'd discussed implementing it in class but maybe we just covered the theory. I know of the six major processes ARC0, LGWR0, DBWR0, SMON, PMON, and CKPT. I was sure they mentioned DBWR, LGWR, and ARC with 0's to implicate the ability to have additional processes. as I remember having multiple writers was mentioned as a way of managing performance and eliminating some of those bottlenecks. thanks. === Lerone Streeter System Analyst Abbott LBG [EMAIL PROTECTED] === -Original Message- Sent: Wednesday, April 18, 2001 4:35 AM To: Multiple recipients of list ORACLE-L "Eric D. Pierce" wrote: > If you can get ahold of him, or can wait until he > isn't so busy, Paul Drake of this list is the resident RAID/hardware > expert. Eric - this is one of the funniest pages I have seen: http://www.ultratech-llc.com/Personal/Files/?File=~MoreInfo.TXT uh oh. I don't know if I'm honored, or scared. I'm not nearly the resident RAID expert - but would like to be someday. These 4-drive JBOD workstations at home don't quite give me the room to play. Does the word "Heuristics" mean anything to you? I'd rather have stats than guidelines. Here is my one word of caution before heading off into some other direction: multiple database writer processes are not supported for Oracle 8.1.7 on NT. This is straight off of the platform-specific docs off of the Docs CDROM. I did receive an ORA-00600 error relating to running multiple db_writers on NT with 8.1.6. Don't do it. >From Metalink: Subject: Re : Db Writer Process Oracle on NT only allows/needs a single database writer process (DBWR). Multiple DBWRs on UNIX is not multiple "real" DBWRs which all go scan for dirty buffers and write them to disk. It's really just one "real" DBWR and some I/O slaves. The "real" DBWR tells the slave DBWRs to do I/O. On NT, there's really no need for this since async I/O and NT will take care of all that for you. NT acts as the I/O slaves and the "real" DBWR [1 DBWR thread] then checks the "slaves" to see if the I/O is done. Melissa Holman Oracle Support Will this produce the rate-limiting-factor in your system? I do not know. *Something* will be a rate limiting factor for a particular process. But as OLTP users' transactions vary so much from the batch processes, what exactly is the overall rate_limiting_factor is tough to say - your mileage will vary. My background is in Chemical Engineering. I spent some time in R & D wearing a white labcoat. Chemical Engineering - Process Debottlenecking - is all about "Where is the bottleneck?" Usually, a 15-20% margin is designed in such that the plant can run safely over its design spec without a retrofit of key components. Beyond that - you have to find what component needs to be increased to add capacity - safely. For your system - design in more capacity that you need, with room for expansion. Empty drive bays in external storage cabinets are good. The rebuilds of drive arrays are painful - but if you had a 7 bay drive cage (half a 14 bay - e.g. Storageworks 4200) with only a 4 drive array, hiking it up to a 6 drive RAID 0+1 (I'd rather duplex these) with a hot spare is tolerable. On an Ultra 160/m channel, 6 drives is reasonable. Here is a baseline - I/Os per second. A standard drive can accommodate 80-100 I/Os per second. Larger bufffers (cache) on the hard drive can increase that number, as can read-ahead caching algorithms on the RAID controllers. But if the reads are scattered, as in index reads and nested loops - the cache hits are not very likely. Lots of memory reduces the amount of read I/O required for a query. It does not reduce the amount of memory bandwidth required, nor does it completely reduce the overhead of creating consistent reads (reading rollback segs to provide cr blocks). This is a large part of the non-linear nature of scaling - interference of user processes creating additional overhead. When you say transactions per minute - these need to be translated out to actual logical and
RE: (Fwd) Tar # 1339336.996:run all on one win2000 box: OEM, DB,
I would not run any web server on the same box as my database for security reasons. A web server is another entity which can possibly be exploited by unscrupulous individuals, perhaps giving them control of the machine. I certainly don't want this happening on a database server. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Tuesday, April 17, 2001 5:15 PM To: Multiple recipients of list ORACLE-L Forms web server Larry & folks, Still not sure what it all means! They don't seem to be able to answer specifically on Forms *Server* for some reason. I'll continue asking tomorrow. regards, ep --- Forwarded message follows --- 1339336.996 Open Date 14-APR-01 00:45:05 Support Identifier xxx Last Update 17-APR-01 20:08:12 Product Oracle Server - Enterprise Edition Product Version 8.1.7 Platform MS Windows 2000 Abstract run all on one win2000 box: OEM, DB, Forms web server ? Resolution History 14-APR-01 00:45:06 GMT ... ### Please describe your problem:### Can all three run on one win2000 box: OEM, DB, Forms web server ? What I need is a statement elaborating on the same issues as raised in tar #123 1449.996 My recollection of what I was told on the phone during discussion of the issues in tar #123 1449 .996 was that as of db v 8.1.7, it is not possible torun the db and forms web server on the same win2000 box. I hope I'm not confusing something, e.g., what was actually said previously was that you [*]can't[*] run the db v 8.1.7 and *OEM* on the same win2000 box. Please provide info on pertinent notes, white papers, etc., if possible. Thanks! Eric email:[EMAIL PROTECTED] (or phone 916-278-7586 if that is easier/ more appropriate) ### If you are receiving errors, please list exact error messages and text:### giant juicy chocolate chip peanut butter cookies!!! ... ### What is the impact to your business becauseof this problem? ### planning Contact me via : E-mail -> [EMAIL PROTECTED] --- 14-APR-01 01:26:16 GMT I think for the forms server / database running on the same box , the best group to talk to would be the forms group. xfr dataserver -> forms --- 15-APR-01 06:07:51 GMT Forms 6.0.8.10.3 (Patch 1), 6.0.8.11.3 (Rel 2), 6.0.8.12.1(Patch 3A), 6.0.8.13, (Patch 4) are certified for windows 2000. forms 6.0.8.12.1 (Patch 3) has been withdrawn for the 8.1.7 database. The check any other certified products for oracle you can go to metalink.oracle.com Then go to product lifcycle and certifications the verify and product certifications for any platform. @scl --- 16-APR-01 19:03:12 GMT New info : Uh can somone explain how that "answer" was related to the original question (see subject line), or what it means otherwise? Better yet, please look up the name of the analyst who called me on tar #1231449.996, and have her contact me again to clarify the issue. Thanks, Eric email: [EMAIL PROTECTED] ph# (916) 278-7586 --- 17-APR-01 15:21:34 GMT Your tar has been assigned to an analyst --- 17-APR-01 18:50:28 GMT OEM 2.2 and 8.1.7 enterprize editions can live together on the same machine in the same Oracle_home. Developer 6i can reside on the same machine but has to be installed into a seperate oracle home. Intelegent agent comes with 8.1.7 and does not need to be installed from OEM. Hope this helps. --- 17-APR-01 19:33:08 GMT New info : Thanks for letting me know that OEM and DB can run on the same box. I don't think I would want to run Developer6i (to develop a form) on the same server box, but I would want to use the "webified" version of such a form on the same box. So, part of the original question remains: can the Forms *web* server run on the same box as DB (&/or OEM)? In the previous tar, I was told (verbally) that it is *not* possible to run the equivalent of WebDB and DB8.1.7on the same box, but I wanted "written" clarification of that point. As you might notice if you read the previous tar on this subject, I'm not currently using these products (and therefor may not be using the precise terminology that Oracle Tech Support is comfortable with), we are doing design and capacity planning for a small application, and I want to know if
RE: Temporary Tablespace Design
Jared, Thanks for pointing that out. Yes, the free() is not immediate. However, I was generalizing all the OS actions before & after the sort. Unfortunately I missed Steve's post on this. I will search his Web site to see if there is anything related to this. - Kirti > -Original Message- > From: Jared Still [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, April 18, 2001 10:25 AM > To: Multiple recipients of list ORACLE-L > Subject: Re: Temporary Tablespace Design > > On Wednesday 18 April 2001 07:40, you wrote: > > Hi John, > > After the sort phase is complete, Oracle will trigger memory > > de-allocation and the OS will go at work again. > > Kirti, > > I seem to recall a post from Steve Adams not too long ago about > this. As I recall, free() is not called immediately, but may be delayed. > > Know anything about that? > > Jared > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jared Still > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Prompt and accept a value inside a procedure
In short, no. Perhaps with Java (i'm not sure), but definitely not with PL.SQL. There are a couple of things you can do depending on what you're trying to accomplish. Prompt for input in sqlplus before executing your pl/sql. If you're running a stored procedure, pass the input as an argument. If you're running anonymous pl/sql, embed the substitution variable in the pl/sql where you need it. If you need to prompt after executing some logic, don't use pl/sql. Use a shell script to do what you want and issue sql commands against the database via sqlplus. You can read results from a sql command into shell variables like this. #!/bin/ksh print -n "Enter table name:" read tn sqlplus -s< Sent: Wednesday, April 18, 2001 9:50 AM > Hi All, > > Is there a way to prompt a user and accept input from the user inside a > procedure. I cannot get the prompt/accept to work > inside a procedure. It works it sql*plus. > > Thanks > Rick > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Cale, Rick T (Richard) > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Lawrence To's article on standby databases
Hi Everyone, Where can I get Lawrence To's article on standby databases as mentioned in a recent standby discussion? Thanks...Mundo -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edmundo Vasquez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Temporary Tablespace Design
Well, not quite. SORT_AREA_SIZE specifies the maximum amount, in bytes, of memory to use for a sort. After the sort is complete and all that remains to do is to return the rows, the memory is released down to the size specified by SORT_AREA_RETAINED_SIZE. After the last row is returned, all memory is freed. Temporary disk is used for the intermediate sort runs only if the SORT_AREA_SIZE isn't large enough to handle the sort in memory. The SORT_AREA_RETAINED_SIZE controls the size of the read buffer which is used to maintain a portion of the sort in memory. Myself, I use 2048000 for SORT_AREA_SIZE, and 512000 for SORT_AREA_RETAINED_SIZE. >>> [EMAIL PROTECTED] 04/18/01 11:36AM >>> I think you are not correct. First - Oracle allocates memory for sorting as needed by 8K chunks up to sort_area_size. Second if your sort_area_size is large enough to do sort in memory and your sort_area_retained_size < sort_area_size oracle will dump sorted data into temporary tablespace and then read from this tablespace. So it is a tradeoff between late memory release and temporary tablespace I/O. Alex Hillman -Original Message- Sent: Wednesday, April 18, 2001 10:40 AM To: Multiple recipients of list ORACLE-L Hi John, As you mentioned, it depends on a number of factors. I believe I replied to a question about improving performance of index building after an import. Anyway, here is the reasoning behind why I said that When Oracle begins sorting, it starts with the memory equal to sort_area_retained_size value and may eventually acquire memory equal to sort_area_size. This involves at OS level, malloc (and may be some other) memory mgmt routine(s). After the sort phase is complete, Oracle will trigger memory de-allocation and the OS will go at work again. If there are servile sessions performing sorts, there can be an increased OS level memory mgmt activity (while attaining sort_area_size and releasing it back to sort_area_retained_size) . But if the server has *enough* memory keeping sort_area_retained_size equal to sort_area_size may actually help. I also assume that these days most installations have ample memory. And that the advice of using 50% (which I had heard of) of sort area size for sort area retained size should always be viewed in light of one's requirements and resource availability. But I can't stop wondering... how come it was 50% and not 10% or 20%.. so it's all relative. I have been using what I suggested, for a number of years now with no problems or complaints. Cheers ! - Kirti Deshpande Verizon Information Services http://www.superpages.com > -Original Message- > From: Hallas, John [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, April 18, 2001 6:21 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Temporary Tablespace Design > > FOR YOUR INFORMATION > > ESIS and EPFAL are now part of Logica. The Internet email addresses of the > staff has changed to the following - [EMAIL PROTECTED] eg > [EMAIL PROTECTED] Emails using the old format will continue to be > delivered until 30th June 2001. > > > Kirti Deshpande writes > > "Also, keep SORT_AREA_RETAINED_SIZE the same as SORT_AREA_SIZE. As the > former will be used while fetching data after sort phase is completed." > > I thought best advice was to make the retained size smaller (50%?) of > sort_area_size to allow initial sort memory to be released after the first > part of the sort is managed and only the merge phase of a disk sort is > left > to do. > > I appreciate that a lot depends on the amount of memory available and the > number of concurrent processes that may be performing sorts but surely the > aim is to free memory up as soon as possible down to the > sort_area_retained_size > > Thanks > > John > > (PS I do apologise for the rubbish at the top of this mail but it is > inserted after I have sent the mail into our mail gateway) > > > > > > This e-mail and any files transmitted with it, are confidential to Logica > and are intended solely for the use of the individual or entity to whom > they are addressed. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Hallas, John > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego,
filesystem buffer ON/OFF ?
Title: filesystem buffer ON/OFF ? Hi, all I have a database running oracle8.1.6 on HP-UX 11 (64 bit). We enable file system buffer on OS level. I think bypassing the filesystem buffer will improve performance because right now, both oracle and the filesystem are performing this operation. If I am right, should I turn off file system buffer only for data, index segments, how about system,rollback segs. and temporary segs. ? Any suggestion are very welcomed, and any website talking about this? because I have to convince my SA guys. thanks in advance. li
Priv to view package bodies created by other users
I am a relatively new DBA and looking for advise. I have a request from our developers for the ability to view package body information which has been created by other users. We are currently using Oracle8i Enterprise Edition Release 8.1.6.1.0. I have been advised that in order to view package bodies created by other users you need the "create any procedure" system privilege. To view type bodies created by other users you need the "create any type" system privilege. Are there any object level privileges that would accomplish this request? TIA for your help, Debbie James -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Debbie James INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Locally Managed Tablespaces
We've been using them here for about 8 months now with no problems. Solaris 2.7, Oracle 8.1.6.3. Patricia Ashe <[EMAIL PROTECTED]>@fatcity.com on 04/17/2001 07:35:49 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: I am interested in some statistics on Oracle locally managed tablespaces. I have been looking for any bugs or negative info about them. Are they in use at alot of sites? Seems like all the information I have come across is positive. Which is great! But maybe they aren't being used at alot of sites. Can I hear about experiences from others on this list? how many sites are actually using them? I have several databases that I am getting ready to go production soon and would like to create the tablespaces as locally managed, but need more statistics. PA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Patricia Ashe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Temporary Tablespace Design
On Wednesday 18 April 2001 07:40, you wrote: > Hi John, > After the sort phase is complete, Oracle will trigger memory > de-allocation and the OS will go at work again. Kirti, I seem to recall a post from Steve Adams not too long ago about this. As I recall, free() is not called immediately, but may be delayed. Know anything about that? Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Temporary Tablespace Design
I think you are not correct. First - Oracle allocates memory for sorting as needed by 8K chunks up to sort_area_size. Second if your sort_area_size is large enough to do sort in memory and your sort_area_retained_size < sort_area_size oracle will dump sorted data into temporary tablespace and then read from this tablespace. So it is a tradeoff between late memory release and temporary tablespace I/O. Alex Hillman -Original Message- Sent: Wednesday, April 18, 2001 10:40 AM To: Multiple recipients of list ORACLE-L Hi John, As you mentioned, it depends on a number of factors. I believe I replied to a question about improving performance of index building after an import. Anyway, here is the reasoning behind why I said that When Oracle begins sorting, it starts with the memory equal to sort_area_retained_size value and may eventually acquire memory equal to sort_area_size. This involves at OS level, malloc (and may be some other) memory mgmt routine(s). After the sort phase is complete, Oracle will trigger memory de-allocation and the OS will go at work again. If there are servile sessions performing sorts, there can be an increased OS level memory mgmt activity (while attaining sort_area_size and releasing it back to sort_area_retained_size) . But if the server has *enough* memory keeping sort_area_retained_size equal to sort_area_size may actually help. I also assume that these days most installations have ample memory. And that the advice of using 50% (which I had heard of) of sort area size for sort area retained size should always be viewed in light of one's requirements and resource availability. But I can't stop wondering... how come it was 50% and not 10% or 20%.. so it's all relative. I have been using what I suggested, for a number of years now with no problems or complaints. Cheers ! - Kirti Deshpande Verizon Information Services http://www.superpages.com > -Original Message- > From: Hallas, John [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, April 18, 2001 6:21 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Temporary Tablespace Design > > FOR YOUR INFORMATION > > ESIS and EPFAL are now part of Logica. The Internet email addresses of the > staff has changed to the following - [EMAIL PROTECTED] eg > [EMAIL PROTECTED] Emails using the old format will continue to be > delivered until 30th June 2001. > > > Kirti Deshpande writes > > "Also, keep SORT_AREA_RETAINED_SIZE the same as SORT_AREA_SIZE. As the > former will be used while fetching data after sort phase is completed." > > I thought best advice was to make the retained size smaller (50%?) of > sort_area_size to allow initial sort memory to be released after the first > part of the sort is managed and only the merge phase of a disk sort is > left > to do. > > I appreciate that a lot depends on the amount of memory available and the > number of concurrent processes that may be performing sorts but surely the > aim is to free memory up as soon as possible down to the > sort_area_retained_size > > Thanks > > John > > (PS I do apologise for the rubbish at the top of this mail but it is > inserted after I have sent the mail into our mail gateway) > > > > > > This e-mail and any files transmitted with it, are confidential to Logica > and are intended solely for the use of the individual or entity to whom > they are addressed. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Hallas, John > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing L
Re: Weird link problem
You will get the number of non-null values for name. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, April 18, 2001 10:40 AM What happens if you do select count(name) from tab1.table? >>> [EMAIL PROTECTED] 04/18/01 09:55AM >>> AIX 4.3 RDBMS 8.1.6.2 When I select through a database link I have having a strange problem. If I select a field or fields it works. If I select a count it hangs. Any ideas? select count(*) from tab1.table; **hangs** select name, address from tab1.table; **works fine** Help!!! ..tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Sawmiller INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chuck Hamilton INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Designer 6i Versioning vs. ?
Anyone familiar with Designer 6i's versioning capabilties? Does it compare well with Clearcase, PVCS, etc? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Sawmiller INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Insert Cardinalities into the data dictionary directly
I am trying again -- To obtain a good analysis of a SQL statement especially in a new development environment, based on the environment that it is to be deployed on, it would good to sometime reflect/simulate the production volume. Is there a way to input table cardinalities directly into the data dictionary so that the Optimizer could be made to act like in production. I am aware that if this is was an environment with good cardinalities, these values could be exported and imported into another environment. My question is this possible from scratch? Regards, Murali Vallath _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Murali Vallath INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Weird link problem
What happens if you do select count(name) from tab1.table? >>> [EMAIL PROTECTED] 04/18/01 09:55AM >>> AIX 4.3 RDBMS 8.1.6.2 When I select through a database link I have having a strange problem. If I select a field or fields it works. If I select a count it hangs. Any ideas? select count(*) from tab1.table; **hangs** select name, address from tab1.table; **works fine** Help!!! ..tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Sawmiller INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Qualifying columns to improve performance?
Title: RE: Qualifying columns to improve performance? -Original Message- From: Arn Klammer [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 02, 2001 10:30 PM To: Multiple recipients of list ORACLE-L Subject: Qualifying columns to improve performance? At a course some years ago, we were told that in join statements, we should qualify ALL our column names with the appropriate table name, not just those that may be ambiguous. The reason was that the parser would not need to spend time checking multiple tables to determine the table to which each column belongs. Is this still a valid rule? Does this apply to statements in PL/SQL procedures/packages? Or are they different, because they are checked at compile time? -a ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **
RE: Temporary Tablespace Design
Hi John, As you mentioned, it depends on a number of factors. I believe I replied to a question about improving performance of index building after an import. Anyway, here is the reasoning behind why I said that When Oracle begins sorting, it starts with the memory equal to sort_area_retained_size value and may eventually acquire memory equal to sort_area_size. This involves at OS level, malloc (and may be some other) memory mgmt routine(s). After the sort phase is complete, Oracle will trigger memory de-allocation and the OS will go at work again. If there are servile sessions performing sorts, there can be an increased OS level memory mgmt activity (while attaining sort_area_size and releasing it back to sort_area_retained_size) . But if the server has *enough* memory keeping sort_area_retained_size equal to sort_area_size may actually help. I also assume that these days most installations have ample memory. And that the advice of using 50% (which I had heard of) of sort area size for sort area retained size should always be viewed in light of one's requirements and resource availability. But I can't stop wondering... how come it was 50% and not 10% or 20%.. so it's all relative. I have been using what I suggested, for a number of years now with no problems or complaints. Cheers ! - Kirti Deshpande Verizon Information Services http://www.superpages.com > -Original Message- > From: Hallas, John [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, April 18, 2001 6:21 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Temporary Tablespace Design > > FOR YOUR INFORMATION > > ESIS and EPFAL are now part of Logica. The Internet email addresses of the > staff has changed to the following - [EMAIL PROTECTED] eg > [EMAIL PROTECTED] Emails using the old format will continue to be > delivered until 30th June 2001. > > > Kirti Deshpande writes > > "Also, keep SORT_AREA_RETAINED_SIZE the same as SORT_AREA_SIZE. As the > former will be used while fetching data after sort phase is completed." > > I thought best advice was to make the retained size smaller (50%?) of > sort_area_size to allow initial sort memory to be released after the first > part of the sort is managed and only the merge phase of a disk sort is > left > to do. > > I appreciate that a lot depends on the amount of memory available and the > number of concurrent processes that may be performing sorts but surely the > aim is to free memory up as soon as possible down to the > sort_area_retained_size > > Thanks > > John > > (PS I do apologise for the rubbish at the top of this mail but it is > inserted after I have sent the mail into our mail gateway) > > > > > > This e-mail and any files transmitted with it, are confidential to Logica > and are intended solely for the use of the individual or entity to whom > they are addressed. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Hallas, John > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Unemployed ????
Hi, you're kidding right ? You're not really unemployed ?? I always thought you were definitely on the absolutely skilled side of DBAing. Jared Still schrieb: > > Yes, the list owner and others would appreciate it. > > Also, use of the crude language contained in the original > post can get you removed from the list. > > And as I'm currently unemployed, I have a lot of time to > monitor this list. :) > > Jared Still > > On Monday 16 April 2001 15:01, John Lewis wrote: > > Perhaps we should keep ideologies/politics off of this list. > > If anyone (Chinese or American) wants to debate they are lots of other > > lists set up for that. Take it there. I'm here for Oracle not > > bashing. > > > > regards - > > > > john f. lewis > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jared Still > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > --- > This Mail has been checked for Viruses > Attention: Encrypted Mails can NOT be checked ! > > *** > > Diese Mail wurde auf Viren ueberprueft > Hinweis: Verschluesselte Mails koennen NICHT geprueft werden! > -- Regards, Stefan Jahnke -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Import issue..
This solution worked for me... -Original Message- Gramolini Sent: Wednesday, April 18, 2001 9:35 AM To: Multiple recipients of list ORACLE-L Try using the 8.0.5 version of exp to export the file. Then it should go into the 8.0.5 database. HTH, Ruth - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, April 18, 2001 5:15 AM > Hi List, > > I've two databases like this, > > 1. Oracle version 8.1.6 running on sun solaris 2.7 > 2. Oracle version 8.0.5 running on sun solarsi 2.6 > > When I tried importing a dump of 8.1.6 into the 8.0.5 version it didn't go > thro. I got the following errors > IMP-00010: not a valid export file, header failed verification > IMP-00021: operating system error - error code (dec 2, hex 0x2) > IMP-0: Import terminated unsuccessfully > > I thought its because of the wrong mode of file transfer and did it again. > But again I got this error. Is this kind of downward compatibility on sun > is not allowed?? > > thanks in advance, > Manivannan.M > -- > > > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Manivannan.M > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: udaycb INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Prompt and accept a value inside a procedure
Hi All, Is there a way to prompt a user and accept input from the user inside a procedure. I cannot get the prompt/accept to work inside a procedure. It works it sql*plus. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Weird link problem
AIX 4.3 RDBMS 8.1.6.2 When I select through a database link I have having a strange problem. If I select a field or fields it works. If I select a count it hangs. Any ideas? select count(*) from tab1.table; **hangs** select name, address from tab1.table; **works fine** Help!!! ..tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Import issue..
Try using the 8.0.5 version of exp to export the file. Then it should go into the 8.0.5 database. HTH, Ruth - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, April 18, 2001 5:15 AM > Hi List, > > I've two databases like this, > > 1. Oracle version 8.1.6 running on sun solaris 2.7 > 2. Oracle version 8.0.5 running on sun solarsi 2.6 > > When I tried importing a dump of 8.1.6 into the 8.0.5 version it didn't go > thro. I got the following errors > IMP-00010: not a valid export file, header failed verification > IMP-00021: operating system error - error code (dec 2, hex 0x2) > IMP-0: Import terminated unsuccessfully > > I thought its because of the wrong mode of file transfer and did it again. > But again I got this error. Is this kind of downward compatibility on sun > is not allowed?? > > thanks in advance, > Manivannan.M > -- > > > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Manivannan.M > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Meeting at IOUG
Disney provides a lot of inter-area transportation. The need for a car depends on where your lodging is located. If you are on the Disney campus there sould not be a need for a car. If you would like to visit other Orlando events then a car would be needed. a shuttle is usually provided by the area lodging to and from the airport and to and from the Disney resorts. ROR mª¿ªm >>> [EMAIL PROTECTED] 04/17/01 06:00PM >>> Anybody knows if one need to rent a car there? Alex Hillman -Original Message- Sent: Tuesday, April 17, 2001 4:11 PM To: Multiple recipients of list ORACLE-L No offence to anyone but I enjoy being the designated driver. I have a good sense of humor and a great time at any get-together. Any location that the group can centrally find is ideal. ROR mª¿ªm -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle 9iAs on Linux intel
TARUN SHARMA wrote: Moreover, I'm working first time on > Linux so I don't know how to set path and oracle home variables. > permanently. Is there any file like '.bat' in windows which set the >path. Help needed. Also if there is any book for oracle 9i and linux. > Yes. It depends on the shell you are using. By default Linux uses the Bash shell (/bin/bash). To set your environment variables in bash edit the file ~/.bash_profile and include something like: ORACLE_BASE=/usr/oraInventory ORACLE_HOME=/usr/oracle EXPORT ORACLE_BASE ORACLE_HOME If you are using the C-shell (/bin/csh), you can edit your ~/.cshrc file, the syntax is slightly different: set ORACLE_BASE = (/usr/oraInventory) set ORACLE_HOME = (/usr/oracle) If you are not sure which shell you are using, you can find out by typing: echo $SHELL also, the "~" thingy is a short hand for your home directory. So if your user name is "tsharma" ~/.bash_profile and /home/tsharma/.bash_profile refer to the same file. --tom. -- C-x C-c -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: tom panzarella INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: weird bitmap error
for those that care bug# 1666543 is the psycho bitmap count(*) error problem. broke since it looks like 8.0.5 and still not fixed in 8.1.7 joe > >Joe, > >I saw this same problem post on lazydba just yesterday. > >It was a bug that is supposedly fixed in 8.1.7, but you >know how that goes. > >It apparently does not corrupt data. You probably should >look up the details on MetaClink. > >Jared > > >On Tuesday 17 April 2001 08:00, Joseph Testa wrote: > > Scenario: > > > > AIX, 8.1.6.3. > > > > Cognos cube building going on. > > > > select count(*) from ft_sales, shows up 0 rows, its using a bitmap index > > out of the explain plan. > > > > I force full table scan or force to use other bitmap index, or PK(normal > > index) and it comes back with correct count(20+ million). > > > > Anyone seen anything like this before? > > > > thanks, joe > > > > _ > > Get your FREE download of MSN Explorer at http://explorer.msn.com _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joseph Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: move schema away from system table space
Another option: 1. Export the users table data. 2. alter user USERNAME default tablespace NEW_TABLESPACE temporary tablespace TEMP_TABLESPACE; 3. duplicate the schema with temp table names - tablename_temp 4. import the data 5. drop the old tables from the SYSTEM tablespace 6. alter table TABLENAME_TEMP rename to TABLENAME; Or: 1. alter user USERNAME default tablespace NEW_TABLESPACE temporary tablespace TEMP_TABLESPACE; 2. create table TABLENAME_TEMP as (select * from TABLENAME); 3. drop existing SYSTEM tables for the user 4. alter table TABLENAME_TEMP rename to TABLENAME; With both of these methods you will not need to shutdown the system at all. The second option should be done at a quite time, as you *could* (depending on the amount of data within these tables) be doing a huge select, and you don't want users updating to the table in SYSTEM, as you will loose data in the new tables. If you do have users that are connected 24*7, this could be difficult, but, if you are prepared to make them wait a while, you could lock the table in exclusive mode whilst doing the switch and drop. HTH Mark -Original Message- Ashe Sent: Wednesday, April 18, 2001 12:36 To: Multiple recipients of list ORACLE-L Best way is to use export/import utilities. This can be done with server on line and you will get all table data if done at quite time when table is not being updated. See Oracle Backup and Recovery Guide for info on exp/imp. PA -Original Message- Sent: Tuesday, April 17, 2001 3:50 PM To: Multiple recipients of list ORACLE-L Hi All, I need to move a user/schema away from the system tablespace to a newly created user tablespace. Could somebody point me to the references on how to do the job? Since a production server is running off the schema, is it possible to do the move without having to shut down the server? Thanks a lot. WL __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: lwm INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Patricia Ashe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
db link hangs
AIX 4.3 RDBMS 8.1.6.x We have a problem using a db link. The listener-side server port (1521) is hanging. This happens about once a week - and ends up crashing the far database because queries through the link just hang and don't return - then we die with a max processes error. Has anyone else had any problems with this? How did you solve it? Can I (should I) get the Oracle listener to listen on more than one port? What are some other ways to solve the problem? PS: I can connect and query the table when logged in locally. Other connections (not using 1521) work to the server. netstat (and telnet to 1521) shows only connections thru 1521 are a problem. thanks, ..tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Problem with DBMS_SQL
Hi Bhat, DBA is a role under Oracle7 (although is was a system privilege under version 6). Roles are not effective in stored procedures. Invoker's rights changes that somewhat in 8i, but for now you have to grant the system privilege directly to the procedure owner. You cannot rely on privileges obtained via roles. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Wednesday, 18 April 2001 21:16 To: Multiple recipients of list ORACLE-L Hi Steve, The account has DBA privilege and by using a direct DDL I am able to create the table. Anything else I can check-up. Thanks, - Bhat -Original Message- From: Steve Adams [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 18, 2001 7:01 PM To: Multiple recipients of list ORACLE-L Subject:RE: Problem with DBMS_SQL Hi Bhat, The CREATE TABLE privilege probably needs to be granted directly. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ PS. Please don't copy "[EMAIL PROTECTED]" on questions to the list. -Original Message- Sent: Wednesday, 18 April 2001 19:14 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Hi Gurus, I am experincing a problem with a procedure containing DBMS_SQL to create a table. On execution of the script I get the message PL/SQL procedure successfully completed, but the table doesn't get created. In the error log file I can see ORA-01031: insufficient privileges message. Any ideas. HP-UX : Oracle 7.3.4.4.1 Thanks. - Bhat Here is the procedure create or replace PROCEDURE create_table_mbn015 IS dyn_sql LONG; cid INTEGER; a integer; b varchar2(100); abcdinteger; BEGIN cid := DBMS_SQL.OPEN_CURSOR; dyn_sql := 'CREATE TABLE mbn015 STORAGE (INITIAL 5M NEXT 5M) TABLESPACE MUGDBDATA1 AS( SELECT DISTINCT p.item, p.loc, p.cppprodmethod, c.loadoffsetdur, p.scheddate, (p.scheddate - c.loadoffsetdur/1440) calcdate FROM stsc.planorder p,stsc.cppprodmethodstep c WHERE p.item = c.item AND p.loc = c.loc AND p.cppprodmethod = c.cppprodmethod AND c.stepnum = 20)'; DBMS_SQL.PARSE(cid, dyn_sql, dbms_sql.v7); abcd := DBMS_SQL.EXECUTE(cid); dbms_output.put_line(abcd); DBMS_SQL.CLOSE_CURSOR(cid); EXCEPTION WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR(cid); a := sqlcode; b := substr(sqlerrm,1,100); INSERT INTO errors VALUES (sysdate, 'A:CT', a, b); END create_table_mbn015; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official O
Re: Meeting at IOUG
Thats great, b/c I prefer to be the designated drinker. However, I am willing to share that duty. Thanks Ron. >>> [EMAIL PROTECTED] 04/17/01 04:11PM >>> No offence to anyone but I enjoy being the designated driver. I have a good sense of humor and a great time at any get-together. Any location that the group can centrally find is ideal. ROR mª¿ªm -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gene Sais INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Temporary Tablespace Design
FOR YOUR INFORMATION ESIS and EPFAL are now part of Logica. The Internet email addresses of the staff has changed to the following - [EMAIL PROTECTED] eg [EMAIL PROTECTED] Emails using the old format will continue to be delivered until 30th June 2001. Kirti Deshpande writes "Also, keep SORT_AREA_RETAINED_SIZE the same as SORT_AREA_SIZE. As the former will be used while fetching data after sort phase is completed." I thought best advice was to make the retained size smaller (50%?) of sort_area_size to allow initial sort memory to be released after the first part of the sort is managed and only the merge phase of a disk sort is left to do. I appreciate that a lot depends on the amount of memory available and the number of concurrent processes that may be performing sorts but surely the aim is to free memory up as soon as possible down to the sort_area_retained_size Thanks John (PS I do apologise for the rubbish at the top of this mail but it is inserted after I have sent the mail into our mail gateway) This e-mail and any files transmitted with it, are confidential to Logica and are intended solely for the use of the individual or entity to whom they are addressed. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hallas, John INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Conventions for naming indexes, constraints etc.
Hi Gints Plivna, Thanks for the URL. BTW where are you now? Whom are you working for? Regards, Ranganath -Original Message- [EMAIL PROTECTED] Sent: Wednesday, April 18, 2001 4:06 PM To: Multiple recipients of list ORACLE-L Hi! How it is in our case http://www.itsystems.lv/gints/eng/naming_conventions.htm But this was not mentioned earlier because I created it some minutes ago ;) Gints Plivna "Ranganath K" group.com> cc: Sent by: Subject: Conventions for naming indexes, constraints etc. [EMAIL PROTECTED] 01.04.18 12:20 Please respond to ORACLE-L Hi listers, A couple of days back somebody in the list had sent an URL about the convention to be followed for naming indexes, constraints etc. I have lost it somewhere. Could any of you who have saved and have it now send it to me? I am in need of it very badly. TIA and regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Problem with DBMS_SQL
Hi If you can create the table in pure sql, then I maybe you don't have the execute privielge for dbms_sql package. Remember you must have this privilege explicitly granted to user not by a role. I hope this helps. Gyula > see ORA-01031: insufficient privileges message. Any ideas. > > create or replace PROCEDURE create_table_mbn015 IS >dyn_sql LONG; >cid INTEGER; >a integer; >b varchar2(100); >abcdinteger; > BEGIN >cid := DBMS_SQL.OPEN_CURSOR; >dyn_sql := 'CREATE TABLE mbn015 > STORAGE (INITIAL 5M NEXT 5M) > TABLESPACE MUGDBDATA1 > AS( SELECT DISTINCT p.item, p.loc, > p.cppprodmethod, c.loadoffsetdur, p.scheddate, >(p.scheddate - c.loadoffsetdur/1440) calcdate FROM > stsc.planorder p,stsc.cppprodmethodstep c > WHERE p.item = c.item AND p.loc = c.loc AND > p.cppprodmethod > = c.cppprodmethod AND c.stepnum = 20)'; >DBMS_SQL.PARSE(cid, dyn_sql, dbms_sql.v7); >abcd := DBMS_SQL.EXECUTE(cid); >dbms_output.put_line(abcd); >DBMS_SQL.CLOSE_CURSOR(cid); > EXCEPTION > WHEN OTHERS THEN >DBMS_SQL.CLOSE_CURSOR(cid); >a := sqlcode; >b := substr(sqlerrm,1,100); >INSERT INTO errors VALUES (sysdate, 'A:CT', a, b); > END create_table_mbn015; > / -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andor Gyula INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Problem with DBMS_SQL
Hi Steve, The account has DBA privilege and by using a direct DDL I am able to create the table. Anything else I can check-up. Thanks, - Bhat -Original Message- From: Steve Adams [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 18, 2001 7:01 PM To: Multiple recipients of list ORACLE-L Subject:RE: Problem with DBMS_SQL Hi Bhat, The CREATE TABLE privilege probably needs to be granted directly. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ PS. Please don't copy "[EMAIL PROTECTED]" on questions to the list. -Original Message- Sent: Wednesday, 18 April 2001 19:14 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Hi Gurus, I am experincing a problem with a procedure containing DBMS_SQL to create a table. On execution of the script I get the message PL/SQL procedure successfully completed, but the table doesn't get created. In the error log file I can see ORA-01031: insufficient privileges message. Any ideas. HP-UX : Oracle 7.3.4.4.1 Thanks. - Bhat Here is the procedure create or replace PROCEDURE create_table_mbn015 IS dyn_sql LONG; cid INTEGER; a integer; b varchar2(100); abcdinteger; BEGIN cid := DBMS_SQL.OPEN_CURSOR; dyn_sql := 'CREATE TABLE mbn015 STORAGE (INITIAL 5M NEXT 5M) TABLESPACE MUGDBDATA1 AS( SELECT DISTINCT p.item, p.loc, p.cppprodmethod, c.loadoffsetdur, p.scheddate, (p.scheddate - c.loadoffsetdur/1440) calcdate FROM stsc.planorder p,stsc.cppprodmethodstep c WHERE p.item = c.item AND p.loc = c.loc AND p.cppprodmethod = c.cppprodmethod AND c.stepnum = 20)'; DBMS_SQL.PARSE(cid, dyn_sql, dbms_sql.v7); abcd := DBMS_SQL.EXECUTE(cid); dbms_output.put_line(abcd); DBMS_SQL.CLOSE_CURSOR(cid); EXCEPTION WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR(cid); a := sqlcode; b := substr(sqlerrm,1,100); INSERT INTO errors VALUES (sysdate, 'A:CT', a, b); END create_table_mbn015; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Problem with DBMS_SQL
Hi Bhat, The CREATE TABLE privilege probably needs to be granted directly. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ PS. Please don't copy "[EMAIL PROTECTED]" on questions to the list. -Original Message- Sent: Wednesday, 18 April 2001 19:14 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Hi Gurus, I am experincing a problem with a procedure containing DBMS_SQL to create a table. On execution of the script I get the message PL/SQL procedure successfully completed, but the table doesn't get created. In the error log file I can see ORA-01031: insufficient privileges message. Any ideas. HP-UX : Oracle 7.3.4.4.1 Thanks. - Bhat Here is the procedure create or replace PROCEDURE create_table_mbn015 IS dyn_sql LONG; cid INTEGER; a integer; b varchar2(100); abcdinteger; BEGIN cid := DBMS_SQL.OPEN_CURSOR; dyn_sql := 'CREATE TABLE mbn015 STORAGE (INITIAL 5M NEXT 5M) TABLESPACE MUGDBDATA1 AS( SELECT DISTINCT p.item, p.loc, p.cppprodmethod, c.loadoffsetdur, p.scheddate, (p.scheddate - c.loadoffsetdur/1440) calcdate FROM stsc.planorder p,stsc.cppprodmethodstep c WHERE p.item = c.item AND p.loc = c.loc AND p.cppprodmethod = c.cppprodmethod AND c.stepnum = 20)'; DBMS_SQL.PARSE(cid, dyn_sql, dbms_sql.v7); abcd := DBMS_SQL.EXECUTE(cid); dbms_output.put_line(abcd); DBMS_SQL.CLOSE_CURSOR(cid); EXCEPTION WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR(cid); a := sqlcode; b := substr(sqlerrm,1,100); INSERT INTO errors VALUES (sysdate, 'A:CT', a, b); END create_table_mbn015; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Problem with DBMS_SQL
Hi Gurus, I am experincing a problem with a procedure containing DBMS_SQL to create a table. On execution of the script I get the message PL/SQL procedure successfully completed, but the table doesn't get created. In the error log file I can see ORA-01031: insufficient privileges message. Any ideas. HP-UX : Oracle 7.3.4.4.1 Thanks. - Bhat Here is the procedure create or replace PROCEDURE create_table_mbn015 IS dyn_sql LONG; cid INTEGER; a integer; b varchar2(100); abcdinteger; BEGIN cid := DBMS_SQL.OPEN_CURSOR; dyn_sql := 'CREATE TABLE mbn015 STORAGE (INITIAL 5M NEXT 5M) TABLESPACE MUGDBDATA1 AS( SELECT DISTINCT p.item, p.loc, p.cppprodmethod, c.loadoffsetdur, p.scheddate, (p.scheddate - c.loadoffsetdur/1440) calcdate FROM stsc.planorder p,stsc.cppprodmethodstep c WHERE p.item = c.item AND p.loc = c.loc AND p.cppprodmethod = c.cppprodmethod AND c.stepnum = 20)'; DBMS_SQL.PARSE(cid, dyn_sql, dbms_sql.v7); abcd := DBMS_SQL.EXECUTE(cid); dbms_output.put_line(abcd); DBMS_SQL.CLOSE_CURSOR(cid); EXCEPTION WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR(cid); a := sqlcode; b := substr(sqlerrm,1,100); INSERT INTO errors VALUES (sysdate, 'A:CT', a, b); END create_table_mbn015; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Conventions for naming indexes, constraints etc.
Hi! How it is in our case http://www.itsystems.lv/gints/eng/naming_conventions.htm But this was not mentioned earlier because I created it some minutes ago ;) Gints Plivna "Ranganath K" group.com> cc: Sent by: Subject: Conventions for naming indexes, constraints etc. [EMAIL PROTECTED] 01.04.18 12:20 Please respond to ORACLE-L Hi listers, A couple of days back somebody in the list had sent an URL about the convention to be followed for naming indexes, constraints etc. I have lost it somewhere. Could any of you who have saved and have it now send it to me? I am in need of it very badly. TIA and regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Acces forum
I would like to know if there is a website for access developing where I can ask questios? Roland Sköldblom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
authentication externally on WinNT
Hi! Oracle8.1.7 on WinNT. I created database user with external authentication with whom I'm logged on that WinNT server. When I start exp there is error invalid username/password logon denied. What am I missing? It works just fine on UNIX! TIA, Sonja -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-2?Q?Sonja_=A9ehovi=E6?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Conventions for naming indexes, constraints etc.
Hi listers, A couple of days back somebody in the list had sent an URL about the convention to be followed for naming indexes, constraints etc. I have lost it somewhere. Could any of you who have saved and have it now send it to me? I am in need of it very badly. TIA and regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: server sizing [NT: need XEON?]
"Eric D. Pierce" wrote: > If you can get ahold of him, or can wait until he > isn't so busy, Paul Drake of this list is the resident RAID/hardware > expert. Eric - this is one of the funniest pages I have seen: http://www.ultratech-llc.com/Personal/Files/?File=~MoreInfo.TXT uh oh. I don't know if I'm honored, or scared. I'm not nearly the resident RAID expert - but would like to be someday. These 4-drive JBOD workstations at home don't quite give me the room to play. Does the word "Heuristics" mean anything to you? I'd rather have stats than guidelines. Here is my one word of caution before heading off into some other direction: multiple database writer processes are not supported for Oracle 8.1.7 on NT. This is straight off of the platform-specific docs off of the Docs CDROM. I did receive an ORA-00600 error relating to running multiple db_writers on NT with 8.1.6. Don't do it. >From Metalink: Subject: Re : Db Writer Process Oracle on NT only allows/needs a single database writer process (DBWR). Multiple DBWRs on UNIX is not multiple "real" DBWRs which all go scan for dirty buffers and write them to disk. It's really just one "real" DBWR and some I/O slaves. The "real" DBWR tells the slave DBWRs to do I/O. On NT, there's really no need for this since async I/O and NT will take care of all that for you. NT acts as the I/O slaves and the "real" DBWR [1 DBWR thread] then checks the "slaves" to see if the I/O is done. Melissa Holman Oracle Support Will this produce the rate-limiting-factor in your system? I do not know. *Something* will be a rate limiting factor for a particular process. But as OLTP users' transactions vary so much from the batch processes, what exactly is the overall rate_limiting_factor is tough to say - your mileage will vary. My background is in Chemical Engineering. I spent some time in R & D wearing a white labcoat. Chemical Engineering - Process Debottlenecking - is all about "Where is the bottleneck?" Usually, a 15-20% margin is designed in such that the plant can run safely over its design spec without a retrofit of key components. Beyond that - you have to find what component needs to be increased to add capacity - safely. For your system - design in more capacity that you need, with room for expansion. Empty drive bays in external storage cabinets are good. The rebuilds of drive arrays are painful - but if you had a 7 bay drive cage (half a 14 bay - e.g. Storageworks 4200) with only a 4 drive array, hiking it up to a 6 drive RAID 0+1 (I'd rather duplex these) with a hot spare is tolerable. On an Ultra 160/m channel, 6 drives is reasonable. Here is a baseline - I/Os per second. A standard drive can accommodate 80-100 I/Os per second. Larger bufffers (cache) on the hard drive can increase that number, as can read-ahead caching algorithms on the RAID controllers. But if the reads are scattered, as in index reads and nested loops - the cache hits are not very likely. Lots of memory reduces the amount of read I/O required for a query. It does not reduce the amount of memory bandwidth required, nor does it completely reduce the overhead of creating consistent reads (reading rollback segs to provide cr blocks). This is a large part of the non-linear nature of scaling - interference of user processes creating additional overhead. When you say transactions per minute - these need to be translated out to actual logical and physical I/Os. Best bet here is to vary the number of sessions running in a scripted mode, and benchmark the I/Os required per transaction. So where would your bottleneck be? LGWR? DBWR? ARCH? Will your checkpoints really hurt with only a single DBWR? Will it be in the network I/O, Storage subsystem, memory bandwidth or just the capacity of the PCI bus channels? Definitely go for the 64-bit, 66 MHz RAID controllers, as many PCI bus channels and memory controllers as you can find. I can tell you this from experience: when the CPUs are I/O-bound, they do not appear as active in NT Task Manager. try this out: perform an export of a schema with datafiles, indexes, temp and the dump file all on one drive. then perform an export of the same schema with all of the above files on separate drives. I saw the average CPU utilization INCREASE from 20% to 80% average in performing this back on 7.3. Did the export utility crush the CPU? No. The I/O bottleneck (disc) was somewhat removed, and the CPU was free to perform useful work. The overall time of execution of the export dropped - but I don't have the scaling factors around. I've seen an NT box (Compaq Proliant 7000) run (4 CPUs, 26 hard drives, 7 I/O channels, 3.2 GB RAM) that averages a CPU utilization of around 85% under full load. But its executing user tasks much more quickly than a comperable dual CPU box. What this config tells you is - with sufficient available I/O - the CPUs will attempt to run at 100% utilization provided there are requests in the queue. You do have some flexib
Overlapping date intervals?
Hi Gurus ! I should examine date intervals regrding to overlapping. How could I do it in the simpliest way? I suppose there is a solution with "union/intersect" select, but I can't find out it. Please help me. Thanks in advance. Gyula -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andor Gyula INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locally Managed Tablespaces
FOR YOUR INFORMATION ESIS and EPFAL are now part of Logica. The Internet email addresses of the staff has changed to the following - [EMAIL PROTECTED] eg [EMAIL PROTECTED] Emails using the old format will continue to be delivered until 30th June 2001. I have noted a very minor issue on our AIX 4.3 systems running 8.1.6.0.0 when creating a LMT. We get the error message ORA-01543 tablespace XXX already exists but it then goes on to create the tablespace. This is repeatable on several systems all at the same version. On an 8.1.6.3 system it does not happen. There is nothing on Metalink. The command I am using is create tablespace john datafile '' size 100M extent management local uniform size 1M; Anybody getting this on an 8.1.6.0 system? John -Original Message- From: Stephen Andert [mailto:[EMAIL PROTECTED]] Sent: 18 April 2001 01:03 To: Multiple recipients of list ORACLE-L Subject:Re: Locally Managed Tablespaces Patricia, FWIW, We have been using them for several months now and have not seen anything negative as a result. We decided to start using them for all new tablespaces and we add a new set of tablespaces every month to accomodate our partitioning strategy. Our biggest databases that we are doing this with are over 200MB on Compaq Tru64. Database version was 8.1.6.0 and we have moved to 8.1.6.2 with no noted problems. Good luck. Stephen >>> [EMAIL PROTECTED] 04/17 4:35 PM >>> I am interested in some statistics on Oracle locally managed tablespaces. I have been looking for any bugs or negative info about them. Are they in use at alot of sites? Seems like all the information I have come across is positive. Which is great! But maybe they aren't being used at alot of sites. Can I hear about experiences from others on this list? how many sites are actually using them? I have several databases that I am getting ready to go production soon and would like to create the tablespaces as locally managed, but need more statistics. PA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Patricia Ashe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Andert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This e-mail and any files transmitted with it, are confidential to Logica and are intended solely for the use of the individual or entity to whom they are addressed. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hallas, John INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
