Menu de navegação

[souzaParticipante]

Pessoal ,

Tenho uma tablespace TEMP em modo autoextend (não sei quem deixou assim), e a tablespace chegou a 12Gb e acabou com o espaço livre na minha partição. Agora já tirei do modo autoextend , porém não consigo diminuir o tamanho do datafile.

Alguém teria alguma sugestão ?

Obrigado

[vieriParticipante]

Provavelmente vc está levando um :
ORA-03297: file contains used data beyond requested RESIZE value

Sua tarefa:

1°Vc terá que montar o banco(startup mount)
2°criar uma nova com o tamanho desejado
3°colocar a nova como a default e dropar a antiga !!
4°subir o banco

Sacou ?

[]s

[CleitonHanzenParticipante]

Opá..

A instrução do Vieri está incorreta…desde o oracle 9i, existe um comando:

“alter database default temporary tablespace”

Que pode ser executado com a instance rodando normalmente, sem a necessidade de baixar o banco.

Ou seja, o procedimento correto é:

1. Criar uma nova tablespace temp
2. Setar a nova tablespace como default
3. Remover ou recriar a antiga

Se ao droar a tablespace, você receba erro informando que a tablespace está em uso, utilize a query abaixo para determinar as sessões:

SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

Flw, e pessoal, vamos manter a qualidade do fórum, por favor, dêem dicas somente do que vcs tem certeza que funciona, não passar ‘qualquer procedimento’.

[vieriParticipante]

Concordo plenamente com você SantosClay.

Tanto nas considerações pessoais como nas considerações técnicas.
(você foi ainda + prudente do que eu) deve ter experiência em ambientes
ORACLE.

Um erro, uma indisponibilidade longa, pode custar o emprego de um DBA
em ambientes críticos.

Assumo que minha resposta foi ofensiva.

Porém deve haver um respeito mútuo, parceria e cumplicidade entre as pessoas que ajudam no forúm.
Pois os ajudantes são DBA’s de mercado e possuem um nome a zelar.
E não é façil encontrar DBA’s que possuem esse principio de querer ajudar pelo simples fato de estar ajudando.

Isso aqui não é orkut para escrever oque quiser sobre quem quiser.
E afirmar oque quiser sem embasamento ou critério.

E como oque as pessoas buscam aqui é conhecimento segue….

Contents

Overview
Dropping / Recreating Temporary Tablespace Method
Drop Tempfile Command Method – (Oracle9i and higher)

---

Overview

In many database configurations, the DBA will choose to allow their temporary tablespace (actually the tempfile(s) for the temporary tablespace) to autoextend. A runaway query or sort can easily chew up valuable space on the disk as the tempfiles(s) extends to accommodate the request for space. If the increase in size of the temporary tablespace (the tempfiles) gets exceedingly large because of a particular anomaly, the DBA will often want to resize the temporary tablespace to a more reasonable size in order to reclaim that extra space. The obvious action would be to resize the tempfiles using the following statement:

SQL> alter database tempfile ‘/u02/oradata/TESTDB/temp01.dbf’ resize 250M;
alter database tempfile ‘/u02/oradata/TESTDB/temp01.dbf’ resize 250M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
Ouch. You next bounce the database and attempt the same statement only to be greeted with the same error!
Several methods exist to reclaim the used space used for a larger than normal temporary tablespace depending on which release of Oracle you are running. The method that exists for all releases of Oracle is to simply drop and recreate the temporary tablespace back to its original (or another reasonable) size. If you are using Oracle9i or higher, you can apply another method which is to drop the large tempfile (which will drop the tempfile from the data dictionary AND the O/S file system) using the alter database tempfile ” drop including datafiles; command. Each method is explained below.

---

Dropping / Recreating Temporary Tablespace Method

Keep in mind that the procedures documented here for dropping and recreating your temporary tablespace should be performed during off hours with no users logged on performing work.
If you are working with a temporary tablespace in Oracle8i or a temporary tablespace in Oracle9i that is NOT the default temporary tablespace for the database, this process is straight forward. Simply drop and recreate the temporary tablespace:

SQL> DROP TABLESPACE temp;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE TEMP
2 TEMPFILE ‘/u02/oradata/TESTDB/temp01.dbf’ SIZE 500M REUSE
3 AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.
Oracle9i Default Temporary Tablespace
The procedures above document how to drop a temporary tablespace that is not the default temporary tablespace for the database. You will know fairly quickly if the tablespace is a default temporary tablespace when you are greeted with the following exception:

SQL> DROP TABLESPACE temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
In cases where the temporary tablespace you want to resize (using the drop/recreate method) is the default temporary tablespace for the database, you have several more steps to perform, all documented below. The first step you need to perform is create another temporary tablespace (lets call it TEMP2). The next step would be to remove the temporary tablespace you want to resize from being the default temporary tablespace (in our example, this will be a tablespace named TEMP) by making TEMP2 the default. Drop / recreate the TEMP tablespace to the size you want. Finally, make the newly created TEMP tablespace your default temporary tablespace for the database and drop the TEMP2 tablespace. A full example session is provided below:

SQL> CREATE TEMPORARY TABLESPACE temp2
2 TEMPFILE ‘/u02/oradata/TESTDB/temp2_01.dbf’ SIZE 5M REUSE
3 AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE temp
2 TEMPFILE ‘/u02/oradata/TESTDB/temp01.dbf’ SIZE 500M REUSE
3 AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

---

Drop Tempfile Command Method – (Oracle9i and higher)

If you are using Oracle9i or higher, another method exists that allows you to simply drop a tempfile. As with the above method, this should be performed during off hours with no users logged on performing work.
The first step is to obtain the name of the tempfile to drop. For this example, my temporary tablespace name is TEMP and the name of the tempfile is /u02/oradata/TESTDB/temp2_01.dbf:

SQL> SELECT tablespace_name, file_name, bytes
2 FROM dba_temp_files WHERE tablespace_name = ‘TEMP’;

TABLESPACE_NAME FILE_NAME BYTES

---

TEMP /u02/oradata/TESTDB/temp01.dbf 13,107,200,000

The normal size of my temporary tablespace is 512MB which has always been more than adequate for this environment. A bad query, however, increased the size of this tablespace (my tempfile) to over 13GB and I would like to reclaim that space. In the example below, I simply drop and recreate the tempfile:

SQL> ALTER DATABASE TEMPFILE ‘/u02/oradata/TESTDB/temp01.dbf’ DROP INCLUDING DATAFILES;

Database altered.

SQL> ALTER TABLESPACE temp ADD TEMPFILE ‘/u02/oradata/TESTDB/temp01.dbf’ SIZE 512m
2 AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;

Tablespace altered.
On some platforms (i.e. Windows 2000), it is possible for the tempfile to be deleted from DBA_TEMP_FILES but not from the hard drive of the server.
If this occurs, simply delete the file using regular O/S commands.

SQL> SELECT tablespace_name, file_name, bytes
2 FROM dba_temp_files WHERE tablespace_name = ‘TEMP’;

TABLESPACE_NAME FILE_NAME BYTES

---

TEMP /u02/oradata/TESTDB/temp01.dbf 536,870,912

If users are currently accessing the tempfile you are attempting to drop, you may receive the following error:

SQL> ALTER DATABASE TEMPFILE ‘/u02/oradata/TESTDB/temp01.dbf’ DROP INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE ‘/u02/oradata/TESTDB/temp01.dbf’ DROP INCLUDING DATAFILES
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time
As for the poor users who were using the tempfile, their transaction will end and will be greeted with the following error message:

SQL> @testTemp.sql
join dba_extents c on (b.segment_name = c.segment_name)
*
ERROR at line 4:
ORA-00372: file 601 cannot be modified at this time
ORA-01110: data file 601: ‘/u02/oradata/TESTDB/temp01.dbf’
ORA-00372: file 601 cannot be modified at this time
ORA-01110: data file 601: ‘/u02/oradata/TESTDB/temp01.dbf’
If this happens, you should attempt to drop the tempfile again so the operation is successful:

SQL> ALTER DATABASE TEMPFILE ‘/u02/oradata/TESTDB/temp01.dbf’ DROP INCLUDING DATAFILES;

Database altered.

8)

[santosclayParticipante]

Cara é isso valeu!!!

Vamos manter esse principio:

Somos pessoas normais, passivéis de erro e queremos ajudar pelo simples fato de estar ajudando.

Assim todos crescerão a comunidade GPO e seus membros.

Um grande abraço a todos.

Clayton.

[lugbestrioParticipante]

[quote=”santosclay”:114yfx6n]Cara é isso valeu!!!

Vamos manter esse principio:

Somos pessoas normais, passivéis de erro e queremos ajudar pelo simples fato de estar ajudando.

Assim todos crescerão a comunidade GPO e seus membros.

Um grande abraço a todos.

Clayton.[/quote]

Olá meus amigos.

Aproveitando esse tópico, tenho uma dúvida que pode paracer idiota para muitos. Mas mesmo assim não custa perguntar não é mesmo?
A tablespace temp sendo autoextend false, o que acontece quando seu tamanho chegar no limite? Pode prejudicar os esquemas nas transações que a têm como default temporary tablespace?

Desde já agradeço a tolerância.

[Autor]

Posts