Simple Write-Up: 1Z0-047 - SQL Expert - ALTER TABLE SET UNUSED

Nos estudos deste exame notei que caiu várias questões sobre o assunto do comando ALTER TABLE com a opção SET UNSED.Outra dica é estudar além do óbivio.Como assim além do óbivio?Pois bem percebo que nos exames é que as questões não visam somente avaliar se conhecemos o básicos como comandos,mas sim o que eles fazem além daquilo,se tem volta,o causam,o que é possívem reverter o que não é,existe outra forma de fazer tal coisa?É isso que percebi que eles exigem.

Pensando nisso vou escrever posts relacionados ao com as questões mais intrigantes do exame e fazer a prova prática que pro sinal é o mais interessante.

Vamos ao que interessa :

--Cria tabela com constraint CHECK em uma das colunas

[oracle@jamaica ~]$ sqlplus dbajcc

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jan 12 07:16:32 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

dbajcc@OCADB on 12-JAN-10 >create table tst_column_unused (col1 number,col2 varchar2(40),col3 number(3)
constraint check_col3 check (col3 > 4)) ;
2
-- insere dados na tabela tst_column_unused
insert into tst_column_unused select 1,'primeira linha',6 from dual ;

insert into tst_column_unused select 1,'segunda linha',7 from dual;

-- Cria tabela com campo LONG

create table tst_column_unused2(col1 number,col2 long,col3 number(2)) ;

--insere dados na tabela tst_column_unused2

insert into tst_column_unused2 values(1,'texto muito longo',1) ;

commit ;
Table created.

Elapsed: 00:00:00.06
dbajcc@OCADB on 12-JAN-10 >dbajcc@OCADB on 12-JAN-10 >dbajcc@OCADB on 12-JAN-10 >
1 row created.

Elapsed: 00:00:00.02
dbajcc@OCADB on 12-JAN-10 >dbajcc@OCADB on 12-JAN-10 >
1 row created.

Elapsed: 00:00:00.01
dbajcc@OCADB on 12-JAN-10 >dbajcc@OCADB on 12-JAN-10 >dbajcc@OCADB on 12-JAN-10 >dbajcc@OCADB on 12-JAN-10 >dbajcc@OCADB on 12-JAN-10 >
Table created.

Elapsed: 00:00:00.04
dbajcc@OCADB on 12-JAN-10 >dbajcc@OCADB on 12-JAN-10 >dbajcc@OCADB on 12-JAN-10 >dbajcc@OCADB on 12-JAN-10 >
1 row created.

Elapsed: 00:00:00.02
dbajcc@OCADB on 12-JAN-10 >dbajcc@OCADB on 12-JAN-10 >

Commit complete.

Elapsed: 00:00:00.01
dbajcc@OCADB on 12-JAN-10 >

Após o SET UNUSED :

1) Quando você faz um SET UNUSED em uma columa não existe a opção SET USED,como é um DDL so sorry.Você também não consegue mais visualizá-la com o comando DESCRIBE

2)Depois de executar SET UNUSED os dados continuam lá na coluna porém inacessível até você executar um DROP.O espaço utilizado pela coluna na tabela também não é liberado até o DROP COLUMN.

3)Você consegue criar uma coluna com o mesmo nome da anterior exceto se a coluna que está com o SET UNUSED for do tipo LONG

dbajcc@OCADB on 12-JAN-10 >select * from tst_column_unused ;

   COL1 COL2 COL3
---------- ---------------------------------------- ----------
   1 primeira linha 6
   1 segunda linha 7

Elapsed: 00:00:00.02

dbajcc@OCADB on 12-JAN-10 >select constraint_name,constraint_type ,table_name,status
from user_constraints
where table_name = upper('tst_column_unused') ; 2 3

CONSTRAINT_NAME C TABLE_NAME STATUS
------------------------------ - ------------------------------ --------
CHECK_COL3 C TST_COLUMN_UNUSED ENABLED

Elapsed: 00:00:00.01

dbajcc@OCADB on 12-JAN-10 >select * from tst_column_unused2;

COL1 COL2 COL3
---------- -------------------------------------------------------------------------------- ----------
1 texto muito longo 1

Elapsed: 00:00:00.03
dbajcc@OCADB on 12-JAN-10 >

Vamos colocar a columa col3 da tabela tst_column_unused como UNUSED :

dbajcc@OCADB on 12-JAN-10 >alter table tst_column_unused set unused column col3 ;

Table altered.

Elapsed: 00:00:00.29

dbajcc@OCADB on 12-JAN-10 >

Neste momento a coluna está pronta para DROP.O DROP também se torna mais rápido que se fossemos executar o DROP diretamente.

Vamos executar um DESCRIBE

dbajcc@OCADB on 12-JAN-10 >describe tst_column_unused ;

Name Null? Type

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------------------------------------------------------

COL1 NUMBER

COL2 VARCHAR2(40)

Como visto ela não está mais disponível.

Vamos ver a constraint que tinhamos nesta coluna :

dbajcc@OCADB on 12-JAN-10 >select constraint_name,constraint_type ,table_name,status

from user_constraints

where table_name = upper('tst_column_unused') ; 2 3

no rows selected

Elapsed: 00:00:00.01

dbajcc@OCADB on 12-JAN-10 >

Nervoso hein?

Vamos criar uma coluna com o mesmo nome :

dbajcc@OCADB on 12-JAN-10 >alter table tst_column_unused add col3 number(3) ;

Table altered.

Elapsed: 00:00:00.02

Agora vamos fazer o DROP da coluna que está com SET UNUSED :

dbajcc@OCADB on 12-JAN-10 >select * from user_unused_col_tabs ;

TABLE_NAME COUNT

------------------------------ ----------

TESTE_DEL 1

TST_COLUMN_UNUSED 1

Elapsed: 00:00:00.02

Temos duas tabelas entre elas a que estamos fazendo o teste.

dbajcc@OCADB on 12-JAN-10 >alter table tst_column_unused drop unused columns ;

Table altered.

Elapsed: 00:00:00.62

dbajcc@OCADB on 12-JAN-10 >

dbajcc@OCADB on 12-JAN-10 >select * from user_unused_col_tabs ;

TABLE_NAME COUNT

------------------------------ ----------

TESTE_DEL 1

Elapsed: 00:00:00.01

dbajcc@OCADB on 12-JAN-10 >

Ok.

Outra coisa interessante é sobre as tabelas com colunas com datatype LONG que depois de ter marcado ela como UNUSED você não consegue adicionar outra coluna no lugar dela.

dbajcc@OCADB on 12-JAN-10 >alter table tst_column_unused2 set unused column col2;

Table altered.

Elapsed: 00:00:00.31
dbajcc@OCADB on 12-JAN-10 >select * from user_unused_col_tabs ;

TABLE_NAME COUNT
------------------------------ ----------
TESTE_DEL 1
TST_COLUMN_UNUSED2 1

Elapsed: 00:00:00.07
dbajcc@OCADB on 12-JAN-10 >alter table tst_column_unused2 add col4 long ;
alter table tst_column_unused2 add col4 long
*
ERROR at line 1:
ORA-01754: a table may contain only one column of type LONG

Elapsed: 00:00:00.01

dbajcc@OCADB on 12-JAN-10 >alter table tst_column_unused2 drop unused columns ;

Table altered.

Elapsed: 00:00:00.26

dbajcc@OCADB on 12-JAN-10 >select * from user_unused_col_tabs ;

TABLE_NAME COUNT

------------------------------ ----------

TESTE_DEL 1

Elapsed: 00:00:00.01

dbajcc@OCADB on 12-JAN-10 >drop table tst_column_unused ;

Table dropped.

Elapsed: 00:00:00.17

dbajcc@OCADB on 12-JAN-10 >drop table tst_column_unused2 ;