Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle set and remove columns unavailable     - Oracle common internal event tracking number (Database)

- Ubuntu users install the video driver Nvidia Driver 334.21 (Linux)

- Usage Linux device tree (Programming)

- MongoDB simple replication configuration (Database)

- Comparison of one-time transaction and CTE insert data (Database)

- Install mono offline on CentOS (Server)

- Ubuntu 14.04 / 13.10 users how to install Ubuntu Touch core applications (Linux)

- Iptables application layer plug (Linux)

- Based on OpenSSL for HTTPS service configuration (Server)

- Linux server alarms using Java (Server)

- Alternative methods of intrusion bundled executable file new thinking (Linux)

- Linux disk partition, format, mount the directory (Linux)

- Linux performance monitoring and common commands Introduction (Linux)

- Oracle JDK installation under Ubuntu Linux (Linux)

- Linux kernel compilation, the configuration of the motor drive (Programming)

- Linux Network Security Tips Share (Linux)

- ARM Linux system call (Linux)

- Hadoop 2.5 Pseudo distribution installation (Server)

- Ubuntu 14.04 LTS next upgrade gcc to gcc-4.9, gcc-5 version (Linux)

- Json data with double backslashes to a single backslash Json data processing (Programming)

 
         
  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:      
 
- Getting the Linux shell variable test (Programming)
- Let your PHP 7 faster (GCC PGO) (Linux)
- Ubuntu disable graphics card (Linux)
- Ubuntu Locale configuration problem solving Can not set LC_CTYPE (Linux)
- Ubuntu 14.04 LTS installed Hadoop 1.2.1 (distributed cluster mode) (Server)
- ACL permissions Linux command (Linux)
- Creating and extracting archives 11 tar command examples in Linux (Linux)
- Flask installation environment (Linux)
- By creating a personal technology blog Detailed GitHub (Linux)
- CentOS 7 install Hadoop-cdh-2.5 on Mesos (Server)
- Gentoo: startx problem appears Failed to load module (Linux)
- Linux system boot process ARM platforms (Linux)
- Zabbix system email alert Python script (Server)
- PXE install CentOS 6.4 (Linux)
- GRUB2 boot Ubuntu Manual (Linux)
- Change all child files and subdirectories in the owner permissions Ubuntu (Linux)
- Ubuntu 15.10 How to install TeamViewer 11 (Linux)
- CentOS 6.4 under PXE + Kickstart unattended operating system installation (Programming)
- Linux NIC configuration (Linux)
- Installation of Python2.7.10 under CentOS 6.4 (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.