User Management

USER

Creating the user

SQL>select tablespace_name,file_name from dba_data_files;

SQL>select tablespace_name,file_name from dba_temp_files;

SQL> Create user USER01 identified by USER01 default tablespace USER01_ts   temporary tablespace temp                            

quota unlimited on USER01_ts

quota 100m on USER01_demo_ts

password expire

account lock;

Assigning the quota to the user on other tablespace

SQL> alter user USER01 quota 10m on tbs1;

How To unlock the user account

SQL> alter user USER01 account unlock;

How To force user to change the Password

SQL> alter user USER01 password expire;

How to Grant privileges to users

SQL>grant connect, resource to user02 identified by user02;

SQL> grant create session, create table, create sequence to USER01;

SQL> conn USER01/USER01;

Views

SQL> select * from session_privs;

SQL> select * from role_sys_privs;

 

How to Assign sysdba and sysoper privilages to user

1. Creating Password file for instance

$orapwd file=orapw<sid>  password=<password> force=y ignorecase=y

Eg:

 $orapwd file=orapwdevlp password=password force=y ignorecase=y

Note:

If u don’t create password file, then you will get the error  as Below

ERROR at line 1:

ORA-01994: GRANT failed: password file missing or disabled

2. Grant using command

Sql> grant sysdba to user01;

Sql>grant sysoper to user01;

Now connect that user, as below

$sqlplus user01/user01  as sysdba

Sql>show user

       SYS

$sqlplus user01/user01 as sysoper

Sql>show user

       PUBLIC

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB  SYSOP  SYSAS

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

SYS                                    TRUE   TRUE    FALSE

USER01                                            TRUE   FALSE  FALSE

*** By default password is case sensitive, to disable it set the following parameter to false            

Sec_case_sensitive_logon=false;

SQL>alter system set sec_case_sensitive_logon=true scope=both;

Profiles

Creating profile

Sql> create profile profile1 limit

                Failed_login_attempts 3   (no of attempts)

                Password_lock_time 1       (no of days)   1/24  (for 1 hour)

                Password_life_time 7         (no of days)

                Sessions_per_user 5            (no of total sessions)

                Idle_time 1                           (in minutes)

                Connect_time 600;             (10 hours)

Assigning profile

Sql>  create user emp1 idenfied by emp1 profile profile1;

Sql> alter user emp1 profile profile1;

SQL>select * from dba_profiles where profile=’profile1’;

SQL>select username, profile from dba_users where profile=’PROFILE1’;

SQL>alter profile profile1 limit password_lock_time 2;

SQL>select * from dba_profiles where profile=’PROFILE1’;

·         To enforce kernel/resource parameters the following parameter must be set

Sql> alter system set resource_limit=true scope=both;

How to Apply password restriction in profile:

SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql;

SQL>alter profile default limit password_verify_function null;

SQL> alter profile prof1 limit Password_verify_function verify_function;

SQL> alter profile prof1 limit Password_verify_function null;

Important views for Managing Users:

v$process

v$session

v$sqlarea

v$sqltext

v$lock

v$session_wait

v$sess_io

Data Pump Gets Better

Today I will Discuss some new Features of Datapump in 11G

One of the big issues with Data Pump was that the dumpfile couldn't be compressed while getting created. That was something easily done in the older Export/Import utility. In Oracle Database 11g, Data Pump can compress the dumpfiles while creating them. This is done via a parameter COMPRESSION in the expdp command line. The parameter has three options:

METDATA_ONLY - only the metadata is compressed; the data is left as it is (available in Oracle Database 10.2 as well).

DATA_ONLY - only the data is compressed; the metadata is left alone.

ALL - both the metadata and data are compressed.

NONE - this is the default; no compression is performed.

Here is how you compress the export of the table UNITS_FACT:

$ expdp global/global directory=dump_dir tables=units_fact dumpfile=units_fact_comp.dmp compression=all

When importing the dumpfile, the import does not have to decompress the file first; it decompresses as it reads it, making the process really fast.

The two other enhancements in Data Pump are:

Encryption: the dumpfile can be encrypted while getting created. The encryption uses the same technology as TDE (Transparent Data Encryption) and uses the wallet to store the master key. This encryption occurs on the entire dumpfile, not just on the encrypted columns as it was in the case of Oracle Database 10g.

Masking: when you import data from production to QA, you may want to make sure sensitive data such as social security number, etc. are obfuscated (altered in such a way that they are not identifiable). Data Pump in Oracle Database 11g enables you do that by creating a masking function and then using that during import.

General Tips to Prepare for an Oracle DBA Job Interview

Preparing for an interview beforehand is preferred as it helps to avoid stressful situations during the process. In this article, I will provide several tips on how one should prepare for a DBA job interview.

It's important to pay close attention to body language, tone of voice and overall behavior. 

Interview mark that... Does the person seem happy or stressed out or depressed? .... It may be a good indicator of how you’d feel working there.

In the Technical Interview ...

Technical questions are a must. These help the employer in judging the skills of the candidates.

Answer technical questions as briefly and accurately as possible

These questions will be based on the nature of the job applied for. There are certain essential factors that should be kept in mind while answering them. For instance, the way the answers are presented, the confidence level, the candidate’s knowledge about the industry and the latest technical knowhow are some of the important factors in this regard.

Think about exactly what it is they’re asking for.  You need to show that you can stay focused.

Try to avoid filler words like uh, um and other verbal pauses. The key is to take a little time before you answer to organize your thoughts and have a clear outline of what you’ll say before you speak.

Try to avoid talking with your hands.

They may also ask Questions like...

Tell me about a difficult problem you had to solve successfully.

Tell me about a recent failure / difficulty working with another person.

Be prepared for these kind of Questions. I will describe this in my next post

You must ask Questions in the end of the Interview

-             Ask about the Responsibilities – is the job more production or development?

-         Why is the position open? Again, this ’ll give you a feel for the place. Did they fire the last person, are they expanding? If given an intentionally vague answer, you might want to keep looking.

-          Expectations – how is performance evaluated? What is the management style like?

-          Training opportunities, especially for learning new versions of the database software.

“I would like more information on…what are the key responsibility areas of my job profile ”

U can ask, what’s the environment u are working.

U can ask about the company about the profile .

Ask About, what will be your profile/role that u are going to take up.

What work they will expect from you.

Will you be working in team or not.

How many DB’s etc , etc

These kinds of questions are important for two reasons:

The first is to get an idea of what it’s like to work there, and the second is to get a feel for his/her personality.

You may find that the work environment isn’t what you want, or that you wouldn’t like working with that person.

Towards the end of the interview, be sure to express your gratitude and interest in the position, as well as summarize any strong characteristics/experience that pertain to the job.

What not to do..

·         You could be the most knowledgeable DBA in the universe, but if your personality is a poor fit, you’re not going to get the job. It’s also important to think about customer service questions and answer those properly. 

            Most DBA positions involve interfacing with end-users of some kind, and the ability to explain difficult concepts and have a good sense of customer support is critical.

·         Don’t fight with the interviewer, or try to make excuses. Be respectful and polite at all times.

·         Don't Act superior. Even if the job is for less pay or less responsibility, it’s never okay to be superior.

·         Do not Dominate the interview with grandiose stories of how awesome of a DBA you are.

An interview at its core is a conversation, so there should be some give and take

Post Interview Things..

It’s important to do a follow-up thank you correspondence, mostly by email .

In it you want to address the interviewers by name and thank them again for their time.

There you have it, all of my knowledge on how to survive the DBA job interview.

Please keep in mind, these are my opinions and experience, your mileage may vary.

Good luck …!

See Also
Daily Activities of an Oracle DBA  *HOT*

What are the Daily a DBA do ?