Syntax we use in database for administration
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
No comments:
Post a Comment