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.

Different ways to delete duplicate rows in a table

CREATE TABLE duplicate_test(name VAR CHAR2(20), age NUMBER);

begin
INSERT INTO duplicate_test VALUES('srik',20);
INSERT INTO duplicate_test VALUES('srik',20);
INSERT INTO duplicate_test VALUES('sr',22);
INSERT INTO duplicate_test VALUES('sr',22);
INSERT INTO duplicate_test VALUES('ik',24);
INSERT INTO duplicate_test VALUES('ik',24)
end;
/

way1:

DECLARE
CURSOR c1
IS
SELECT DISTINCT *
FROM duplicate_test;
r1 c1%ROWTYPE;
BEGIN
OPEN c1;
DELETE FROM duplicate_test;
FETCH c1
INTO r1;
WHILE c1%FOUND
LOOP
INSERT INTO duplicate_test
VALUES r1;
FETCH c1
INTO r1;
END LOOP;
COMMIT;
END;
/

Way2:
CREATE TABLE duplicate_test(age NUMBER);

INSERT INTO duplicate_test VALUES(20)
INSERT INTO duplicate_test VALUES(20)
INSERT INTO duplicate_test VALUES(22)
INSERT INTO duplicate_test VALUES(22)

CREATE OR REPLACE TYPE ntable IS TABLE OF NUMBER;

DECLARE
t1 ntable;
BEGIN
SELECT age BULK COLLECT INTO t1 FROM duplicate_test;
t1 := SET(t1);
DELETE FROM duplicate_test;
INSERT INTO duplicate_Test SELECT * FROM TABLE(t1);
END;

Way 3:
Using the flashback query

Way 4:
Using the distinct key word

Way 5:
Using the rowid

Function to find the number of times a pattern is repeated.

CREATE FUNCTION pattern_count(INSTRING VARCHAR2, INPATTERN VARCHAR2)

RETURN NUMBER

IS

COUNTER NUMBER;

NEXT_INDEX NUMBER;

STRING VARCHAR2(2000);

PATTERN VARCHAR2(2000);

BEGIN

COUNTER := 0;

NEXT_INDEX := 1;

STRING := LOWER(INSTRING);

PATTERN := LOWER(INPATTERN);

FOR I IN 1 .. LENGTH(STRING) LOOP

IF (LENGTH(PATTERN) <= LENGTH(STRING)-NEXT_INDEX+1)

AND (SUBSTR(STRING,NEXT_INDEX,LENGTH(PATTERN)) = PATTERN) THEN

COUNTER := COUNTER+1;

END IF;

NEXT_INDEX := NEXT_INDEX+1;

END LOOP;

RETURN COUNTER;

END;

Performance Views:

v$sql the details -if you have multiple copies of the query:
"select * from T" in shared pool, v$sql will have a row per query. This can happen if user U1 and user U2 both have a table T and both issue "select * from T". Those are entirely different queries with different plans and so on. v$sql will have 2 rows.

v$sqlarea is a aggregate of v$sql. It selects out DISTINCT sql.
"select * from T" will appear there.

v$sqltext is simply a way to see the entire query.
The v$sql and v$sqlarea views only show the first 1000 bytes. newlines and other control characters are replace with whitespace.

v$sqltext_with_newlines is v$sqltext without the whitespace replacment.

Get the current sql query or sql query inside a plsql or plsql call that is being exceuted by a session:

SELECT sql_text
FROM v$session s, V$SQLTEXT T
WHERE s.sql_id = T.sql_id AND s.SID =
ORDER BY piece

This query does not show the plsql code that is being executed rather it will show the sql query inside the plsql block as plsql code is executed as a unit. It will show only the top block of the plsql code.