All Checks

All Checks

Database Version

SQL> desc v$version

SQL> select banner from v$version;

To check the EBS version

external hard disk mount
mount -t ntfs-3g /dev/sdb1 /prasad123

For unmount
umount -t ntfs-3g /dev/sdb1 /prasad123

create password file at dbs
orapwd file=orapwTESTC password=sys@123 ignorecase=y

Log as sysdba using created password file for test purpose

sqlplus sys/sys@123@TEST as sysdba

To check the ORACLE_HOME & ORACLE_SID

$ echo $ORACLE_HOME
$ echo $ORACLE_SID

To chec SID in Applications

$ echo $TWO_TASK

To unlock a database user

CONN / AS SYSDBA

ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

To create a directory

CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

To get the applicatoin url page

select home_url from apps.icx_parameters;

select home_url from icx_parameters;

grep -i s_login $CONTEXT_FILE

Datapump related queries

select * from dba_datapump_jobs;

SYNTAX WE USE as a DBA

CREATING AN SPFILE:
           
create SPFILE=’</Source path>’ from PFILE=’</Destination path>’;

ALTERING THE DATABASE:

alter database<Database name> NOMOUNT;
alter database<Database name> MOUNT;
alter database<Database name> OPEN;

OPENING THE DATABASE IN READONLY MODE:

alter database<Database name> open READONLY;

OPENING DATABASE IN RESTRICTED MODE:

startup RESTRICT;
alter system enable RESTRICTED session;

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

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

FORCING A LOG SWITCH:

alter system switch logfile;

ADDING REDO LOG ‘GROUPS’:

alter database ADD logfile GROUP 1 (‘</Path of logfile to be placed>’ ‘</ another logfile to be placed>’) Size 1M;

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;

DROPPING REDO LOG ‘GROUPS’:

alter database DROP logfile GROUP <group no.>;

DROPPING REDO LOG ‘MEMBERS’:

alter database DROP logfile MEMBER ‘</Path of log file>’;

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;

CREATING TABLESPACE:

create tablespace <tablespace name> DATAFILE’</Path>’ size <no.> Autoextend on next<size.> maxsize <size>;

CREATING LOCALLY MANAGED TABLESPACES:

create tablespace <tablespace name> DATAFILE ‘</Destination of datafile>’ size<no.> Extent management local uniform size <no.>;

CREATING UNDO TABLESPACES:

create undotablespace <tablespace name> DATAFILE ‘</Destination of datafile>’ size<no.>;

CREATING TEMPORARY TABLESPACES:

create temporary tablespace <tablespace name> TEMPFILE ‘</Destination>’ size<no.> Extent management local uniform size<no.>;

SETTING DEFAULT TEMPORARY TABLESPACES:

alter database default temporary tablespace <tablespace name>;

SETTING TABLESPACE IN ‘READONLY’ MODE:

alter tablespace <tablespace name> READONLY;

TAKING TABLESPACE OFFLINE AND ONLINE:

alter tablespace <tablespace name> OFFLINE;
alter tablespace <tablespace name> ONLINE;

MANUALLY RESIZING DATAFILE:

alter database DATAFILE ‘</path>’ RESIZE <NO.>;

ADDING DATAFILE TO TABLESPACE:

alter tablespace <tablespace name> ADD DATAFILE ‘</path>’ size <no.>;

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;

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.

TABLESPACE DROPPING:

drop tablespace <tablespace name> including contents and datafiles;

AUTOMATIC SEGMENT SPACE MANAGEMENT:

create tablespace <tablespace name> DATAFILE ‘</path>’ size<no.> Extent management local uniform size <no.> Segment space management AUTO;

SWITCHING UNDO TABLESPACE:

alter system set UNDO_TABLESPACE=<undo name>;

DROPPING UNDO TABLESPACE:

drop tablespace <undo tablespace name>;

MANUALLY ALLOCATING EXTENTS:

alter table <tablespace name> Allocate extent (size<no.> DATAFILE ‘</path>’);

MOVING ONE TABLE TO OTHER TABLESPACE:

alter table <table name> Move tablespace<tablespace name>;

TRUNCATING TABLE:

truncate table <table name>;

DROPPING TABLE:

drop table <table name> cascade constraints;

DROPPING COLUMNS:

alter table<table name> drop column comments Cascade constraints checkpoint <1000>;

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

REBUILDING INDEX:

alter index <index name> REBUILD Tablespace<tablesapce name>;

REBUILDING INDEXES ONLINE:

alter index <index name> REBUILD ONLINE;

DROPPING INDEX

drop index <index name>;

Tablespace related queries:

To check temporary tablespace

SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free) FROM V$temp_space_header GROUP BY tablespace_name;

To resize temporary tablespace

alter database tempfile '/R12/oracle/SHIV/db/apps_st/data/temp01.dbf' resize 100M;

To add a datafile to temporay tablespace

ALTER TABLESPACE temp1 ADD TEMPFILE '/u01/findb/db/apps_st/data/temp01a.dbf' SIZE 4096M;

Resize the datafile of the particular tablespace.

ALTER DATABASE DATAFILE '/d01/oracle/VIS/db/apps_st/data/data01.dbf' RESIZE 2048M;

Add a datafile to tablespace

ALTER TABLESPACE APPS_TS_TX_DATA ADD DATAFILE '/d01/oracle/VIS/db/apps_st/data/data02.dbf' SIZE 1024M;

Set Default Temporary Tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_name;

To resize temporary tablespace:

ALTER DATABASE TEMPFILE '/u01/findb/db/apps_st/data/temp01a.dbf' RESIZE 4096M;

Create a New User and Grant

Login to SYS user.

CREATE USER XXCUST IDENTIFIED BY ORACLE
DEFAULT TABLESPACE XXCUST_TABLESPACE TEMPORARY TABLESPACE TEMP;

GRANT RESOURCE, CONNECT TO XXCUST;

To view current users in Oracle database and their current status:

select username, account_status from dba_users;

Rename a table in Oracle

alter table <Current table name> rename to <New table name>;

Get the objects consumed by SYSAUX tablespace

SELECT * FROM V$SYSAUX_OCCUPANTS

How to check the last resetlogs

SQL> select name,to_char(RESETLOGS_TIME,'DD-MON-YYYY HH:MI::SS') from v$database;

How to find a table size on Oracle Database

SELECT segment_name, tablespace_name, ROUND((BYTEs/1073741824*100)/100) sizeGB, ROUND((BYTEs/1048576*100)/100) sizeMB, ROUND((BYTES/1024*100)/100) sizeKB FROM USER_SEGMENTS order by sizeKB desc;

To check the hidden parameters in a database

SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';

To check SGA and PGA sizes

SELECT * FROM V_$SGA_TARGET_ADVICE;
SELECT * FROM V_$PGA_TARGET_ADVICE;
or
select * from v$memory_target_advice;