What Is Oracle?
Oracle is a company. Oracle is also a database server, which manages data in a very structured way. It allows users to store and retrieve related data in a multiuser environment so that many users can concurrently access the same data. All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.
What Is an Oracle Database?
An Oracle database is a collection of data treated as a big unit in the database server.
What Is an Oracle Instance?
Every running Oracle database is associated with an Oracle instance. When a database is started on a database server (regardless of the type of computer), Oracle allocates a memory area called the System Global Area (SGA) and starts one or more Oracle processes. This combination of the SGA and the Oracle processes is called an Oracle instance. The memory and processes of an instance manage the associated database's data efficiently and serve the one or multiple users of the database.
What Is a Parameter File?
A parameter file is a file that contains a list of initialization parameters and a value for each parameter. You specify initialization parameters in a parameter file that reflect your particular installation. Oracle supports the following two types of parameter files:
What Is a Server Parameter File?
A server parameter file is a binary file that acts as a repository for initialization parameters. The server parameter file can reside on the machine where the Oracle database server executes. Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist across instance shutdown and startup.
What Is a Initialization Parameter File?
An initialization parameter file is a text file that contains a list of initialization parameters. The file should be written in the client's default character set. Sample initialization parameter files are provided on the Oracle distribution medium for each operating system. A sample file is sufficient for initial use, but you will probably want to modify the file to tune the database for best performance. Any changes will take effect after you completely shut down and restart the instance.
What is System Global Area (SGA)?
The System Global Area (SGA) is a memory area that contains data shared between all database users such as buffer cache and a shared pool of SQL statements. The SGA is allocated in memory when an Oracle database instance is started, and any change in the value will take effect at the next startup.
What is Program Global Area (PGA)?
A Program Global Area (PGA) is a memory buffer that is allocated for each individual database session and it contains session specific information such as SQL statement data or buffers used for sorting. The value specifies the total memory allocated by all sessions, and changes will take effect as new sessions are started.
What Is a User Account?
A user account is identified by a user name and defines the user's attributes, including the following:
Password for database authentication
Privileges and roles
Default tablespace for database objects
Default temporary tablespace for query processing work space
What Is the Relation of a User Account and a Schema?
User accounts and schemas have a one-to-one relation. When you create a user, you are also implicitly creating a schema for that user. A schema is a logical container for the database objects (such as tables, views, triggers, and so on) that the user creates. The schema name is the same as the user name, and can be used to unambiguously refer to objects owned by the user.
What Is a User Role?
A user role is a group of privileges. Privileges are assigned to users through user roles. You create new roles, grant privileges to the roles, and then grant roles to users.
What is a Database Schema?
A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include: tables, views, and other types of data objects.
What Is a Database Table?
A database table is a basic unit of data logical storage in an Oracle database. Data is stored in rows and columns. You define a table with a table name, such as employees, and a set of columns. You give each column a column name, such as employee_id, last_name, and job_id; a datatype, such as VARCHAR2, DATE, or NUMBER; and a width. The width can be predetermined by the datatype, as in DATE. If columns are of the NUMBER datatype, define precision and scale instead of width. A row is a collection of column information corresponding to a single record.
What Is a Table Index?
Index is an optional structure associated with a table that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.
What Is an Oracle Tablespace?
An Oracle tablespace is a big unit of logical storage in an Oracle database. It is managed and used by the Oracle server to store structures data objects, like tables and indexes.
Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.
What Is an Oracle Data File?
An Oracle data file is a big unit of physical storage in the OS file system. One or many Oracle data files are organized together to provide physical storage to a single Oracle tablespace.
What Is a Static Data Dictionary?
Data dictionary tables are not directly accessible, but you can access information in them through data dictionary views. To list the data dictionary views available to you, query the view DICTIONARY. Many data dictionary tables have three corresponding views:
An ALL_ view displays all the information accessible to the current user, including information from the current user's schema as well as information from objects in other schemas, if the current user has access to those objects by way of grants of privileges or roles.
A DBA_ view displays all relevant information in the entire database. DBA_ views are intended only for administrators. They can be accessed only by users with the SELECT ANY TABLE privilege. This privilege is assigned to the DBA role when the system is initially installed.
A USER_ view displays all the information from the schema of the current user. No special privileges are required to query these views.
What Is a Dynamic Performance View?
Oracle contains a set of underlying views that are maintained by the database server and accessible to the database administrator user SYS. These views are called dynamic performance views because they are continuously updated while a database is open and in use, and their contents relate primarily to performance. Although these views appear to be regular database tables, they are not. These views provide data on internal disk structures and memory structures. You can select from these views, but you can never update or alter them.
What Is a Recycle Bin?
Recycle bin is a logical storage to hold the tables that have been dropped from the database, in case it was dropped in error. Tables in recycle bin can be recovered back into database by the Flashback Drop action. Oracle database recycle save the same purpose as the recycle bin on your Windows desktop.
Recycle bin can be turned on or off in the recyclebin=on/off in your parametere file.
What Is a Database Table?
A database table is a basic unit of data logical storage in an Oracle database. Data is stored in rows and columns. You define a table with a table name, such as employees, and a set of columns. You give each column a column name, such as employee_id, last_name, and job_id; a datatype, such as VARCHAR2, DATE, or NUMBER; and a width. The width can be predetermined by the datatype, as in DATE. If columns are of the NUMBER datatype, define precision and scale instead of width. A row is a collection of column information corresponding to a single record.
How Many Types of Tables Supported by Oracle?
Oracle supports 4 types of tables based on how data is organized in storage:
Ordinary (heap-organized) table - This is the basic, general purpose type of table. Its data is stored as an unordered collection (heap)
Clustered table - A clustered table is a table that is part of a cluster. A cluster is a group of tables that share the same data blocks because they share common columns and are often used together.
Index-organized table - Unlike an ordinary (heap-organized) table, data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner. Besides storing the primary key column values of an index-organized table row, each index entry in the B-tree stores the nonkey column values as well.
Partitioned table - Partitioned tables allow your data to be broken down into smaller, more manageable pieces called partitions, or even subpartitions. Each partition can be managed individually, and can operate independently of the other partitions, thus providing a structure that can be better tuned for availability and performance.
How To Create a New Table in Your Schema?
If you want to create a new table in your own schema, you can log into the server with your account, and use the CREATE TABLE statement. The following script shows you how to create a table:
>.\bin\sqlplus /nolog
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE tip (id NUMBER(5) PRIMARY KEY,
2 subject VARCHAR(80) NOT NULL,
3 description VARCHAR(256) NOT NULL,
4 create_date DATE DEFAULT (sysdate));
Table created.
This scripts creates a testing table called "tip" with 4 columns in the schema associated with the log in account "HR".
How To Create a New Table by Selecting Rows from Another Table?
Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the CREATE TABLE...AS SELECT statement to do this. Here is an example script:
>.\bin\sqlplus /nolog
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE emp_dept_10
2 AS SELECT * FROM employees WHERE department_id=10;
Table created.
SQL> SELECT first_name, last_name, salary FROM emp_dept_10;
FIRST_NAME LAST_NAME SALARY
-------------------- ------------------------- ----------
Jennifer Whalen 4400
How To Turn On or Off Recycle Bin for the Instance?
You can turn on or off the recycle bin feature for an instance in the instance parameter file with "recyclebin=on/off". You can also turn on or off the recycle bin feature on the running instance with a SQL*Plus command, if you log in as SYSTEM. See the following example:
SQL> connect SYSTEM/fyicenter
Connected.
SQL> SHOW PARAMETERS RECYCLEBIN
NAME TYPE VALUE
------------------------------------ ----------- -------
recyclebin string on
SQL> ALTER SYSTEM SET RECYCLEBIN = OFF;
System altered.
SQL> SHOW PARAMETERS RECYCLEBIN
NAME TYPE VALUE
------------------------------------ ----------- -------
recyclebin string OFF
Warning: Turning off the recycle bin feature will give your users hard times on recovering dropped tables.
How To View the Dropped Tables in Your Recycle Bin?
You can look what's in your recycle bin through the predefined view called RECYCLEBIN. You can use the SELECT statement to list the dropped tables as shown in the following script:
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> SELECT COUNT(*) FROM emp_dept_90;
COUNT(*)
----------
3
SQL> DROP TABLE emp_dept_90;
Table dropped.
SQL> COL original_name FORMAT A14
SQL> SELECT object_name, original_name, droptime FROM recyclebin;
OBJECT_NAME ORIGINAL_NAME DROPTIME
------------------------------ -------------- -------------------
BIN$uaSS/heeQuys53HgXRhEEQ==$0 EMP_DEPT_10 2006-04-01:18:57:37
BIN$gSt95r7ATKGUPuALIHy4dw==$0 EMP_DEPT_10 2006-04-01:19:59:11
BIN$bLukbcgSQ6mK1P2QVRf+fQ==$0 EMP_DEPT_90 2006-04-01:20:47:22
As you can use the EMP_DEPT_10 was dropped twice. If the same table was dropped multiple times, you need to restore by using the object name in the recycle bin with FLASHBACK statement.
Note that RECYCLEBIN is just an alias of USER_RECYCLEBIN.
How To Empty Your Recycle Bin?
If your recycle bin is full, or you just want to clean your recycle bin to get rid of all the dropped tables, you can empty it by using the PURGE statement in two formats:
Here is an example script on how to use the PURGE statement:
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE emp_dept_10
2 AS SELECT * FROM employees WHERE department_id=10;
Table created.
SQL> DROP TABLE emp_dept_10;
Table dropped.
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> DROP TABLE emp_dept_90;
Table dropped.
SQL> SELECT COUNT(*) FROM recyclebin;
COUNT(*)
----------
5
SQL> PURGE TABLE emp_dept_90;
Table purged.
SQL> SELECT COUNT(*) FROM recyclebin;
COUNT(*)
----------
4
SQL> PURGE RECYCLEBIN;
Recyclebin purged.
SQL> SELECT COUNT(*) FROM recyclebin;
COUNT(*)
----------
0
How To Turn On or Off Recycle Bin for the Session?
If you want to control the recycle bin feature in your own session, you can use the ALTER SESSION statement to turn on or off. Here is an example SQL script:
SQL> connect HR/fyicenter
Connected.
SQL> SELECT COUNT(*) FROM recyclebin;
COUNT(*)
----------
0
SQL> ALTER SESSION SET recyclebin = off;
Session altered.
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> DROP TABLE emp_dept_90;
Table dropped.
SQL> SELECT COUNT(*) FROM recyclebin;
COUNT(*)
----------
0
Warning: Turning off the recycle bin feature in your session will give yourself hard times on recovering dropped tables.
How To List All Tables in Your Schema?
If you log in with your Oracle account, and you want to get a list of all tables in your schema, you can get it through the USER_TABLES view with a SELECT statement, as shown in the following SQL script:
SQL> connect HR/fyicenter
Connected.
SQL> SELECT table_name, status, num_rows FROM USER_TABLES;
TABLE_NAME STATUS NUM_ROWS
------------------------------ -------- ----------
REGIONS VALID 4
LOCATIONS VALID 23
DEPARTMENTS VALID 27
JOBS VALID 19
EMPLOYEES VALID 107
JOB_HISTORY VALID 10
COUNTRIES VALID 25
7 rows selected.
What Is a Table Index?
Index is an optional structure associated with a table that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.
How To List All Indexes in Your Schema?
If you log in with your Oracle account, and you want to get a list of all indexes in your schema, you can get it through the USER_INDEXES view with a SELECT statement, as shown in the following SQL script:
SELECT index_name, table_name, uniqueness
FROM USER_INDEXES WHERE table_name = 'EMPLOYEES';
INDEX_NAME TABLE_NAME UNIQUENES
----------------------- --------------------- ---------
EMP_EMAIL_UK EMPLOYEES UNIQUE
EMP_EMP_ID_PK EMPLOYEES UNIQUE
EMP_DEPARTMENT_IX EMPLOYEES NONUNIQUE
EMP_JOB_IX EMPLOYEES NONUNIQUE
EMP_MANAGER_IX EMPLOYEES NONUNIQUE
EMP_NAME_IX EMPLOYEES NONUNIQUE
As you can see, the pre-defined table EMPLOYEES has 6 indexes defined in the default sample database.
How To Recover a Dropped Index?
If you have the recycle bin feature turned on, dropped indexes are stored in the recycle bin. But it seems to be command to restore a dropped index out of the recycle bin. FLASHBACK INDEX is not a valid statement. See the following script:
ALTER SESSION SET recyclebin = on;
Statement processed.
CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
first_name VARCHAR(80) NOT NULL,
last_name VARCHAR(80) NOT NULL,
birth_date DATE NOT NULL,
social_number VARCHAR(80) UNIQUE NOT NULL);
Table created.
CREATE INDEX student_birth_date ON student(birth_date);
Index created.
DROP INDEX STUDENT_BIRTH_DATE;
Index dropped.
SELECT object_name, original_name, type, droptime FROM recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME
------------------------------ ------------------ ----- ----------
BIN$1LlsjTxERKOKKq+Cuz0I7A==$0 STUDENT_BIRTH_DATE INDEX 2006-04-01
FLASHBACK INDEX student_birth_date TO BEFORE DROP;
ORA-00905: missing keyword
How To See the Table Columns Used in an Index?
You can a list of indexes in your schema from the USER_INDEXES view, but it will not give you the columns used in each index in the USER_INDEXES view. If you want to see the columns used in an index, you can use the USER_IND_COLUMNS view. Here is an example script for you:
SELECT index_name, table_name, column_name
FROM USER_IND_COLUMNS WHERE table_name = 'EMPLOYEES';
INDEX_NAME TABLE_NAME COLUMN_NAME
-------------------- ---------------- ----------------
EMP_EMAIL_UK EMPLOYEES EMAIL
EMP_EMP_ID_PK EMPLOYEES EMPLOYEE_ID
EMP_DEPARTMENT_IX EMPLOYEES DEPARTMENT_ID
EMP_JOB_IX EMPLOYEES JOB_ID
EMP_MANAGER_IX EMPLOYEES MANAGER_ID
EMP_NAME_IX EMPLOYEES LAST_NAME
EMP_NAME_IX EMPLOYEES FIRST_NAME
Tablespace & Datafiles
What Is an Oracle Tablespace?
An Oracle tablespace is a big unit of logical storage in an Oracle database. It is managed and used by the Oracle server to store structures data objects, like tables and indexes.
What Is an Oracle Data File?
An Oracle data file is a big unit of physical storage in the OS file system. One or many Oracle data files are organized together to provide physical storage to a single Oracle tablespace.
How a Tablespace Is Related to Data Files?
Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.
How a Database Is Related to Tablespaces?
A database's data is collectively stored in the datafiles that constitute each tablespace of the database. For example, the simplest Oracle database would have one tablespace and one datafile. Another database can have three tablespaces, each consisting of two datafiles (for a total of six datafiles).
How To View the Tablespaces in the Current Database?
If you want to get a list of all tablespaces used in the current database instance, you can use the DBA_TABLESPACES view as shown in the following SQL script example:
SQL> connect SYSTEM/fyicenter
Connected.
SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM USER_TABLESPACES;
TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------
SYSTEM ONLINE PERMANENT
UNDO ONLINE UNDO
SYSAUX ONLINE PERMANENT
TEMP ONLINE TEMPORARY
USERS ONLINE PERMANENT
What Are the Predefined Tablespaces in a Database?
When you create a new database, Oracle server will create 4 required tablespaces for the new database:
SYSTEM Tablespace - Every Oracle database contains a tablespace named SYSTEM, which Oracle creates automatically when the database is created. The SYSTEM tablespace is always online when the database is open.
SYSAUX Tablespace - The SYSAUX tablespace was installed as an auxiliary tablespace to the SYSTEM tablespace when you created your database. Some database components that formerly created and used separate tablespaces now occupy the SYSAUX tablespace.
UNDO Tablespace - UNDO tablespaces are special tablespaces used solely for storing undo information. You cannot create any other segment types (for example, tables or indexes) in undo tablespaces. Each database contains zero or more undo tablespaces. In automatic undo management mode, each Oracle instance is assigned one (and only one) undo tablespace. Undo data is managed within an undo tablespace using undo segments that are automatically created and maintained by Oracle.
TEMP Tablespace - When the SYSTEM tablespace is locally managed, you must define at least one default temporary tablespace when creating a database. A locally managed SYSTEM tablespace cannot be used for default temporary storage.
How To View the Data Files in the Current Database?
If you want to get a list of all tablespaces used in the current database instance, you can use the DBA_TABLESPACES view as shown in the following SQL script example:
SQL> connect SYSTEM/fyicenter
Connected.
SQL> col tablespace_name format a16;
SQL> col file_name format a36;
SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES FROM DBA_DATA_FILES;
TABLESPACE_NAME FILE_NAME BYTES
---------------- ---------------------------------- ----------
USERS \ORACLEXE\ORADATA\XE\USERS.DBF 104857600
SYSAUX \ORACLEXE\ORADATA\XE\SYSAUX.DBF 461373440
UNDO \ORACLEXE\ORADATA\XE\UNDO.DBF 94371840
SYSTEM \ORACLEXE\ORADATA\XE\SYSTEM.DBF 356515840
How To Create an Oracle Database?
There are two ways to create a new database:
How To Create an Oracle Database Manually?
Based on Oracle's Administrator Guide, there are 11 steps to create a database with the CREATE DATABASE statement:
Step 1: Decide on Your Instance Identifier (SID)
Step 2: Establish the Database Administrator Authentication Method
Step 3: Create the Initialization Parameter File
Step 4: Connect to the Instance
Step 5: Create a Server Parameter File (Recommended)
Step 6: Start the Instance
Step 7: Issue the CREATE DATABASE Statement
Step 8: Create Additional Tablespaces
Step 9: Run Scripts to Build Data Dictionary Views
Step 10: Run Scripts to Install Additional Options (Optional)
Step 11: Back Up the Database.
How To Build Data Dictionary View an New Database?
This is Step 9. The Oracle Administrator Guide suggests to run two SQL scripts provided by Oracle as shown bellow:
SQL> @/u01/oracle/rdbms/admin/catalog.sql
SQL> @/u01/oracle/rdbms/admin/catproc.sql
