Ocp1z0-047:alter table set unused after a variety of situation processing

Source: Internet
Author: User
Tags create index

For the use of ALTER TABLE setunused, check the official documentation:

alter_table::=

column_clauses::=

Drop_column_clause:: =

SET Unused Clause

Specify SET unused to mark one or more columns asunused. Specifying this clause does is not actually remove the target columns Fromeach row in the table. The It does not restore the disk space used Bythese columns. Therefore, the response time are faster than when you execute THEDROP clause.

can view all tables with columns marked unused in the data dictionary views user_unused_col_tabs, Dba_unused_col_tabs, and All_unused_col_tabs.

Unused columns are treated as if they were dropped, even though the data theircolumn in the table rows. After the A column has been marked unused, you have no access to that column. A SELECT * Query won't retrieve data from Unusedcolumns. In addition, the names and types of columns marked unused, won't be displayed during a DESCRIBE, and your can add to the Table A new column with the same name as a unusedcolumn.

For the above questions, first do the experiment, look at the test results, a verification:

1. Create a table under the Gyj User:

Gyj@ocm> CREATE TABLE emp as select Employee_id,first_name,manager_idfrom hr.employees;

Table created.

2. Create a common synonym E

gyj@ocm> create public synonym E for emp;

Synonym created.

Gyj@ocm> select * from E;

employee_id first_name manager_id

----------- -------------------- ----------

198 Donald 124

199 Douglas 124

Omit results ...

3. Create a private synonym M

gyj@ocm> create synonym M foremp;

Synonym created.

Gyj@ocm> select * from M;

employee_id first_name manager_id

----------- -------------------- ----------

198 Donald 124

199 Douglas 124

Omit results ...

4. Create a conditional check constraint on the field manager_id column

Gyj@ocm> ALTER TABLE EMP Add (Constraint C_emp_manageridcheck (manager_id>=100));

Table altered.

Gyj@ocm> Select constraint_name from user_constraints wheretable_name= ' EMP ';

Constraint_name

------------------------------

C_emp_managerid

gyj@ocm> INSERT INTO EMP values ($, ' Guoyjoe ', 99);

INSERT into EMP values (' Guoyjoe ', 99)

*

ERROR at line 1:

Ora-02290:check constraint (GYJ. C_emp_managerid) violated

An error is a description that does not meet the constraint, indicating that the constraint is working

5. Create a View v_emp

Gyj@ocm> CREATE VIEW v_emp as SELECT * from EMP;

View created.

Gyj@ocm> select * from V_emp;

employee_id first_name manager_id

----------- -------------------- ----------

198 Donald 124

199 Douglas 124

Omit results ...

6, create an index on the column manager_id

Gyj@ocm> CREATE INDEX idx_id on EMP (manager_id);

Index created.

Gyj@ocm> Select Index_name from user_indexes where table_name= ' EMP ';

Index_name

------------------------------

idx_id

OK, let's start the ALTER TABLE set unused operation by pressing the above instructions:

Gyj@ocm> altertable EMP Set unused (MANAGER_ID);

Table altered.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Synonyms can still be used in the data dictionary without rebuilding, so answer a is wrong.

Gyj@ocm> Select object_name from user_objects where object_type= ' synonym ' and object_name in (' M ', ' E ');

object_name

--------------------------------------------------

M

Gyj@ocm> select * from E;

employee_id first_name manager_id

----------- -------------------- ----------

198 Donald 124

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.