Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle view object space usage show_space     - C ++ precision performance test function (Programming)

- Developing a Web server yourself (Server)

- RMAN parameters of ARCHIVELOG DELETION (Database)

- C ++ multithreading and critical resource instance (Programming)

- Using Ruby to build a simple HTTP service and sass environment (Server)

- Use Vagrant up a local development environment tutorials (Server)

- Linux scheduling summary (Linux)

- CentOS 6.5 start ActiveMQ being given to solve (Server)

- Linux, set and view environment variables (Linux)

- Oracle bug Ora-04043 (Database)

- Enable Intel Rapid Start in GNU / Linux (Linux)

- ORA-04031 Error Resolution (Database)

- How to Set Free SSH password on CentOS / RHEL (Linux)

- Binary search -Java achieve (Programming)

- High-performance JavaScript reflows and repaints (Programming)

- The array of C language (Programming)

- Compare Several MySQL environmental issues (Database)

- Open source backup software installed on Ubuntu Systemback 1.6.201 (Linux)

- ORA-30926 and MERGE tables empty the temporary occupation problem (Database)

- The several technical presentation Raid under Linux (Linux)

 
         
  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:      
 
- Java by Spy Memcached to cache data (Programming)
- How to Install Focuswriter 1.4.5 (Linux)
- Ubuntu 15.10 How to install TeamViewer 11 (Linux)
- Oracle 11g DataGuard service to start automatically (Database)
- Android 5.1 OTA package compilation error (Programming)
- Let VMware ESXi virtual switch support VLAN (Linux)
- Using IntelliJ IDEA 13 integrated development environment to build Android (Linux)
- How do I cancel (almost) any operations in Git, (Linux)
- See Linux kernel, CPU, memory, and various versions of the command module and means (Linux)
- Sudo and Root Account in Ubuntu related (Linux)
- C language macro definition #define Usage (Programming)
- Change all child files and subdirectories in the owner permissions Ubuntu (Linux)
- Flask deploy applications using Nginx on Ubuntu (Server)
- Linux Programming memory mapping (Programming)
- How to Install Xombrero 1.6.4 (minimalist Web browser) on Ubuntu and Archlinux (Linux)
- Ubuntu install Lighttpd + Mono support .net (Linux)
- Ubuntu dual-card system configuration method (Server)
- Use Android Studio and Gradle packaged multi-version APK (Programming)
- Git uses a standard process (Linux)
- Linux --- process tracking (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.