1. Multitable Insert command:
Multi-table insert is a new feature of Oracle 9i Release 1 (9.0). An extension to INSERT..SELECT, this feature enables us to define multiple insert targets for a source dataset. Until the introduction of this feature, only SQL*Loader had a similar capability.
This article provides an overview of multi-table inserts and how they are used.
syntax overview
There are two types of multi-table insert as follows:
INSERT FIRST
INSERT ALL
Multi-table inserts are an extension to INSERT..SELECT. Syntax is of the following form:
INSERT ALL|FIRST
[WHEN condition THEN] INTO target [VALUES]
[WHEN condition THEN] INTO target [VALUES]
...
[ELSE] INTO target [VALUES]
SELECT ...
FROM source_query;
Example:
INSERT
WHEN MOD(deptno,2)=0 THEN
INTO even_employees (empno, ename) VALUES (empno, ename)
WHEN MOD(deptno,2)=1 THEN
INTO uneven_employees (empno, ename) VALUES (empno, ename)
ELSE
INTO unknow_employees (empno, ename) VALUES (empno, ename)
SELECT empno, ename, deptno FROM emp;
2. Regular Expressions:
Oracle 10g introduced regular expression functions in SQL with the functions REGEXP_SUBSTR, REGEXP_REPLACE, REGEXP_INSTR and REGEXP_LIKE. Oracle 11g extends the set of available expressions with REGEXP_COUNT.
SELECT
ENAME,
REGEXP_SUBSTR(ENAME,'DAM') SUBSTR,
REGEXP_INSTR(ENAME, 'T') INSTR,
REGEXP_REPLACE(ENAME,'AM','@') REPLACE,
REGEXP_COUNT(ENAME, 'A') COUNT
FROM
EMP
WHERE
REGEXP_LIKE(ENAME,'S');
ENAME SUBSTR INSTR REPLACE COUNT
---------- ---------- ---------- ---------- ----------
SMITH 4 SMITH 0
JONES 0 JONES 0
SCOTT 4 SCOTT 0
ADAMS DAM 0 AD@S 2
JAMES 0 J@ES 1
REGEXP_SUBSTR returns the substring DAM if found, REGEXP_INSTR returns the position of the first 'T', REGEXP_REPLACE replaces the strings 'AM' with '@' and REGEXP_COUNT counts the occurrences of 'A'. REGEXP_LIKE returns the strings that contain the pattern 'S'.
SELECT
REGEXP_SUBSTR('Programming','[[:alpha:]]+',1,2)
FROM
DUAL;
REGEXP
------
Oracle
'[[:alpha:]]' is a POSIX regular expression that matches any letter. The second set of consecutive word characters is returned. The '+' specifies that the number of characters to be matched is one or more. '.' matches exactly one character; '.?' matches zero or one character; '.*' match zero, one or more character; '.+' matches one or more character; '.{3}' matches exactly three characters; '.{4,6}' matches 4, 5 or 6 characters; '.{7,}' matches 7 or more characters. The third argument is the starting position. The default 1 means the pattern will be searched from the beginning of the substring. The fourth argument in 11g represents the occurrence of the substring.
SELECT
REGEXP_SUBSTR('Programming','\w+',1,2)
FROM
DUAL;
REGEXP
------
Oracle
Oracle 10gR2 introduced Perl-influenced regular expressions. '\w' represents any letter, number and the underscore. Unfortunately, in comparison to the old-style approach with INSTR and SUBSTR, the 10g regular expressions perform poorly.
SET TIMING ON
DECLARE
X VARCHAR2(40);
BEGIN
FOR I IN 1..10000000 LOOP
X := 'Programming';
X := SUBSTR(X,
INSTR(X, ' ')+1,
INSTR(X, ' ', 1,2)-INSTR(X, ' ')-1);
END LOOP;
END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:20.40
SET TIMING ON
DECLARE
X VARCHAR2(40);
BEGIN
FOR I IN 1..10000000 LOOP
X := 'Programming';
X := REGEXP_SUBSTR(X,'\w+',1,2);
END LOOP;
END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:02:10.82
REPLACE replaces all occurrence of a string. REGEXP_REPLACE has the same behavior by default, but when the fifth parameter, OCCURRENCE, is set to a value greater than zero, the substitution is not global.
SELECT
REGEXP_REPLACE
(
'Programming',
'([[:alpha:]]+)[[:space:]]([[:alpha:]]+)',
'\2: \1',
1,
1
)
FROM
DUAL;
REGEXP_REPLACE('ADVANCEDORACLESQ
--------------------------------
Oracle: Advanced SQL Programming
The search pattern contains a group of one or more alphabetic characters, followed by a space, then followed by a group of one or more alphabetic characters. This pattern is present more than once in the string, but only the first occurrence is affected. The replace pattern contains a reference to the second word, followed by a column and a space, followed by the first string.
SELECT
REGEXP_SUBSTR
(
'Programming',
'(\w).*?\1',
1,
1,
'i'
)
FROM
DUAL;
REGE
----
Adva
The search pattern contains any alphabetic character followed by a non-greedy number of characters followed by the same character as in the group. The search starts at the character one and looks for the first match of the pattern. The modifier 'i' indicates a case insensitive search. Non-greedy expressions appeared in 10gR2. The difference between a non-greedy expression like '.*?', '.+?', '.??', '.{2}?', '.{3,5}?' or '.{6,}?' and a greedy expression like '.*', '.+', '.?', '.{2}', '.{3,5}' or '.{6,}' is that the non-greedy searches for the smallest possible string and the greedy for the largest possible string.
SELECT
REGEXP_SUBSTR
(
'Oracle',
'.{2,4}?'
) NON_GREEDY,
REGEXP_SUBSTR
(
'Oracle',
'.{2,4}'
) GREEDY
FROM
DUAL;
NON_GREEDY GREEDY
---------- ------
Or Orac
Both patterns select from two to four characters. In this case, it could be 'Or', 'Ora' or 'Orac'. The non-greedy pattern returns two and the greedy four:
SELECT
ENAME,
REGEXP_SUBSTR(ENAME,'^K') "^K",
REGEXP_SUBSTR(ENAME,'T$') "T$",
REGEXP_SUBSTR(ENAME,'^[ABC]') "^[ABC]",
REGEXP_SUBSTR(ENAME,'^.?M') "^.?M",
REGEXP_SUBSTR(ENAME,'(RD|ES)$') "(RD|ES)$",
REGEXP_SUBSTR(ENAME,'(..R){2}') "(..R){2}",
REGEXP_SUBSTR(ENAME,'^.{4}[^A-E]') "^.{4}[^A-E]"
FROM
EMP;
ENAME ^K T$ ^[ABC] ^.?M (RD|ES)$ (..R){2} ^.{4}[^A-E
---------- -- -- ------ ---- -------- -------- ----------
SMITH SM SMITH
ALLEN A ALLEN
WARD RD
JONES ES JONES
MARTIN M MARTI
BLAKE B
CLARK C CLARK
SCOTT T SCOTT
KING K
TURNER TURNER
ADAMS A ADAMS
JAMES ES JAMES
FORD RD
MILLER M
The function REGEXP_SUBSTR matches ENAME to a pattern and returns the matched string. The first pattern checks if the name starts with 'K', the second checks if it ends with 'T', the third checks if it starts with A, B or C, the fourth checks if the string start with one or zero characters followed by M (which means the first or second character is a 'M'), the fifth checks if it ends with either ES or RD, the sixth checks if the pattern “one character + one character + the letter R” is found twice consecutively and the last pattern checks if the fifth character (the character following 4 characters at the beginning of the string) is not in the range A-E. Note that KING is not matched because the fifth character is not a character different from A-E. To test a string less than five characters, the pattern ^.{1,4}$ could be used.
What Are Regular Expressions?
Regular expressions enable you to search for patterns in string data by using standardized syntax conventions. You specify a regular expression through the following types of characters:
Metacharacters, which are operators that specify search algorithms Literals, which are the characters for which you are searching
A regular expression can specify complex patterns of character sequences. For example, the following regular expression searches for the literals f or ht, the t literal, the p literal optionally followed by the s literal, and finally the colon (:) literal:
(f|ht)tps?:
The parentheses are metacharacters that group a series of pattern elements to a single element; the pipe symbol (|) matches one of the alternatives in the group. The question mark (?) is a metacharacter indicating that the preceding pattern, in this case the s character, is optional. Thus, the preceding regular expression matches the http:, https:, ftp:, and ftps: strings.
How Are Regular Expressions Useful?
Regular expressions are a powerful text processing component of programming languages such as PERL and Java. For example, a PERL script can process each HTML file in a directory, read its contents into a scalar variable as a single string, and then use regular expressions to search for URLs in the string. One reason that many developers write in PERL is for its robust pattern matching functionality.
Oracle Database support of regular expressions enables developers to implement complex match logic in the database. This technique is useful for the following reasons:
By centralizing match logic in Oracle Database, you avoid intensive string processing of SQL results sets by middle-tier applications. For example, life science customers often rely on PERL to do pattern analysis on bioinformatics data stored in huge databases of DNAs and proteins. Previously, finding a match for a protein sequence such as [AG].{4}GK[ST] was handled in the middle tier. The SQL regular expression functions move the processing logic closer to the data, thereby providing a more efficient solution.
Prior to Oracle Database 10g, developers often coded data validation logic on the client, requiring the same validation logic to be duplicated for multiple clients. Using server-side regular expressions to enforce constraints solves this problem.
The built-in SQL and PL/SQL regular expression functions and conditions make string manipulations more powerful and less cumbersome than in previous releases of Oracle Database.
Oracle Database Implementation of Regular Expressions
Oracle Database implements regular expression support with a set of Oracle Database SQL functions and conditions that enable you to search and manipulate string data. You can use these functions in any environment that supports Oracle Database SQL. You can use these functions on a text literal, bind variable, or any column that holds character data such as CHAR, NCHAR, CLOB, NCLOB, NVARCHAR2, and VARCHAR2 (but not LONG).
3. Partitioning Tables:
Basics of Partitioning
Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces, where each piece of such a database object is called a partition. Each partition has its own name, and may optionally have its own storage characteristics.
From the perspective of a database administrator, a partitioned object has multiple pieces that can be managed either collectively or individually. This gives the administrator considerable flexibility in managing partitioned objects. However, from the perspective of the application, a partitioned table is identical to a non-partitioned table; no modifications are necessary when accessing a partitioned table using SQL queries and DML statements.
Partitioning Key
Each row in a partitioned table is unambiguously assigned to a single partition. The partitioning key is comprised of one or more columns that determine the partition where each row will be stored. Oracle automatically directs insert, update, and delete operations to the appropriate partition through the use of the partitioning key.
Partitioned Tables
Any table can be partitioned into a million separate partitions except those tables containing columns with LONG or LONG RAW datatypes. You can, however, use tables containing columns with CLOB or BLOB datatypes.
To reduce disk usage and memory usage (specifically, the buffer cache), you can store tables and partitions of a partitioned table in a compressed format inside the database. This often leads to a better scaleup for read-only operations. Table compression can also speed up query execution. There is, however, a slight cost in CPU overhead.
When to Partition a Table:
Here are some suggestions for when to partition a table:
When to Partition an Index:
Here are some suggestions for when to consider partitioning an index:
Benefits of Partitioning
Partitioning can provide tremendous benefit to a wide variety of applications by improving performance, manageability, and availability. It is not unusual for partitioning to improve the performance of certain queries or maintenance operations by an order of magnitude. Moreover, partitioning can greatly simplify common administration tasks.
Partitioning also enables database designers and administrators to tackle some of the toughest problems posed by cutting-edge applications. Partitioning is a key tool for building multi-terabyte systems or systems with extremely high availability requirements.
4. Dropping columns:
Dropping Table Columns
You can drop columns that are no longer needed from a table, including an index-organized table. This provides a convenient means to free space in a database, and avoids your having to export/import data then re-create indexes and constraints.
You cannot drop all columns from a table, nor can you drop columns from a table owned by SYS. Any attempt to do so results in an error.
When you issue an ALTER TABLE...DROP COLUMN statement, the column descriptor and the data associated with the target column are removed from each row in the table. You can drop multiple columns with one statement.
The following statements are examples of dropping columns from the hr.admin_emp table. The first statement drops only the sal column:
ALTER TABLE hr.admin_emp DROP COLUMN sal;
The next statement drops both the bonus and comm columns:
ALTER TABLE hr.admin_emp DROP (bonus, commission);
Logical Delete:
On large tables the process of physically removing a column can be very time and resource consuming. For this reason you may decide to logically delete it.
ALTER TABLE table_name SET UNUSED (column_name);
ALTER TABLE table_name SET UNUSED (column_name1, column_name2);
Once this is done the columns will no longer be visible to the user. If at a later date you have time to physically delete the columns this can be done using the following.
ALTER TABLE table_name DROP UNUSED COLUMNS;
On large tables you can reduce the amount of undo logs accumulated by using the CHECKPOINT option which forces a checkpoint after the specified number of rows have been processed.
ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 250;
The DBA_UNUSED_COL_TABS view can be used to view the number of unused columns per table.
5. Dropping column contaning data possibel?
YES.
The actions on dropping column will include at least the following:
6. Dropping column which is part of user key or composite priamry key:
First option, drop the primary key constarint, drop the column and then re-create the constraint.
Second option, Use cascade option in the alter table command to drop the column in the composite priamry key.
7. Does creating primary key constraint automatically creates unique index?
Unless the table is a temporary table to stage the data while you work on it, you always want to put a primary key on the table and here's why:
1 - a unique constraint can allow nulls but a primary key never allows nulls. If you run a query with a join on columns with null values you eliminate those rows from the resulting data set because null is not equal to null. This is how even big companies can make accounting errors and have to restate their profits. Their queries didn't show certain rows that should have been included in the total because there were null values in some of the columns of their unique index. Shoulda used a primary key.
2 - a unique index will automatically be placed on the primary key, so you don't have to create one.
3 - most database engines will automatically put a clustered index on the primary key, making queries faster because the rows are stored contiguously in the data blocks. (This can be altered to place the clustered index on a different index if that would speed up the queries.) If a table doesn't have a clustered index, the rows won't be stored contiguously in the data blocks, making the queries slower because the read/write head has to travel all over the disk to pick up the data.
4 - many front end development environments require a primary key in order to update the table or make deletions.
8. SET UNUSED Option:
Marking Columns Unused
If you are concerned about the length of time it could take to drop column data from all of the rows in a large table, you can use the ALTER TABLE...SET UNUSED statement. This statement marks one or more columns as unused, but does not actually remove the target column data or restore the disk space occupied by these columns. However, a column that is marked as unused is not displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. All constraints, indexes, and statistics defined on the column are also removed.
To mark the hiredate and mgr columns as unused, execute the following statement:
ALTER TABLE hr.admin_emp SET UNUSED (hiredate, mgr);
You can later remove columns that are marked as unused by issuing an ALTER TABLE...DROP UNUSED COLUMNS statement. Unused columns are also removed from the target table whenever an explicit drop of any particular column or columns of the table is issued.
The data dictionary views USER_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS, or DBA_UNUSED_COL_TABS can be used to list all tables containing unused columns. The COUNT field shows the number of unused columns in the table.
SELECT * FROM DBA_UNUSED_COL_TABS;
OWNER TABLE_NAME COUNT
--------------------------- --------------------------- -----
HR ADMIN_EMP 2
For external tables, the SET UNUSED statement is transparently converted into an ALTER TABLE DROP COLUMN statement. Because external tables consist of metadata only in the database, the DROP COLUMN statement performs equivalently to the SET UNUSED statement.
Removing Unused Columns
The ALTER TABLE...DROP UNUSED COLUMNS statement is the only action allowed on unused columns. It physically removes unused columns from the table and reclaims disk space.
In the ALTER TABLE statement that follows, the optional clause CHECKPOINT is specified. This clause causes a checkpoint to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space.
ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;
Once you user SET UNUSED COLUMN then you cannot get back the column again, it is treated as a dropped column. Moreover you can add a new column with that name.
Any time you can drop the SET UNUSED COLUMNS with the following command.
ALTER TABLE table_name DROP UNUSED COLUMNS;
When you drop a column it moves into recycle bin while when you mark a column unused it is like logically dropping it but physically preserving it.
Sometimes marking a column as unused and then using the alter table name drop unused column statement is useful because it allows the DBA to take away column access quickly and immediately. Later on, during a routine database maintenance weekend or after business hours, you can then remove the column with the alter table name drop unused column to reclaim the space.
On the other hand, marking the column unused won't free up any space and when there is an need to free up space and remove the columns that are not needed you would be better off dropping it.
9. SYS and SYSTEM Users in Oracle:
SYS:
SYSTEM:
SYS AND SYSTEM Users
The following administrative user accounts are automatically created when you install Oracle Database. They are both created with the password that you supplied upon installation, and they are both automatically granted the DBA role.
SYS
This account can perform all administrative functions. All base (underlying) tables and views for the database data dictionary are stored in the SYS schema. These base tables and views are critical for the operation of Oracle Database. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator. You must not create any tables in the SYS schema.
The SYS user is granted the SYSDBA privilege, which enables a user to perform high-level administrative tasks such as backup and recovery.
SYSTEM
This account can perform all administrative functions except the following:
While this account can be used to perform day-to-day administrative tasks, Oracle strongly recommends creating named users account for administering the Oracle database to enable monitoring of database activity.
SYSDBA and SYSOPER System Privileges
SYSDBA and SYSOPER are administrative privileges required to perform high-level administrative operations such as creating, starting up, shutting down, backing up, or recovering the database. The SYSDBA system privilege is for fully empowered database administrators and the SYSOPER system privilege allows a user to perform basic operational tasks, but without the ability to look at user data.
The SYSDBA and SYSOPER system privileges allow access to a database instance even when the database is not open. Control of these privileges is therefore completely outside of the database itself. This control enables an administrator who is granted one of these privileges to connect to the database instance to start the database.
You can also think of the SYSDBA and SYSOPER privileges as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other way. For example, if you have the SYSDBA privilege, then you can connect to the database using AS SYSDBA.
The SYS user is automatically granted the SYSDBA privilege upon installation. When you log in as user SYS, you must connect to the database as SYSDBA or SYSOPER. Connecting as a SYSDBA user invokes the SYSDBA privilege; connecting as SYSOPER invokes the SYSOPER privilege. Oracle Enterprise Manager Database Control does not permit you to log in as user SYS without connecting as SYSDBA or SYSOPER.
When you connect with the SYSDBA or SYSOPER privilege, you connect with a default schema, not with the schema that is generally associated with your user name. For SYSDBA this schema is SYS; for SYSOPER the schema is PUBLIC.
10. Synonyms in Oracle:
About Synonyms
Creating Synonyms
To create a private synonym in your own schema, you must have the CREATE SYNONYM privilege. To create a private synonym in another user's schema, you must have the CREATE ANY SYNONYM privilege. To create a public synonym, you must have the CREATE PUBLIC SYNONYM system privilege.
Create a synonym using the CREATE SYNONYM statement. The underlying schema object need not exist, nor do you need privileges to access the object. The following statement creates a public synonym named public_emp on the emp table contained in the schema of jward:
CREATE PUBLIC SYNONYM public_emp FOR jward.emp
When you create a synonym for a remote procedure or function, you must qualify the remote object with its schema name. Alternatively, you can create a local public synonym on the database where the remote object resides, in which case the database link must be included in all subsequent calls to the procedure or function.
User_synonyms contains the list of all synonyms present in the database.
List synonyms in the current schema:
SELECT synonym_name, table_owner, table_name FROM user_synonyms;
11. Data Dictionary Views:
The data dictionary views, also known as catalog views, let you monitor the state of the database in real time:
Oracle Data Dictionary Concepts
The data dictionary is full of “Metadata”, information about what is going-on inside your database. The data dictionary is presented to us in the form of a number of views. The dictionary views come in two primary forms:
The DBA, ALL or USER views
- These views are used to manage database structures.
The V$ Dynamic Performance Views
- These views are used to monitor real time database statistics
Throughout the rest of this book we will introduce you to data dictionary views that you can use to manage your database. You will find the entire list of Oracle Data Dictionary views documented in the Oracle documentation online.
There are hundreds of views in the data dictionary. To see the depth of the data dictionary views, here are the views that store data about Oracle tables:
* dba_all_tables
* dba_indexes
* dba_ind_partitions
* dba_ind_subpartitions
* dba_object_tables
* dba_part_col_statistics
* dba_subpart_col_statistics
* dba_tables
* dba_tab_cols
* dba_tab_columns
* dba_tab_col_statistics
* dba_tab_partitions
* dba_tab_subpartitions
The data dictionary is a repository of information about the Oracle database, known as metadata. Metadata is “information about information,” and the data dictionary is information about the database.
Oracle Data Dictionary
Managing Oracle requires the use of a number of Oracle supplied views. These views include the data dictionary and the dynamic performance views. Together these views allow you to:
In this chapter we will fist look at the data dictionary views. We will then look at the dynamic performance views available in Oracle Database 10g.
At the heart of every Oracle database is the data dictionary. The data dictionary is generated when the database is first created. In this section we will discuss the Oracle data dictionary. In it we will discuss:
The Purpose of the Data Dictionary
Metadata is data about data, or data that defines other data. The Oracle data dictionary is metadata about the database. For example, if you create a table in Oracle, metadata about that table is stored in the data dictionary. Such things as column names, length, and other attributes are stored. Thus, the data dictionary contains a great volume of useful information about your database. Pretty much everything you would want to know about your database is contained in the data dictionary in some form.
As a DBA then, you can see why the data dictionary is so important. Since you can’t possibly remember everything about your database (like the names of all the tables and columns) Oracle remembers this for you. All you need to do is learn how to find that information. We will be showing you how to do this in a later section in this book.
The Architecture of the Data Dictionary
The data dictionary is created when the Oracle database is created. It is owned by the SYS user, and is stored principally in the SYSTEM tablespace, though some components are stored in the SYSAUX tablespace in Oracle Database 10g.
The data dictionary is comprised of a number of tables and Oracle views. Oracle wants you to keep your hands off these tables, and unless you are a real expert I’d recommend you do just that.
Of course, the data dictionary would be pretty worthless if we could not access the data. Oracle supplies a number of views that you can query that will give you direct access into the data dictionary tables. These views are generally tuned by Oracle for quick access to the underlying objects and the names of the views often reflect the use of that view much better than the names of the underlying objects. The data dictionary views come in three main flavors:
For example, if you want to look at user information there are three views, USER_USERS, ALL_USERS and dba_users. Each of these views sees the user a bit differently.
All views that start with USER only sees the information that pertains to the user you are logged in as. For example, if you are logged in as SCOTT, when you look at the user_tables view, you will only see information on tables that are owned by the SCOTT user. You might have access to tables in the GEORGE schema, but you won’t see them in the user_tables view. Here is an example of a simple query against the user_tables view:
SELECT table_name FROM user_tables;
The ALL views allow you to see all objects that you have access to. For example, if you are logged in as SCOTT and you query the all_tables view, you will see all the tables owned by SCOTT but you will also see any tables you have access to that are owned by GEORGE, or any other user. You have to have access rights to these objects (which you would have received via the grant command which we discussed in an earlier chapter).
Generally the two main differences between the USER and ALL views is that the owner of the object is included in the ALL views, and this is not included in the USER views which makes sense since you will only be seeing your objects. In this example, we query the all_tables view for all tables that start with EMP:
SELECT
table_name
FROM
all_tables
WHERE
table_name LIKE ’EMP%’;
The granddaddy of the data dictionary views are the DBA views. These views are unrestricted windows into all Oracle data dictionary objects. Because of this, they are only accessible by DBA’s (as the name seems to suggest). All DBA views start with DBA. In this example, we query the dba_tables view for all tables that start with EMP and owned by users whose names start with ROBERT:
SELECT
table_name
FROM
dba_tables
WHERE
table_name LIKE ’EMP%’
AND
owner like ’ROBERT%’;
You can find the data dictionary tables documented in the Oracle Database 10g Reference Guide, which is part of the overall Oracle database documentation set. There are almost 600 DBA views in Oracle Database 10g alone, and a like number of USER and ALL views. The ALL and USER views are pretty much children of the DBA views, and you will not find ALL or USER views for each DBA view. You can also find the views documented within the data dictionary itself. The DICTIONARY (or DICT for short) view contains all the tables of the data dictionary, plus comments on what each table is used for.
12. Sequence with CYCLE:
CYCLE
Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.
NOCYCLE
Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.
If MINVALUE is specified, the the seq will start again from minvalue, if MINVALUE is not specified, it will start with 1.
eg. CREATE SEQUENCE "INTADMIN"."SEQ_TEST2" MAXVALUE 10 INCREMENT BY 1 START WITH 5 NOCACHE CYCLE ;
Multi-table insert is a new feature of Oracle 9i Release 1 (9.0). An extension to INSERT..SELECT, this feature enables us to define multiple insert targets for a source dataset. Until the introduction of this feature, only SQL*Loader had a similar capability.
This article provides an overview of multi-table inserts and how they are used.
syntax overview
There are two types of multi-table insert as follows:
INSERT FIRST
INSERT ALL
Multi-table inserts are an extension to INSERT..SELECT. Syntax is of the following form:
INSERT ALL|FIRST
[WHEN condition THEN] INTO target [VALUES]
[WHEN condition THEN] INTO target [VALUES]
...
[ELSE] INTO target [VALUES]
SELECT ...
FROM source_query;
Example:
INSERT
WHEN MOD(deptno,2)=0 THEN
INTO even_employees (empno, ename) VALUES (empno, ename)
WHEN MOD(deptno,2)=1 THEN
INTO uneven_employees (empno, ename) VALUES (empno, ename)
ELSE
INTO unknow_employees (empno, ename) VALUES (empno, ename)
SELECT empno, ename, deptno FROM emp;
2. Regular Expressions:
Oracle 10g introduced regular expression functions in SQL with the functions REGEXP_SUBSTR, REGEXP_REPLACE, REGEXP_INSTR and REGEXP_LIKE. Oracle 11g extends the set of available expressions with REGEXP_COUNT.
SELECT
ENAME,
REGEXP_SUBSTR(ENAME,'DAM') SUBSTR,
REGEXP_INSTR(ENAME, 'T') INSTR,
REGEXP_REPLACE(ENAME,'AM','@') REPLACE,
REGEXP_COUNT(ENAME, 'A') COUNT
FROM
EMP
WHERE
REGEXP_LIKE(ENAME,'S');
ENAME SUBSTR INSTR REPLACE COUNT
---------- ---------- ---------- ---------- ----------
SMITH 4 SMITH 0
JONES 0 JONES 0
SCOTT 4 SCOTT 0
ADAMS DAM 0 AD@S 2
JAMES 0 J@ES 1
REGEXP_SUBSTR returns the substring DAM if found, REGEXP_INSTR returns the position of the first 'T', REGEXP_REPLACE replaces the strings 'AM' with '@' and REGEXP_COUNT counts the occurrences of 'A'. REGEXP_LIKE returns the strings that contain the pattern 'S'.
SELECT
REGEXP_SUBSTR('Programming','[[:alpha:]]+',1,2)
FROM
DUAL;
REGEXP
------
Oracle
'[[:alpha:]]' is a POSIX regular expression that matches any letter. The second set of consecutive word characters is returned. The '+' specifies that the number of characters to be matched is one or more. '.' matches exactly one character; '.?' matches zero or one character; '.*' match zero, one or more character; '.+' matches one or more character; '.{3}' matches exactly three characters; '.{4,6}' matches 4, 5 or 6 characters; '.{7,}' matches 7 or more characters. The third argument is the starting position. The default 1 means the pattern will be searched from the beginning of the substring. The fourth argument in 11g represents the occurrence of the substring.
SELECT
REGEXP_SUBSTR('Programming','\w+',1,2)
FROM
DUAL;
REGEXP
------
Oracle
Oracle 10gR2 introduced Perl-influenced regular expressions. '\w' represents any letter, number and the underscore. Unfortunately, in comparison to the old-style approach with INSTR and SUBSTR, the 10g regular expressions perform poorly.
SET TIMING ON
DECLARE
X VARCHAR2(40);
BEGIN
FOR I IN 1..10000000 LOOP
X := 'Programming';
X := SUBSTR(X,
INSTR(X, ' ')+1,
INSTR(X, ' ', 1,2)-INSTR(X, ' ')-1);
END LOOP;
END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:20.40
SET TIMING ON
DECLARE
X VARCHAR2(40);
BEGIN
FOR I IN 1..10000000 LOOP
X := 'Programming';
X := REGEXP_SUBSTR(X,'\w+',1,2);
END LOOP;
END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:02:10.82
REPLACE replaces all occurrence of a string. REGEXP_REPLACE has the same behavior by default, but when the fifth parameter, OCCURRENCE, is set to a value greater than zero, the substitution is not global.
SELECT
REGEXP_REPLACE
(
'Programming',
'([[:alpha:]]+)[[:space:]]([[:alpha:]]+)',
'\2: \1',
1,
1
)
FROM
DUAL;
REGEXP_REPLACE('ADVANCEDORACLESQ
--------------------------------
Oracle: Advanced SQL Programming
The search pattern contains a group of one or more alphabetic characters, followed by a space, then followed by a group of one or more alphabetic characters. This pattern is present more than once in the string, but only the first occurrence is affected. The replace pattern contains a reference to the second word, followed by a column and a space, followed by the first string.
SELECT
REGEXP_SUBSTR
(
'Programming',
'(\w).*?\1',
1,
1,
'i'
)
FROM
DUAL;
REGE
----
Adva
The search pattern contains any alphabetic character followed by a non-greedy number of characters followed by the same character as in the group. The search starts at the character one and looks for the first match of the pattern. The modifier 'i' indicates a case insensitive search. Non-greedy expressions appeared in 10gR2. The difference between a non-greedy expression like '.*?', '.+?', '.??', '.{2}?', '.{3,5}?' or '.{6,}?' and a greedy expression like '.*', '.+', '.?', '.{2}', '.{3,5}' or '.{6,}' is that the non-greedy searches for the smallest possible string and the greedy for the largest possible string.
SELECT
REGEXP_SUBSTR
(
'Oracle',
'.{2,4}?'
) NON_GREEDY,
REGEXP_SUBSTR
(
'Oracle',
'.{2,4}'
) GREEDY
FROM
DUAL;
NON_GREEDY GREEDY
---------- ------
Or Orac
Both patterns select from two to four characters. In this case, it could be 'Or', 'Ora' or 'Orac'. The non-greedy pattern returns two and the greedy four:
SELECT
ENAME,
REGEXP_SUBSTR(ENAME,'^K') "^K",
REGEXP_SUBSTR(ENAME,'T$') "T$",
REGEXP_SUBSTR(ENAME,'^[ABC]') "^[ABC]",
REGEXP_SUBSTR(ENAME,'^.?M') "^.?M",
REGEXP_SUBSTR(ENAME,'(RD|ES)$') "(RD|ES)$",
REGEXP_SUBSTR(ENAME,'(..R){2}') "(..R){2}",
REGEXP_SUBSTR(ENAME,'^.{4}[^A-E]') "^.{4}[^A-E]"
FROM
EMP;
ENAME ^K T$ ^[ABC] ^.?M (RD|ES)$ (..R){2} ^.{4}[^A-E
---------- -- -- ------ ---- -------- -------- ----------
SMITH SM SMITH
ALLEN A ALLEN
WARD RD
JONES ES JONES
MARTIN M MARTI
BLAKE B
CLARK C CLARK
SCOTT T SCOTT
KING K
TURNER TURNER
ADAMS A ADAMS
JAMES ES JAMES
FORD RD
MILLER M
The function REGEXP_SUBSTR matches ENAME to a pattern and returns the matched string. The first pattern checks if the name starts with 'K', the second checks if it ends with 'T', the third checks if it starts with A, B or C, the fourth checks if the string start with one or zero characters followed by M (which means the first or second character is a 'M'), the fifth checks if it ends with either ES or RD, the sixth checks if the pattern “one character + one character + the letter R” is found twice consecutively and the last pattern checks if the fifth character (the character following 4 characters at the beginning of the string) is not in the range A-E. Note that KING is not matched because the fifth character is not a character different from A-E. To test a string less than five characters, the pattern ^.{1,4}$ could be used.
What Are Regular Expressions?
Regular expressions enable you to search for patterns in string data by using standardized syntax conventions. You specify a regular expression through the following types of characters:
Metacharacters, which are operators that specify search algorithms Literals, which are the characters for which you are searching
A regular expression can specify complex patterns of character sequences. For example, the following regular expression searches for the literals f or ht, the t literal, the p literal optionally followed by the s literal, and finally the colon (:) literal:
(f|ht)tps?:
The parentheses are metacharacters that group a series of pattern elements to a single element; the pipe symbol (|) matches one of the alternatives in the group. The question mark (?) is a metacharacter indicating that the preceding pattern, in this case the s character, is optional. Thus, the preceding regular expression matches the http:, https:, ftp:, and ftps: strings.
How Are Regular Expressions Useful?
Regular expressions are a powerful text processing component of programming languages such as PERL and Java. For example, a PERL script can process each HTML file in a directory, read its contents into a scalar variable as a single string, and then use regular expressions to search for URLs in the string. One reason that many developers write in PERL is for its robust pattern matching functionality.
Oracle Database support of regular expressions enables developers to implement complex match logic in the database. This technique is useful for the following reasons:
By centralizing match logic in Oracle Database, you avoid intensive string processing of SQL results sets by middle-tier applications. For example, life science customers often rely on PERL to do pattern analysis on bioinformatics data stored in huge databases of DNAs and proteins. Previously, finding a match for a protein sequence such as [AG].{4}GK[ST] was handled in the middle tier. The SQL regular expression functions move the processing logic closer to the data, thereby providing a more efficient solution.
Prior to Oracle Database 10g, developers often coded data validation logic on the client, requiring the same validation logic to be duplicated for multiple clients. Using server-side regular expressions to enforce constraints solves this problem.
The built-in SQL and PL/SQL regular expression functions and conditions make string manipulations more powerful and less cumbersome than in previous releases of Oracle Database.
Oracle Database Implementation of Regular Expressions
Oracle Database implements regular expression support with a set of Oracle Database SQL functions and conditions that enable you to search and manipulate string data. You can use these functions in any environment that supports Oracle Database SQL. You can use these functions on a text literal, bind variable, or any column that holds character data such as CHAR, NCHAR, CLOB, NCLOB, NVARCHAR2, and VARCHAR2 (but not LONG).
3. Partitioning Tables:
Basics of Partitioning
Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces, where each piece of such a database object is called a partition. Each partition has its own name, and may optionally have its own storage characteristics.
From the perspective of a database administrator, a partitioned object has multiple pieces that can be managed either collectively or individually. This gives the administrator considerable flexibility in managing partitioned objects. However, from the perspective of the application, a partitioned table is identical to a non-partitioned table; no modifications are necessary when accessing a partitioned table using SQL queries and DML statements.
Partitioning Key
Each row in a partitioned table is unambiguously assigned to a single partition. The partitioning key is comprised of one or more columns that determine the partition where each row will be stored. Oracle automatically directs insert, update, and delete operations to the appropriate partition through the use of the partitioning key.
Partitioned Tables
Any table can be partitioned into a million separate partitions except those tables containing columns with LONG or LONG RAW datatypes. You can, however, use tables containing columns with CLOB or BLOB datatypes.
To reduce disk usage and memory usage (specifically, the buffer cache), you can store tables and partitions of a partitioned table in a compressed format inside the database. This often leads to a better scaleup for read-only operations. Table compression can also speed up query execution. There is, however, a slight cost in CPU overhead.
When to Partition a Table:
Here are some suggestions for when to partition a table:
- Tables greater than 2 GB should always be considered as candidates for partitioning.
- Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.
- When the contents of a table need to be distributed across different types of storage devices.
When to Partition an Index:
Here are some suggestions for when to consider partitioning an index:
- Avoid rebuilding the entire index when data is removed.
- Perform maintenance on parts of the data without invalidating the entire index.
- Reduce the impact of index skew caused by an index on a column with a monotonically increasing value.
Benefits of Partitioning
Partitioning can provide tremendous benefit to a wide variety of applications by improving performance, manageability, and availability. It is not unusual for partitioning to improve the performance of certain queries or maintenance operations by an order of magnitude. Moreover, partitioning can greatly simplify common administration tasks.
Partitioning also enables database designers and administrators to tackle some of the toughest problems posed by cutting-edge applications. Partitioning is a key tool for building multi-terabyte systems or systems with extremely high availability requirements.
4. Dropping columns:
Dropping Table Columns
You can drop columns that are no longer needed from a table, including an index-organized table. This provides a convenient means to free space in a database, and avoids your having to export/import data then re-create indexes and constraints.
You cannot drop all columns from a table, nor can you drop columns from a table owned by SYS. Any attempt to do so results in an error.
When you issue an ALTER TABLE...DROP COLUMN statement, the column descriptor and the data associated with the target column are removed from each row in the table. You can drop multiple columns with one statement.
The following statements are examples of dropping columns from the hr.admin_emp table. The first statement drops only the sal column:
ALTER TABLE hr.admin_emp DROP COLUMN sal;
The next statement drops both the bonus and comm columns:
ALTER TABLE hr.admin_emp DROP (bonus, commission);
Logical Delete:
On large tables the process of physically removing a column can be very time and resource consuming. For this reason you may decide to logically delete it.
ALTER TABLE table_name SET UNUSED (column_name);
ALTER TABLE table_name SET UNUSED (column_name1, column_name2);
Once this is done the columns will no longer be visible to the user. If at a later date you have time to physically delete the columns this can be done using the following.
ALTER TABLE table_name DROP UNUSED COLUMNS;
On large tables you can reduce the amount of undo logs accumulated by using the CHECKPOINT option which forces a checkpoint after the specified number of rows have been processed.
ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 250;
The DBA_UNUSED_COL_TABS view can be used to view the number of unused columns per table.
5. Dropping column contaning data possibel?
YES.
The actions on dropping column will include at least the following:
- The data stored in that column is lost.
- Views referencing that column are invalidated - but (according to Gary - thanks!) they're not dropped; they stay invalid until revised to work with the modified schema.
- Stored procedures referencing that column are invalidated - same caveat.
- The column ID numbers of following columns will be reset.
- Permissions granted on the column will be removed.
- Indexes referencing that column will be dropped.
6. Dropping column which is part of user key or composite priamry key:
First option, drop the primary key constarint, drop the column and then re-create the constraint.
Second option, Use cascade option in the alter table command to drop the column in the composite priamry key.
7. Does creating primary key constraint automatically creates unique index?
Unless the table is a temporary table to stage the data while you work on it, you always want to put a primary key on the table and here's why:
1 - a unique constraint can allow nulls but a primary key never allows nulls. If you run a query with a join on columns with null values you eliminate those rows from the resulting data set because null is not equal to null. This is how even big companies can make accounting errors and have to restate their profits. Their queries didn't show certain rows that should have been included in the total because there were null values in some of the columns of their unique index. Shoulda used a primary key.
2 - a unique index will automatically be placed on the primary key, so you don't have to create one.
3 - most database engines will automatically put a clustered index on the primary key, making queries faster because the rows are stored contiguously in the data blocks. (This can be altered to place the clustered index on a different index if that would speed up the queries.) If a table doesn't have a clustered index, the rows won't be stored contiguously in the data blocks, making the queries slower because the read/write head has to travel all over the disk to pick up the data.
4 - many front end development environments require a primary key in order to update the table or make deletions.
8. SET UNUSED Option:
Marking Columns Unused
If you are concerned about the length of time it could take to drop column data from all of the rows in a large table, you can use the ALTER TABLE...SET UNUSED statement. This statement marks one or more columns as unused, but does not actually remove the target column data or restore the disk space occupied by these columns. However, a column that is marked as unused is not displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. All constraints, indexes, and statistics defined on the column are also removed.
To mark the hiredate and mgr columns as unused, execute the following statement:
ALTER TABLE hr.admin_emp SET UNUSED (hiredate, mgr);
You can later remove columns that are marked as unused by issuing an ALTER TABLE...DROP UNUSED COLUMNS statement. Unused columns are also removed from the target table whenever an explicit drop of any particular column or columns of the table is issued.
The data dictionary views USER_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS, or DBA_UNUSED_COL_TABS can be used to list all tables containing unused columns. The COUNT field shows the number of unused columns in the table.
SELECT * FROM DBA_UNUSED_COL_TABS;
OWNER TABLE_NAME COUNT
--------------------------- --------------------------- -----
HR ADMIN_EMP 2
For external tables, the SET UNUSED statement is transparently converted into an ALTER TABLE DROP COLUMN statement. Because external tables consist of metadata only in the database, the DROP COLUMN statement performs equivalently to the SET UNUSED statement.
Removing Unused Columns
The ALTER TABLE...DROP UNUSED COLUMNS statement is the only action allowed on unused columns. It physically removes unused columns from the table and reclaims disk space.
In the ALTER TABLE statement that follows, the optional clause CHECKPOINT is specified. This clause causes a checkpoint to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space.
ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;
Once you user SET UNUSED COLUMN then you cannot get back the column again, it is treated as a dropped column. Moreover you can add a new column with that name.
Any time you can drop the SET UNUSED COLUMNS with the following command.
ALTER TABLE table_name DROP UNUSED COLUMNS;
When you drop a column it moves into recycle bin while when you mark a column unused it is like logically dropping it but physically preserving it.
Sometimes marking a column as unused and then using the alter table name drop unused column statement is useful because it allows the DBA to take away column access quickly and immediately. Later on, during a routine database maintenance weekend or after business hours, you can then remove the column with the alter table name drop unused column to reclaim the space.
On the other hand, marking the column unused won't free up any space and when there is an need to free up space and remove the columns that are not needed you would be better off dropping it.
9. SYS and SYSTEM Users in Oracle:
SYS:
- Automatically created when Oracle database is installed
- Automatically granted the DBA role
- Has a default password: CHANGE_ON_INSTALL (make sure you change it)
- Owns the base tables and views for the database data dictionary
- The default schema when you connect as SYSDBA
- Tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator, and no one should create any tables in the schema of user SYS. Database users should not connect to the Oracle database using the SYS account.
SYSTEM:
- Automatically created when Oracle database is installed
- Automatically granted the DBA role
- Has a default password: MANAGER (make sure you change it)
- Used to create additional tables and views that display administrative information
- Used to create internal tables and views used by various Oracle database options and tools
- Never use the SYSTEM schema to store tables of interest to non-administrative users.
SYS AND SYSTEM Users
The following administrative user accounts are automatically created when you install Oracle Database. They are both created with the password that you supplied upon installation, and they are both automatically granted the DBA role.
SYS
This account can perform all administrative functions. All base (underlying) tables and views for the database data dictionary are stored in the SYS schema. These base tables and views are critical for the operation of Oracle Database. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator. You must not create any tables in the SYS schema.
The SYS user is granted the SYSDBA privilege, which enables a user to perform high-level administrative tasks such as backup and recovery.
SYSTEM
This account can perform all administrative functions except the following:
- Backup and recovery
- Database upgrade
While this account can be used to perform day-to-day administrative tasks, Oracle strongly recommends creating named users account for administering the Oracle database to enable monitoring of database activity.
SYSDBA and SYSOPER System Privileges
SYSDBA and SYSOPER are administrative privileges required to perform high-level administrative operations such as creating, starting up, shutting down, backing up, or recovering the database. The SYSDBA system privilege is for fully empowered database administrators and the SYSOPER system privilege allows a user to perform basic operational tasks, but without the ability to look at user data.
The SYSDBA and SYSOPER system privileges allow access to a database instance even when the database is not open. Control of these privileges is therefore completely outside of the database itself. This control enables an administrator who is granted one of these privileges to connect to the database instance to start the database.
You can also think of the SYSDBA and SYSOPER privileges as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other way. For example, if you have the SYSDBA privilege, then you can connect to the database using AS SYSDBA.
The SYS user is automatically granted the SYSDBA privilege upon installation. When you log in as user SYS, you must connect to the database as SYSDBA or SYSOPER. Connecting as a SYSDBA user invokes the SYSDBA privilege; connecting as SYSOPER invokes the SYSOPER privilege. Oracle Enterprise Manager Database Control does not permit you to log in as user SYS without connecting as SYSDBA or SYSOPER.
When you connect with the SYSDBA or SYSOPER privilege, you connect with a default schema, not with the schema that is generally associated with your user name. For SYSDBA this schema is SYS; for SYSOPER the schema is PUBLIC.
10. Synonyms in Oracle:
About Synonyms
- A synonym is an alias for a schema object. Synonyms can provide a level of security by masking the name and owner of an object and by providing location transparency for remote objects of a distributed database. Also, they are convenient to use and reduce the complexity of SQL statements for database users.
- Synonyms allow underlying objects to be renamed or moved, where only the synonym needs to be redefined and applications based on the synonym continue to function without modification.
- You can create both public and private synonyms. A public synonym is owned by the special user group named PUBLIC and is accessible to every user in a database. A private synonym is contained in the schema of a specific user and available only to the user and the user's grantees.
Creating Synonyms
To create a private synonym in your own schema, you must have the CREATE SYNONYM privilege. To create a private synonym in another user's schema, you must have the CREATE ANY SYNONYM privilege. To create a public synonym, you must have the CREATE PUBLIC SYNONYM system privilege.
Create a synonym using the CREATE SYNONYM statement. The underlying schema object need not exist, nor do you need privileges to access the object. The following statement creates a public synonym named public_emp on the emp table contained in the schema of jward:
CREATE PUBLIC SYNONYM public_emp FOR jward.emp
When you create a synonym for a remote procedure or function, you must qualify the remote object with its schema name. Alternatively, you can create a local public synonym on the database where the remote object resides, in which case the database link must be included in all subsequent calls to the procedure or function.
User_synonyms contains the list of all synonyms present in the database.
List synonyms in the current schema:
SELECT synonym_name, table_owner, table_name FROM user_synonyms;
11. Data Dictionary Views:
The data dictionary views, also known as catalog views, let you monitor the state of the database in real time:
- The USER, ALL, and DBA views show information about schema objects that are accessible to you, at different levels of privilege.
- The V$ views show performance-related information.
- The _PRIVS views show privilege information for different combinations of users, roles, and objects.
Oracle Data Dictionary Concepts
The data dictionary is full of “Metadata”, information about what is going-on inside your database. The data dictionary is presented to us in the form of a number of views. The dictionary views come in two primary forms:
The DBA, ALL or USER views
- These views are used to manage database structures.
The V$ Dynamic Performance Views
- These views are used to monitor real time database statistics
Throughout the rest of this book we will introduce you to data dictionary views that you can use to manage your database. You will find the entire list of Oracle Data Dictionary views documented in the Oracle documentation online.
There are hundreds of views in the data dictionary. To see the depth of the data dictionary views, here are the views that store data about Oracle tables:
* dba_all_tables
* dba_indexes
* dba_ind_partitions
* dba_ind_subpartitions
* dba_object_tables
* dba_part_col_statistics
* dba_subpart_col_statistics
* dba_tables
* dba_tab_cols
* dba_tab_columns
* dba_tab_col_statistics
* dba_tab_partitions
* dba_tab_subpartitions
The data dictionary is a repository of information about the Oracle database, known as metadata. Metadata is “information about information,” and the data dictionary is information about the database.
Oracle Data Dictionary
Managing Oracle requires the use of a number of Oracle supplied views. These views include the data dictionary and the dynamic performance views. Together these views allow you to:
- Manage the database
- Tune the database
- Monitor the database
In this chapter we will fist look at the data dictionary views. We will then look at the dynamic performance views available in Oracle Database 10g.
At the heart of every Oracle database is the data dictionary. The data dictionary is generated when the database is first created. In this section we will discuss the Oracle data dictionary. In it we will discuss:
- The purpose of the data dictionary
- The architecture of the data dictionary
- Uses of the data dictionary
The Purpose of the Data Dictionary
Metadata is data about data, or data that defines other data. The Oracle data dictionary is metadata about the database. For example, if you create a table in Oracle, metadata about that table is stored in the data dictionary. Such things as column names, length, and other attributes are stored. Thus, the data dictionary contains a great volume of useful information about your database. Pretty much everything you would want to know about your database is contained in the data dictionary in some form.
As a DBA then, you can see why the data dictionary is so important. Since you can’t possibly remember everything about your database (like the names of all the tables and columns) Oracle remembers this for you. All you need to do is learn how to find that information. We will be showing you how to do this in a later section in this book.
The Architecture of the Data Dictionary
The data dictionary is created when the Oracle database is created. It is owned by the SYS user, and is stored principally in the SYSTEM tablespace, though some components are stored in the SYSAUX tablespace in Oracle Database 10g.
The data dictionary is comprised of a number of tables and Oracle views. Oracle wants you to keep your hands off these tables, and unless you are a real expert I’d recommend you do just that.
Of course, the data dictionary would be pretty worthless if we could not access the data. Oracle supplies a number of views that you can query that will give you direct access into the data dictionary tables. These views are generally tuned by Oracle for quick access to the underlying objects and the names of the views often reflect the use of that view much better than the names of the underlying objects. The data dictionary views come in three main flavors:
- User views
- All views
- DBA views
For example, if you want to look at user information there are three views, USER_USERS, ALL_USERS and dba_users. Each of these views sees the user a bit differently.
All views that start with USER only sees the information that pertains to the user you are logged in as. For example, if you are logged in as SCOTT, when you look at the user_tables view, you will only see information on tables that are owned by the SCOTT user. You might have access to tables in the GEORGE schema, but you won’t see them in the user_tables view. Here is an example of a simple query against the user_tables view:
SELECT table_name FROM user_tables;
The ALL views allow you to see all objects that you have access to. For example, if you are logged in as SCOTT and you query the all_tables view, you will see all the tables owned by SCOTT but you will also see any tables you have access to that are owned by GEORGE, or any other user. You have to have access rights to these objects (which you would have received via the grant command which we discussed in an earlier chapter).
Generally the two main differences between the USER and ALL views is that the owner of the object is included in the ALL views, and this is not included in the USER views which makes sense since you will only be seeing your objects. In this example, we query the all_tables view for all tables that start with EMP:
SELECT
table_name
FROM
all_tables
WHERE
table_name LIKE ’EMP%’;
The granddaddy of the data dictionary views are the DBA views. These views are unrestricted windows into all Oracle data dictionary objects. Because of this, they are only accessible by DBA’s (as the name seems to suggest). All DBA views start with DBA. In this example, we query the dba_tables view for all tables that start with EMP and owned by users whose names start with ROBERT:
SELECT
table_name
FROM
dba_tables
WHERE
table_name LIKE ’EMP%’
AND
owner like ’ROBERT%’;
You can find the data dictionary tables documented in the Oracle Database 10g Reference Guide, which is part of the overall Oracle database documentation set. There are almost 600 DBA views in Oracle Database 10g alone, and a like number of USER and ALL views. The ALL and USER views are pretty much children of the DBA views, and you will not find ALL or USER views for each DBA view. You can also find the views documented within the data dictionary itself. The DICTIONARY (or DICT for short) view contains all the tables of the data dictionary, plus comments on what each table is used for.
12. Sequence with CYCLE:
CYCLE
Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.
NOCYCLE
Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.
If MINVALUE is specified, the the seq will start again from minvalue, if MINVALUE is not specified, it will start with 1.
eg. CREATE SEQUENCE "INTADMIN"."SEQ_TEST2" MAXVALUE 10 INCREMENT BY 1 START WITH 5 NOCACHE CYCLE ;
No comments:
Post a Comment