A To Z Print

SELECT CHR(X.RNO +64) "ALPHA" FROM (
SELECT LEVEL RNO
FROM DUAL
CONNECT BY LEVEL <= 26 ) X

Schema using tablespace

SELECT SUM (BYTES) / 1024 / 1024 mb
FROM dba_segments
WHERE owner = 'HR';

Compiling oracle forms in command prompt

frmcmp Module=C:\Users\Dell\Desktop\170817\MODULE2.fmb userid=lan/lan@orcla Module_type=Form Logon=YES Upgrade=YES Build=YES

Killing Session

select SID,SERIAL#,USER#,USERNAME,OWNERID,SERVER,MACHINE,OSUSER from v$session;

alter system kill session 'sid,serial#';

Find out How Many Rows in Table in a User

DECLARE
val NUMBER;
BEGIN
FOR I IN (SELECT TABLE_NAME FROM USER_TABLES) LOOP
EXECUTE IMMEDIATE 'SELECT count(*) FROM ' || i.table_name INTO val;
DBMS_OUTPUT.PUT_LINE(i.table_name || ' ==> ' || val );
END LOOP;
END;

Drop All Table in a User

BEGIN
FOR c IN (SELECT table_name FROM ALL_tables where owner='TEST_GPG_GL') LOOP
EXECUTE IMMEDIATE ('DROP TABLE "' || c.table_name || '" CASCADE CONSTRAINTS');
END LOOP;
END;

Copy Table One Database To Another Datbase

Syntax:

COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)]
USING query


Using Dblink:

create table target_table_name as select * from source_table_name@dblink 


Using Copy:

copy from hr/hr@ala to gbdml/gbdml@gbdml create employees_copy using select * from employees ;

Be carefull- SQLPlus COPY supports only the following datatypes:

    CHAR
    DATE
    LONG
    NUMBER
    VARCHAR2

Database Designing, Normalization & SDLC

Database Designing

Normalization :

Normalization is the process of decomposing relation with anomalies to produce smaller and well structured relations.

Normal form :

A normal form is a state of relation that results from applying simple rules regarding functional dependencies.

1. Normal form :

2. Normal form :

3. Normal form :

4. Normal form :

5. Normal form :

6. Normal form :

Denormalization :

The process of transforming normalized relations into unnormalized physical record specifications.

SDLC (System Development life cycle)

1. Project defining

a.  Identification, Selection , initialization and Planning of project.

2. System Analysis

a. study of existing system

b. defining goals and objective, scope of the system

3. Logical Design

a. Entity Relationship diagram (ERD)

b. Data flow diagram (DFD)

4. Physical Design

a. database creation, object creation, users, tablespace, tables

5. Development 

a. Forms & report designing

b. Creation  of procedure, function & package.

6. Testing

a. Define test cases

b. Testing forms, reports & application.

7. Implementation

a. Installation of front end, backend software

b. Deployment of application.