Please Answer my Poll

Another year is nearly over. For many of us, this means an annual appraisal and, if we are very lucky, details of the training we will receive next year.

I decided to look and see what is available. I found a company called Firebrand and looked at their OCA 11g course.

This gave me the idea for the poll to the right of this post. It will run until I have had 100 replies then I will publish the results.

Now have a go at the three simple questions below. They were tested on Oracle 11.2.0.2.7. I made them up myself so I cannot guarantee that they will come up in an exam. The answers and explanations are all within this post. If they prove popular, I will publish some more with the answers at a different URL:
 
Question 1:

Which of the statements below creates a view which allows data to be selected but not updated?
 
(a)
create or replace view my_view
as select * from my_table
where last_analyzed > '01-JAN-2012'
with check option
/
 
(b)
create or replace view my_view
as select * from my_table
where last_analyzed > '01-JAN-2012'
with read only
/
 
(c)
create or replace view my_view
as select * from my_table
where last_analyzed > '01-JAN-2012'
no update
/
 
(d)
None of the above.
 
Answer to question 1:
 
Option (a) is a valid SQL statement but it is incorrect as it does not stop you updating values via the view:

SQL> create or replace view my_view
  2  as select * from my_table
  3  where last_analyzed > '01-JAN-2012'
  4  with check option
  5  /
 
View created.
 
SQL> update my_view set owner = 'ANDREW'
  2  /
 
238 rows updated.
 
SQL>

I have already covered with check option here:

Option (b) is correct. If you create a view with read only and try to use it to update the underlying data, you get an ORA-42399:

SQL> create or replace view my_view
  2  as select * from my_table
  3  where last_analyzed > '01-JAN-2012'
  4  with read only
  5  /
 
View created.
 
SQL> update my_view set owner = 'ANDREW'
  2  /
update my_view set owner = 'ANDREW'
                   *
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a
read-only view
 
SQL>

Option (c) is not a valid SQL statement:

SQL> create or replace view my_view
  2  as select * from my_table
  3  where last_analyzed > '01-JAN-2012'
  4  no update
  5  /
no update
*
ERROR at line 4:
ORA-00933: SQL command not properly ended
 
SQL>

Question 2:
 
Choose the only 2 system privileges from the list below:
 
(a) CREATE TABLE
(b) DROP TABLE
(c) CREATE INDEX
(d) MANAGE TABLESPACE
(e) ASSIGN USER
 
Answer to question 2:
 
The correct options are (a) and (d). The others are not valid:
 
SQL> grant create table to andrew
  2  /
 
Grant succeeded.
 
SQL> grant drop table to andrew
  2  /
grant drop table to andrew
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege
 
SQL> grant create index to andrew
  2  /
grant create index to andrew
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege
 
SQL> grant manage tablespace to andrew
  2  /
 
Grant succeeded.
 
SQL> grant assign user to andrew
  2  /
grant assign user to andrew
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege
 
SQL>

Question 3:
 
You want to create a tablespace using Oracle Managed Files but you get the following error:

SQL> create tablespace andrew;
create tablespace andrew
                       *
ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause
 
SQL>

What should you do?

(a)
Make the tablespace locally managed using the following syntax:
create tablespace andrew extent management local;

(b)
Specify a file size like this:
create tablespace andrew datafile size 25m;
 
(c)
Download and apply the appropriate patch as this is a well known Oracle 11 bug.

(d)
Use the ALTER SYSTEM command to set the db_create_file_dest parameter and try again.
 
Answer to Question 3:
 
Option (a) results in the same error:

  1* create tablespace andrew extent management local
SQL> /
create tablespace andrew extent management local
                                               *
ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause

Option (b) gives an ORA-02236:

  1* create tablespace andrew datafile size 25m
SQL> /
create tablespace andrew datafile size 25m
                                  *
ERROR at line 1:
ORA-02236: invalid file name

I haven’t checked option (c) carefully but I made it up myself and I am not aware of such a bug.
 
Option (d) is correct as you can see below:
 
  1  alter system set db_create_file_dest
  2*  = '/export/home/oracle/andrew'
SQL> /
 
System altered.
 
SQL> create tablespace andrew;
 
Tablespace created.
 
SQL>