SYNTAX WE USE as a DBA

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
                 drop index <index name>;

No comments:

ORA-00059: Maximum Number Of DB_FILES Exceeded in 19C database

When I am adding datafile to my 19C database facing the below error. SQL> alter tablespace DATA  add datafile '/u01/data/data15.dbf...