LINUX & ORACLE QUICK REFERENCE

/*   to view the available tables in the current user    */

Sql> select * from user_objects where object_type = ‘TABLE’;   

/*        to voew the available tables    */

Select * from tab;   (or)  select * from cat;     (or)    select tname from user_tables;

Sql> select * from user_tables;   /*          it will display all the tables  */

/*display the tables which are all having constraints  */

Sql> select constraint_name,table_name from user_constraints;  

To findout what view we have created

Select view_name from user_views;

To findout what constraints are defined in our database

Select constraint_name from user_constraints;

To make each transaction to be automatically committed right after it is executed

Sql> set autocommit on;     in reverse   sql> set autocommit off;

To display the files which are available in the database

Sql> select file_name from dba_data_files;

To modify the constraint null to notnulll in a table

Sql> alter table employee modify empno not null;

Alter Table Commands

Alter table employee add constraint constraint_name unique(employee_id);

Alter table table_name disable constraint constraint_name;

Alter table employee add emp_mail_id varchar2(20);

Alter table employee rename column empno ro employeeno;

Alter table employee modify(empid varchar2(4));

Alter table emp rename to employee;

Alter table employee drop constraint constraint_name;

Drop table command

Drop table table_name;

Drop table table_name purge;

Drop table table_name cascade constraints;

Flashback table emp to before drop;

Create Table Commands

Create table employee (empno number(3) not null, empid number(4));

Insert into employee values(1,null);

Insert into employee values(null,3);

/*  the second insert statement will throw error   */

Alter table employee modify empno null;

Create table employee ( empno number unique, empname varchar2(10));

Constraints

To drop a constraint:

Alter table employee drop constraint constraint_name;

To add a unique constraint:

Alter table employee add constraint uq_emp unique(empid);

Create table employee ( empno number, ename varchar2, unique(empno,ename));

Create table employee( empno number, empname varchar2(10), constraint uq_emp unique(empno,empname);

Primary key constraint:

Create table employee ( empno number primary key, empname varchar2(15));

Naming primary key:

Create table employee( empno number, empid number, constraint pk_emp primary key(empno));

Check constraint:

Create table employee( empno number check(empno between 100 and 150), empname varchar2(10));

This allows only numbers that are between 100 and 150 in the column empno.

Check constraint can be added after a table had been created.

Alter table employee add constraint check_emp check(empno>50);

Create table employee( empno number, empid number, empname varchar2(5), check(empno<empid));

To disabling constraint:

Alter table employee disable unique(empno,ename);

Alter table employee  disable constraint uq_emp;

Foreign key

Alter table department add constraint fk_dept_empno foreign key(empno) references employees(empno);

Create table emp as select * from employee;

Select tablespace_name from dba_data_files;

Select username,default_tablespace from dba_users;

Select * from tab where name like’HR’;

Select object_type from user_objects;

Select * from user_catalog;

UNCONDITIONAL INSERT ALL

Insert all

            Into sal_history values( empno,hiredate,sal)

            Into mgr_history values( empno,mgr,sal)

            Select emp_no  empno, here_data  hiredate, salary  sal,  mgr)

From employees where emp_no > 100;