Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle set and remove columns unavailable     - How to configure FirewallD in RHEL / CentOS 7 and Fedora in (Linux)

- How do I delete a NEEDS RECOVERY rollback state of undo tablespace (Database)

- Linux iostat command example explanation (Linux)

- C # asynchronous delegates (Programming)

- Linux system on how to use rsync to synchronize data (Server)

- Ubuntu will be written in a command file, executable file, source command (Linux)

- To install the Ubuntu Touch emulator on Ubuntu (Linux)

- Linux System Getting Started Learning: On Linux how to convert text files to PDF (Linux)

- Use the TC flow control test under Linux (Linux)

- Linux System Getting Started Learning: Disable HTTP forwarding wget in (Linux)

- Connect to the Oracle Database Help class (Database)

- Linux, see picture not resolve the problem (Linux)

- MongoDB upgrade from 2.4.9 to 2.6.0 and PHP record of mongo extension upgrade from 1.4.5 to 1.5.1 (Database)

- Linux Bash share tips for getting started (Linux)

- Delegate in C # (Programming)

- Java heap (Heap) and stack difference (Programming)

- shell script error dirname: invalid option - b (Database)

- MongoDB3.0.6 wiredtiger MMAPv1 (Database)

- JDK comes with tools jinfo (Linux)

- How to install Linux Go Language (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:      
 
- Installation Sublime Text 3 (Build 3065) text editor in Ubuntu (Linux)
- How to use Monit to deploy a server to monitor the system (Server)
- Perl loop (Programming)
- Configure the Linux kernel and use iptables to do port mapping (Linux)
- Android graphic introduction NDK installation and simple jni demo implementation (Linux)
- Linux file time Comments ctime mtime atime (Linux)
- Install Rubinius testing Ubuntu 14.04 native threads (Linux)
- Ubuntu Linux Change the PATH (Linux)
- CentOS 6.4 of cron scheduled task configuration (Linux)
- PHP 7.0 Upgrade Notes (Linux)
- Installation and Configuration ISC DHCP server on Debian Linux (Server)
- C ++ Fundamentals study notes (Programming)
- CentOS install pip (Linux)
- Java concurrent programming combat (using synchronized synchronization method) (Programming)
- Ubuntu Locale configuration problem solving Can not set LC_CTYPE (Linux)
- Django Signals from practice to source code analysis (Programming)
- ORA-00020: No more process state objects available (Database)
- Hardware Firewall Basics (Linux)
- About Linux iptables firewall interview questions and answers (Linux)
- Linux for enterprises to build a firewall (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.