Daily Archives: August 21, 2009

Oracle, Oracle 10g, oracle 9i, SQL

Mid-Level DBA Interview Questions

Leave a comment

With well over a decade of Oracle experience from version 6 to 11g, I have had my share of interviewing prospective DBA employees in a range from Junior to Senior DBAs. Needless to say in this field, technical competence is the key to any new hire and early on I made the blunder of not asking the write questions during the interview. I am sure every senior person or manager has made this mistake and had to deal with either training the person from scratch or letting them go. Either of these options is not desirable in our fast past business world. So, I decided to write a couple articles which cover what I believe a Junior, Mid, and Senior Oracle DBA should know to fill their prospective positions.

In this article we will cover what a Mid Level DBA should know to be successful. If you have any comments or questions, please email me at larry.catt@relidb.com.

Questions:

1. Define the directory where Oracle developed scripts reside for the maintenance of an Oracle database and how would you call this directory from within the Oracle database?

2. Define the difference between, USER_, ALL_, and DBA_ data dictionary views and define the one column missing from USER_ which exists in both ALL_ and DBA_ views?

3. How would you find the number of invalid objects in an Oracle database and how would you resolve them?

4. Where are the network related files located on a standard Oracle installation and name two of the standard files you would find there?

5. Define the Oracle utility provided for backup and recovery of an Oracle database and the two options for storing information about backed up databases?

6. Define the Oracle utility used to manage an Oracle listener?

7. Define two UNIX layer system variables required for an Oracle database to operate properly?

8. Define the Oracle utility normally used to install CPU patches and where is this utility stored?

9. Name the Oracle package which allows you to schedule routine jobs within the Oracle database, just like CRON in UNIX or Scheduler in Windows?

10. Name the utility used to format perfstat report trace files?

Answers:

1. Define the directory where Oracle developed scripts reside for the maintenance of an Oracle database and how would you call this directory from within the Oracle database?

– The directory where Oracle stores the maintenance scripts is $ORACLE_HOME/rdbms/admin.
– The question mark represents the ORACLE_HOME within the database, thus you would call this directory with ‘?/rdbms/admin’. Another acceptable answer is ‘@?/rdbms/admin’.

2. Define the difference between, USER_, ALL_, and DBA_ data dictionary views and define the one column missing from USER_ which exists in both ALL_ and DBA_ views?

– USER_ – These views only contain definition of objects which you own.
– ALL_ – These views contain definitions of all objects which you have access to.
– DBA_ – These views contain definitions of all objects in the database, even if you do not have access to them.

– The USER_ views do not contain a column named OWNER, by definition of the view you are the owner.

3. How would you find the number of invalid objects in an Oracle database and how would you resolve them?

– By using the data dictionary view DBA_OBJECTS with the statement “select count(*) from dba_objects where status=’INVALID’;”
– You would use the Oracle script, “UTLRP.SQL” located in directory $ORACLE_HOME/rdbms/admin.

4. Where are the network related files located on a standard Oracle installation and name two of the standard files you would find there?

– In a standard Oracle installation the network files are located under “$ORACLE_HOME/network/admin”.
– Files located in this directory are: “LISTENER.ORA”, “TNSNAMES.ORA”, or “SQLNET.ORA”.

5. Define the Oracle utility provided for backup and recovery of an Oracle database and the two options for storing information about backed up databases?

– RMAN (Recovery Manager)
– The two methods for storing information about backed up database are: the controlfile or Recovery Catalog.

6. Define the Oracle utility used to manage an Oracle listener?

– LSNRCTL (LISTENER CONTROL)

7. Define two UNIX layer system variables required for an Oracle database to operate properly?

– $ORACLE_HOME – defines the directory where the Oracle binaries are installed.
– $ORACLE_SID – defines the instance name used to access a database.

8. Define the Oracle utility normally used to install CPU patches and where is this utility stored?

– “opatch” is the utility which is used to install CPU patches.
– The “opatch” utility is located in the directory “$ORACLE_HOME/OPatch”. NOTE: “opatch” is not a valid answer in a standard Oracle installation the directory has a capital O and P.

9. Name the Oracle package which allows you to schedule routine jobs within the Oracle database, just like CRON in UNIX or Scheduler in Windows?

– DBMS_JOBS – This answer is valid for all versions of Oracle.
– DBMS_SCHEDULER – This answer is only valid for Oracle 10g or higher.

10. Name the utility used to format perfstat report trace files?

– TKPROF

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, RMAN

Recreate Oracle RDBMS User accounts

Leave a comment

With the advent of Oracle 10g Import and Export data pump utility, the need to recreate a user account from scratch has been greatly reduced. However, there are still situation when you may need the SQL to recreate a specific user within another Oracle environment or just desire to see the creation statements. The script below will generate the statements to create the user defined in the CURR variable from the current Oracle RDBMS environment.

1. First we will create a user named LJCATT with various roles and privileges.

SQL> create user LJCATT identified by LJCATT;
User created.
SQL> alter user LJCATT default tablespace USERS;
User altered.
SQL> alter user LJCATT temporary tablespace TEMP;
User altered.
SQL> alter user LJCATT profile DEFAULT;
User altered.
SQL> grant DBA to LJCATT;
Grant succeeded.
SQL> grant CREATE TABLE to LJCATT with admin option;
Grant succeeded.
SQL> grant UNLIMITED TABLESPACE to LJCATT;
Grant succeeded.
SQL> grant EXECUTE on SYS.UTL_FILE to LJCATT with grant option;
Grant succeeded.
SQL>

2. Now we will execute the following PL/SQL block to regenerate the statements to recreate the user from the data dictionary.

set serveroutput on
spool ./create_user_account.sql

declare

curr varchar2(30):= ‘LJCATT’;
v_ext varchar2(3);

begin

— Create original user definition
for user in(select * from dba_users where username = curr)
loop
dbms_output.put_line(‘create user ‘||user.username||’ identified by
‘||user.username||’;’);
dbms_output.put_line(‘alter user ‘||user.username||’ default tablespace
‘||user.default_tablespace||’;’);
dbms_output.put_line(‘alter user ‘||user.username||’ temporary tablespace
‘||user.temporary_tablespace||’;’);
dbms_output.put_line(‘alter user ‘||user.username||’ profile
‘||user.profile||’;’);
if user.account_status<>‘OPEN’
then
dbms_output.put_line(‘alter user ‘||user.username||’ account lock;’);
end if;
end loop;

— Grant all roles defined for the user.

for role in(select * from dba_role_privs where grantee=curr)
loop
if role.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’ with
admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’;’);
end if;
end loop;

— Grant all system privileges for the user.

for sys_priv in(select * from dba_sys_privs where grantee=curr)
loop
if sys_priv.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to ‘||sys_priv.grantee||’
with admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to
‘||sys_priv.grantee||’;’);
end if;
end loop;

— Grant all object privileges for the user.

for tab_priv in(select * from dba_tab_privs where grantee=curr)
loop
if tab_priv.grantable = ‘YES’
then
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’ with
grant option;’);
else
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’;’);
end if;
end loop;
end;
/

spool off

Sample Output results:

47 48 49 50 51 52 53 54 55 56 57 58 59 60 61
create user LJCATT identified by LJCATT;
alter user LJCATT default tablespace USERS;
alter user LJCATT temporary tablespace TEMP;
alter user LJCATT profile DEFAULT;
grant DBA to LJCATT;
grant CREATE TABLE to LJCATT with admin option;
grant UNLIMITED TABLESPACE to LJCATT;
grant EXECUTE on SYS.UTL_FILE to LJCATT with grant option;

PL/SQL procedure successfully completed.

SQL>

3. The above SQL statements can now be used against any other Oracle database to recreate the user account with the same rights which exist in this database. NOTE: There exist several dependencies in the above statements, such as: tablespaces, profiles, roles, and object privileges. For example, we need to take care that a default tablespace defined in the alter user statement truly exist in the destination database.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com