This shows that "alter table x move" reclaims the space.
As seen here in: http://www.dba-oracle.com/t_alter_table_move_shrink_space.htm
It says it doesn't change the table size.
SQL> begin
2 for i in 1..1000000 loop
3 insert into audadm.t1 values (i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> analyze table audadm.t1 compute statistics;
Table analyzed.
SQL> select blocks,empty_blocks,num_rows from dba_tables where table_name='T1';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
2276 1819 1500000
SQL> select blocks, extents from dba_segments where segment_name='T1';
BLOCKS EXTENTS
---------- ----------
4096 2
SQL> select sum(bytes) from dba_segments where segment_name = 'T1';
SUM(BYTES)
----------
33554432
SQL> delete from audadm.t1 where mod(x,2)=0;
500000 rows deleted.
SQL> commit;
Commit complete.
SQL> analyze table audadm.t1 compute statistics;
Table analyzed.
SQL> select blocks,empty_blocks,num_rows from dba_tables where table_name='T1';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
2276 1819 1000000
SQL> select blocks, extents from dba_segments where segment_name='T1';
BLOCKS EXTENTS
---------- ----------
4096 2
SQL> select sum(bytes) from dba_segments where segment_name = 'T1';
SUM(BYTES)
----------
33554432
SQL> alter table audadm.t1 move;
Table altered.
SQL> analyze table audadm.t1 compute statistics;
Table analyzed.
SQL> select blocks,empty_blocks,num_rows from dba_tables where table_name='T1';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
1520 527 1000000
SQL> select blocks, extents from dba_segments where segment_name='T1';
BLOCKS EXTENTS
---------- ----------
2048 1
SQL> select sum(bytes) from dba_segments where segment_name = 'T1';
SUM(BYTES)
----------
16777216
SQL>
Thursday, August 25, 2011
Table Reorganization
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> create table t
2 as
3 select * from all_objects;
Table created.
SQL> insert into t select * from t;
75458 rows created.
SQL> insert into t select * from t;
150916 rows created.
SQL> commit;
Commit complete.
SQL> create index idx_t on t(OBJECT_ID);
Index created.
Index is valid:
SQL> select index_name,index_type,status
2 from user_indexes
3 where index_name='IDX_T';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IDX_T NORMAL VALID
SQL> set linesize 200;
SQL> set autotrace traceonly explain;
SQL> select * from t where object_id=9;
Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 632 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 4 | 632 | 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T | 4 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=9)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> set autotrace off;
SQL> analyze table t compute statistics;
Table analyzed.
Check the HWM:
SQL> select blocks, extents from dba_segments where segment_name='T';
BLOCKS EXTENTS
---------- ----------
4352 49
SQL> select blocks,empty_blocks,num_rows from dba_tables where table_name='T';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
4292 59 301832
SQL> delete from t where mod(OBJECT_ID,2)=0;
150920 rows deleted.
Issue the move command to lower high water mark:
SQL> alter table t move;
Table altered.
Index will become unusable, need to rebuild:
SQL>
SQL> select index_name,index_type,status
2 from user_indexes
3 where index_name='IDX_T';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IDX_T NORMAL UNUSABLE
SQL> set autotrace traceonly explain;
SQL> select * from t where object_id=9;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 400 | 1167 (1)| 00:00:15 |
|* 1 | TABLE ACCESS FULL| T | 4 | 400 | 1167 (1)| 00:00:15 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=9)
SQL> set autotrace off;
High water mark gets lowered:
SQL> select blocks, extents from dba_segments where segment_name='T';
BLOCKS EXTENTS
---------- ----------
2176 32
SQL> select blocks,empty_blocks,num_rows from dba_tables where table_name='T';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
4292 59 301832
Rebuild index:
SQL> alter index idx_t rebuild;
Index altered.
SQL> set autotrace traceonly explain;
SQL> select * from t where object_id=9;
Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 400 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 4 | 400 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T | 4 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=9)
SQL> set autotrace off;
Aside, when trying to shrink table, it complains that tablespace segment type is wrong.
Looks like it will work if segment type is AUTO.
SQL> alter table t enable row movement ;
Table altered.
SQL> alter table t shrink space cascade;
alter table t shrink space cascade
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type
SQL> alter table t disable row movement ;
Table altered.
SQL>
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> create table t
2 as
3 select * from all_objects;
Table created.
SQL> insert into t select * from t;
75458 rows created.
SQL> insert into t select * from t;
150916 rows created.
SQL> commit;
Commit complete.
SQL> create index idx_t on t(OBJECT_ID);
Index created.
Index is valid:
SQL> select index_name,index_type,status
2 from user_indexes
3 where index_name='IDX_T';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IDX_T NORMAL VALID
SQL> set linesize 200;
SQL> set autotrace traceonly explain;
SQL> select * from t where object_id=9;
Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 632 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 4 | 632 | 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T | 4 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=9)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> set autotrace off;
SQL> analyze table t compute statistics;
Table analyzed.
Check the HWM:
SQL> select blocks, extents from dba_segments where segment_name='T';
BLOCKS EXTENTS
---------- ----------
4352 49
SQL> select blocks,empty_blocks,num_rows from dba_tables where table_name='T';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
4292 59 301832
SQL> delete from t where mod(OBJECT_ID,2)=0;
150920 rows deleted.
Issue the move command to lower high water mark:
SQL> alter table t move;
Table altered.
Index will become unusable, need to rebuild:
SQL>
SQL> select index_name,index_type,status
2 from user_indexes
3 where index_name='IDX_T';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IDX_T NORMAL UNUSABLE
SQL> set autotrace traceonly explain;
SQL> select * from t where object_id=9;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 400 | 1167 (1)| 00:00:15 |
|* 1 | TABLE ACCESS FULL| T | 4 | 400 | 1167 (1)| 00:00:15 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=9)
SQL> set autotrace off;
High water mark gets lowered:
SQL> select blocks, extents from dba_segments where segment_name='T';
BLOCKS EXTENTS
---------- ----------
2176 32
SQL> select blocks,empty_blocks,num_rows from dba_tables where table_name='T';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
4292 59 301832
Rebuild index:
SQL> alter index idx_t rebuild;
Index altered.
SQL> set autotrace traceonly explain;
SQL> select * from t where object_id=9;
Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 400 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 4 | 400 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T | 4 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=9)
SQL> set autotrace off;
Aside, when trying to shrink table, it complains that tablespace segment type is wrong.
Looks like it will work if segment type is AUTO.
SQL> alter table t enable row movement ;
Table altered.
SQL> alter table t shrink space cascade;
alter table t shrink space cascade
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type
SQL> alter table t disable row movement ;
Table altered.
SQL>
Monday, August 22, 2011
Installing Oracle 9.2.0.4 on linux 64-bit problems
While installing the oracle binaries for linux 64-bit, 9.2.0.4, the installer hangs at about 17%.
It hangs on the file: naeet.o
I followed another source from user CharlesC.
---
1. Make sure Java libraries are up to date..
2. Do not start installation from the 9204 base installation- Oracle Universal Installer has problem
2.1 Install first the OUI "only" from the 9206 patchset (p3948480_9206_Linux-x86-64.zip)
using the Oracle_Home youve just decided
2.2 In ../Disk1/install/oraparam.ini edit the Linux value under [Certified Versions]
Linux=redhat-2.1AS,redhat-2.1,redhat-3,redhat-4,redhat-5,UnitedLinux-1.0,SuSE-9,SuSE-
* value must include redhat-5 inorder to suppress the error message for OS version incompat.
2.3 After completion of OUI install, edit again ../product/9204/oui/oraparam.ini as instructed
in step 2.2
2.4 Now install the 9204 base using the OUI from 9206. Run ../product/9204/oui/runInstaller BUT
this time, select the "products.jar" from 9204 base installation set.
(ex: /u01/app/oracle/Disk1/stage/products.jar)
2.5 From here u should be able to install the SOFTWARE ONLY option. DO NOT create a database yet.
---
Another problem I saw relates to launching x-windows.
Initializing Java Virtual Machine from /tmp/OraInstall2011-08-16_02-41-34PM/jre/bin/java. Please wait...
Exception in thread "main" java.lang.UnsatisfiedLinkError: /tmp/OraInstall2011-08-16_02-41-34PM/jre/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory
Solve it be installing following packages:
libX11.i686
libXext.i686
libXt.i686
xorg-x11-deprecated-libs-6.8.1-12.i386.rpm
libXtst.i686
It hangs on the file: naeet.o
I followed another source from user CharlesC.
---
1. Make sure Java libraries are up to date..
2. Do not start installation from the 9204 base installation- Oracle Universal Installer has problem
2.1 Install first the OUI "only" from the 9206 patchset (p3948480_9206_Linux-x86-64.zip)
using the Oracle_Home youve just decided
2.2 In ../Disk1/install/oraparam.ini edit the Linux value under [Certified Versions]
Linux=redhat-2.1AS,redhat-2.1,redhat-3,redhat-4,redhat-5,UnitedLinux-1.0,SuSE-9,SuSE-
* value must include redhat-5 inorder to suppress the error message for OS version incompat.
2.3 After completion of OUI install, edit again ../product/9204/oui/oraparam.ini as instructed
in step 2.2
2.4 Now install the 9204 base using the OUI from 9206. Run ../product/9204/oui/runInstaller BUT
this time, select the "products.jar" from 9204 base installation set.
(ex: /u01/app/oracle/Disk1/stage/products.jar)
2.5 From here u should be able to install the SOFTWARE ONLY option. DO NOT create a database yet.
---
Another problem I saw relates to launching x-windows.
Initializing Java Virtual Machine from /tmp/OraInstall2011-08-16_02-41-34PM/jre/bin/java. Please wait...
Exception in thread "main" java.lang.UnsatisfiedLinkError: /tmp/OraInstall2011-08-16_02-41-34PM/jre/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory
Solve it be installing following packages:
libX11.i686
libXext.i686
libXt.i686
xorg-x11-deprecated-libs-6.8.1-12.i386.rpm
libXtst.i686
Subscribe to:
Posts (Atom)