Data Dictionary

Data Dictionary
During the database creation, oracle server creates additional object structures within the data files. These include
• Data dictionary tables (includes base table and data dictionary views)
• Dynamic performance tables
Data dictionary is a set of read only tables and views that record, verify, and provide information about its associated database.
Includes two types of objects
• Base tables
• Data dictionary views

Base tables
Created during the creation of database
Created using sql.bsq e.g ind$ stores index related information
Data dictionary views
Created using catalog.sql
Data dictionary views categories

Data dictionary consists of three main sets of static views
DBA_
What is in all the schemas. Generally these views are accessed by DBA. Any user granted ‘SELECT ANY TABLE’ system privilege can also access DBA prefixed views
ALL_
What the user can access
USER_
What is in the user’s schema

Dynamic Performance Views
Oracle server records the current database activity in the virtual tables called dynamic performance views. These virtual tables exist only when the database is running and is represented by V$.
Dynamic Performance Views record current database activity. These get information from memory and control files. These are dynamic because the information in these views is updated when the data is open and running.
Dynamic performance views are owned by ‘SYS’ user.

To get an overview of data dictionary tables DICTIONARY or its synonym DICT synonym can be used. To get columns data we can use DICT_COLUMNS

--The following gives the database name
select name "database_name" from v$database


--The following gives the instance name
select instance "Instance_name" from v$thread

--The following gives the used space in bytes
select sum(bytes) "used space in bytes" from dba_segments

--The following gives the used space giga bytes
select sum(bytes)/(1024*1024*1024) "used space in Giga Bytes" from dba_segments

--Free space in giga bytes
select sum(bytes)/(1024*1024*1024) "Free space in Giga Bytes" from dba_free_space

--Users and when were they got created
select username, created from dba_users

--Gives the data file and corresponding table space names
select * from v$datafile

Creation of tablespaces with non standard block size
The standard block size is the size of the default database block size which is specified by the parameter DB_BLOCK_SIZE. The other data block sizes that we create in the database are non standard block size.
When we create db_block_size of 8K or any size, oracle expects memory allocated to DB_CACHE_SIZE parameter. In the same manner, if we wanted to create a non standard block size of ‘n’ then oracle expects the memory size allocated to DB_CACHE_SIZE and DB_Nk_CAHCE_SIZE parameter where n is equal to the ‘n’ in the non standard block size.
Note: For ‘n’ the possible values are 2, 4, 6, 8, 16, 32 etc..

SQL> create tablespace test_ts
2 datafile 'd:\tablespacetest.dbf'
3 blocksize 16k;
create tablespace test_ts
*
ERROR at line 1:
ORA-29339: tablespace block size 16384 does not match configured block sizes

SQL> alter system set db_16k_cache_size=1M
SQL> /
System altered.

SQL> create tablespace test_ts
2 datafile 'd:\tablespacetest.dbf'
3 size 200k
4* blocksize 16k
SQL> /
Tablespace created.
Note: if we wish to create a non standard block size of 4k then there should be a memory allocated to DB_4K_CACHE_SIZE parameter. This rule applies to all the non standard block sizes.

SGA_TAREGET, Automatic Memory Management

SGA_TAREGET, Automatic Memory Management
SGA_TARGET is a database initialization parameter (introduced in Oracle 10g) that can be used for automatic SGA memory sizing. If SGA_TARGET parameter has some value assigned then oracle server goes for automatic memory management.
Below, sga_target has some value set for it. So oracle goes for automatic memory management. In this mode oracle takes care of the allocation of memory for the below parameters.
DB_CACHE_SIZE (DEFAULT buffer pool)
SHARED_POOL_SIZE (Shared Pool)
LARGE_POOL_SIZE (Large Pool)
JAVA_POOL_SIZE (Java Pool)

SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- -----------------
sga_target big integer 276M

when database is in automatic memory management mode the parameters (db_cache_size, shared_pool_size, large_pool_size and java_pool_size) are set to zero. It does not mean they are actually zero.
In automatic memory management , - the __ (underscore/underscore) settings are placed in the parameter file to remember their last setting - the last value we decided was right - so that when restart, start where we left off
But when we query like,

SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ----------- --------------------------
db_cache_size big integer 0
SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ----------- --------------------------
shared_pool_size big integer 0

We get zeros since there is no set for any of the cache sizes - the default MINIMUM size is specified - and that is zero. When using automatic memory management - the *_cache sizes are MINIMUMS and the __ are our reminders of the last size we decided to use.