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

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