Master Oracle Database: Create, Backup, Restore, and Manage Tables & Data

Oracle Database Operations

Create a database create database databasename; Delete a database drop database dbname; Backup a database (using the original exp utility)

Full export:

exp demo/demo@orcl buffer=1024 file=d:\back.dmp full=y

Parameters: demo/demo – username/password; buffer – client cache size (KB); file – destination dump file; full=y – export all objects; ignore=y – ignore errors and overwrite existing objects.

Export only SYSTEM and SYS schemas:

exp demo/demo@orcl file=d:\backup\1.dmp owner=(system,sys)

Export selected tables:

exp demo/demo@orcl file=d:\backup2.dmp tables=(teachers,students)

Export with a WHERE clause and optional compression/log:

exp demo/demo@orcl file=d:\back.dmp tables=(table1) query="where field1 like 'fg%'" compress=y log=d:\log.txt

Remote export:

exp username/password@remoteIP:port/instance file=path\filename.dmp full=y

Restore a database (using imp)

Full import:

imp demo/demo@orcl file=d:\back.dmp full=y ignore=y log=D:\implog.txt

Log file helps diagnose errors.

Import selected tables:

imp demo/demo@orcl file=d:\backup2.dmp tables=(teachers,students)

Remote import:

imp username/password@remoteIP:port/instance file=path\filename.dmp full=y

Oracle Table Operations

Create a table

create table tabname (col1 type1 [not null] [primary key], col2 type2 ...);

Copy an existing table (SQL Server syntax): select * into table_new from table_old; Oracle‑specific “create‑as‑select”:

create table tab_new as select col1, col2 … from tab_old;

Delete a table drop table tabname; Rename a table alter table tabname rename to newtablename; Add a column

alter table tabname add (column_name column_type default_value not null);

Modify a column

alter table tabname modify (column_name column_type default_value not null);

Rename a column

alter table tabname rename column oldcol to newcol;

Drop a column alter table tabname drop column column_name; Primary key

alter table tabname add primary key (col);

alter table tabname drop primary key;

Index

create [unique] index idxname on tabname(col1, col2 ...);

drop index idxname;

Indexes cannot be altered; they must be dropped and recreated.

View

create view viewname as select ...;

drop view viewname;

Oracle Data Operations

Query data

select column_list from table_name [where condition] [order by column_name [asc|desc]];

Insert data

insert into table_name values (val1, val2, ...);
insert into table_name (col1, col2) values (val1, val2);

Update data

update table_name set column = new_value [where condition];
-- without WHERE updates all rows

Delete data

delete from table_name where condition;

delete from table_name;

truncate table table_name;

drop table table_name;

commit;

rollback;

Copy data

Copy rows between tables: insert into table1 (select * from table2); Copy only structure:

create table table1 as select * from table2 where 1>1;

Copy structure and data: create table table1 as select * from table2; Copy selected columns:

create table table1 as select id, name from table2 where 1>1;

Database Copy Command Illustration