sql for dbaspresentation

SQL For DBAsDevelopers and Users will learn something as well!

Daniel W. Fink

Overview♦ Where is the information

– Data Dictionary views & tables♦ How can I extract it?

– SQL, functions♦ How can I use it?

– SQL*Plus to build and execute the script

Method♦ Build a script that generates the ‘create

tablespace’ commands for the database♦ Not all options considered

– AUTOEXTEND– MINEXTENT– ONLINE/OFFLINE– *I have to leave something for you to add…

♦ Not all tablespaces– Enough to demonstrate likely permutations

Command♦ Rules

– Tablespace can be TEMPORARY– TEMPORARY tablespace can use DATA files

or TEMP files– A single tablespace can have multiple data files

♦ Command structureCREATE [TEMPORARY] TABLESPACE tablespacename

[TEMPORARY]

{DATAFILE|TEMPFILE} filename filesize

EXTENT MANAGEMENT [DICTIONARY|LOCAL

[{AUTOALLOCATE|UNIFORM} [size {K|M}]]}]

Oracle’s Data Dictionary♦ Access via Views

– 1390 in 8i– 1820 in 9i

♦ DBA_ - Everything in database ♦ ALL_ - What you can see♦ USER_ What you own♦ Other – Miscellaneous views

Where does it come from?♦ Views of underlying base objects♦ Provides level of abstraction

– Easy to understand– Hides translations and calculations– READ ONLY!

View Textselect ts.name,

ts.blocksize * ts.dflinit,

decode(bitand(ts.flags, 3), 1, to_number(NULL),

ts.blocksize*ts.dflincr),

ts.dflminext,

decode(ts.contents$, 1, to_number(NULL), ts.dflmaxext),

decode(bitand(ts.flags, 3), 1, to_number(NULL),

ts.dflextpct),

ts.blocksize * ts.dflminlen,

decode(ts.online$,1,'ONLINE',2,'OFFLINE',

4,'READ ONLY','UNDEFINED'),

decode(ts.contents$, 0, 'PERMANENT', 1, 'TEMPORARY'),

decode(ts.dflogging, 0, 'NOLOGGING', 1, 'LOGGING'),

decode(ts.bitmapped, 0, 'DICTIONARY', 'LOCAL'),

decode(bitand(ts.flags,3),0,'USER',1,'SYSTEM',2,'UNIFORM',

'UNDEFINED'),

decode(ts.plugged, 0, 'NO', 'YES')

from sys.ts$ ts

where ts.online$ != 3

ColumnsTABLESPACE_NAME

INITIAL_EXTENT

NEXT_EXTENT

MIN_EXTENTS

MAX_EXTENTS

PCT_INCREASE

MIN_EXTLEN

STATUS

CONTENTS

LOGGING

EXTENT_MANAGEMENT

ALLOCATION_TYPE

PLUGGED_IN

Where can I find more information?♦ Oracle Documentation – Server Reference

Guide♦ DBA_VIEWS

– text contains the actual text of the view– SET LONG 10000 in order to see all the text

♦ catalog.sql♦ sql.bsq

Notes on Data Dictionary♦ ID

– COLUMN_ID starts at 1– EXTENT_ID starts at 0

♦ Not All DBA_* views have ALL_* and/or USER_* counterparts

♦ DBA_EXTENTS and USER_EXTENTS have different information– USER_EXTENTS does not contain file information

♦ DBA_TAB_COLUMNS– Not just table columns, also views, clusters

♦ DBA_IND_COLUMNS– Reverse Key Indexes have function name instead of table column

name for leading column

Quick Tour of the Data Dictionary

Tables/ViewsDBA_TABLES

DBA_TAB_COLUMNS

DBA_TAB_COL_STATISTICS

DBA_TAB_HISTOGRAMS

DBA_PART_TABLES

DBA_TAB_PARTITIONS

DBA_TAB_SUBPARTITIONS

DBA_CONSTRAINTS

DBA_CONS_COLUMNS

DBA_VIEWS

StorageDBA_TABLESPACES

DBA_DATA_FILES

DBA_TEMP_FILES

DBA_SEGMENTS

DBA_EXTENTS

DBA_ROLLBACK_SEGS

DBA_FREE_SPACEUsersDBA_USERS

DBA_ROLES

DBA_ROLE_PRIVS

DBA_TAB_PRIVS

DBA_SYS_PRIVS

DBA_PROFILES

IndexesDBA_INDEXES

DBA_IND_COLUMNS

DBA_IND_PARTITIONS

DBA_IND_SUBPARTITIONS

DBA_PART_INDEXES

Data Dictionary views for script♦ DBA_TABLESPACES

– All tablespaces in database♦ DBA_DATAFILES

– All DATAFILEs for all tablespaces♦ DBA_TEMPFILES

– *New in Oracle 8i– Identifies TEMPFILES created by command

“create temporary tablespace”

Using SQL to Write SQL♦ Joins♦ Unions♦ Functions♦ Sorting

Joins♦ A relationship using common data (natural or

derived) must exist♦ Inner

– If a related row exists, return both main and related row– If a related row does not exist, do not return the main

row

♦ Outer– If a related row exists, return both main and related row– If a related row does not exist, return main row and null

values for related row

Joinselect t.tablespace_name,

d.file_name

from dba_tablespaces t,

dba_data_files d

where d.tablespace_name = t.tablespace_name;

TABLESPACE_NAME FILE_NAME

--------------- ---------------------------------

SYSTEM C:\ORADATA\ORA817\SYSTEM01.DBF

TOOLS C:\ORADATA\ORA817\TOOLS01.DBF

TOOLS C:\ORADATA\ORA817\TOOLS02.DBF

TEMP2 C:\ORADATA\ORA817\TEMP201.DBF

Union♦ Set Operation♦ Used to combine sets of data

– Joins & Relationships are not used♦ Columns must be same number and datatype

– A literal expression can be used♦ Returns one row for each distinct set of values

– UNION ALL will return duplicates

select statement1

unionselect statement2

File List♦ File names are listed in

– DBA_DATA_FILES (10 rows)– DBA_TEMP_FILES (1 row)

♦ Common Data?– Both contain tablespace_name…

File Listselect d.file_name, t.file_name

from dba_data_files d,

dba_temp_files t


no rows selected

♦ Why?– Tablespaces use either data files or temp

files…not both– Even an outer join will miss rows

File List

select file_name

from dba_data_files

union

select file_name

from dba_temp_files;

FILE_NAME

------------------------------

C:\ORADATA\ORA817\RBS01.DBF

C:\ORADATA\ORA817\SYSTEM01.DBF

C:\ORADATA\ORA817\TEMP01.DBF


C:\ORADATA\ORA817\TOOLS01.DBF


Tablespace/File List

select tablespace_name

from dba_tablespaces

union

select file_name

from dba_data_files

union

select file_name

from dba_temp_files;

TABLESPACE_NAME

------------------------------------

C:\ORADATA\ORA817\SYSTEM01.DBF





SYSTEM

TEMP

TEMP2

TOOLS

Functions♦ Concatenation

– Combine 2 or more strings together♦ DECODE/CASE

– Conditional display/decision-making♦ TO_CHAR

– Converts a number or date to a character string♦ CHR

– Displays the ASCII character♦ ASCII

– Displays the ASCII number

Concatenation♦ Combine two or more strings together

– Remember to add blanks♦ CREATE TABLESPACE <ts_name>

CONCAT(‘CREATE TABLESPACE ’,tablespace_name)

‘CREATE TABLESPACE ’||tablespace_name

DECODE/CASE♦ Rudimentary decision making function♦ DECODE available in 7/8/9♦ CASE available from 8.1.6+♦ Use to determine

– Type of tablespace– Extent Management– Datafile separation

DECODE♦ DECODE(expression,

comparison1, return1,comparison2, return2,…default)

♦ Can only use equality comparison

DECODEIf DBA_TABLESPACES.CONTENTS = ‘TEMPORARY’ display

‘TEMPORARY’ELSE IF DBA_TABLESPACES.CONTENTS = ‘PERMANENT’ display NULLELSE display NULL

select ‘CREATE ‘||decode(contents,

‘TEMPORARY’,‘TEMPORARY ’,

‘PERMANENT’,NULL,NULL)||

‘TABLESPACE ‘||tablespace_name

from dba_tablespaces;

CREATE TABLESPACE SYSTEM

CREATE TEMPORARY TABLESPACE TEMP

CREATE TEMPORARY TABLESPACE TEMP2

CREATE TABLESPACE TOOLS

CASE♦ Introduced in 8.1.6♦ Able to return based on variable conditions

CASE WHEN condition1 THEN result1WHEN condition2 THEN result2

…ELSE default

CASEselect ‘CREATE ‘||

CASE WHEN contents=‘TEMPORARY’ THEN ‘TEMPORARY ’

WHEN contents=‘PERMANENT’ THEN NULL

ELSE NULL

END||’TABLESPACE ‘||tablespace_name

from dba_tablespaces;





♦ Permanent = NULL not currently needed, but included for example

Character String Conversion♦ TO_CHAR([date|number]{,format string})

– Converts numbers or dates to character string– Uses format codes to change display

♦ TO_CHAR(number)– Does not require format string– Oracle will also convert numeric string to number– If you are not formatting, Oracle will automatically

convert to string…UNLESS it is used in a UNION

Character String Conversionselect 12345||’ is a number’ from dual;

12334 is a number

select ‘12345’-10 from dual;

12335

select 12345 from dual

union

select ‘is a number’ from dual;

ORA-01790: expression must have same datatype ascorresponding expression

CHR♦ CHR is used to ‘print’ special characters

– CHR(<ascii_number>)♦ ASCII can be used to determine the number

of the character– ASCII(‘<character>’)

♦ Data files in statement must be enclosed in single tic (‘)– ‘ is a character and can be enclosed in ‘ as a character

string– but I can never remember how many I need

(I think it’s 4…or is it 3…What if I am using it to end a string?)

♦ CHR(39) prints a single ‘

Single Tic

Single Tic

SQL> select ’’’’||file_name||’’’’2 from dba_data_files;

'C:\ORADATA\ORA817\SYSTEM01.DBF''C:\ORADATA\ORA817\TOOLS01.DBF'

SQL> select chr(39)||file_name||chr(39)

2 from dba_data_files;

'C:\ORADATA\ORA817\SYSTEM01.DBF'

'C:\ORADATA\ORA817\TOOLS01.DBF'

New Line♦ CHR(10) ‘prints’ a newline character

– Able to output single row of data on multiple lines

select ‘Line 1’||chr(10)||’Line 2’

from dual;

Line 1

Line 2

CREATE TABLESPACEselect ‘CREATE TABLESPACE ‘||

t.tablespace_name||chr(10)||

‘DATAFILE ‘||chr(39)||

d.file_name||chr(39)

from dba_tablespaces t,

dba_data_files d


CREATE TABLESPACE ErrorCREATE TABLESPACE SYSTEM

DATAFILE 'C:\ORADATA\ORA817\SYSTEM01.DBF'


DATAFILE 'C:\ORADATA\ORA817\TOOLS01.DBF'


DATAFILE 'C:\ORADATA\ORA817\TOOLS02.DBF'

CREATE TABLESPACE TEMP2

DATAFILE 'C:\ORADATA\ORA817\TEMP201.DBF‘

♦ 2nd ‘CREATE TABLESPACE TOOLS’ statement will fail…how do we work around?

UNION♦ UNION of DBA_TABLESPACES,

DBA_DATA_FILES, DBA_TEMP_FILES♦ Each SQL statement is executed

independently, then the results are combined

♦ Output will be sorted, in column order♦ ORDER BY must be last line

UNIONselect 'CREATE '||decode(contents, 'TEMPORARY', 'TEMPORARY ', NULL)||

’TABLESPACE ‘||tablespace_name

from dba_tablespacesunionselect 'DATAFILE '||chr(39)||file_name||chr(39)||' SIZE

'||to_char(bytes/1024)||'K'from dba_data_filesunionselect 'TEMPFILE '||chr(39)||file_name||chr(39)||' SIZE

'||to_char(bytes/1024)||'K'from dba_temp_files;

CREATE TABLESPACE SYSTEMCREATE TEMPORARY TABLESPACE TEMPCREATE TEMPORARY TABLESPACE TEMP2

CREATE TABLESPACE TOOLSDATAFILE 'C:\ORADATA\ORA817\SYSTEM01.DBF' SIZE 280576KDATAFILE 'C:\ORADATA\ORA817\TEMP201.DBF' SIZE 102400KDATAFILE 'C:\ORADATA\ORA817\TOOLS01.DBF' SIZE 12288KDATAFILE 'C:\ORADATA\ORA817\TOOLS02.DBF' SIZE 102400KTEMPFILE 'C:\ORADATA\ORA817\TEMP01.DBF' SIZE 204800K

Errata♦ Tablespace to File relationship correct, but not in

proper order for SQL statement– Proper order of lines in command is missing– We will use columns to sort…but suppress the printing

♦ Still missing storage/extent management clauses…– Later addition…don’t want to clutter up the statements

just yet

♦ Statement contains an error

SQL*Plus Commands♦ COLUMN

– noprint♦ SET

– sqlprompt– pagesize– feedback

COLUMN♦ The CREATE TABLESPACE line needs to

be associated with DATAFILE line♦ COLUMN command can be used to

suppress printing– COLUMN column_name NOPRINT– Size of column will be factored in to

LINESIZE

COLUMNselect tablespace_name ts_name,'CREATE '||

decode(contents, 'TEMPORARY', 'TEMPORARY ',NULL)||’TABLESPACE ‘||tablespace_name

sql_commandfrom dba_tablespacesunionselect tablespace_name ts_name, 'DATAFILE '||chr(39)||

file_name||chr(39)||' SIZE '||to_char(bytes/1024)||'K' sql_command

from dba_data_filesunionselect tablespace_name ts_name,'TEMPFILE '||chr(39)||file_name

||chr(39)||' SIZE '||to_char(bytes/1024)||'K' sql_commandfrom dba_temp_files;

COLUMN

TS_NAME SQL_COMMAND

------- -------------------------------------------------------

SYSTEM CREATE TABLESPACE SYSTEM

SYSTEM DATAFILE 'C:\ORADATA\ORA817\SYSTEM01.DBF' SIZE 280576K

TEMP CREATE TEMPORARY TABLESPACE TEMP

TEMP TEMPFILE 'C:\ORADATA\ORA817\TEMP01.DBF' SIZE 204800K

TEMP2 CREATE TEMPORARY TABLESPACE TEMP2

TEMP2 DATAFILE 'C:\ORADATA\ORA817\TEMP201.DBF' SIZE 102400K

TOOLS CREATE TABLESPACE TOOLS

TOOLS DATAFILE 'C:\ORADATA\ORA817\TOOLS01.DBF' SIZE 12288K

TOOLS DATAFILE 'C:\ORADATA\ORA817\TOOLS02.DBF' SIZE 102400K

COLUMN Formatting

SQL> COLUMN ts_name NOPRINT

SQL> /

SQL_COMMAND

-------------------------------------------------------


DATAFILE 'C:\ORADATA\ORA817\SYSTEM01.DBF' SIZE 280576K


TEMPFILE 'C:\ORADATA\ORA817\TEMP01.DBF' SIZE 204800K


DATAFILE 'C:\ORADATA\ORA817\TEMP201.DBF' SIZE 102400K


DATAFILE 'C:\ORADATA\ORA817\TOOLS01.DBF' SIZE 12288K


Formatting Output♦ Supress all non-sql statement strings♦ SET

– PAGESIZE 0 – suppress all breaks, headings, etc.

– FEEDBACK OFF – suppress ’10 Rows Returned’ message

– SQLPROMPT “” – removes SQL> prompt• Be careful…it may look like the command hangs

Formatting Output

SQL> col ts_name noprintSQL> set pagesize 0 feedback off sqlprompt ""/CREATE TABLESPACE SYSTEMDATAFILE 'C:\ORADATA\ORA817\SYSTEM01.DBF' SIZE 280576KCREATE TEMPORARY TABLESPACE TEMPTEMPFILE 'C:\ORADATA\ORA817\TEMP01.DBF' SIZE 204800KCREATE TEMPORARY TABLESPACE TEMP2DATAFILE 'C:\ORADATA\ORA817\TEMP201.DBF' SIZE 102400KCREATE TABLESPACE TOOLSDATAFILE 'C:\ORADATA\ORA817\TOOLS01.DBF' SIZE 12288KDATAFILE 'C:\ORADATA\ORA817\TOOLS02.DBF' SIZE 102400K

OOPS♦ DBA_TABLESPACES.CONTENTS lists

contents, but not type of file…– Each command creates a TEMPORARY

tablespace, but the file specs are not the same!CREATE TABLESPACE TEMPORARY <name> TEMPFILE <name> <specs>

CREATE TABLESPACE <name> TEMPORARY DATAFILE <name> <specs>

♦ Multiple data or temp files must be separated by a comma

Proper Syntax for TEMPORARYselect tablespace_name ts_name,

'CREATE TABLESPACE '||tablespace_name||

decode(contents,'TEMPORARY',' TEMPORARY',NULL) sql_commandfrom dba_tablespaceswhere tablespace_name in (select tablespace_name from dba_data_files)unionselect tablespace_name ts_name,

'CREATE '||decode(contents,'TEMPORARY','TEMPORARY ',NULL)||

’TABLESPACE ‘|| tablespace_name sql_commandfrom dba_tablespaceswhere tablespace_name in (select tablespace_name from dba_temp_files)unionselect tablespace_name ts_name,

'DATAFILE '||chr(39)||file_name||chr(39)||' SIZE '||

to_char(bytes/1024)||'K' sql_commandfrom dba_data_filesunionselect tablespace_name ts_name,

'TEMPFILE '||chr(39)||file_name||chr(39)||' SIZE '||to_char(bytes/1024)||'K' sql_command

from dba_temp_files

Proper Syntax for TEMPORARYCREATE TABLESPACE SYSTEM

DATAFILE 'C:\ORADATA\ORA817\SYSTEM01.DBF' SIZE 280576K



CREATE TABLESPACE TEMP2 TEMPORARY





Proper Syntax for Multiple FilesCREATE TABLESPACE <tsname>

DATAFILE <filename> <filespecs>,

<filename> <filespecs>

♦ Where can the comma go?– After the file specs OR– Before the next file name

• Logic easier for 2nd choice

♦ File order is not important– Each file has a numeric ID– Skip the file with the lowest ID

Proper Syntax for Multiple Files

♦ Add file_id as a column and sort by it♦ While we are at it…add sql_sort column to

insure proper order of statements♦ Current Sort Order

– tablespace_name– ts_sort (each part of union has a new value)– sql_sort (file_id or derived value)

Proper Syntax for Multiple Filesselect tablespace_name ts_name, 0 ts_sort, 0 sql_sort,

<create tablespace line>

union

select tablespace_name ts_name, 1 ts_sort, file_id sql_sort

<datafile line>

union

select tablespace_name ts_name, 1 ts_sort, file_id sql_sort

<tempfile line>

union

select tablespace_name ts_name, 2 ts_sort, 0 sql_sort

<storage line>

order by ts_name, ts_sort, sql_sort;

Multiple Filesselect df.tablespace_name ts_name, 1 ts_sort, file_id

sql_sort,

decode(df.file_id,tfi.min_file_id,'DATAFILE ',

', ')||chr(39)||df.file_name||chr(39)||

' SIZE '||to_char(df.bytes/1024)||'K'sql_command

from dba_data_files df,

(select tablespace_name t_name, min(file_id)min_file_id

from dba_data_files

group by tablespace_name) tfi

where df.tablespace_name = tfi.t_name

and tablespace_name != 'SYSTEM'

♦ Because we are sorting on file_id, we know that the ‘first’ file will be the first displayed

Proper Output (with columns)TS_NAME TS SQL SQL_COMMAND

------- ---- ----- ------------------------------------------------------------

TEMP 0 0 CREATE TEMPORARY TABLESPACE TEMP

TEMP 1 1 TEMPFILE 'C:\ORADATA\ORA817\TEMP01.DBF' SIZE 204800K

TEMP2 0 0 CREATE TABLESPACE TEMP2 TEMPORARY

TEMP2 1 4 DATAFILE 'C:\ORADATA\ORA817\TEMP201.DBF' SIZE 102400K

TOOLS 0 0 CREATE TABLESPACE TOOLS

TOOLS 1 5 DATAFILE 'C:\ORADATA\ORA817\TOOLS01.DBF' SIZE 12288K

TOOLS 1 10 , 'C:\ORADATA\ORA817\TOOLS02.DBF' SIZE 102400K

Proper Output (without columns)CREATE TEMPORARY TABLESPACE TEMP


CREATE TABLESPACE TEMP2 TEMPORARY




, 'C:\ORADATA\ORA817\TOOLS02.DBF' SIZE 102400K

What’s Next?♦ Extent Management & Storage Clause

– Dictionary Extent Management is default, no need to specify

– DEFAULT STORAGE and LOCAL extent management are mutually exclusive

♦ Interesting notes…– Not all Tablespaces have NEXT defined– Not all Tablespaces have MAXEXTENTS defined– What else may be NULL?

Extent Management & Storage

♦ Extent Management can be– LOCAL

• AUTOALLOCATE• UNIFORM SIZE

– DICTIONARY• default…no need to specify

Dictionary Extent ManagementCASE WHEN ts.extent_management = 'DICTIONARY'

THEN 'DEFAULT STORAGE ('||

decode(ts.initial_extent, NULL, NULL,

' INITIAL '||to_char(ts.initial_extent/1024)||'K')||

decode(ts.next_extent, NULL, NULL,

' NEXT'||to_char(ts.next_extent/1024)||'K')||

decode(ts.min_extents, NULL, NULL,

' MINEXTENTS '||to_char(ts.min_extents))||

decode(ts.max_extents, NULL, NULL,

' MAXEXTENTS '||to_char(ts.max_extents))||

decode(ts.pct_increase, NULL, NULL,

' PCTINCREASE'||to_char(ts.pct_increase))||');‘

Local Extent Management

♦ Allocation type and the command are not the same!

WHEN ts.extent_management = 'LOCAL'

THEN 'EXTENT MANAGEMENT LOCAL '||

CASE WHEN ts.allocation_type = 'SYSTEM'

THEN 'AUTOALLOCATE;'

WHEN ts.allocation_type = 'UNIFORM'

THEN 'UNIFORM SIZE '||

to_char(ts.initial_extent/1024)||'K;'

END

Storage Clause/Extent ManagmentOEM_REPOSITORY EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

TEMP EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10240K;

TEMP2 DEFAULT STORAGE ( INITIAL 40K

NEXT 40K

MINEXTENTS 1

PCTINCREASE 50);

TOOLS DEFAULT STORAGE ( INITIAL 32K

NEXT 32K

MINEXTENTS 1

MAXEXTENTS 4096

PCTINCREASE 0);

Spooling Output♦ SPOOL <filename>♦ SPOOL OFF♦ SPOOL command will overwrite the file if

is exists…and not even ask you “Are You Sure?”– be careful not to SPOOL

<sql_filename>…you’ll lose your work

Complete Script

♦ Complete script is attached at the end♦ Has been tested in 8.1.7 and 9.0.1 on

Win2K♦ Still room for improvement and missing

clauses…– Same script was very simple in 7.3!

Notes & Addendum♦ Formatting not always exact

– Especially Line Spacing♦ Verify all scripts with new versions and

features– Data Dictionary structure and data can change

between releases

sql for dbaspresentation

Documents

tablespaces dba

columns dba

indexes dba

privs dba

database dba

rows dba

script dba

subpartitions dba