CREATE USER AND PRIVILEGES 

Purpose

Use the CREATE USER statement to create and configure a database user, which is an account through which you can log in to the database, and to establish the means by which Oracle Database permits access by the user.

CREATE USER sidney 
    IDENTIFIED BY out_standing1 
    DEFAULT TABLESPACE example 
    QUOTA 10M ON example     
    PASSWORD EXPIRE;

 Privileges

System privileges

System privileges are privileges given to users to allow them to perform certain functions that deal with managing the database and the server . Most of the different types of permissions supported by the database vendors fall under the system privilege category. Let’s go through some examples of system privileges in Oracle and SQL Server.



EXAMPLE



GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO SIDNEY (WITH ADMIN OPTION);



TO CHECK PRIVILEGE



SELECT * FROM USER_SYS_PRIVS;



Object privileges

Object privileges are privileges given to users so that they can perform certain actions upon certain database objects – where database objects are things like tables, stored procedures, indexes, etc. Some examples of object privileges include granting a particular database user the right to DELETE and/or SELECT from a particular table. This is done using the GRANT clause.



GRANT SELECT,UPDATE,DELETE,INSERT ON SCOTT.DEPT TO SIDNEY (WITH GRANT OPTION);



TO CHECK PRIVILEGE



DESC USER_TAB_PRIVS_RECD.



REVOKE statement



Use the REVOKE statement to remove privileges from a specific user or role, or from all users, to perform actions on database objects. You can also use the REVOKE statement to revoke a role from a user, from PUBLIC, or from another role.




REVOKE CREATE TABLE, CREATE VIEW FROM SIDNEY.

REVOKE SELECT, UPDATE, DELETE,INSERT FROM SIDNEY.

Oracle DBA Interview Questions and Answers - Architecture

   Oracle DBA Interview Questions and Answers - Architecture

What is difference between oracle SID and Oracle service name?
Oracle SID is the unique name that uniquely identifies your instance/database where as

the service name is the TNS alias can be same or different as SID.

What are the steps to install oracle on Linux system? List two kernel parameter that effect oracle installation?
Initially set up disks and kernel parameters, then create oracle user and DBA group, and finally run installer to start the installation process. The SHMMAX & SHMMNI two kernel parameter required to set before installation process.

What are bind variables?
With bind variable in SQL, oracle can cache queries in a single time in the SQL cache area. This avoids a hard parse each time, which saves on various locking and latching resource we use to check object existence and so on.

What is the difference between data block/extent/segment?
A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

What is the difference between PGA and UGA?
When you are running dedicated server then process information stored inside the process global area (PGA) and

when you are using shared server then the process information stored inside user global area (UGA).

What is SGA? Define structure of shared pool component of SGA?
The system global area is a group of shared memory area that is dedicated to oracle instance. All oracle process uses the SGA to hold information. The SGA is used to store incoming data and internal control information that is needed by the database. You can control the SGA memory by setting the parameter db_cache_size, shared_pool_size and log_buffer.
Shared pool portion contain three major area: 
Library cache (parse SQL statement, cursor information and execution plan), 
data dictionary cache (contain cache, user account information, privilege user information, segments and extent information, 
data buffer cache for parallel execution message and control structure.


What is the difference between SMON and PMON processes?
SGA-"Shared global area" or "system global area". This is the shared memory that's 
allocated from the O/S at instance startup. It is SHARED. That is, all oracle server 
processes have access to it .SMON (System Monitor) performs recovery after instance failure, monitor temporary segments and extents; clean temp segment, coalesce free space. It is mandatory process of DB and starts by default.

PGA - "program global area" or "private global area". This is private, non-shared program 
memory which only an individual server process has access to PMON (Process Monitor) failed process resources. In shared server architecture monitor and restarts any failed dispatcher or server process. It is mandatory process of DB and starts by default.

What is a system change number (SCN)?  SCN is System Change Number. It is an unique incremental number in the database (as your clock time). The SCN number is incremented every 3 seconds. This number is very useful while recovering the database or instance. All the datafile headers will have the same scn number when the instance is shutdown normally. You can get the current scn number from dbms_flashback.get_system_change_number or if you are using 10g your can query v$database

What is the main purpose of ‘CHECKPOINT’ in oracle database? How do you automatically force the oracle to perform a checkpoint?
 A checkpoint is a database event, which synchronize the database blocks in memory with the datafiles on disk. It has two main purposes: To establish a data consistency and enable faster database Recovery.


The following are the parameter that will be used by DBA to adjust time or interval of how frequently its checkpoint should occur in database.
LOG_CHECKPOINT_TIMEOUT = 3600;  # Every one hour
LOG_CHECKPOINT_INTERVAL = 1000; # number of OS blocks.

What happens when we fire SQL statement in Oracle?
First it will check the syntax and semantics in library cache, after that it will create execution plan. 
If already data is in buffer cache it will directly return to the client. 
If not it will fetch the data from datafiles and write to the database buffer cache after that it will send server and finally server send to the client.

What is the use of large pool, which case you need to set the large pool?
You need to set large pool if you are using: MTS (Multi thread server) and RMAN Backups. Large pool prevents RMAN & MTS from competing with other sub system for the same memory. RMAN uses the large pool for backup & restore when you set the DBWR_IO_SLAVES or BACKUP_TAPE_IO_SLAVES parameters to simulate asynchronous I/O. If neither of these parameters is enabled, then Oracle allocates backup buffers from local process memory rather than shared memory. Then there is no use of large pool.

What does database do during the mounting process?
While mounting the database oracle reads the data from controlfile which is used for verifying physical database files during sanity check. Background processes are started before mounting the database only.

What are logfile states?
“CURRENT” state means that redo records are currently being written to that group. It will be until a log switch occurs. At a time there can be only one redo group current.
If a redo group containing redo’s of a dirty buffer that redo group is said to be ‘ACTIVE’ state. As we know log file keep changes made to the data blocks then data blocks are modified in buffer cache (dirty blocks). These dirty blocks must be written to the disk (RAM to permanent media).
And when a redolog group contains no redo records belonging to a dirty buffer it is in an "INACTIVE" state. These inactive redolog can be overwritten.
One more state ‘UNUSED’ initially when you create new redo log group its log file is empty on that time it is unused. Later it can be any of the above mentioned state.

What is log switch?
The point at which oracle ends writing to one online redo log file and begins writing to another is called a log switch. Sometimes you can force the log switch.
ALTER SYSTEM SWITCH LOGFILE;
How to check Oracle database version?
SQL> Select * from v$version;

Explain Oracle Architecture?

Oracle Instance:

a means to access an Oracle database,always opens one and only one database and consists of memory structures and background process.

Oracle server:

a DBMS that provides an open, comprehensive, integrated approach to information management,Consists of an Instance and a database.

Oracle database:

a collection of data that is treated as a unit,Consists of Datafiles, Control files, Redo log files. (optional param file, passwd file, archived log)

Instance memory Structures:

System Global Area (SGA):

Allocated at instance startup, and is a fundamental component of an Oracle Instance.

SGA Memory structures:

Includes Shared Pool,  Database Buffer Cache, Redo Log Buffer among others.

Shared Pool :

Consists of two key performance-related memory structures Library Cache and  Data Dictionary Cache.

Library Cache:

Stores information about the most recently used SQL and PL/SQL statements and enables the sharing of commonly used statements.

Data Dictionary Cache :

Stores collection of the most recently used definitions in the database Includes db files, tables, indexes, columns etc. Improves perf. During the parse phase, the server process looks at the data dictionary for information to resolve object names and validate access.

Database Buffer Cache:

Stores copies of data blocks that have been retrieved from the datafiles. Everything done here.

Redo Log Buffer :

Records all changes made to the database data blocks, Primary purpose is recovery. Redo entries contain information to reconstruct or redo changes.

User process:

Started at the time a database User requests connection to the Oracle server. requests interaction with the Oracle server, does not interact directly with the Oracle server.

Server process:

Connects to the Oracle Instance and is Started when a user establishes a session.

fulfills calls generated and returns results.

Each server process has its own nonshared PGA when the process is started.

Server Process Parses and run SQL statements issued through the application, Reads necessary data blocks from datafiles on disk into the shared database buffers of the SGA, if the blocks are not already present in the SGA and Return results in such a way that the application can process the information.

In some situations when the application and Oracle Database operate on the same computer, it is possible to combine the user process and corresponding server process into a single process to reduce system overhead.

Program Global Area (PGA):  

Memory area used by a single Oracle server process.

Allocated when the server process is started, deallocated when the process is terminated and used by only one process.

Used to process SQL statements and to hold logon and other session information.

Background processes: 

Started when an Oracle Instance is started.

Background Processes Maintains and enforces relationships between physical and memory structures

There are two types of database processes:

      1.      Mandatory background processes

      2.      Optional background processes

Mandatory background processes:

– DBWn, PMON, CKPT,  LGWR,  SMON

Optional background processes:

– ARCn, LMDn, RECO, CJQ0, LMON, Snnn, Dnnn, Pnnn, LCKn, QMNn

DBWn writes when:

• Checkpoint occurs

• Dirty buffers reach threshold

• There are no free buffers

• Timeout occurs

• RAC ping request is made

• Tablespace OFFLINE

• Tablespace READ ONLY

• Table DROP or TRUNCATE

• Tablespace BEGIN BACKUP

Log Writer (LGWR) writes:

• At commit

• When 1/3rd full

• When there is 1 MB of redo

• Every 3 seconds

• Before DBWn writes

System Monitor (SMON) Responsibilities:

• Instance recovery

– Rolls forward changes in redo logs

– Opens database for user access

– Rolls back uncommitted transactions

• Coalesces free space

• Deallocates temporary segments.

Process Monitor (PMON) Cleans up after failed processes by:

• Rolling back the transaction

• Releasing locks

• Releasing other resources

• Restarting dead dispatchers

Checkpoint (CKPT) Responsible for:

• Signaling DBWn at checkpoints

• Updating datafile headers with checkpoint information

• Updating control files with checkpoint information

Archiver (ARCn)

• Optional background process

• Automatically archives online redo logs when ARCHIVELOG mode is set

• Preserves the record of all changes made to the database

Why do you run orainstRoot and ROOT.SH once you finalize the Installation?

orainstRoot.sh needs to be run to change the Permissions and groupname to 770 and to dba.

Root.sh (ORACLE_HOME) location needs to be run to create a ORATAB in /etc/oratab or /opt/var/oratab in Solaris and to copy dbhome, oraenv and coraenv to /usr/local/bin.

orainstRoot.sh

[root@oracle11g ~]# /u01/app/oraInventory/orainstRoot.sh

Changing permissions of /u01/app/oraInventory to 770.

Changing groupname of /u01/app/oraInventory to dba.

The execution of the script is complete

root.sh

[root@oracle11g ~]# /u01/app/oracle/product/11.1.0/db_1/root.sh

Running Oracle 11g root.sh script...

The following environment variables are set as:

ORACLE_OWNER= oracle

ORACLE_HOME= /u01/app/oracle/product/11.1.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:

Copying dbhome to /usr/local/bin ...

Copying oraenv to /usr/local/bin ...

Copying coraenv to /usr/local/bin ...

Creating /etc/oratab file...

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root.sh script.

Now product-specific root actions will be performed.

Finished product-specific root actions.

For Oracle installation on unix/linux, we will be prompted to run a script 'root.sh' from the oracle inventory directory.this script needs to run the first time only when any oracle product is installed on the server. 

It creates the additional directories and sets appropriate ownership and permissions on files for root user.

File type	Extension	Default  location (when created with OMF)
Pfile :	ORA	C:\oracle\product\10.2.0\admin\orcl\pfile
Spfile:	ORA	C:\oracle\product\10.2.0\db_1\database
Control file:	CTL	C:\oracle\product\10.2.0\oradata\orcl
Redo log file:	LOG	C:\oracle\product\10.2.0\oradata\orcl
Archive log file:	LOG	C:\oracle\product\10.2.0\flash_recovery_area\ORCL\ARCHIVELOG
Data file:	DBF	C:\oracle\product\10.2.0\oradata\orcl
Alert log files:	LOG	C:\oracle\product\10.2.0\admin\orcl\adump
Trace log files:	TRC	C:\oracle\product\10.2.0\admin\orcl\udump|bdump|cdump
Password file:	ORA	C:\oracle\product\10.2.0\db_1\database

CREATE TABLE in SQL

                       

 How to CREATE TABLE in SQL

The SQL CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in a database.

Syntax

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

SQL CREATE TABLE Example

The following example creates a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City:

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255) 
);

Create Table Using Another Table

A copy of an existing table can be created using a combination of the CREATE TABLE statement and the SELECT statement.

The new table gets the same column definitions. All columns or specific columns can be selected.

If you create a new table using an existing table, the new table will be filled with the existing values from the old table.

CREATE TABLE new_table_name AS SELECT * FROM existing_table_name WHERE 1=2;

Dynamic Performance Examples

Dynamic Performance

• V$CONTROLFILE

• V$DATABASE

• V$DATAFILE

• V$INSTANCE

• V$PARAMETER

• V$SESSION

• V$SGA

• $SPPARAMETER

• V$TABLESPACE

• V$THREAD

• V$VERSION

Examples

• V$CONTROLFILE: Lists the names of the control files

• V$DATABASE: Contains database information from the control file.

• V$DATAFILE: Contains data file information from the control file

• V$INSTANCE: Displays the state of the current instance

• V$PARAMETER: Lists parameters and values currently in effect for the session

• V$SESSION: Lists session information for each current session

• V$SGA: Contains summary information on the system global area (SGA)

• V$SPPARAMETER: Lists the contents of the SPFILE

• V$TABLESPACE: Displays tablespace information from the control file

• V$THREAD: Contains thread information from the control file.

• V$VERSION: Version numbers of core library components in the Oracle server

More detail of examples:

1.The V$FIXED_TABLE view can also be queried to get a listing of the dynamic performance views:

SQL> SELECT * FROM V$FIXED_TABLE;

2.To get an overview of the data dictionary views, the DICTIONARY view or its synonym

DICT can be queried.

SQL> SELECT * FROM dictionary;

Include the WHERE clause to narrow your responses:

SQL> SELECT * FROM dictionary

2 WHERE table_name LIKE ‘DBA_SEG%’

3.To view the contents of the view, use the SELECT command.

SQL> SELECT * from V$INSTANCE;

4.To get a list of columns within a view, use the DESCRIBE keyword:

SQL> DESCRIBE V$INSTANCE; 

Data Dictionary Views in Oracle

Data Dictionary Views in Oracle

Oracle data dictionary views will start with any one of 3 character strings i.e. starts with DBA_ or ALL_ or USER_

 DBA: What is in all the schemas
ALL: What the user can access
USER: What is in the user’s schema


DBA_ views --> All information and objects in the Oracle data dictionary. These will be accessible by users having DBA privileges.

ALL_ views --> All information and objects accessible by a user.

USER_ views --> All information and objects owned by a user.