Oracle Database

Oracle SID and DB name

ORACLE_SID is used to distinguish this instance from other Oracle Database
instances that you may create later and run concurrently on the same host computer. 

The maximum number of characters for ORACLE_SID is 12, and only letters and
numeric digits are permitted. On some platforms, the SID is case-sensitive.

It is common practice to set the SID to be equal to the database
name. The maximum number of characters for the database name is
eight.

ORACLE_SID max characters = 12
db_name max characters = 8
------------------------------------------

select * from v$controlfile_record_section;

displays information about the control file record sections

For more details go to the below link:
https://docs.oracle.com/cd/B12037_01/server.101/b10755/dynviews_1050.htm


As soon as we created tablespace, the space will be occupied in full from the hard disk.

A tablespace managing extent info into its header is called Locally Managed Tablespace (LMT).  LMT is also called as self managing tablespace.

A tablespace managing extent info in data dictionary is called Dictionary Managed Tablespace (DMT).

Checkpoint in one word is called synchronization.

Database in mount mode means entire database is offline.

If datafile is empty we can decrease its size.

Swap is known as performance killer.


To view total db size:

select sum(bytes)/1024/1024 "mb" from dba_data_files;


To view free space by tablespace:

select tablespace_name, sum(bytes)/1024/1024 "mb"
from dba_free_space
group by tablespace_name;


How many extents are already allocated to a table ?

select segment_name, segment_type, block_id, bytes, blocks
from dba_extents
where segment_name = 'EMP'
and segment_type = 'TABLE';

select extent_id, block_id, blocks
from dba_extents
where segment_name='EMP';
0 144      8

This means starting from block id 144 its using total 8 blocks.


To see exactly which row is stored in which block:

select empno,
dbms_rowid.rowid_relative_fno(rowid) file#,
dbms_rowid.rowid_block_number(rowid) block#
from scott.emp;

     EMPNO      FILE#     BLOCK#
---------- ---------- ----------
      7369          4        151
      7499          4        151
      7521          4        151
      7566          4        151
      7654          4        151
      7698          4        151
      7782          4        151
      7788          4        151
      7839          4        151
      7844          4        151
      7876          4        151
      7900          4        151
      7902          4        151
      7934          4        151