RE: deletion of data from a large table
Rukmini, Try to do coalesce on the tablespace on which the table is sitting in after deleting rows. alter tablespace TSNAME coalesce; Baskar -Original Message- Sent: 26 July 2001 12:41 To: Multiple recipients of list ORACLE-L Hi All, I have deleted 3 lakhs records from a large table. But there is no effect on tablespace i.e. before I delete the data freespace in TS is 100MB , after deletion also it is showing 100MB. What could be the reason ? How to get the freespace after deleting the data ? Thanks rukmini -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rukmini Devi 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: Ramasamy, Baskar 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: version
Rukmini, select * from V$version Baskar -Original Message- Sent: 22 July 2001 17:17 To: Multiple recipients of list ORACLE-L Hi All, How can we find what version of oracle (standard/enterprise/workgroup) is running in the system. Is there any view/table ? Thanks rukmini -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rukmini Devi 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: Ramasamy, Baskar 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: How to deal with special character like in ' '
You could do select 'string1 '|| chr(38) ||' string2' from dual Baskar -Original Message- Sent: 23 July 2001 18:51 To: Multiple recipients of list ORACLE-L Hi, I want to insert a string like 'AM University' but has problem with '' I want the database exact AM University. But it always ask me new or old value because of symbol '' how could I deal with that. Thanks. HL _ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ http://phonecard.yahoo.com/?.refer=mailtagline -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ramasamy, Baskar 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: setting DbName in SQL*PLUS
Copy all three files into some directory (eg. c:\yourname\*.sql) Modify your registry for SQLPATH to c:\yourname(your first entry in SQLPATH) THen if you try to login to SQLPLUS, the prompt willbe always your global name. you might need to modify the login.sql file according to the path you have copied all the attached sql files. Baskar -Original Message- Sent: 23 July 2001 17:26 To: Multiple recipients of list ORACLE-L Try this link: http://www.iherve.com/oracle/prompt_sqlplus.htm Script and explaination included. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, July 20, 2001 1:27 PM Has anyone ever worked on setting the DbName as the sqlprompt in SQL*PLUS thru glogin.sql? Shailesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yadav, Shailesh 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). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrew Lieu 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). conn.sql login.sql LOGTEST.SQL
RE: Exp/Imp - suppress screen output?
run the script in background export.sh import.sh -Original Message- Sent: 20 July 2001 14:15 To: Multiple recipients of list ORACLE-L Does anyone know of a way, within Unix, to suppress the screen output from the Export and Import utilities? Thanks! -w __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter 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: Ramasamy, Baskar 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: Tracing
Best option would be turn on the sql_trace and timed_statistice parameter in init.ora and re-start the database. But makes ure you have enought space to write trc files in OS Baskar -Original Message- Sent: 16 July 2001 16:12 To: Multiple recipients of list ORACLE-L On Jul 16, 2001 at 06:45:36AM, Libal, Ivo wrote: Hello I would like to start tracing for different session than my one. How it is possible in 8.1.7 EE? I want to start tracing for different sessions with different levels (not necessary diff. levels). I found that it should be possible with dbms_support package, but i didnt find this package and I also didnt find creation script in my rdbms/admin directory (it should be dbmssupp.sql). Please help me where I can find it or how to do it. Ivo Libal dbms_system.set_ev(sid, serial, event, level, NULL); -- Vladimir Begun | Without freedom of choice there is no http://vbegun.net/ | creativity. http://vbegun.net/wap/ | -- Kirk, The return of the [EMAIL PROTECTED]| Archons, stardate 3157.4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vladimir Begun 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: Ramasamy, Baskar 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: sqlplus question ???
you have to put break on report before compute sum Baskar -Original Message- Sent: 13 July 2001 09:50 To: Multiple recipients of list ORACLE-L Andrea Oracle wrote: Hi all, I have these in sqlplus: set pagesize 999 Set linesize 100 Col file_name format a50 Col bytes format 999,999,999,999 compute sum of bytes on report select file_name, bytes from dba_data_files order by file_name; The query returns all the data files back, but it did NOT show the sum of the bytes. Is there anything I forgot to set? Thank you. Andrea Yes : break on report -- Regards, Stephane Faroult Oriole Corporation -- http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Ramasamy, Baskar 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: rollback gen
no -Original Message- Sent: 12 July 2001 14:51 To: Multiple recipients of list ORACLE-L will a ddl statement generate rollback. eg: create table or alter table or drop table ... thnx in adv. svs __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kommareddy sreenivasa 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: Ramasamy, Baskar 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: unable to allocate 4096 bytes of shared memory - HELP!!!!
you could do alter system flush shared_pool from the svrmgr Baskar -Original Message- Sent: 11 July 2001 14:17 To: Multiple recipients of list ORACLE-L We have just upgraded from 8.0.5 to 8.1.7 and are getting problems as follows : It appears to be related to dbms_job Can any one help? John Errors in file /u01/app/oracle/admin/PRTGH1/bdump/snp2_28976_prtgh1.trc: ORA-12012: error on auto execute of job 11223917 ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,SERVER _PRINTER_RESET,PL/SQL MPCODE,BAMIMA: Bam Buffer) This kind of message is usually linked to a fragmented shared pool. Try to check which are the big packages which are reloaded most often (out of memory I do not remember which is the V$ view to check - something like V$CACHED_OBJECTS or similar - anyway, I know that there are scripts to do this on our site in the DBA toolkit section, and I am sure that you may find it elsewhere as well) and try in the future to have them pinned in memory (a trigger on startup, perhaps) to avoid fragmentation. Once you have identified these packages, a (very) short term fix may be ALTER SYSTEM FLUSH SHARED_POOL. And increasing the size of your shared pool is probably something to do as well. Like with almost anything else, any new version means more memory, more packages, more stuff in tablespace SYSTEM ... Regards, Stephane Faroult Oriole Corporation - Performance tools for Oracle ® http://www.oriole.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Ramasamy, Baskar 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: GETTING FIRST 250 CHARACTERS FROM A LONG DATATYPE
Stephane, You could write a plsql by defiining a variable with 32K (like fetch_log_valuevarchar2(32760)) You could fetch the long data type column value into fetch_long_value variable after opening the cursor. After that you can use substr on this variable. Baskar -Original Message- Sent: 04 July 2001 23:20 To: Multiple recipients of list ORACLE-L Mark Liggayu wrote: HI GURUS, If I have a table that has a field with long data type and I want to get the first 250 characters out of it how can I do it. I tried using SUBSTR(long data type field, 1, 250) but it complained about ORA-00932: inconsistent datatypes. I then tried TO_CHAR(SUBSTR(long data type field, 1, 250)) but still gave me the same result. Tried TO_VARCHAR2 but to no avail. Thanks, Mark You can't apply a function to a LONG (but perhaps the LONG-to-LOB conversions of 8i). If this is possible to you (ie Oracle version supporting it), convert to CLOB or similar. Otherwise, all I can think of is a user-written PL/SQL function (would work with Oracle 7.3 and, of course, above). In PL/SQL, VARCHAR2 can be up to 32K (or around) and you CAN select a LONG into a large enough VARCHAR2. If your LONGs are under 32K, it is easy to write a function LONGSTART which fetches the suitable LONG into a suitably long VARCHAR2, and returns the 250 first characters of this VARCHAR2. Note that I am talking about feasibility, not performance (it may not be THAT bad but I wouldn't do it on millions of rows). -- HTH, Stephane Faroult Oriole Corporation Voice: +44 (0) 7050-696-269 Fax:+44 (0) 7050-696-449 Performance Tools Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Ramasamy, Baskar 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: GETTING FIRST 250 CHARACTERS FROM A LONG DATATYPE
Mark, You could write a plsql by defiining a variable with 32K (like fetch_log_valuevarchar2(32760)) You could fetch the long data type column value into fetch_long_value variable after opening the cursor. After that you can use substr on this variable. Baskar -Original Message- Sent: 04 July 2001 21:56 To: Multiple recipients of list ORACLE-L HI GURUS, If I have a table that has a field with long data type and I want to get the first 250 characters out of it how can I do it. I tried using SUBSTR(long data type field, 1, 250) but it complained about ORA-00932: inconsistent datatypes. I then tried TO_CHAR(SUBSTR(long data type field, 1, 250)) but still gave me the same result. Tried TO_VARCHAR2 but to no avail. Thanks, Mark -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Liggayu 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: Ramasamy, Baskar 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).
Drop table problem
Hi Could anyone tell me how to solve the below problem. I am trying to drop the table and I am getting the error ora 2266. I have disabled all the foreign key constraints also, but still I am getting the same error, Early response much appreciated Baskar -Original Message- Sent: 23 April 2001 20:02 To: Multiple recipients of list ORACLE-L Hello List, For those of you that use Perl or would like to use Perl for your regular DBA tasks, I have a request. Do you have any tasks you perform with a series of SQL scripts, shell scripts, etc, that you feel are somewhat of a kludge? Have you every asked yourself There must be a better way to do this? Or do you just have a wishlist of tools that would make day to day DBA life easier, but feel that it's too complex or unwieldy to tackle with PL/SQL and SQL*Plus? I'm looking for just those kinds of ideas for a project I'm working on. No ideas too silly or complicated. Granted, they may be too silly or complicated to actually implement, but you never know. No idea is a bad idea until it's thought through. Come to think of it, the same holds true for good ideas also. Anyway, let's hear it! Some examples of tools that I have used in Perl: * Create multiple users from a CSV file generated from MS Excel. Works great when someone asks for 100 users to be created. * Monitor databases for SNIPED sessions then KILL them. * Monitor the alert.log for Errors and mail to DBA's and or selectect other individuals. Errors can be filtered by type and selectively mailed. I've used this for duhvelopers that don't want to be bothered with error trapping and send hundreds of emails to the DBA's due to their error messages. Que's up messages for 5 minutes or 100 messages, whichever comes first ( avoids swamping the mail server in a crisis ) * How about a way to serve up passwords to users for use with command line utilities in a secure manner? Oh wait, I don't have that one yet. As you can see, I have a wishlist too. Lemme hear what you would like! TIA 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: Ramasamy, Baskar 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 Date change
Hi, Could any one tell me how to change the date and time in Oracle Database to future rather than doing in unix server. Baskar -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ramasamy, Baskar 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).
