Basics Of Tablespace, Data files, Extents And Blocks

Understanding The Basic Terminologies: Tablespaces, Extents, and Blocks

An Oracle database can contain many files. But you don't directly choose which file to store your data in. Instead, you specify which tablespace to use. A tablespace is simply a logical grouping of files.

To discover the tablespaces in your database, query dba_tablespaces:

SELECT tablespace_name

FROM dba_tablespaces.

Some common tablespaces in Oracle databases include

SYSTEM for Oracle's meta data,

TEMP tablespace for temporary objects and

RBS for rollback segments .

To discover which files are used for each tablespace, execute the following SQL:

SELECT file_name,tablespace_name

FROM dba_data_files;

When you create a table in a tablespace, Oracle allocates a chunk of space for the object. Each chunk is called an extent. As you will see, each extent can vary in size. An extent is itself made up of a number of blocks, the smallest unit of space . So a tablespace is a grouping of files that stores data in extents, of which each is made up of a number of blocks. Now that you hopefully understand the basic terminology involved, let's look at the various decisions one makes when allocating space in Oracle.

Allocating Space for Data

As I said above, the smallest unit of space in Oracle is the block. You can determine the size you want when you create a database in Oracle. Once you've created the database, however, you cannot change this setting. The sample sizes that Oracle provides in its configuration file are 2K for a small database, 8K for a medium-sized one, and 16K for a large one. A row can span more than one block. So if a row in a table takes up 12K and you have chosen an 8K-block size, 8K of the row will be stored in one block and the remaining 4K will be stored in a separate block. This is called row chaining. As you can imagine, it is more effective to fit most of your rows in one block.

Each extent is made up of a number of blocks. As a table grows, the initial extent that Oracle allocated can be used up. Oracle then allocates another extent for the table which again is made up of a number of blocks. In Oracle, you can specify the size of the initial extent and all subsequent extents. Typically, you specify these settings when you create a tablespace. Tables will use the storage settings of the tablespace they are created in. If necessary, however, you can override these tablespace settings by specifying different settings in the CREATE TABLE statement itself.

Creating a Tablespace

Let's create a tablespace with the initails (default) size of extent as 256K to start. If a table needs more space, I'll allocate it in sizes of 500K increments. However, because this tablespace is for smaller tables, I'll set the maximum number of extents to 20. If a table needs more space than that, I'll move it to another tablespace that is built for larger tables.

CREATE TABLESPACE TECHAEON

datafile 'd:\tablespaces\appl_data01.dbf' SIZE 50M,

'd:\tablespaces\appl_data02.dbf' SIZE 50M

DEFAULT STORAGE

(

INITIAL 256K

NEXT 500K

PCTINCREASE 0

MAXEXTENTS 20

)

Locally Managed Tablespaces: Autoallocate And Uniform Clause:

On a locally managed tablespace, you cannot specify the default storage settings. Instead, you have two additional settings that can be used to simplify space allocation. If you specify AUTOALLOCATE, Oracle will automatically handle the sizing for objects. If you specify the UNIFORM SIZE clause, however, Oracle creates all extents in the locally managed tablespace using size you give it.

Here's an example of two tablespaces that I've created using the above options:

create tablespace appl_data

datafile 'd:\tablespaces\appl_data01.dbf' size 50M,

‘d:\tablespaces\appl_data.dbf' size 50M

extent management local uniform size 512K

create tablespace large_acctdata

datafile 'd:\tablespaces\appl_data01.dbf' size 50M,

‘d:\tablespaces\appl_data.dbf' size 50M

extent management local autoallocate;

To see the settings on your tablespaces, you can execute the following query:

select tablespace_name, initial_extent,next_extent,

min_extents, max_extents, pct_increase,

extent_management,allocation_type

from dba_tablespaces

Purity Levels Of Function - PRAGMA RESTRICT_REFERENCES

An Oracle database cannot determine the work done by a packaged function when the function is executed from inside a DML statement. Therefore, if packaged functions are to be executed from within a DML statement, developers must use a PRAGMA to define a purity level for functions defined as part of the package spec. A PRAGMA is a compiler directive that instructs the compiler to handle code in a specific manner. To define a purity level for a packaged function, the PRAGMA RESTRICT_REFERENCES is used.

A purity level defined within a package spec instructs Oracle about the kinds of operations that the function performs. Below are the four purity levels that can be defined for a function.

WNDS	Write No Database State. The function doesn’t alter the contents of any database table.
RNDS	Read No Database State. The function doesn’t read the contents of any database table.
WNPS	Write No Package State. The function doesn’t alter any variables within another package.
RNPS	Read No Package State. The function doesn’t read any variables within another package.

The following example shows, how a purity level could mentioned for a function, while defining the package specification. The example makes the function get_id to the WNDS purity level.

CREATE OR REPLACE PACKAGE P_TEST

FUNCTION GET_ID RETURN NUMBER;

PRAGME RESTRICT_REFERENCES(GET_ID,WNDS);

END;

Note: The purity level for the functions can also be defined for the functions used in an Oracle object type.

Oracle - USER_JOBS Data Dictionary

The USER_JOBS Data Dictionary provides the details of all the jobs scheduled by the user. The following columns form the USER_JOBS table.

    JOB

      Identifier of job. Neither import/export nor repeated executions change it.

   LOG_USER

      USER who was logged in when the job was submitted

   PRIV_USER

      USER whose default privileges apply to this job

   SCHEMA_USER

      select * from bar means select * from schema_user.bar

   LAST_DATE

      Date that this job last successfully executed

   LAST_SEC

      Same as LAST_DATE. This is when the last successful execution started.

   THIS_DATE

      Date that this job started executing (usually null if not executing)

   THIS_SEC

      Same as THIS_DATE. This is when the last successful execution started.

   NEXT_DATE

      Date that this job will next be executed

   NEXT_SEC

      Same as NEXT_DATE. The job becomes due for execution at this time.

   TOTAL_TIME

      Total wallclock time spent by the system on this job,in seconds

   BROKEN

      If Y,no attempt is being made to run this job.Will be ‘N’ for running jobs.

   INTERVAL

      A date function

   FAILURES

      How many times has this job started and failed since its last success?

   WHAT

      Body of the anonymous PL/SQL block that this job executes

   NLS_ENV

      alter session parameters describing the NLS environment of the job

   MISC_ENV

      a versioned raw maintained by the kernel

   INSTANCE

      Instance number restricted to run the job

Oracle - PL/SQL - ASCIISTR Function

In Oracle/PLSQL, the asciistr function converts a string in any character set to an ASCII string using the database character set.

The syntax for the asciistr function is:

asciistr( string )

string is a string in any character set that you want converted to an ASCII string in the database character set.

Applies To:

• Oracle 9i, Oracle 10g, Oracle 11g

For example:

asciistr('A B C Ä Ê')	would return 'A B C \00C4 \00CA'
asciistr('A B C Õ Ø')	would return 'A B C \00D5 \00D8'
asciistr('A B C Ä Ê Í Õ Ø')	would return 'A B C \00C4 \00CA \00CD \00D5 \00D8'

Oracle - PL/SQL - ASCII Function

In Oracle/PLSQL, the ascii function returns the NUMBER code that represents the specified character.

The syntax for the ascii function is:

ascii( single_character )

single_character is the specified character to retrieve the NUMBER code for. If more than one character is entered, the function will return the value for the first character and ignore all of the characters after the first.

Applies To:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:

ascii('t')	would return 116.
ascii('T')	would return 84.
ascii('T2')	would also return 84.

ORACLE - ASCII Table - A Quick reference

Dec Hex Oct Char Description 0 0 000 null 1 1 001 start of heading 2 2 002 start of text 3 3 003 end of text 4 4 004 end of transmission 5 5 005 enquiry 6 6 006 acknowledge 7 7 007 bell 8 8 010 backspace 9 9 011 horizontal tab 10 A 012 new line 11 B 013 vertical tab 12 C 014 new page 13 D 015 carriage return 14 E 016 shift out 15 F 017 shift in 16 10 020 data link escape 17 11 021 device control 1 18 12 022 device control 2 19 13 023 device control 3 20 14 024 device control 4 21 15 025 negative acknowledge 22 16 026 synchronous idle 23 17 027 end of trans. block 24 18 030 cancel 25 19 031 end of medium 26 1A 032 substitute 27 1B 033 escape 28 1C 034 file separator 29 1D 035 group separator 30 1E 036 record separator 31 1F 037 unit separator 32 20 040 space 33 21 041 ! 34 22 042 " 35 23 043 # 36 24 044 $ 37 25 045 % 38 26 046 & 39 27 047 ' 40 28 050 ( 41 29 051 ) 42 2A 052 * 43 2B 053 + 44 2C 054 , 45 2D 055 - 46 2E 056 . 47 2F 057 / 48 30 060 0 49 31 061 1 50 32 062 2 51 33 063 3 52 34 064 4 53 35 065 5 54 36 066 6 55 37 067 7 56 38 070 8 57 39 071 9 58 3A 072 : 59 3B 073 ; 60 3C 074 < 61 3D 075 = 62 3E 076 > 63 3F 077 ?

Dec Hex Oct Char 64 40 100 @ 65 41 101 A 66 42 102 B 67 43 103 C 68 44 104 D 69 45 105 E 70 46 106 F 71 47 107 G 72 48 110 H 73 49 111 I 74 4A 112 J 75 4B 113 K 76 4C 114 L 77 4D 115 M 78 4E 116 N 79 4F 117 O 80 50 120 P 81 51 121 Q 82 52 122 R 83 53 123 S 84 54 124 T 85 55 125 U 86 56 126 V 87 57 127 W 88 58 130 X 89 59 131 Y 90 5A 132 Z 91 5B 133 [ 92 5C 134 \ 93 5D 135 ] 94 5E 136 ^ 95 5F 137 _ 96 60 140 ` 97 61 141 a 98 62 142 b 99 63 143 c 100 64 144 d 101 65 145 e 102 66 146 f 103 67 147 g 104 68 150 h 105 69 151 i 106 6A 152 j 107 6B 153 k 108 6C 154 l 109 6D 155 m 110 6E 156 n 111 6F 157 o 112 70 160 p 113 71 161 q 114 72 162 r 115 73 163 s 116 74 164 t 117 75 165 u 118 76 166 v 119 77 167 w 120 78 170 x 121 79 171 y 122 7A 172 z 123 7B 173 { 124 7C 174 125 7D 175 } 126 7E 176 ~ 127 7F 177 DEL