SYNTAX
WE USE as a DBA
1} CREATING AN SPFILE:
à create
SPFILE=’</Source path>’ from PFILE=’</Destination path>’;
2} ALTERING THE DATABASE:
à alter database<Database name> NOMOUNT;
à alter database<Database name> MOUNT;
à alter database<Database name> OPEN;
3} OPENING THE DATABASE IN READONLY MODE:
à alter database<Database name> open READONLY;
4} OPENING DATABASE IN RESTRICTED MODE:
à startup RESTRICT;
à alter system enable RESTRICTED session;
5} MULTIPLEXING CONTROL FILES USING ‘SPFILE’:
àalter system set
controlfile=’</Source path>’ ‘</Destination
path>’
scope=spfile;
à shut down database;
à copy controlfiles
using O.S command.
==>cp ‘</source path>’ ‘</destination path>’
à startup database
6}MULTIPLEXING CONTROL FILES USING ‘PFILE’:
à Shut the database;
à copy controlfiles
using O.S command.
==>cp ‘</source path>’ ‘</destination
path>’
àEdit the pfile with new
control file locations.
à startup the
database
7}FORCING A LOG SWITCH:
à alter system switch
logfile;
8} ADDING REDO LOG ‘GROUPS’:
à alter database ADD
logfile GROUP 1
(‘</Path of logfile to be placed>’ ‘</ another logfile to be
placed>’)
Size 1M;
9}ADDING ONLINE REDO LOG FILE ‘MEMBERS’:
à alter database ADD
logfile MEMBER
‘</Path of logfile>’ to GROUP 1,
‘</Path of logfile>’ to GROUP 2,
‘</Path of logfile>’ to GROUP 3;
10}DROPPING REDO LOG ‘GROUPS’:
à alter database DROP
logfile GROUP <group no.>;
11} DROPPING REDO LOG ‘MEMBERS’:
à alter database DROP
logfile MEMBER ‘</Path of log file>’;
12}RELOCATING (OR) RENAMING LOGFILES:
à Copy redo log files
to new location.
== > cp ‘</source path>’ ‘</Destination path>’
à alter database
clear logfile ‘</Path and new name>’;
àDrop old members;
13}CREATING TABLESPACE:
à create tablespace
<tablespace name> DATAFILE’</Path>’ size <no.>
Autoextend on next<size.> maxsize <size>;
14} CREATING LOCALLY MANAGED TABLESPACES:
à create tablespace
<tablespace name>
DATAFILE ‘</Destination of datafile>’ size<no.>
Extent management local uniform size <no.>;
15}CREATING UNDO TABLESPACES:
à create
undotablespace <tablespace name>
DATAFILE ‘</Destination of datafile>’
size<no.>;
16}CREATING TEMPORARY TABLESPACES:
à create temporary
tablespace <tablespace name>
TEMPFILE ‘</Destination>’ size<no.>
Extent management local uniform size<no.>;
17} SETTING DEFAULT TEMPORARY TABLESPACES:
à alter database
default temporary tablespace <tablespace name>;
18} SETTING TABLESPACE IN ‘READONLY’ MODE:
à alter tablespace
<tablespace name> READONLY;
19} TAKING TABLESPACE OFFLINE AND ONLINE:
àalter tablespace
<tablespace name> OFFLINE;
à alter tablespace
<tablespace name> ONLINE;
20} MANUALLY RESIZING DATAFILE:
à alter database
DATAFILE ‘</path>’ RESIZE <NO.>;
21}ADDING DATAFILE TO TABLESPACE:
à alter tablespace
<tablespace name>
ADD DATAFILE ‘</path>’ size <no.>;
22} METHODS OF MOVING(RENAMING) DATAFILES:
à Take tablespace
offline.
à use O.S command to
copy datafile to different location.
à alter database
‘</path>’ RENAME
Datafile ‘</Source
path>’ to ‘</Destination path>’;
à Bring tablespace
online;
23}IF TABLESPACE CANNOT BE TAKEN OFFLINE FOR MOVING:
à Shut down database.
à use O.S command to
copy datafile to different location.
à Mount database.
àalter database
‘</path>’ RENAME
Datafile ‘</Source
path>’ to ‘</Destination path>’;
à open database.
24}TABLESPACE DROPPING:
àdrop tablespace
<tablespace name> including contents and datafiles;
25} AUTOMATIC SEGMENT SPACE MANAGEMENT:
à create tablespace
<tablespace name>
DATAFILE ‘</path>’ size<no.>
Extent management local uniform size <no.>
Segment space management AUTO;
26}SWITCHING UNDO TABLESPACE:
àalter system set
UNDO_TABLESPACE=<undo name>;
27} DROPPING UNDO TABLESPACE:
à drop tablespace
<undo tablespace name>;
28} MANUALLY ALLOCATING EXTENTS:
à alter table
<tablespace name>
Allocate extent (size<no.> DATAFILE
‘</path>’);
29} MOVING ONE TABLE TO OTHER TABLESPACE:
à alter table
<table name>
Move tablespace<tablespace name>;
30} TRUNCATING TABLE:
à truncate table
<table name>;
31} DROPPING TABLE:
à drop table
<table name> cascade constraints;
32} DROPPING COLUMNS:
à alter
table<table name> drop column comments
Cascade constraints checkpoint <1000>;
33} DIFFERENT UNUSED OPTIONS:
à MARK
== > alter table <table name> set UNUSED COLUMN comments
Cascade constraints;
àDROP
== > alter table <table name> DROP UNUSED COLUMN
checkpoint 1000;
à CONTINUE
== >
alter table <table name> DROP COLUMN CONTINUE
checkpoint 1000;
34}CREATING B-TREE INDEXES:
à create Index
<tablename_column_name_idx>
On <tablename>(column name)
Pctfree 30
Storage(initial <no.> next <no.>
Pct increase 0 maxextents <no.>)
Tablespace <tablespace name>;
35} CREATE BITMAP INDEX:
à create BITMAP index
< tablename_column_name_idx >
On
<tablename>(column name)
Pctfree 30
Storage(initial <no.> next <no.>
Pct increase 0 maxextents <no.>)
Tablespace <tablespace name>;
36} REBUILDING INDEX:
à alter index <index
name> REBUILD
Tablespace<tablesapce name>;
37} REBUILDING INDEXES ONLINE:
à alter index
<index name> REBUILD ONLINE;
38}DROPPING INDEX
à drop index
<index name>;
