Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle view object space usage show_space     - HTTPS and SSH and use the difference between the way: Git User's Manual (Linux)

- Storm how to assign tasks and load balancing (Programming)

- The difference between statement and preparedStatement of the jdbc (Database)

- Sniffer Linux Environment (Linux)

- pureftpd basis: Install, configure, implement, anonymous logon (Linux)

- Linux file system structure Introduction (Linux)

- Present Situation and Development Trend of firewall products (Linux)

- Linux, Apache Web site security settings (Linux)

- An example of troubleshooting of embedded Linux OpenWRT (Linux)

- Manual cleaning Oracle audit records (Database)

- Use scripts easily install the latest Linux kernel in Ubuntu (Linux)

- Use in Linux ipmitool tool (Linux)

- When a software RAID data recovery and reconstruction of how failure (Linux)

- Use Markdown editor for document work under Linux (Linux)

- Du and df show disk space usage inconsistent Causes and Treatment (Linux)

- Keepalived achieve high availability Nginx Reverse Proxy (Server)

- Using shell users or virtual users to login to pureftpd (Linux)

- Linux source code to install Apache and solutions to common errors (Server)

- How to Start a Linux application running in the background using the terminal mode (Linux)

- Python exception summary (Programming)

 
         
  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 data structures - the linear form of the single-chain applications (Programming)
- BusyBox build root file system (Linux)
- Chkconfig command Detailed service is added and shut down the system in two ways to start service under Linux (Linux)
- Zabbix monitors the status of TCP connections (Server)
- 14 useful example Linux Sort command (Linux)
- How Oracle implements random reads from specific combinations (Database)
- Python pickle module for simple use notes (Programming)
- Android annotation support (Support Annotations) (Programming)
- Timing task Crontab under Linux system (Linux)
- Intel Graphics Installer 1.0.3 released, support for Ubuntu (Linux)
- 8 Docker knowledge you may not know (Server)
- systemctl Command Complete Guide (Linux)
- Kickstart automated installation and deployment RHEL 7.0 (Linux)
- How to update the Linux kernel to improve system performance (Linux)
- The most concise explanation of JavaScript closures (Programming)
- CentOS6 5 Configure SSH password Free (Linux)
- The multiplexed signal driving IO (Programming)
- How to Install lightweight Budgie desktop on Ubuntu 14.04 (v8) (Linux)
- Ubuntu 14.04 Boot Repair (Linux)
- ARM runtime environment built from scratch using QEMU emulator (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.