ORACLE : More ...

CREATING TABLE FROM ANOTHER PRE-EXISTING TABLE IN ORACLE :

USE : CREATE TABLE <tablename> (<field1>, <field2>,....) AS SELECT <field1>, <field2>,.... FROM <tablename>; 
e.g. CREATE TABLE PORTBLAIR (ID,NAME,PHONE) AS SELECT ID,NAME,PHONE FROM CHENNAI;

NOTE : You can create same or lesser number of columns from existing tables to create a new table.


 

RECYCLE BIN FUNCTION IN ORACLE : This feature is added in Oracle 10g where where we can retrieve dropped data with FLASHBACK.

USE : SHOW RECYCLEBIN;  to see any deleted object. USE : PURGE RECYCLEBIN; to delete data permanently and clear recyclebin.



RESTORING A DELETED TABLE FROM RECYCLEBIN IN ORACLE :

USE : FLASHBACK TABLE <tablename> TO BEFORE DROP;                    e.g. FLASHBACK TABLE KATCHAL TO BEFORE DROP;

DELETING DIRECTLY WITHOUT SENDING TO RECYCLE BIN :

USE : DROP TABLE <tablename> PURGE;                                              e.g. DROP TABLE CHENNAI PURGE;

CREATE TABLESPACE :

Command allows creating a tablespace whihc stores tables (objects)

CREATE TABLESPACE RITESH_SPACE
DATAFILE 'RITESH_SPACE.dat' SIZE 20M
DEFAULT STORAGE (INITIAL 10K NEXT 50K MINEXTEBTS 1 MAXEXTENTS 999 PCTINCREASE 10) ONLINE;

Create a DBA user for tablespace RITESH_SPACE




CREATE USER "DBA_RITESH"
PROFILE "DEFAULT"
IDENTIFIED BY "blahblah_password"
DEFAULT TABLESPACE "RITESH_SPACE"
TEMPORARY TABLESPACE "TEMP"
ACCOUNT UNLOCK;

DATA TYPES : CHAR | VARCHAR2 | NVARCHAR | NUMBER | LONG | DATE | BINARY_FLOAT | BINARY_DOUBLE | TIMESTAMP WITH LOCAL TIME ZONE | RAW | UROWID | NCHAR | BLOB | CLOB | NCLOB

Resources : www.oracle.com