21.Oracle - The self documenting dictionary

Oracle - The self documenting dictionary

Oracle's Data dictionary is itself self documenting. we can query DICTIONARY AND DICT_COLUMNS views for descriptions of the data dictionary views and their columns.


The following query gives the descriptions of all of the data dictionary views.

SELECT table_name, comments
FROM dictionary
ORDER BY table_name;

It gives the large amout of output. By using where class we can focus a smaller set of views as like the following query.

SELECT table_name, comments
FROM dictionary
WHERE table_name LIKE '%TABLE%'
ORDER BY table_name

It returns all views containing the word 'TABLE'

Like wise We can query the DICT_COLUMNS view also. It gives the descriptions for the columns for all views. Following query retrieves descriptions for the columns in ALL_TAB_COLUMNS.


SELECT column_name, comments
FROM dict_columns
WHERE table_name = 'ALL_TAB_COLUMNS';

This is a sample one. We can learn lot about oracle through data dictionary views, they are not available at any oracle site.


Thanks..!

20. TRIM Function

Which SELECT statement will the result 'elloworld' from the string 'HelloWorld'?

A. SELECT SUBSTR( 'HelloWorld',1) FROM dual;
B. SELECT INITCAP(TRIM ('HelloWorld', 1,1)) FROM dual;
C. SELECT LOWER(SUBSTR('HellowWorld', 1, 1) FROM dual;
D. SELECT LOWER(SUBSTR('HelloWorld', 2, 1) FROM dual;
E. SELECT LOWER(TRIM ('H' FROM 'HelloWorld')) FROM dual;

Answer: E

Explanation:
TRIM function accept a string describing the data you would like to trim from a column value. It can trim from both side of column value i.e. left and right. In the following statement this functionwill trim as
SELECT LOWER(TRIM ('+' FROM 'HelloWorld')) FROM dual;

From the above statement trim function will remove the character 'H' from 'HelloWorld' and LOWER function will convert the remaining character to lower case.

19. Oracle Processes

Processes

Oracle uses many small (focused) processes to manage and control the Oracle instance. This allows for optimum execution on multi-processor systems using multi-core and multi-threaded technology.

Some of these processes include:

PMON - Process Monitor

SMON - System Monitor

ARCn - Redo Log Archiver

LGWR - Redo Log Writer

DBWn - Database Writer

CKPT - Checkpoint process

RECO - Recoverer

CJQn - Job Queue Coordinator

QMNn - Queue-monitor processes

Dnnn - Dispatcher Processes (multiplex server-processes on behalf of users)

Snnn - Shared server processes (serve client-requests)

MMAN - Internal process (used for internal database tasks)

LSP0 - Logical standby coordinator process (controls Data Guard log-application)

MRP - Media-recovery process (detached recovery-server process)

MMON - Memory-monitor process

MMNL - Memory monitor light (gathers and stores AWR statistics)

PSP0 - Process-spawner (spawns Oracle processes)

RFS - Remote file server process (archive to a remote site)

DBRM - DB resource manager (new in 11g)

DIAGn - Diagnosability process (new in 11g)

FBDA - Flashback data archiver process (new in 11g)

VKTM - Virtual Timekeeper (new in 11g)

Wnnn - Space Management Co-ordination process (new in 11g)

SMCn - Space Manager process (new in 11g)

An instance can mount and open one and only one database.
A database can normally only be mounted and opened by one instance. However, when using Real Application Clusters (RAC) a database can be mounted and opened by many instances.

18. ADDING CHECK CONSTRAINT WHILE INSERTING.

Examine the structure of the EMPLOYEES table:

EMPLOYEE_ID NUMBER NOT NULL
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20)
SAL NUMBER
MGR_ID NUMBER
DEPARTMENT_ID NUMBER

You want to create a SQL script file that contains an INSERT statement.
When the script is run, the INSERT statement should insert a row with the specified values into the EMPLOYEES table.
The INSERT statement should pass values to the table columns as specified below:

EMPLOYEE_ID: Next value from the sequence

EMP_ID_SEQEMP_NAME and JOB_ID: As specified by the user during run time, through substitution variables

SAL: 2000

MGR_ID: No value

DEPARTMENT_ID: Supplied by the user during run time throughsubstitutionvariable.

The INSERT statement should fail if the user supplies a value other than20 or 50.

Which INSERT statement meets the above requirements?

A. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL,&did);

B. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL,&did IN (20,50));

C. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50)) VALUES(emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);

D. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50) WITH CHECKOPTION) VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);

E. INSERT INTO (SELECT * FROM employees WHERE (department_id = 20 AND department_id = 50) WITH CHECK OPTION ) VALUES (emp_id_seq.NEXTVAL, '&ename','&jobid', 2000, NULL, &did);

Answer: D

Question 17:How to get the 'n' th maximum value of a column?

We can get the nth maximum value through query by using the combination of order by clause and rownum pseudo column.

The Query following.

SELECT MIN(columnname)
FROM ( SELECT coumnname FROM tablename ORDER BY columnname DESC)
WHERE rownum <= n