Constraints

Coisas estranhas que descobri com constraints

1
desabilitei em lote varias constraints de varias tabelas, alguns erros aconteceram por que existia uma ordem. Nao sabia disso. qual seria?

2
Eh necessario o uso de tablespace temp para habilitar.

3 Tentei habilitar uma constraint com o seguinte nome:

alter table RF02_PAYS enable constraint FK_RF02_PAYS _RF01_ZONE

se prestar atençao tem um espaço apos pays

so funcionou assim:

alter table RF02_PAYS enable constraint "FK_RF02_PAYS _RF01_ZONE"

para renomear fazer assim:

ALTER TABLE table_name RENAME CONSTRAINTconstraint_name TO new_constraint_name;

alter table RF02_PAYS rename constraint "FK_RF02_PAYS _RF01_ZONE" TO FK_RF02_PAYS_RF01_ZONE

Ora-01652 : Unable to extend temp segment by 128 in tablespace

Erro ORA-01652 não é possível estender o segmento temp. em 64 no tablespace TEMP Luciano 3 resposta(s)
Olá pessoal, estou tentando executar um select no servidor e estou recebendo essa mensagem de erro:
"ORA-01652 não é possível estender o segmento temp. em 64 no tablespace TEMP"

O que posso fazer para resolver esse problema ??

Atenciosamente,

Luciano Henrique

Enviado por: Chiappa em 25/11/2004
Como referido nos manuais de Admin, o procedimento seria :

ALTER DATABASE DATAFILE 'pathenomedodatafileaaumentar' RESIZE nnn;

ou

ALTER TABLESPACE nomedatablespace ADD DATAFILE 'pathenomedoNOVOdatafileaaumentar' SIZE nnn;

onde nnn é o tamanho que vc quer. Pra consultar dados da tablespace e dos datafiles, vc faz :

SELECT * FROM DBA_TABLESPACES;

e

SELECT * FROM DBA_DATA_FILES;

(ou, se a tablespace estiver usando tempfiles, vc faz SELECT * FROM DBA_TEMP_FILES; Neste último caso a sintaxe pra adicionar é um pouco diferente, consulte nos manuais que vc acha...

[]s

resource busy and acquire with NOWAIT specified

ORA-00054:
resource busy and acquire with NOWAIT specified
Cause:
Resource interested is busy.
Action:
Retry if necessary.

Erro dado quando numa sessao c/ usuario agodba fiz insert sem commit e na outra, tbm com usuario agodba, dei truncate.
Teste: se eu der rollback na segunda sessao, aquela q dei truncate, nada acontece pq nao houve manip de dados nessa sessao.
Se eu der rollback ou commit, o truncate na outra sessao executa.


SQL> delete from ag22_mvt;
0 rows deleted.
SQL> truncate table ag22_mvt;truncate table ag22_mvt *ERROR at line 1:ORA-00054: resource busy and acquire with NOWAIT specified

import export in oracle

http://www-it.desy.de/systems/services/databases/oracle/impexp/impexp.html.en

http://www.orafaq.com/wiki/Import_Export_FAQ

What are the common Import/ Export problems?
ORA-00001: Unique constraint (...) violated
You are importing duplicate rows. Use IGNORE=NO to skip tables that already exist (imp will give an error if the object is re-created).
ORA-01555: Snapshot too old
Ask your users to STOP working while you are exporting or use parameter CONSISTENT=YES
ORA-01562: Failed to extend rollback segment
Create bigger rollback segments or set parameter COMMIT=Y while importing
IMP-00015: Statement failed ... object already exists...
Use the IGNORE=Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows.

Oracle: Why is shutdown immediate not immediate ?

Author's name: Norman Dunbar;
Author's Email: Oracle@bountifulsolutions.co.uk
Date written: 22/08/2001
Oracle version(s): 8.1.7

There are four ways to shut down a database :

Shutdown
Shutdown immediate
Shutdown transactional
Shutdown abort
Shutdown waits for everyone to finish & log out before it shuts down. The database is cleanly shutdown.

Shutdown immediate rolls back all uncommitted transactions before it shuts down. The database is cleanly shutdown.

Shutdown transactional waits for all current transactions to commit or rollback before it shuts down. The database is cleanly shutdown.

Shutdown abort quickly shuts down - the next restart will require instance recovery. The database is technically crashed.

The key reason for an immediate shutdown not being immediate is because of the need to rollback all current transactions. If a user has just started a transaction to update emp set sal = sal * 2 where emp_id = 1000; then this will be rolled back almost instantaneously.

However, if another user has been running a huge update for the last four hours, and has not yet committed, then four hours of updates have to be rolled back and this takes time.

So, if you really want to shutdown right now, then the advised route is :

shutdown abort
startup restrict
shutdown
When you shutdown abort, Oracle kills everything immediately. Startup restrict will allow only dba users to get in but, more importantly, will carry out instance recovery and recover back to a consistent state using the current on-line redo logs. The final shutdown will perform a clean shutdown. Any cold backups taken now will be of a consistent database.

There has been much discussion on this very subject on the Oracle Server newsgroups. Some people are happy to backup the database after a shutdown abort, others are not. I prefer to use the above method prior to taking a cold backup - if I have been unable to shutdown or shutdown immediate that is.

Marcado pra morrer

SQL> alter system kill session '12,1600';alter system kill session '12,1600'

*ERROR at line 1:ORA-00031: session marked for kill

ORA-00031:
session marked for kill
Cause:
The session specified in an ALTER SYSTEM KILL SESSION command cannot be killed immediately (because it is rolling back or blocked on a network operation), but it has been marked for kill. This means it will be killed as soon as possible after its current uninterruptable operation is done.
Action:
No action is required for the session to be killed, but further executions of the ALTER SYSTEM KILL SESSION command on this session may cause the session to be killed sooner.