Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle set and remove columns unavailable     - MySQL Installation Troubleshooting (Database)

- Oracle 12c of the auto-increment Identity Columns (Database)

- CentOS7 installed VMware 10 (Linux)

- Debian users to install FFmpeg 2.2.2 (Linux)

- VNC connection VMware vSphere ESXi 5.5 (Linux)

- Java learning problems encountered (Programming)

- Install Ubuntu text editor KKEdit 0.2.10 (Linux)

- Security Knowledge: redirection command application security (Linux)

- The basic principles for the protection of a good linux server security (Linux)

- CentOS 6.4 (64bit) install Python 2.7.5 (Linux)

- Use libcurl library download url image (Programming)

- Automatic batch resolve dependencies problem locally installed rpm package (Linux)

- Service manager OpenBSD on rccl (Server)

- Logging information through the web GUI (LogAnalyzer) (Server)

- CentOS yum install LAMP (Server)

- Linux ls command (Linux)

- Ubuntu install video conversion tool Selene (Linux)

- using the ssh command to check the socket / Network Connections (Linux)

- Configuring Eclipse Note Templates (Linux)

- apt-get install openstack pkg Troubleshooting (Linux)

 
         
  Oracle set and remove columns unavailable
     
  Add Date : 2017-08-31      
         
       
         
  Oracle set and remove columns unavailable

1, not with what is listed?

That table is set to 1 or ALTER TABLE ... SET UNUSED statement can no longer be used as a column procedure multiple columns.

2, the use of scenarios?
If you are concerned about the length of time it could take to drop column data from
all of the rows in a large table, you can use the ALTER TABLE ... SET UNUSED statement.

If you are worried may remove a lot of time from a large table, you can use the ALTER TABLE ... SET UNUSED statement.

If you have the need to remove some columns in a read and write frequently a large table,
If you execute ALTER TABLE ABC DROP (COLUMN) directly in the busy time;
You may receive the ORA-01562 - failed to extend rollback segment number string,
This is because in this process you delete a column might might consume the entire rollback table space caused this error.

3, the use of reason (the principles and advantages)?
3.1 setting is unavailable column
This statement marks one or more columns as unused, but does not actually remove
the target column data or restore the disk space occupied by these columns.
a column that is marked as unused is not displayed in queries or data dictionary
views, and its name is removed so that a new column can reuse that name.

This statement may be one or more columns identified as unavailable, but it is not actually removed the column data or recycling these columns footprint.
An unavailable column does not appear in the query or data dictionary views that the column name be removed so that it can be reused new column column name.

In most cases, constraints, indexes, and statistics defined on the column are also removed.
In most cases, the constraint on the columns, indexes, and statistical information is also removed.

The exception is that any internal indexes for LOB columns that are marked unused are not removed.
The exception is identified as unavailable internal index LOB column will not be removed.

3.2 Delete Unavailable columns
ALTER TABLE ... DROP UNUSED COLUMNS statement only for the column is not available for formal delete is marked as unavailable column (physically remove columns while recovering occupied space).

In the ALTER TABLE statement that follows, the optional clause CHECKPOINT is specified.
This clause causes a checkpoint to be applied after processing the specified number of
rows, in this case 250. Checkpointing cuts down on the amount of undo logs
accumulated during the drop column operation to avoid a potential exhaustion of
undo space.

In the next one in ALTER TABLE statement, the optional conditions CHECKPOINT.
This condition will trigger a checkpoint at the time the process reaches a specified number of lines, here 250. checkpoints reduced the number accumulated in the delete column operation undo logs, thus avoiding potential undo space consumed.

ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;

4, the use restrictions?
1) You can not delete belonging to SYS table columns
2)

5, grammatical structure?
ALTER TABLE ... SET UNUSED (C1, C2 ..)
ALTER TABLE ... DROP UNUSED COLUMNS

E.g:
ALTER TABLE hr.admin_emp SET UNUSED (hiredate, mgr);
ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS;

5, data dictionary
USER_UNUSED_COL_TABS
ALL_UNUSED_COL_TABS
DBA_UNUSED_COL_TABS

SELECT * FROM DBA_UNUSED_COL_TABS;
OWNER TABLE_NAME COUNT
--------------------------- ----------------------- ---- -----
HR ADMIN_EMP 2-count column represents the number of columns is unavailable

6, use cases?

SCOTT @ orcl> create table tmp_all_objects
  2 AS
  3 SELECT object_id, object_name
  4 from dba_objects
  5;

Table has been created. SYS @ orcl> exec show_space ( 'TMP_ALL_OBJECTS', 'SCOTT');
Unformatted Blocks .................... 0
FS1 Blocks (0-25) .................... 0
FS2 Blocks (25-50) .................... 0
FS3 Blocks (50-75) .................... 0
FS4 Blocks (75-100) .................... 0
Full Blocks .................... 352
Total Blocks ........................... 384
Total Bytes ........................... 3,145,728
Total MBytes ........................... 3
Unused Blocks ........................... 18
Unused Bytes ........................... 147,456
Last Used Ext FileId .................... 4
Last Used Ext BlockId ................... 14,592
Last Used Block ......................... 110

PL / SQL procedure successfully completed.

 


SYS @ orcl> DESC DBA_UNUSED_COL_TABS
 Whether the name is empty? Type
 ---------------------------------------- ---------- -------------------------
 OWNER NOT NULL VARCHAR2 (30)
 TABLE_NAME NOT NULL VARCHAR2 (30)
 COUNT NUMBER

SYS @ orcl> SELECT * FROM DBA_UNUSED_COL_TABS;

Unselected row

SCOTT @ orcl> ALTER TABLE TMP_ALL_OBJECTS SET UNUSED (OBJECT_NAME);

Table has been changed.

SYS @ orcl> SELECT * FROM DBA_UNUSED_COL_TABS;

OWNER TABLE_NAME COUNT
------------------------------ -------------------- ---------- ----------
SCOTT TMP_ALL_OBJECTS 1

SYS @ orcl> exec show_space ( 'TMP_ALL_OBJECTS', 'SCOTT');
Unformatted Blocks .................... 0
FS1 Blocks (0-25) .................... 0
FS2 Blocks (25-50) .................... 0
FS3 Blocks (50-75) .................... 0
FS4 Blocks (75-100) .................... 0
Full Blocks .................... 352
Total Blocks ........................... 384
Total Bytes ........................... 3,145,728
Total MBytes ........................... 3
Unused Blocks ........................... 18
Unused Bytes ........................... 147,456
Last Used Ext FileId .................... 4
Last Used Ext BlockId ................... 14,592
Last Used Block ......................... 110

PL / SQL procedure successfully completed.

- Delete Unavailable columns
SCOTT @ orcl> ALTER TABLE TMP_ALL_OBJECTS DROP UNUSED COLUMNS CHECKPOINT 250;

Table has been changed.

SYS @ orcl> SELECT * FROM DBA_UNUSED_COL_TABS;

Unselected row


SYS @ orcl> exec show_space ( 'TMP_ALL_OBJECTS', 'SCOTT');
Unformatted Blocks .................... 0
FS1 Blocks (0-25) .................... 0
FS2 Blocks (25-50) .................... 1
FS3 Blocks (50-75) .................... 350
FS4 Blocks (75-100) .................... 1
Full Blocks .................... 0
Total Blocks ........................... 384
Total Bytes ........................... 3,145,728
Total MBytes ........................... 3
Unused Blocks ........................... 18
Unused Bytes ........................... 147,456
Last Used Ext FileId .................... 4
Last Used Ext BlockId ................... 14,592
Last Used Block ......................... 110

PL / SQL procedure successfully completed.

--move operations, reduce fragmentation
SCOTT @ orcl> ALTER TABLE TMP_ALL_OBJECTS MOVE;

Table has been changed.


SYS @ orcl> exec show_space ( 'TMP_ALL_OBJECTS', 'SCOTT');
Unformatted Blocks .................... 0
FS1 Blocks (0-25) .................... 0
FS2 Blocks (25-50) .................... 0
FS3 Blocks (50-75) .................... 0
FS4 Blocks (75-100) .................... 0
Full Blocks .................... 113
Total Blocks ........................... 128
Total Bytes ........................... 1,048,576
Total MBytes ........................... 1
Unused Blocks ........................... 5
Unused Bytes ........................... 40,960
Last Used Ext FileId .................... 4
Last Used Ext BlockId ................... 14,808
Last Used Block ......................... 3

PL / SQL procedure successfully completed.

- You can see the total number of blocks drops

- Remove the test table
SCOTT @ orcl> drop table TMP_ALL_OBJECTS;

Table has been deleted. 1
7, on the recovery column is not available (or less taken from the network)
Just now, someone asked me how to fix the field is set to UNUSED, I thought a moment, the following methods can be restored (before the following steps to make a backup), inexperienced DBA Do not try.

1. Create a test table TTTA

SQL> CREATE TABLE TTTA (A INTEGER, B INTEGER, C VARCHAR2 (10), D INTEGER);
Table has been created.
SQL> INSERT INTO TTTA VALUES (1,2, '3', 4);
It has created a row.
SQL> INSERT INTO TTTA VALUES (2,3, '4', 5);
It has created a row.
SQL> COMMIT;
Submit completed.
ALTER TABLE TTTA SET UNUSED COLUMN C;
2, the following recovery

SQL> SELECT OBJ # FROM OBJ $ WHERE NAME = 'TTTA';
      OBJ #
----------
    32067
SELECT COL #, INTCOL #, NAME FROM COL $ WHERE OBJ # = 32067;
      COL # INTCOL # NAME
---------- ---------- ------------------------------
        1 1 A
        2 2 B
        0 3 SYS_C00003_08031720: 09: 55 $ is a field UNUSED
        3 4 D
SQL> SELECT COLS FROM TAB $ WHERE OBJ # = 32067;
      COLS
----------
        3 ------ 3 into a number of fields


SQL> UPDATE COL $ SET COL # = INTCOL # WHERE OBJ # = 32067;
Updated 4 rows.
SQL> UPDATE TAB $ SET COLS = COLS + 1 WHERE OBJ # = 32067;
Updated 1 line.
UPDATE COL $ SET NAME = 'C' WHERE OBJ # = 32067 AND COL # = 3;
UPDATE COL $ SET PROPERTY = 0 WHERE OBJ # = 32067;
SQL> COMMIT; 3, restart the database

SQL> SELECT * FROM SCOTT.TTTA;
        A B C D
---------- ---------- ---------- ----------
        1234
        2345 complete restoration
     
         
       
         
  More:      
 
- Syncthing: Private Security Synchronization tool to synchronize files between computers / folder (Linux)
- Nine tips to protect the security of Linux desktop (Linux)
- Linux package manager - yum (Linux)
- Gnu Linux - Ubuntu System Clean-term consolidation (Linux)
- Linux vi command list (Linux)
- Install Firefox 28 on Ubuntu, Linux Mint (Linux)
- How to make Linux a non-root user uses less than 1024 ports (Linux)
- Overall Physical Migration of Oracle Database with (Database)
- Samba public folder permissions (Server)
- Recover accidentally deleted Nginx logs (Server)
- Java transient keyword (Programming)
- CentOS7 install JDK (Linux)
- CentOS7 virtual machine starts appear Permission denied (Linux)
- Linux installation beautify early experience (Linux)
- AngularJS notes --- Scope and controller (Programming)
- Linux installed and tested the deployment of Kafka distributed cluster (Server)
- Linux System Getting Started Learning: the Linux Wireshark interface dead solve (Linux)
- CentOS 6 adds disk quota limit (Linux)
- Btrfs file system creation and their characteristics in Linux (Linux)
- Docker Private Registry Installation Guide at CentOS6.X (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.