Technology changes very fast, keep UNlearning

1- Export and Import are the only ways to reorganize tables

Below is a demostration of how a table and its index can be compressed online;

drop table tong_test purge ;
create table tong_test nologging parallel 2 as select * from dba_source order by owner, name;
create index nui_tong_test on tong_test(owner, name) nologging parallel 2 ;
analyze table tong_test compute statistics for table for all indexed columns;

select blocks, bytes from dba_segments where segment_name = ‘TONG_TEST’ ;

BLOCKS BYTES
———- ———-
2560 41943040

alter table tong_test move nologging parallel 2 compress;
select blocks, bytes from dba_segments where segment_name = ‘TONG_TEST’ ;

BLOCKS BYTES
———- ———-
1920 31457280

alter table tong_test move nologging parallel 2 NOcompress;
select blocks, bytes from dba_segments where segment_name = ‘TONG_TEST’ ;

BLOCKS BYTES
———- ———-
2240 36700160

select blocks, bytes from dba_segments where segment_name = ‘NUI_TONG_TEST’ ;

BLOCKS BYTES
———- ———-
640 10485760

alter index nui_tong_test rebuild nologging parallel 2 compress online;
select blocks, bytes from dba_segments where segment_name = ‘NUI_TONG_TEST’ ;

BLOCKS BYTES
———- ———-
320 5242880

alter index nui_tong_test rebuild nologging parallel 2 NOcompress online;
select blocks, bytes from dba_segments where segment_name = ‘NUI_TONG_TEST’ ;

BLOCKS BYTES
———- ———-
640 10485760

Also TABLESPACE or STORAGE is optionally can be used, enabling you to move the table with or without reorganizing it.

ALTER TABLE tong_test MOVE TABLESPACE tbs_data nologging parallel 2 ;
ALTER TABLE tong_test MOVE STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0 MINEXTENTS 5) nologging parallel 2 ;

After version 9i when you add the word ONLINE to the syntax, the table or the index can be moved even while users are updating it. Parallel DML is not supported during online MOVE with ALTER TABLE option but, you can also perform online table redefinition with the Enterprise Manager Reorganize Objects wizard or with the DBMS_REDEFINITION package.

So,

– “when a table needs to be moved, reorganized, or defragmented, it’s a painful process, the table must be exported to an external “dump” file. The original table must then be dropped, optionally re-created manually in some cases, and finally imported again from the dump file.”

is obsolete.

2- Columns can’t be dropped, can’t be renamed or reorganized

drop table tong_test purge ;
create table tong_test nologging parallel 2 as select * from user_source;
analyze table tong_test compute statistics for table for all indexed columns;

— rename column example
ALTER TABLE tong_test RENAME COLUMN name TO object_name;

— drop column example
ALTER TABLE tong_test DROP COLUMN object_name;

— Set A Column Unused
ALTER TABLE tong_test SET UNUSED COLUMN object_name;

— Drop Unused Columns On A Very Large Table
ALTER TABLE tong_test DROP UNUSED COLUMNS CHECKPOINT 1000;

DBMS_REDEFINITION supplied PL/SQL package enables a DBA to change a table’s column structure while the table is online and available to users;

1. Use DBMS_REDEFINITION.CAN_REDEF_TABLE to check if the table qualifies for online redefinition, and specify if the redefinition will be by primary key (recommended) or by row IDs.
2. Create an empty table in the same schema, but with the desired layout. Omit columns you want to drop; include new columns you’d like to create.
3. Use DBMS_REDEFINITION.START_REDEF_TABLE to begin the redefinition process. The parameters to this procedure indicate the old table, the new one, and how to map the existing columns to the columns of the new table.
4. Create any constraints (disabled), triggers, indexes, and grants desired on the new table.
5. Use DBMS_REDEFINITION.FINISH_REDEF_TABLE to complete the process. The original table is locked for a short time regardless of how large or small it is, while the definitions are swapped between the two tables.
6. Drop the temporary table used in the redefinition; it is no longer needed.

Also you can rename a trigger or a constraint;

ALTER TRIGGER bi_t RENAME TO new_trigger_name;

ALTER TABLE person RENAME CONSTRAINT pk_person TO person_pk;

So,

– “when a column needs to be dropped, renamed, or restructures, it’s a painful process, the table must be recreated with a create table as select process and the original table must then be dropped.”

is obsolete.

3- Only the owner of a table can grant permission to use it

The DBA role has a system privilege called GRANT ANY OBJECT PRIVILEGE. In the past, a statement like

GRANT SELECT ON scott.emp TO hr;

would fail unless SCOTT had first granted the DBA the SELECT privilege on his table WITH GRANT OPTION.

So,

– “DBA cannot grant permissions on an object unless he or she logins as the owner of the object. To do this is an administration difficulty because you need to hack the password of the user if you dont know it by the help of PASSWORD column in the DBA_USERS view and ALTER USER IDENFITIFIED BY VALUES statement.”

is obsolete.

4- Block size is fixed, Tablespace can not be renamed, While dropping a tablespace datafiles must be cleaned also from OS.

Here is a demostration on how to create a 4K(non-standard block size) tablespace on a 8K db_block_size database;

create tablespace tbs_1 datafile ‘D:\oraclexe\oradata\XE\tbs_1.dbf’ size 10M Blocksize 4K;
create tablespace tbs_1 datafile ‘D:\oraclexe\oradata\XE\tbs_1.dbf’ size 10M Blocksize 4K
*
ERROR at line 1:
ORA-29339: tablespace block size 4096 does not match configured block sizes

ALTER SYSTEM SET db_4K_cache_size = 8M;

System altered.

create tablespace tbs_1 datafile ‘D:\oraclexe\oradata\XE\tbs_1.dbf’ size 10M Blocksize 4K;

Tablespace created.

select name, value from v$parameter where name like ‘db_%_size’ and value > ‘0’ ;

NAME VALUE
—————————— ——————————
db_block_size 8192
db_4k_cache_size 8388608
db_recovery_file_dest_size 10737418240

select a.tablespace_name, a.block_size from dba_tablespaces a where a.tablespace_name like ‘TB%’ ;

TABLESPACE_NAME BLOCK_SIZE
—————————— ———-
TBS_1 4096

ALTER TABLESPACE TBS_1 RENAME TO TBS_2 ;

Tablespace altered.

DROP TABLESPACE TBS_2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

So,

– “once the block size is chosen, it cannot be altered without reloading the database”,
– “once a tablespace created can not be renamed”
– “if you drop a tablespace you also must clean the related datafiles of that tablespace from operating system”

are obsolete.

Mr.Dee Hock, the founder of Visa, says: “The problem is never how to get new, innovative thoughts into your mind, but how to get the old ones out.”

Mr.Thomas Kyte, vice president of Oracle, says; “It’s a constant learning and re-learning. that is the only reason I’ve stayed here over 13 years now :) It would be utterly boring any other way.”

Things change, except that and keep UNlearning by reading the Oracle® Database New Features Guide with every new release of Oracle. This pain in short-run will in deed make your life easier in long-run. An expert is never after short-run profits..

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.2.0Refences Used :
http://www.psoug.org/library.html
Rethink five outdated ideas about Oracle
Five more things to unlearn about Oracle