RE: Urgent: Retrieving Disk Space
Thank you all very, very much. Indeed, ORACLE-L is a great place to share problems and the solution. Ferenc, can you give the exact syntax of the command please? Thanks, Aleem -Original Message- Sent: Wednesday, June 05, 2002 3:23 AM To: Multiple recipients of list ORACLE-L Subject:RE: Urgent: Retrieving Disk Space Why don't you try to 'alter table move' the table to the tablespace it should be in. If no other objects are beyond the block of the block 0 of the segment, you should be able to resize the system01.dbf file. If something else got created and is owned by SYS beyond the last block of this dubious segment, I guess you are SOL. alternative is to export the entire DB, re-create it, and give NO mortal user any quota on SYS, period. The import it. Since you're only talking 7 GB of which most is crap anyway, this should not be a problem. Regards: Ferenc Mantfeld Senior Performance Engineer Siebel Performance Engineering Melbourne, 3000, VIC, Australia -Original Message- Sent: Tuesday, 4 June 2002 2:49 AM To: Multiple recipients of list ORACLE-L The size of System01.dbf has grown from 1GB to 4GB Try to never make a user with the SYSTEM as a default tablespace, neither create files there, which is probaly your case. -- Alexandre The size of temp01.dbf is around 1GB now. Best Regards, Aleem -Original Message- Sent: Tuesday, June 04, 2002 1:18 PM To: Multiple recipients of list ORACLE-L Subject: Re: Urgent: Retrieving Disk Space Hi What has grown? you don't mention datafiles. So if there is only the three types of files you mention there I can only assume that the part that has grown is the number of archive logs. These are needed for recovery since last backup and can not be thrown away. can be zipped though. If I'm not mistaken if you use the insert /*+ APPEND */ hint it uses direct load and does not create so much redo (archives) and is faster as well Jack Abdul Aleem dmit@beaconhouseTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .edu.pk cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: Urgent: Retrieving Disk Space [EMAIL PROTECTED] 04-06-2002 09:58 Please respond to ORACLE-L Hi! Our Oracle database is installed on second partition of the drive capacity 7GB. Last night one the developers executed a query to create and populate a table from another table. The query wasn't successful i.e., the table couldn't get created. However the database size has grown enormous almost occupying the whole disc space. There are control files, redo log files, archive log files. What to do? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). == De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose
RE: Urgent: Retrieving Disk Space
alter table table_name move tablespace target_TS storage (initial bloody_big_number rest_of_Storage_clause) pctfree nn ; HTH. Regards: Ferenc Mantfeld Senior Performance Engineer Siebel Performance Engineering Melbourne, 3000, VIC, Australia -Original Message- Sent: Thursday, 6 June 2002 1:28 AM To: Multiple recipients of list ORACLE-L Thank you all very, very much. Indeed, ORACLE-L is a great place to share problems and the solution. Ferenc, can you give the exact syntax of the command please? Thanks, Aleem -Original Message- Sent: Wednesday, June 05, 2002 3:23 AM To: Multiple recipients of list ORACLE-L Subject:RE: Urgent: Retrieving Disk Space Why don't you try to 'alter table move' the table to the tablespace it should be in. If no other objects are beyond the block of the block 0 of the segment, you should be able to resize the system01.dbf file. If something else got created and is owned by SYS beyond the last block of this dubious segment, I guess you are SOL. alternative is to export the entire DB, re-create it, and give NO mortal user any quota on SYS, period. The import it. Since you're only talking 7 GB of which most is crap anyway, this should not be a problem. Regards: Ferenc Mantfeld Senior Performance Engineer Siebel Performance Engineering Melbourne, 3000, VIC, Australia -Original Message- Sent: Tuesday, 4 June 2002 2:49 AM To: Multiple recipients of list ORACLE-L The size of System01.dbf has grown from 1GB to 4GB Try to never make a user with the SYSTEM as a default tablespace, neither create files there, which is probaly your case. -- Alexandre The size of temp01.dbf is around 1GB now. Best Regards, Aleem -Original Message- Sent: Tuesday, June 04, 2002 1:18 PM To: Multiple recipients of list ORACLE-L Subject: Re: Urgent: Retrieving Disk Space Hi What has grown? you don't mention datafiles. So if there is only the three types of files you mention there I can only assume that the part that has grown is the number of archive logs. These are needed for recovery since last backup and can not be thrown away. can be zipped though. If I'm not mistaken if you use the insert /*+ APPEND */ hint it uses direct load and does not create so much redo (archives) and is faster as well Jack Abdul Aleem dmit@beaconhouseTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .edu.pk cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: Urgent: Retrieving Disk Space [EMAIL PROTECTED] 04-06-2002 09:58 Please respond to ORACLE-L Hi! Our Oracle database is installed on second partition of the drive capacity 7GB. Last night one the developers executed a query to create and populate a table from another table. The query wasn't successful i.e., the table couldn't get created. However the database size has grown enormous almost occupying the whole disc space. There are control files, redo log files, archive log files. What to do? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). == De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van
Re: Urgent: Retrieving Disk Space
Hi What has grown? you don't mention datafiles. So if there is only the three types of files you mention there I can only assume that the part that has grown is the number of archive logs. These are needed for recovery since last backup and can not be thrown away. can be zipped though. If I'm not mistaken if you use the insert /*+ APPEND */ hint it uses direct load and does not create so much redo (archives) and is faster as well Jack Abdul Aleem dmit@beaconhouseTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .edu.pk cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: Urgent: Retrieving Disk Space [EMAIL PROTECTED] 04-06-2002 09:58 Please respond to ORACLE-L Hi! Our Oracle database is installed on second partition of the drive capacity 7GB. Last night one the developers executed a query to create and populate a table from another table. The query wasn't successful i.e., the table couldn't get created. However the database size has grown enormous almost occupying the whole disc space. There are control files, redo log files, archive log files. What to do? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). == De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has
RE: Urgent: Retrieving Disk Space
All the files you mention here are critical files. What is the Temp File Size. Did u see that. Why did the table not get created. Best Regards, Ganesh R Tel : +971 (4) 397 3337 Ext 420 Fax : +971 (4) 397 6262 HP : +971 (50) 745 6019 Live to learn... forget... and learn again. -Original Message- Sent: Tuesday, June 04, 2002 11:58 AM To: Multiple recipients of list ORACLE-L Hi! Our Oracle database is installed on second partition of the drive capacity 7GB. Last night one the developers executed a query to create and populate a table from another table. The query wasn't successful i.e., the table couldn't get created. However the database size has grown enormous almost occupying the whole disc space. There are control files, redo log files, archive log files. What to do? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ganesh Raja 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: Urgent: Retrieving Disk Space
Thanx Jack, The size of System01.dbf has grown from 1GB to 4GB The size of temp01.dbf is around 1GB now. Best Regards, Aleem -Original Message- Sent: Tuesday, June 04, 2002 1:18 PM To: Multiple recipients of list ORACLE-L Subject:Re: Urgent: Retrieving Disk Space Hi What has grown? you don't mention datafiles. So if there is only the three types of files you mention there I can only assume that the part that has grown is the number of archive logs. These are needed for recovery since last backup and can not be thrown away. can be zipped though. If I'm not mistaken if you use the insert /*+ APPEND */ hint it uses direct load and does not create so much redo (archives) and is faster as well Jack Abdul Aleem dmit@beaconhouseTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .edu.pk cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: Urgent: Retrieving Disk Space [EMAIL PROTECTED] 04-06-2002 09:58 Please respond to ORACLE-L Hi! Our Oracle database is installed on second partition of the drive capacity 7GB. Last night one the developers executed a query to create and populate a table from another table. The query wasn't successful i.e., the table couldn't get created. However the database size has grown enormous almost occupying the whole disc space. There are control files, redo log files, archive log files. What to do? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). == De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen 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
Re: Urgent: Retrieving Disk Space
Aleem, Did you mention control files, redo log files, archive log files with recovery in mind? If so, you probably don't need it. Read next if you have all the files for the database on this one poor hard drive: Review the cause for create table as select... command. If it is the message like unable to allocate extent... something (don't remeber exact phrase) then your datafiles have AUTOEXTEND ON option. Once they extended to take all the space on the disk, you've got the error message. And your log information has probably also noticeable grown as Jack sad in another answer. You can shrink you datafile(s). But should not trash you archive logs (unless of course you want to have you database safe). To get rid of archive log info you may perform a full backup. -- Alexandre Hi! Our Oracle database is installed on second partition of the drive capacity 7GB. Last night one the developers executed a query to create and populate a table from another table. The query wasn't successful i.e., the table couldn't get created. However the database size has grown enormous almost occupying the whole disc space. There are control files, redo log files, archive log files. What to do? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev 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: Urgent: Retrieving Disk Space
SYSTEM @ 4GB i have never seen this kind of size for a system datafile UNLESS .. objects other than the data dictionary reside in system tablespace ... if that's the case... u have to cleanup all objects which does not belong to SYS/SYSTEM and are in the SYSTEM tablespace .. TEMP will keep the space occupide even if a transaction failed.. , sometimes on my version 7.3.4 i have to bounce the DB to cleanup the temp segments... also, after you clean up the SYSTEM tablespaces... you should switch off the autoextend for SYSTEM's datafile too. HTH PS: this is all assuming that the SYSTEM01.dbf does indeed belong to the SYSTEM tablespace. !!! -- From: Abdul Aleem[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Tuesday, June 04, 2002 4:03 PM To: Multiple recipients of list ORACLE-L Subject: RE: Urgent: Retrieving Disk Space Thanx Jack, The size of System01.dbf has grown from 1GB to 4GB The size of temp01.dbf is around 1GB now. Best Regards, Aleem -Original Message- Sent: Tuesday, June 04, 2002 1:18 PM To: Multiple recipients of list ORACLE-L Subject: Re: Urgent: Retrieving Disk Space Hi What has grown? you don't mention datafiles. So if there is only the three types of files you mention there I can only assume that the part that has grown is the number of archive logs. These are needed for recovery since last backup and can not be thrown away. can be zipped though. If I'm not mistaken if you use the insert /*+ APPEND */ hint it uses direct load and does not create so much redo (archives) and is faster as well Jack Abdul Aleem dmit@beaconhouseTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .edu.pk cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: Urgent: Retrieving Disk Space [EMAIL PROTECTED] 04-06-2002 09:58 Please respond to ORACLE-L Hi! Our Oracle database is installed on second partition of the drive capacity 7GB. Last night one the developers executed a query to create and populate a table from another table. The query wasn't successful i.e., the table couldn't get created. However the database size has grown enormous almost occupying the whole disc space. There are control files, redo log files, archive log files. What to do? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). == De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information
Re: Urgent: Retrieving Disk Space
The size of System01.dbf has grown from 1GB to 4GB Try to never make a user with the SYSTEM as a default tablespace, neither create files there, which is probaly your case. -- Alexandre The size of temp01.dbf is around 1GB now. Best Regards, Aleem -Original Message- Sent: Tuesday, June 04, 2002 1:18 PM To: Multiple recipients of list ORACLE-L Subject: Re: Urgent: Retrieving Disk Space Hi What has grown? you don't mention datafiles. So if there is only the three types of files you mention there I can only assume that the part that has grown is the number of archive logs. These are needed for recovery since last backup and can not be thrown away. can be zipped though. If I'm not mistaken if you use the insert /*+ APPEND */ hint it uses direct load and does not create so much redo (archives) and is faster as well Jack Abdul Aleem dmit@beaconhouseTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .edu.pk cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: Urgent: Retrieving Disk Space [EMAIL PROTECTED] 04-06-2002 09:58 Please respond to ORACLE-L Hi! Our Oracle database is installed on second partition of the drive capacity 7GB. Last night one the developers executed a query to create and populate a table from another table. The query wasn't successful i.e., the table couldn't get created. However the database size has grown enormous almost occupying the whole disc space. There are control files, redo log files, archive log files. What to do? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). == De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: Urgent: Retrieving Disk Space
Ganesh, The query didn't get completed because of the disc space. Temp01.dbf size is about 1GB System01.dbf size is nearly 4GB against last nearly 1GB Aleem -Original Message- Sent: Tuesday, June 04, 2002 1:53 PM To: Multiple recipients of list ORACLE-L Subject:RE: Urgent: Retrieving Disk Space All the files you mention here are critical files. What is the Temp File Size. Did u see that. Why did the table not get created. Best Regards, Ganesh R Tel : +971 (4) 397 3337 Ext 420 Fax : +971 (4) 397 6262 HP : +971 (50) 745 6019 Live to learn... forget... and learn again. -Original Message- Sent: Tuesday, June 04, 2002 11:58 AM To: Multiple recipients of list ORACLE-L Hi! Our Oracle database is installed on second partition of the drive capacity 7GB. Last night one the developers executed a query to create and populate a table from another table. The query wasn't successful i.e., the table couldn't get created. However the database size has grown enormous almost occupying the whole disc space. There are control files, redo log files, archive log files. What to do? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ganesh Raja INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Urgent: Retrieving Disk Space
Alexandre, I mentioned these files to know if any of these can be deleted/copied elsewhere safely? How do I check upon the reason of failure of a particular query? The developer recalls that the message was something like low disc space. TIA! Aleem -Original Message- Sent: Tuesday, June 04, 2002 2:13 PM To: Multiple recipients of list ORACLE-L Subject:Re: Urgent: Retrieving Disk Space Aleem, Did you mention control files, redo log files, archive log files with recovery in mind? If so, you probably don't need it. Read next if you have all the files for the database on this one poor hard drive: Review the cause for create table as select... command. If it is the message like unable to allocate extent... something (don't remeber exact phrase) then your datafiles have AUTOEXTEND ON option. Once they extended to take all the space on the disk, you've got the error message. And your log information has probably also noticeable grown as Jack sad in another answer. You can shrink you datafile(s). But should not trash you archive logs (unless of course you want to have you database safe). To get rid of archive log info you may perform a full backup. -- Alexandre Hi! Our Oracle database is installed on second partition of the drive capacity 7GB. Last night one the developers executed a query to create and populate a table from another table. The query wasn't successful i.e., the table couldn't get created. However the database size has grown enormous almost occupying the whole disc space. There are control files, redo log files, archive log files. What to do? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Urgent: Retrieving Disk Space
I am just going to repeat the question all the people here have asked what was the users Temp Segment when he ran the CTAS. You have Made a Mistake there. Try Resizing the system Tablespace. To a Lower Value but that may not happen. If that does not happen u will have to move the temp tablespace out of that disk and don't ever have your system Tablespace for a User Other Than Sys not even System. HTH Best Regards, Ganesh R Tel : +971 (4) 397 3337 Ext 420 Fax : +971 (4) 397 6262 HP : +971 (50) 745 6019 Live to learn... forget... and learn again. -Original Message- Sent: Tuesday, June 04, 2002 1:54 PM To: Multiple recipients of list ORACLE-L Ganesh, The query didn't get completed because of the disc space. Temp01.dbf size is about 1GB System01.dbf size is nearly 4GB against last nearly 1GB Aleem -Original Message- Sent: Tuesday, June 04, 2002 1:53 PM To: Multiple recipients of list ORACLE-L Subject:RE: Urgent: Retrieving Disk Space All the files you mention here are critical files. What is the Temp File Size. Did u see that. Why did the table not get created. Best Regards, Ganesh R Tel : +971 (4) 397 3337 Ext 420 Fax : +971 (4) 397 6262 HP : +971 (50) 745 6019 Live to learn... forget... and learn again. -Original Message- Sent: Tuesday, June 04, 2002 11:58 AM To: Multiple recipients of list ORACLE-L Hi! Our Oracle database is installed on second partition of the drive capacity 7GB. Last night one the developers executed a query to create and populate a table from another table. The query wasn't successful i.e., the table couldn't get created. However the database size has grown enormous almost occupying the whole disc space. There are control files, redo log files, archive log files. What to do? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ganesh Raja INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ganesh Raja 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: Urgent: Retrieving Disk Space
Jack, I had asked the developer to use another table space instead of system, he did that, but when restoring, the backup he missed that and the tables were restored in system table space. The default table space name is STD. I am not sure of the original size of temp table space. As I have understood from Alexendre's message that the system table space has grown in size (one way process), does not actually occupy that much. So there probably isn't much worry, unless, some DML says out of disc space. Aleem -Original Message- Sent: Tuesday, June 04, 2002 2:43 PM To: Multiple recipients of list ORACLE-L Subject:RE: Urgent: Retrieving Disk Space Hi, Autoextend is on for system tablespace (why?). What's the default tablespace for the account the developer used to create the object (system?), if not system his objects should not go to system tablespace so than maybe his temporary tablespace is system and he used this tablespace to perform a massive sort operation in order to fill that newly created table. Temp is 1GB, mine is 13Gb (on one of our databases) and I don't worry about it. Question is did it grow also or was it 1Gb to begin with? Jack Abdul Aleem dmit@beaconhouseTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .edu.pk cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: RE: Urgent: Retrieving Disk Space [EMAIL PROTECTED] 04-06-2002 11:03 Please respond to ORACLE-L Thanx Jack, The size of System01.dbf has grown from 1GB to 4GB The size of temp01.dbf is around 1GB now. Best Regards, Aleem -Original Message- Sent:Tuesday, June 04, 2002 1:18 PM To: Multiple recipients of list ORACLE-L Hi What has grown? you don't mention datafiles. So if there is only the three types of files you mention there I can only assume that the part that has grown is the number of archive logs. These are needed for recovery since last backup and can not be thrown away. can be zipped though. If I'm not mistaken if you use the insert /*+ APPEND */ hint it uses direct load and does not create so much redo (archives) and is faster as well Jack Abdul Aleem dmit@beaconhouseTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .edu.pk cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: Urgent: Retrieving Disk Space [EMAIL PROTECTED] 04-06-2002 09:58 Please respond to ORACLE-L Hi! Our Oracle database is installed on second partition of the drive capacity 7GB. Last night one the developers executed a query to create and populate a table from another table. The query wasn't successful i.e., the table couldn't get created. However the database size has grown enormous almost occupying the whole disc space. There are control files, redo log files, archive log files. What to do? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). == De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te
Re: Urgent: Retrieving Disk Space
I mentioned these files to know if any of these can be deleted/copied elsewhere safely? You MUST move you archive redo logs if you want your database to be recoverable. You need them if you make backups at all. Anyway you should periodically clean up the space for your archive redo logs, or else your database will hang somewhen because oracle will not be able to write log info. If you dont backup, then disable archivelog mode.(I should say: then backup :), but be sure you understand consequences. You should not delete control files and redo log files, of course. How do I check upon the reason of failure of a particular query? The developer recalls that the message was something like low disc space. If you have SQL_TRACE parameter enabled for the whole database, see the .trc files in the directory set by USER_DUMP_DEST parameter. If not, than your developer probably didn't enabled it for the session as well and you canot see exact error message. Probably, as been told before, server couldn't extend datafile of system tablespace. Almost 100% sure, you problem has just been described by several people anwering here. Take the precautitions adviced. -- Alexandre TIA! Aleem -Original Message- Sent: Tuesday, June 04, 2002 2:13 PM To: Multiple recipients of list ORACLE-L Subject: Re: Urgent: Retrieving Disk Space Aleem, Did you mention control files, redo log files, archive log files with recovery in mind? If so, you probably don't need it. Read next if you have all the files for the database on this one poor hard drive: Review the cause for create table as select... command. If it is the message like unable to allocate extent... something (don't remeber exact phrase) then your datafiles have AUTOEXTEND ON option. Once they extended to take all the space on the disk, you've got the error message. And your log information has probably also noticeable grown as Jack sad in another answer. You can shrink you datafile(s). But should not trash you archive logs (unless of course you want to have you database safe). To get rid of archive log info you may perform a full backup. -- Alexandre Hi! Our Oracle database is installed on second partition of the drive capacity 7GB. Last night one the developers executed a query to create and populate a table from another table. The query wasn't successful i.e., the table couldn't get created. However the database size has grown enormous almost occupying the whole disc space. There are control files, redo log files, archive log files. What to do? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev 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
RE: Urgent: Retrieving Disk Space
Why don't you try to 'alter table move' the table to the tablespace it should be in. If no other objects are beyond the block of the block 0 of the segment, you should be able to resize the system01.dbf file. If something else got created and is owned by SYS beyond the last block of this dubious segment, I guess you are SOL. alternative is to export the entire DB, re-create it, and give NO mortal user any quota on SYS, period. The import it. Since you're only talking 7 GB of which most is crap anyway, this should not be a problem. Regards: Ferenc Mantfeld Senior Performance Engineer Siebel Performance Engineering Melbourne, 3000, VIC, Australia -Original Message- Sent: Tuesday, 4 June 2002 2:49 AM To: Multiple recipients of list ORACLE-L The size of System01.dbf has grown from 1GB to 4GB Try to never make a user with the SYSTEM as a default tablespace, neither create files there, which is probaly your case. -- Alexandre The size of temp01.dbf is around 1GB now. Best Regards, Aleem -Original Message- Sent: Tuesday, June 04, 2002 1:18 PM To: Multiple recipients of list ORACLE-L Subject: Re: Urgent: Retrieving Disk Space Hi What has grown? you don't mention datafiles. So if there is only the three types of files you mention there I can only assume that the part that has grown is the number of archive logs. These are needed for recovery since last backup and can not be thrown away. can be zipped though. If I'm not mistaken if you use the insert /*+ APPEND */ hint it uses direct load and does not create so much redo (archives) and is faster as well Jack Abdul Aleem dmit@beaconhouseTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .edu.pk cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: Urgent: Retrieving Disk Space [EMAIL PROTECTED] 04-06-2002 09:58 Please respond to ORACLE-L Hi! Our Oracle database is installed on second partition of the drive capacity 7GB. Last night one the developers executed a query to create and populate a table from another table. The query wasn't successful i.e., the table couldn't get created. However the database size has grown enormous almost occupying the whole disc space. There are control files, redo log files, archive log files. What to do? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). == De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor
