Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle view object space usage show_space     - CentOS7 installation performance monitoring system (Server)

- Android View event delivery (Programming)

- Linux system with a firewall to prevent the DOS attack (Linux)

- Varnish achieve page jump (Server)

- Ubuntu Eclipse configuration Gtk + 2.0 libraries (Linux)

- The Linux-based security settings Ipchains Firewall (Linux)

- Five kinds of IO Model under Linux (Programming)

- Java implementation chain store binary tree (Programming)

- How Oracle implements random reads from specific combinations (Database)

- How to manage and use Logical Volume Management LVM in Ubuntu (Linux)

- Sysdig: system troubleshooting tool (Linux)

- RM Environment Database RMAN Backup Strategy Formulation (Database)

- Role Object of registerNatives () method (Programming)

- Ubuntu users to install voice switch instructs the applet (Linux)

- Docker deployment practices in Ubuntu (Server)

- The next key to install Linux bash script PowerShell (Linux)

- Share Practical Tutorial GitHub (Linux)

- Ubuntu study notes and related problem solving (Linux)

- How to implement Linux host Telnet SSH password Free (Server)

- To install MySQL 5.6 binary packages under CentOS 6.4 64bit (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:      
 
- Oracle 11g on Linux system boot from the startup settings (Database)
- An Example of GoldenGate Extract Process Hang Problem Solving (Database)
- File SUID, SGID, Sticky property (Linux)
- Stucts2 values on the page and how the attribute values corresponding to the Action (Programming)
- Introduction to Linux system process monitoring tools (Linux)
- Teach you to diagnose problems with strace (Linux)
- Oracle 11g new features of the collection of multi-column statistics (Database)
- wget command examples (Linux)
- Ubuntu 15.04 and CentOS 7 to deploy .NET, Mono and DNX (Server)
- Expert advice: Do not use the computer security IE browser (Linux)
- Android Studio Installation and Configuration Guide tutorial (Linux)
- Java reflection mechanism explained in detail and Method.invoke explanation (Programming)
- ORA-38856: Unable instance UNNAMED_INSTANCE_2 (redo thread 2) marked enabled (Database)
- CentOS7 installed MySQL (Database)
- Oracle 11g DataGuard service to start automatically (Database)
- vector C ++ sequence containers (Programming)
- Linux cd command Detailed (Linux)
- U disk to install Windows and Ubuntu 15.04 dual system (Programming)
- Linux ls command (Linux)
- Python object-oriented programming (Programming)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.