--get hao_show_space from http://space.itpub.net/15415488/viewspace-609531
SYS@XE> insert into t1 select * from dba_objects;
14582 rows created.
SYS@XE> commit;
Commit complete.
SYS@XE> exec hao_show_space('T1');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "SYS.T1"
##################################################
UNUSED BLOCKS...........................64
UNUSED Bytes............................524288
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................0
FS2 Bytes ..............................0
--------------------------------------------------
FS3 Blocks..............................1
FS3 Bytes ..............................8192
--------------------------------------------------
FS4 Blocks..............................17
FS4 Bytes ..............................139264
--------------------------------------------------
FULL BLOCKS.............................288
FULL_BYTES .............................2359296
##################################################
Data Blocks(under HWM)..................306
All Blocks (under HWM)..................320
Total Blocks............................384
Total Bytes.............................3145728
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................521
Last Used Block.........................64
PL/SQL procedure successfully completed.
SYS@XE> delete from t1 where owner='SYS';
6648 rows deleted.
SYS@XE> commit;
Commit complete.
SYS@XE> exec hao_show_space('T1');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "SYS.T1"
##################################################
UNUSED BLOCKS...........................64
UNUSED Bytes............................524288
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................15
FS2 Bytes ..............................122880
--------------------------------------------------
FS3 Blocks..............................61
FS3 Bytes ..............................499712
--------------------------------------------------
FS4 Blocks..............................62
FS4 Bytes ..............................507904
--------------------------------------------------
FULL BLOCKS.............................168
FULL_BYTES .............................1376256
##################################################
Data Blocks(under HWM)..................306
All Blocks (under HWM)..................320
Total Blocks............................384
Total Bytes.............................3145728
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................521
Last Used Block.........................64
PL/SQL procedure successfully completed.
SYS@XE> select bytes from dba_segments where segment_name='T1' and owner='SYS';
BYTES
----------
3145728
SYS@XE> select count(distinct substr(rowid,1,15)) from t1;
COUNT(DISTINCTSUBSTR(ROWID,1,15))
---------------------------------
268
SYS@XE> alter table t1 move ;
Table altered.
SYS@XE> select count(distinct substr(rowid,1,15)) from t1;
COUNT(DISTINCTSUBSTR(ROWID,1,15))
---------------------------------
208
SYS@XE> exec hao_show_space('T1');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "SYS.T1"
##################################################
UNUSED BLOCKS...........................36
UNUSED Bytes............................294912
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................0
FS2 Bytes ..............................0
--------------------------------------------------
FS3 Blocks..............................0
FS3 Bytes ..............................0
--------------------------------------------------
FS4 Blocks..............................0
FS4 Bytes ..............................0
--------------------------------------------------
FULL BLOCKS.............................208
FULL_BYTES .............................1703936
##################################################
Data Blocks(under HWM)..................208
All Blocks (under HWM)..................220
Total Blocks............................256
Total Bytes.............................2097152
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................777
Last Used Block.........................92
PL/SQL procedure successfully completed.
SYS@XE> set autotrace on
SYS@XE> select count(*) from t1;
COUNT(*)
----------
15418
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 62 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 14146 | 62 (2)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
271 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@XE> delete from t1;
15418 rows deleted.
Execution Plan
----------------------------------------------------------
Plan hash value: 775918519
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 61 (0)| 00:00:01 |
| 1 | DELETE | T1 | | | |
| 2 | TABLE ACCESS FULL| T1 | 1 | 61 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
45 recursive calls
17171 db block gets
291 consistent gets
0 physical reads
5533796 redo size
939 bytes sent via SQL*Net to client
929 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
15418 rows processed
SYS@XE> commit;
Commit complete.
SYS@XE> select count(*) from t1;
COUNT(*)
----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 61 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 1 | 61 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
212 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@XE> exec hao_show_space('T1');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "SYS.T1"
##################################################
UNUSED BLOCKS...........................36
UNUSED Bytes............................294912
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................0
FS2 Bytes ..............................0
--------------------------------------------------
FS3 Blocks..............................0
FS3 Bytes ..............................0
--------------------------------------------------
FS4 Blocks..............................208
FS4 Bytes ..............................1703936
--------------------------------------------------
FULL BLOCKS.............................0
FULL_BYTES .............................0
##################################################
Data Blocks(under HWM)..................208
All Blocks (under HWM)..................220
Total Blocks............................256
Total Bytes.............................2097152
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................777
Last Used Block.........................92
PL/SQL procedure successfully completed.
SYS@XE> alter table t1 move ;
Table altered.
SYS@XE> exec hao_show_space('T1');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "SYS.T1"
##################################################
UNUSED BLOCKS...........................5
UNUSED Bytes............................40960
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................0
FS2 Bytes ..............................0
--------------------------------------------------
FS3 Blocks..............................0
FS3 Bytes ..............................0
--------------------------------------------------
FS4 Blocks..............................0
FS4 Bytes ..............................0
--------------------------------------------------
FULL BLOCKS.............................0
FULL_BYTES .............................0
##################################################
Data Blocks(under HWM)..................0
All Blocks (under HWM)..................3
Total Blocks............................8
Total Bytes.............................65536
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................257
Last Used Block.........................3
PL/SQL procedure successfully completed.
SYS@XE> select count(*) from t1;
COUNT(*)
----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 1 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Popular Posts
-
I have one PT env using storage sync for database sync. Thought need to re-register DB after DB refresh, so unregister and register again....
-
configured "CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK; " when RMAN> delete noprompt archivelog u...
-
[symptoms] orarac2poc:ORCL2:/home/oracle> . ./oms_agent_env orarac2poc:ORCL2:/home/oracle> $AGENT_HOME/bin/emctl start agent Oracl...
-
backup piece The physical file format used to store RMAN backup sets backup set A backup of one or more datafiles, control files, SPFILEs...
-
Oracle collects Active Session History (ASH) statistics (mostly wait statistics for different events) for all active sessions every second f...
-
This installation extended to my home hour, since both Postgresql and Zabbix are new to me. Followed few articles without much difficulty.... -
after rounds of recovery testing, I believer there are tons of obsolete files in FRA. There are some stubborn datafile copy can't be de...
-
1. The application error message orarac2poc:ORCL2:/u01/app/oracle/oms12c/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/logs&g... -
ALTER DATABASE DATAFILE OFFLINE DROP Below is my understanding and testing. This command will not drop the datafile (from both OS a...
-
SQL> select * from dba_data_files where tablespace_name='TESTBS'; FILE_NAME ...