Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle set and remove columns unavailable     - RHEL6.4 x86_64 build SVN service (Server)

- Java threads in the life cycle (Programming)

- SYN attack hacker attack and defense of the basic principles and prevention technology (Linux)

- sed command (Linux)

- Android use canvas board painting (Programming)

- Top 10: HTML5, JavaScript 3D game engine and framework (Linux)

- Zabbix Agent for Linux Installation and Configuration (Server)

- Installation Docker FAQ on Ubuntu (Linux)

- Ubuntu 14.04, 13.10 install OpenCV 2.4.9 (Linux)

- Swift used in the application to add a local push at the specified time (Programming)

- Web cache basics: terminology, HTTP headers, and caching policies (Server)

- Zabbix using Omsa monitor hardware information of Dell server (Server)

- MongoDB query timeout exception SocketTimeoutException (Database)

- CentOS 6.5 dual card configuration, one of the external network, a local area network connection (Linux)

- Java objects are taking up much space (Programming)

- MySQL Error Code Complete (Database)

- PHP 5.3 New Features Detail (Linux)

- Git Getting Started tutorial (Linux)

- ARM platform compiler installation Golang (Linux)

- How to statistical data of various size Redis (Database)

 
         
  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:      
 
- How dependent on Fedora, CentOS, RHEL check RPM packages (Linux)
- Installation Eduspec university management systems 17 Linux Mint (Server)
- Zabbix monitors the status of TCP connections (Server)
- jdbc Oracle database connection string writing pluggable (Database)
- After the first remote installation GlassFish Web to remotely access their back office management system error solution appears (Server)
- A simple shell script for monitoring in Linux (Linux)
- VMware virtual machine to install CentOS 6.2 (Linux)
- Setting Linux desktop environment, achieve HiDPI display support (Linux)
- Linux screen commonly commands (Linux)
- Tab set to four spaces in Vim (Linux)
- Linux virtual machines to solve end MySQL database can not remote access (Database)
- Linux how to prohibit the use of Ping command (Linux)
- Oracle to use full-text indexing (Database)
- To compile and install Clang 3.5.0 in Linux CentOS (Linux)
- Oracle 11g em start newspaper site's security certificate has a solution to the problem (Database)
- Recycle Bin function realization in Linux (Linux)
- In Spring AOP example explanation (Programming)
- Getting Started with Linux: Learn how to install and access CentOS 7 Remote Desktop on a VPS (Server)
- Management Linux Container with Docker in Ubuntu (Linux)
- Advanced Linux security settings (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.