Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle view object space usage show_space     - Ubuntu 14.04 compile and install Apache (Server)

- floating IP in OpenStack neutron (Server)

- MySQL migration tool used in the production environment (Database)

- CentOS 5.10 installed Oracle 11G R2 (Database)

- Linux and Windows Security Topics (Linux)

- CentOS network configuration 7, and set the host name and IP-bound problems (Linux)

- CentOS / Linux kernel upgrade (Linux)

- Shared directory settings between Linux and Linux (Linux)

- Java look and feel mode (Facade mode) (Programming)

- Make Windows boot disk to install USB in Ubuntu Linux (Linux)

- TeamCity continuous integration in the Linux installation (Linux)

- After installing minimize RHEL / CentOS 7 we need to do (Linux)

- Oracle 12c In-Memory Study (Database)

- Hadoop 2.0 Detailed Configuration Tutorial (Server)

- Linux Quick Install MongoDB (Database)

- RabbitMQ tutorial examples: RabbitMQ installation under Windows (Linux)

- rpm package specify the installation path (Linux)

- Give your photos Instagram style filters plus program in ubuntu (Linux)

- Laravel configuration PhpStorm + Xdebug + Chrome settings Debug Environment (Server)

- Shell script to crawl through AWR SQL Report Problems (Database)

 
         
  Oracle view object space usage show_space
     
  Add Date : 2017-08-31      
         
       
         
  Oracle view object space usage show_space, a Oracle database objects for viewing space usage.

The following demonstrates how to use:

- Source tools

CREATE OR REPLACE PROCEDURE show_space (p_segname IN VARCHAR2,
p_owner IN VARCHAR2 DEFAULT USER,
p_type IN VARCHAR2 DEFAULT 'TABLE',
p_partition IN VARCHAR2 DEFAULT NULL) AS
  l_free_blks NUMBER;
  l_total_blocks NUMBER;
  l_total_bytes NUMBER;
  l_unused_blocks NUMBER;
  l_unused_bytes NUMBER;
  l_lastusedextfileid NUMBER;
  l_lastusedextblockid NUMBER;
  l_last_used_block NUMBER;
  l_segment_space_mgmt VARCHAR2 (255);
  l_unformatted_blocks NUMBER;
  l_unformatted_bytes NUMBER;
  l_fs1_blocks NUMBER;
  l_fs1_bytes NUMBER;
  l_fs2_blocks NUMBER;
  l_fs2_bytes NUMBER;
  l_fs3_blocks NUMBER;
  l_fs3_bytes NUMBER;
  l_fs4_blocks NUMBER;
  l_fs4_bytes NUMBER;
  l_full_blocks NUMBER;
  l_full_bytes NUMBER;

  PROCEDURE p (p_label IN VARCHAR2,
              p_num IN NUMBER) IS
  BEGIN
    dbms_output.put_line (rpad (p_label, 40, '.') ||
                        to_char (p_num, '999,999,999,999'));
  END;
BEGIN
  EXECUTE IMMEDIATE 'select ts.segment_space_management from dba_segments seg, dba_tablespaces ts where seg.segment_name =: p_segname and (: p_partition is null or seg.partition_name =: p_partition) and seg.owner =: p_owner and seg.tablespace_name = ts.tablespace_name'
    INTO l_segment_space_mgmt
    USING p_segname, p_partition, p_partition, p_owner;

  - Exception
  - When too_many_rows
  - Then
  - Dbms_output.put_line ( 'This must be a partitioned table, use p_partition =>');
  - Return;
  - End;

  IF l_segment_space_mgmt = 'AUTO' THEN
    dbms_space.space_usage (p_owner, p_segname, p_type, l_unformatted_blocks,
    l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
    l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
    l_fs4_blocks, l_fs4_bytes, l_full_blocks,
    l_full_bytes, p_partition);

    p ( 'Unformatted Blocks', l_unformatted_blocks);
    p ( 'FS1 Blocks (0-25)', l_fs1_blocks);
    p ( 'FS2 Blocks (25-50)', l_fs2_blocks);
    p ( 'FS3 Blocks (50-75)', l_fs3_blocks);
    p ( 'FS4 Blocks (75-100)', l_fs4_blocks);
    p ( 'Full Blocks', l_full_blocks);
  ELSE
    dbms_space.free_blocks (segment_owner => p_owner, segment_name => p_segname,
    segment_type => p_type, freelist_group_id => 0,
    free_blks => l_free_blks);
  END IF;

  dbms_space.unused_space (segment_owner => p_owner, segment_name => p_segname,
  segment_type => p_type, partition_name => p_partition,
  total_blocks => l_total_blocks,
  total_bytes => l_total_bytes,
  unused_blocks => l_unused_blocks,
  unused_bytes => l_unused_bytes,
  last_used_extent_file_id => l_lastusedextfileid,
  last_used_extent_block_id => l_lastusedextblockid,
  last_used_block => l_last_used_block);

  p ( 'Total Blocks', l_total_blocks);
  p ( 'Total Bytes', l_total_bytes);
  p ( 'Total MBytes', trunc (l_total_bytes / 1024/1024));
  p ( 'Unused Blocks', l_unused_blocks);
  p ( 'Unused Bytes', l_unused_bytes);
  p ( 'Last Used Ext FileId', l_lastusedextfileid);
  p ( 'Last Used Ext BlockId', l_lastusedextblockid);
  p ( 'Last Used Block', l_last_used_block);
END; - Usage Demo

create table test_space
AS
select * from dba_tables;
/ SYS @ orcl> exec show_space ( 'TEST_SPACE'); Total Blocks ........................... 112
Total Bytes ........................... 917,504
Total MBytes ........................... 0
Unused Blocks ........................... 3
Unused Bytes ........................... 24,576
Last Used Ext FileId .................. .. 1
Last Used Ext BlockId ................... 115,608
Last Used Block ......................... 5

PL / SQL procedure successfully completed.

SYS @ orcl> delete from test_space; 1
3044 has been deleted rows.

SYS @ orcl> commit; 1
Submit completed.

SYS @ orcl> exec show_space ( 'TEST_SPACE'); 1
Total Blocks ........................... 112
Total Bytes ........................... 917,504
Total MBytes ........................... 0
Unused Blocks ........................... 3
Unused Bytes ........................... 24,576
Last Used Ext FileId .................. .. 1
Last Used Ext BlockId ................... 115,608
Last Used Block ......................... 5

PL / SQL procedure successfully completed.

SYS @ orcl> truncate table test_space; Table truncated.

SYS @ orcl> exec show_space ( 'TEST_SPACE');
Total Blocks ........................... 8
Total Bytes ........................... 65,536
Total MBytes ........................... 0
Unused Blocks ........................... 7
Unused Bytes ........................... 57,344
Last Used Ext FileId .................. .. 1
Last Used Ext BlockId ................... 101,872
Last Used Block ......................... 1

PL / SQL procedure successfully completed.

drop table test_space;
     
         
       
         
  More:      
 
- Android start automatically and add and delete a desktop shortcut (Programming)
- Oracle 11g manually create a database (Database)
- Zabbix configuration external network mail alarm (Server)
- Spark On YARN cluster installation deployment (Server)
- Getting the Linux shell flow control statements (Programming)
- CentOS Linux Optimization and real production environment (Linux)
- Spring next ActiveMQ combat (Programming)
- Will Laravel become the most successful PHP framework? (Programming)
- Python virtual environment: Virtualenv (Linux)
- CentOS yum configuration under local sources (Linux)
- Use innobackupex full realization of MySQL hot backup (Database)
- Java MVC CRUD examples (Programming)
- CentOS 6.5 can not connect to the network under VMware (Linux)
- Timeout control related to Python threads and a simple application (Programming)
- CentOS 6.4 under PXE + Kickstart unattended operating system installation (Programming)
- PostgreSQL query lock and kill the process (Database)
- Ubuntu 14.10 How to install office suite Calligra Suite 2.8.7 (Linux)
- How to use Java to read OpenOffice document (Programming)
- Java Foundation - implicit conversion vs cast (Programming)
- Linux environment installation of rvm and ruby (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.