Syntax uses in database for administration

                                      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>;