- Who Is A DBA (Database Administrator)
A Database Administrator is a person or a group of person who are responsible for managing all the activities related to database system. This job requires a high level of expertise by a person or group of person. There are very rare chances that only a single person can manage all the database system activities so companies always have a group of people who take care of database system.
- Role, Duties and Responsibilities of database Administrator ( DBA)
Installing and Configuration of database: DBA is responsible for installing the database software. He configure the software of database and then upgrades it if needed. There are many database software like oracle, Microsoft SQL and MySQL in the industry so DBA decides how the installing and configuring of these database software will take place.
- Deciding the hardware device
Depending upon the cost, performance and efficiency of the hardware, it is DBA who have the duty of deciding which hardware devise will suit the company requirement. It is hardware that is an interface between end users and database so it needed to be of best quality.
2. Managing Data Integrity
Data integrity should be managed accurately because it protects the data from unauthorized use. DBA manages relationship between the data to maintain data consistency.
3. Decides Data Recovery and Back up method
If any company is having a big database, then it is likely to happen that database may fail at any instance. It is require that a DBA takes backup of entire database in regular time span. DBA has to decide that how much data should be backed up and how frequently the back should be taken. Also the recovery of data base is done by DBA if they have lost the database.
4. Tuning Database Performance
Database performance plays an important role for any business. If user is not able to fetch data speedily then it may loss company business. So by tuning an modifying sql commands a DBA can improves the performance of database.
5. Capacity Issues
All the databases have their limits of storing data in it and the physical memory also has some limitations. DBA has to decide the limit and capacity of database and all the issues related to it.
6. Database design
The logical design of the database is designed by the DBA. Also a DBA is responsible for physical design, external model design, and integrity control.
7. Database accessibility
DBA writes subschema to decide the accessibility of database. He decides the users of the database and also which data is to be used by which user. No user has to power to access the entire database without the permission of DBA.
8. Monitoring performance
If database is working properly then it doesn’t mean that there is no task for the DBA. Yes f course, he has to monitor the performance of the database. A DBA monitors the CPU and memory usage.
9. Decides content of the database
A database system has many kind of content information in it. DBA decides fields, types of fields, and range of values of the content in the database system. One can say that DBA decides the structure of database files.
10. Provides help and support to user
If any user needs help at any time then it is the duty of DBA to help him. Complete support is given to the users who are new to database by the DBA.
11. Database implementation
Database has to be implemented before anyone can start using it. So DBA implements the database system. DBA has to supervise the database loading at the time of its implementation.
CREATE USER
The CREATE USER statement allows to create a new database user which you can use to log in to the Oracle database.
The basic syntax of the CREATE USER statement is as follows:
CREATEUSER username IDENTIFIEDBYpassword [DEFAULTTABLESPACEtablespace] [QUOTA {size | UNLIMITED} ONtablespace] [PROFILE profile]
[PASSWORDEXPIRE] [ACCOUNT {LOCK | UNLOCK}];
- CREATE USER examples
The CREATE USER statement.
- Using Oracle CREATE USER statement to create a new local user example
This example uses the CREATE USER statement to create a new local user named john with the password abcd1234:
CREATEUSER john IDENTIFIEDBY abcd1234;
Oracle issues the following output indicating that user john has been created successfully User JOHN created.
To find a list of users with the OPEN status, you query the information from the dba_users:
SELECT
username,
default_tablespace,
profile,
authentication_type
FROM
dba_users
WHERE
account_status = ‘OPEN’;
As you can see from the output, user john has a default tablespace as USERS, profile as DEFAULT, and log in to the database using a PASSWORD.
Let’s use the john account to log in the database.
Launch the SQL*Plus program and enter the following information:
Enter user-name: john@pdborcl
Enter password:<john_password>
Oracle issued the following error:
ERROR: ORA-01045:
User JOHN lacks CREATE SESSION privilege; logon denied
To enable the user john to log in, you need to grant the CREATE SESSION system privilege to the user john by using the following statement:
GRANTCREATESESSIONTO john;
Now, the user john should be able to log in the database.
Enter user-name: john@pdborcl Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release12.2.0.1.0 – 64bit Production
- Using Oracle CREATE USER statement to create a new local user with password expired example
First, use the CREATE USER statement to create a new user jane:
CREATEUSER jane IDENTIFIEDBY abcd1234 PASSWORDEXPIRE;
Second, verify if the user has been created successfully:
SELECT
username, default_tablespace, profile, authentication_type
FROM
dba_users
WHERE
account_status = ‘OPEN’;
Third, grant the CREATE SESSION privilege to the user Jane so that you can use this user to log in the Oracle database.
GRANTCREATESESSIONTO jane;
Finally, use the user jane to log in to the database via the SQL*plus program:
SQL> connect jane@orclpdb/abcd1234 ERROR:
ORA-28001: the password has expired
Oracle requested for changing the password for jane, you must provide the new password and confirm it before you can log in:
Changing password for jane
New password:<new_password>
Retype new password:<new_password>
Password changed
Connected.