Subject: Oracle DBA 1. A table is created with this statement: Create table emp_
ID: 3851013 • Letter: S
Question
Subject: Oracle DBA
1. A table is created with this statement:
Create table emp_copy tablespace users as select * from employees;
Which of the following changes would make it impossible to drop the table?
A. alter tablespace users read only;
B. alter tablespace users offline;
C. alter table emp_copy read only;
D. Any of A, B, and C would make it impossible to drop the table
E. None of the A, B, or C would make it impossible to drop the table
2. You create a tablespace with this command:
Create tablespace g1_tabs datafile ‘u01/oradata/g1_tabs_01.dbf’
Size 1000m autoextend on next 100m maxsize 2000m;
If the critical alert for tablespace usage is set to 90 percent, when will the alert be raised?
A. When the space used by segments that have not been dropped reaches 900MB
B. When the space used by segments that have not been dropped and segments in the recycle bin reaches 900MB
C. When the space used by segments that have not been dropped reaches 1,800MB
D. When the space used by segments that have not been dropped and segments in the recycle bin reaches 1,800MB
3. Which is the effect of running the following command?
alter database add logfile
(‘/u01/oradata/redo04.log’, ‘/u02/oradata/redo05.log’) size 500m;
A. One new group will have two members.
B. Two new groups will have one new member.
C. Two members will be added to the current log file group.
D. The command will fail if the existing log file groups do not consist of members that are 500MB in size.
4. When one is using shared servers, what may happen if more concurrent requests are received than there are shared server processes?
A. Sessions may receive errors.
B. Statements may be queued.
C. No more shared servers may be started automatically.
D. The shared servers may execute several requests in parallel.
5. If a tablespace is made read-only, what can still be done to objects within it?
A. Insertions into a table will work, as long as there is free space within the segment and so no new extents need to be allocated.
B. Operations that are transparent to applications and users (such as creating indexes) can be done in the tablespace, but no DML commands can be executed against tables within it.
C. Objects in the tablespace can be dropped but not created.
D. No SQL commands can be executed against any objects in a read-only tablespace except SELECT statements.
6. What is the effect on established sessions of shutting down the database listener?
A. No impact on any session.
B. Dedicated server sessions will be terminated.
C. Shared server sessions connected through a dispatcher will survive; dedicated server sessions will hang until the listener is restarted.
D. All sessions will be terminated.
Explanation / Answer
Answers:
1. D. Any of A, B, and C would make it impossible to drop the table
option A - the users cannot able to mdify it, not to delete table.
option B, - table will be set to be in offline not to delete the tablespace
option - c - the emp_copy set to read only alone.
to drop the table, use drop table tablename;
so, the option A, B, C will be impossible in oracle.
2. the table size will be extended automatically to 1000m when it reaches to 100m. it if it set to 90% of it maximum size then, its been to 1800m.
Answer C
3. Answer C. Two members will be added to the current log file group.
option A: the statement for one group will have new member is ALTER DATABASE AND LOGFILE '/oracle/dbs/log2b.rdo' '/oracle/dbs/log3b.rdo' TO GROUP 2;
option B: to add new member in two groups, ALTER DATABASE ADD LOGFILE GROUP 4('c: emp ewlog1.log1)SIZE 500M;
4. Answer D . the shared server requests may several requests in parallel.
option A: is false, because the session wont receive errors.
option B: is false, beacuse they will executing in parallel so no queue.
option c: is false, because they will get executed parallely.
5. Answer C.Objects in the tablespace can be dropped but not created.
option A: is false because when the tablespace is in offline, dml commands will work.
option B: is false, becaue the DML statements can execute within it during read only.
option D: is false, because only create or alter object is not possbile during readonly.
6. Answer D: All sessions will be terminated.
option A: it the listener gets shut down, definitely it will affect the session. so it is false.
option B: not only the dedicated session will be teminated. so it is false.
option c: only one listener will get run on a server. so every sessions get shut down. it is also false