Database Management

1. you can deattach or reattch datapump job.

2. you can parallelise datapump job [PARALLEL=4]

3. without export can do import using NETWORK_LINK parameter

4. can calculate export dump file size using ESIMATE_ONLY

5. expdp utility used which is caled by dbms_datapump procudure

6. remapping capabilities during import

[REMAP_DATAFILE,EMAP_SCHEMA,REMAP_TABLESPACE,REUSE_DATAFILES]

select *

from (

select rownum num , empno, ename

from emp

)

where num between 2 and 5

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

we can also use row_number function

it shows run time error.....

choosing pragma autonomous_transaction to overcome this problem

candidate key: when we reduce unnecessary columns from a

super key it is called as candidate key

primary key: primary key is a constraint, in a table

there is only one primary key,a primary key is simple or

composite, a simple primary key is based on one column which

can be specified either inline outline of the constraint,a

composite primary key is based on (2-32)columns which can

be specified only out line of the constraint,a primary dose

not allows duplicates.

yes we can update view

first connect to sysdba user then write

sql>grant resource to scott;

dependent on application. Most transactional based

databases require this to avoid duplication etc. However,

OLAP, these type of databases (typically those used in data

warehouses etc.) are not necesarrily normalised as they are

built for performance of querying the data contained within.

Patch Set Updates (PSUs) are proactive cumulative patches containing recommended bug fixes that are released on a regular and predictable schedule. PSUs are on the same quarterly schedule as the Critical Patch Updates (CPU), specifically the Tuesday closest to the 17th of January, April, July, and October.

Truncate is a DDL command, which has autocommit hence

cant be rolled back where as delete is DML with user commit

& roll back option. Truncate is faster then delete since it

wont store any data to backup tablespace. before delting.

Oracle Clinical or O*C is a database management system

designed by Oracle to provide data management, data entry

and data validation functionalities to Clinical Trials

process.

The Major Functions Supported by Oracle Clinical are:

* Clinical Study Protocol Definition and Management

* Definition of Metadata collected during a Clinical Study.

* Creation of Data Entry System

* Creation of Data Management System to clean and reconcile Data

* Validation Procedures

* Data Loading and Extracting

* Thesaurus Management System for Coding Medical Terms

* Laboratory Reference Range Management System

select deptno,trunc(avg(sal))as avg from emp group by deptno;

There are two types of join inner join and outer join.

(1)Inner join will return all the common records in both the

table

Ex: Select e.ename from Employee e,Dept d where e.did=d.did;

(2)Outer join is of 3 types Left Outer,Right Outer and full

outer.

Left outer retrieves common records from both the table and

all the record from the left table.

Similarly Right Outer all the records from right table and

Full outer from both the tables

Inline query :

select * from emp where empno=(select max(empno) from emp);

Co-Related sub query:

Select * from emp where dob> (select distinct dob where

deptno= 10);

Note: In the Inline subquery , The main query depends upon

the outcome value of the inline sub query which is in-lined

but in case of co-related sub query the main query and sub

query both run simultaneously then the check occurs.

select <string1>|| ' <<Number of spaces needed with in

single queotes>> ' ||<string2> from dual;

ex. select 'RAM' || ' ' || 'PRASAD' FullName from dual;