DBA

Due to error occur on Oracle Listner not properly mentioned

So.., you should check lisntener.ora file and modify

Example :-

(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)

< (SID_DESC =
(SID_NAME = localdb )
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
(SID_NAME = localdb) >

Note : < ... > here check your service name either there or not?localdb -------- db name
And then go to
lsnrctl >stop

lsnrctl>start

Or

>set oracle_sid=localdb
>sqlplus
username : / as sysdba
>startup


& then you connect sys/sys@localdb as sysdba

Your service should be started
------------------------------------------------------------------------------------------
Thanks & Regards
S.Mohamed Azarudeen
azarmohds@gmail.com

Buffer Hit Ratio :-

Buffer hit ratio notes:

• Consistent Gets - The number of blocks accesses made to the block buffer to retrive data in a consistent mode.
• DB Blk gets - The number of blocks accessed via single block gets.
• Physical Reads - The cumulative number of blocks read from disk.
• Logical reads are the sum of consisent gets and db block gets.
• the db block gets statstic value is incremented when a block is read for update and when segment header blocks are accessed.
• Hit ratio should be > 80% , else increase db_block_buffers in init.ora

sql>select sum(decode(name, 'consistent gets',value,0)) "consistent gets",

sum(decode(name,'db block gets',value,0)) "db block gets",

sum(decode(name,'physical reads',value,0)) " physical reads",

round((sum(decode(name,'consistent gets',value,0)) +

sum(decode(name,'db block gets',value,0)) - sum(decode(name,'physical reads',value,0))) /

(sum(decode(name,'consistent gets',value,0)) +

sum(decode(name,'db block gets',value,0))) * 100,2) "Hit Ratio" from v$sysstat;

Data Dict Hit Ratio :-

• Gets - Total number of requests for information on the data object.
• Cache Misses -- Number of data requests resulting in cache misses.
• Hit ratio should be > 90%,else increse Shared_pool_size in init.ora

Sql> Select sum(gets),sum(getmisses), round((1-(sum(getmisses)/sum(gets))) * 100,2) from v$rowcache;

Library Cache Miss Ratio :-

• Executions - The number of times a pin was requested for objects of this namespace.
• Cache misses - any pin of an object that is not the first pin performed since the object handle was created and which requires loading the object from disk.
• Hit ratio should be <>

Sql> Select sum(pins) executions,sum(reloads) cache_misses,sum(reloads) / sum(pins) miss_ratio from v$librarycache;

How Do you move table from sys tablespace to user tablespace :-

sql>alter table tablename move tablespace tablespacename;

Finding out Table and Tablespace Location :-

sql>select owner,table_name,tablespace_name from dba_tables where table_name='tablename';


How many memory layers are in the shared pool?

Shared poll consists of two layers.

• Library Cache ---- parsed sql staments and cursor information its a symmentric check.
• Data Dictionary Cache --- user account,privilege,segments,datafile and extend information stored in the data dictionary cache.

Users Privilege :-

sql>create user azar idenitified by azar;

or

sql>create user azar idenitified by azar default tablespace temporary tablespace quota unlimited on password expire;

sql>grant create session,create table to azar;

or

sql>Grant connect,resource to azar;

sql>show user;
azar

sql>alter user azar account lock;

sql>alter user azar account unlock;

Password Change

sql>alter user azar identified by ;


sql>drop user azar cascade;


System Privilege

sql>Grant create any table to azar;

Revoke Privilege

sql>revoke create any table from azar;

View :- dba_sys_privs

sql>select * from dba_sys_privs where grantee= 'username';


Object Privilege :

sql>Grant select,update,insert,delete on to azar;

sql>Revoke select,update,insert,delete on from azar;

View :- dba_tab_privs


sql>grant dba to azar;

sql>revoke dba from azar;

sql>select * from dba_role_privs where grantee ='dba';

sql>select role from session_roles;

Tablespace :-

Logical

database ---- > tablespace --- > segment ---- > extent ---- > Oracle block

* An Oracle Database can be logically grouped into smaller logical areas of space Known as Tablespace

* Tablespace Consists of one or more datafiles.

* It consists of zero or more segments


Creating Tablespace

sql>create tablespace azar datafile '\oracle\oradata\azar01.dbf' size 20m;

Check your tablespace view dba_tablespaces

sql>select * from dba_tablespaces;

sql>alter tablespace azar read only;

Your tablespace in read only mode you can drop your tablespace contents.

sql>select tablespace_name,status from dba_tablespaces;

sql>alter tablespace azar read write;

sql>select tablespace_name,status from dba_tablespaces;

sql>alter tablespace azar offline;

sql>alter tablespace azar online;

Increase your size tablespace in three Ways :-

sql>alter tablespace azar datafile '\oracle\oradata\azar01.dbf' size 20m autoextend on;

sql>alter tablespace azar add datafile '\oracle\oradata\azar02.dbf' size 10m;

sql>alter database datafile '\oracle\oradata\azar01.dbf' resize 30m;

View:-

dba_data_files

dba_free_space

Moving tablespace from one location to another location

• Offline your tablespace
• Cut your datafile and paste datafile in target place
• sql>alter tablespace azar rename datafile '\oracle\oradata\azar01.dbf' to 'any path' ;
• Online your tablespace

Create temporary tablespace :-

Using for only sort operation

sql> Create temporary tablespace azardba tempfile '\oracle\oradata\temp02.dbf' size 2m;

If you want change your temporary tablespace to default tablespace

sql>alter database default temporay tablespace azardba;

View:-

dba_temp_files

v$tempfile

v$temp_space_header

• When a transaction commits
• Redolog buffer becomes one-third full
• More than a megabytes of changed record in the redo log buffer
• Before database writer writer to the datafile.