List of additional steps to do for cleaning the system completely after uninstall using universal installer.
# Stop any Oracle services that have been left running.
Start->Settings->Control Panel->Services
Look for any services with names starting with ‘Oracle’ and stop them.
# Run regedit and delete the following keys (some may have slightly different names in your registry):
HKEY_CURRENT_USER\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet Services\EventLog\Application\Oracle.oracle
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet Services\OracleDBConsole
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet Services\Oracle10g_home
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet Services\OraclService
Note that the services control panel will still show the old services until you reboot.
# Delete the Oracle home directory
C:\Oracle
# Delete the Oracle Program Files directory:
C:\Program Files\Oracle
# Delete the Oracle Start Menu shortcuts directory:
C:\Documents and Settings\All Users\Start Menu\Programs\Oracle*
Where * indicates the name of your install. Look for and remove all Oracle directories from that location.
# Remove Oracle refereces from the path. To edit your path go to:
Start->Settings->Control Panel->System->Advanced->Environment Variables
Edit both of the environment variables user PATH and system PATH. Remove any Oracle references in them.
# Remove Oracle.DataAccess and any Polic.Oracle files from the GAC which is at:
C:\Windows\assembly\
Thursday, January 31, 2008
This is a collection of 15 FAQs for Oracle DBA on creating Oracle database instances manually using CREATE DATABASE statement. Items in this FAQ collection are organized together to form a complete tutorial guide on creating a new database instance manually. Topics included in this FAQ are:
How To Create an Oracle Database?
How To Create an Oracle Database Manually?
How To Select an Oracle System ID (SID)?
How To Establish Administrator Authentication to the Server?
How To Create an Initialization Parameter File?
How To Connect the Oracle Server as SYSDBA?
How To Create a Server Parameter File?
How To Start an Oracle Instance?
How To Start a Specific Oracle Instance?
How To Start Instance with a Minimal Initialization Parameter File?
How To Run CREATE DATABASE Statement?
How To Do Clean Up If CREATE DATABASE Failed?
How To Run CREATE DATABASE Statement Again?
How To Create Additional Tablespaces for an New Database?
How To Build Data Dictionary View an New Database?
Sample scripts presented in this FAQ are based Oracle 10g Express Edition (XE) for Windows. The script in Step 7 failed due some unknown reason. But it might work on Oracle 10g Standard Edition.
How To Create an Oracle Database?
There are two ways to create a new database:
• Use the Database Configuration Assistant (DBCA) to create a database interactively.
• Use the CREATE DATABASE statement to create a database manually.
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.
Other items in this FAQ collection will follow those steps to help you creating a new database manually from beginning to end.
How To Select an Oracle System ID (SID)?
This is Step 1. If you are planning to create a new database, you need to select an Oracle System ID (SID). This ID will be used to identify the new Oracle database and its Oracle instance. SID must be unique if you want to run multiple databases on a single server.
Let's set SID for the new database to be: FYI.
How To Establish Administrator Authentication to the Server?
This is Step 2. There are two ways to establish administrator authentication to a new database.
• Use a password file.
• Use operating system (OS) authentication.
Using OS authentication is easier on Windows system. If you used your own Windows user account to install Oracle server, it will put your Windows user account into a special Window's user group called SYSDBA. This Window's user group will be fully trusted by Oracle server with SYSDBA privilege.
To continue with other steps, make sure you logged into the Windows system with a user account in the SYSDBA group.
How To Create an Initialization Parameter File?
This is Step 3. To run an Oracle database as an Oracle instance, you need to create an initialization parameter file, which contains a set of initialization parameters.
The easiest way to create an initialization parameter file to copy from the sample file provided by Oracle. You can do this in a command window as shown below:
>cd $ORACLE_HOME
>copy .\config\scripts\init.ora .\database\initFYI_ini.ora
>edit .\database\initFYI_ini.ora
(replace XE by FYI)
In this example, only the SID is changed from XE to FYI. All other parameters are maintained as is.
How To Connect the Oracle Server as SYSDBA?
This is Step 4. The best way to connect to the Oracle server as an administrator is to use SQL*Plus. You need to run SQL*Plus with /nolog option and use the CONNECT with blank user name, blank password and AS SYSDBA option. Here is a sample session:
>cd $ORACLE_HOME
>.\bin\sqlplus /nolog
SQL> CONNECT / AS SYSDBA
Connected.
How To Create a Server Parameter File?
This is Step 5. The initialization parameter file is good to get an Oracle database instance started. But it is not ideal run an instance as production. You need to convert the initialization parameter file into a Server Parameter File (SPFile) using the CREATE SPFILE statement. The script below shows you how do this:
SQL> CREATE SPFILE=$ORACLE_HOME/dbs/SPFILEFYI.ora
2 FROM PFILE=$ORACLE_HOME/database/initFYI_ini.ora;
File created.
Note that $ORACLE_HOME should be replaced by the real path name where your Oracle server is intalled.
The SPFile should be located in the expected directory and named as SPFILE($SID).ora.
How To Start an Oracle Instance?
This is Step 6. Now you are ready to start the new Oracle Instance without any database. This instance will be used to create a database. Starting an instance without database can be done by using STARTUP NOMOUNT statement as shown below:
>.\bin\sqlplus /nolog
SQL> CONNECT / AS SYSDBA
Connected.
SQL> SHUTDOWN
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORA-00821: Specified value of sga_target 16M is too small,
needs to be at least 20M
The SHUTDOWN command is need to bring the default instance XE down.
The STARTUP NOMOUNT command failed because it tried to start the default instance XE, and there is a bad parameter in the XE instance SPFile.
See the next FAQ question to find another way to start the new instance FYI.
How To Start a Specific Oracle Instance?
A simple way to start a specific Oracle instance is to start the instance with the PFILE option as shown in the following example:
>.\bin\sqlplus /nolog
SQL> CONNECT / AS SYSDBA
Connected.
SQL> STARTUP NOMOUNT
PFILE=$ORACLE_HOME/database/initFYI_ini.ora
ORA-02778: Name given for the log directory is invalid
The PFILE option allows you to specify the initialization parameter file of a specific Oracle instance. But the initialization parameter file created in Step 3 has some problem with the log directory.
How To Start Instance with a Minimal Initialization Parameter File?
The sample initialization parameter file provided by Oracle seems to be not working. But we can try to start the new instance with a minimal initialization parameter file (PFile). First you can create another PFile, $ORACLE_HOME/database/initFYI_ini_min.ora, as shown below:
db_name=FYI
control_files=("\oraclexe\oradata\FYI\control.dbf")
undo_management=AUTO
Then start the FYI instance again:
SQL> CONNECT / AS SYSDBA
Connected.
SQL> STARTUP NOMOUNT
PFILE=$ORACLE_HOME/database/initFYI_ini_min.ora
ORACLE instance started.
Total System Global Area 113246208 bytes
Fixed Size 1286028 bytes
Variable Size 58720372 bytes
Database Buffers 50331648 bytes
Redo Buffers 2908160 bytes
(Continued on next part...)
How To Run CREATE DATABASE Statement?
This is Step 7. Oracle Administrator Guide provided a sample CREATE DATABASE statement. But it is a long statement. You can modify and same it in a file, $ORACLE_HOME/configscripts/create_database_fyi.sql, and run the file within SQL*Plus. Here is a copy of the modified CREATE DATABASE statement:
CREATE DATABASE FYI
USER SYS IDENTIFIED BY fyicenter
USER SYSTEM IDENTIFIED BY fyicenter
LOGFILE GROUP 1 ('/oraclexe/oradata/FYI/redo01.log') SIZE 10M,
GROUP 2 ('/oraclexe/oradata/FYI/redo02.log') SIZE 10M,
GROUP 3 ('/oraclexe/oradata/FYI/redo03.log') SIZE 10M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/oraclexe/oradata/FYI/system01.dbf' SIZE 32M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/oraclexe/oradata/FYI/sysaux01.dbf' SIZE 32M REUSE
DEFAULT TABLESPACE tbs_1
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/oraclexe/oradata/FYI/temp01.dbf'SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/oraclexe/oradata/FYI/undotbs01.dbf'
SIZE 20M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Here is how to run this long CREATE DATABASE statement:
SQL> @$ORACLE_HOME\config\scripts\create_database_fyi.sql;
CREATE DATABASE FYI
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-00200: control file could not be created
ORA-00202: control file: 'C:\ORACLEXE\ORADATA\FYI\CONTROL.DBF'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
Don't worry about the error messages. They are caused by the missing directory for data files in file system.
How To Do Clean Up If CREATE DATABASE Failed?
To better organize data files, you should create a dedicated directory for each Oracle database. This can be done by using Windows file explorer to create the \oraclexe\oradata\fyi\ directory. Try the CREATE DATABASE statement again, when you have the directory ready.
If your CREATE DATABASE statement failed half way again, you may have to clean up the partial result of the CREATE DATABASE statement. Here is a list of suggestions for you:
• Run SHUTDOWN command to stop the partial started database instance.
• Remove all files in FYI directory: \oraclexe\oradata\fyi\
• Run STARTUP NOMOUNT PFILE command to start the empty instance again to be ready for CREATE DATABASE statement.
How To Run CREATE DATABASE Statement Again?
After cleaning up the results of a previously failed CREATE DATABASE statement, you can run the CREATE DATABASE statement again as shown below:
SQL> @$ORACLE_HOME\config\scripts\create_database_fyi.sql;
CREATE DATABASE FYI
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
Something went wrong again. This time it might related the limitation that Oracle 10g XE only supports one database instance. With the default instance called "XE" already defined, creating another database instance might be not allowed.
How To Create Additional Tablespaces for an New Database?
This is Step 8. Creating additional tablespaces can be done by using the CREATE TABLESPACE statement as shown in the following sample script:
SQL> CREATE TABLESPACE users
2 DATAFILE '/oraclexe/oradata/FYI/users01.dbf' SIZE 10M;
SQL> CREATE TABLESPACE indx
2 DATAFILE '/oraclexe/oradata/FYI/indx01.dbf' SIZE 10M;
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
How To Create an Oracle Database?
How To Create an Oracle Database Manually?
How To Select an Oracle System ID (SID)?
How To Establish Administrator Authentication to the Server?
How To Create an Initialization Parameter File?
How To Connect the Oracle Server as SYSDBA?
How To Create a Server Parameter File?
How To Start an Oracle Instance?
How To Start a Specific Oracle Instance?
How To Start Instance with a Minimal Initialization Parameter File?
How To Run CREATE DATABASE Statement?
How To Do Clean Up If CREATE DATABASE Failed?
How To Run CREATE DATABASE Statement Again?
How To Create Additional Tablespaces for an New Database?
How To Build Data Dictionary View an New Database?
Sample scripts presented in this FAQ are based Oracle 10g Express Edition (XE) for Windows. The script in Step 7 failed due some unknown reason. But it might work on Oracle 10g Standard Edition.
How To Create an Oracle Database?
There are two ways to create a new database:
• Use the Database Configuration Assistant (DBCA) to create a database interactively.
• Use the CREATE DATABASE statement to create a database manually.
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.
Other items in this FAQ collection will follow those steps to help you creating a new database manually from beginning to end.
How To Select an Oracle System ID (SID)?
This is Step 1. If you are planning to create a new database, you need to select an Oracle System ID (SID). This ID will be used to identify the new Oracle database and its Oracle instance. SID must be unique if you want to run multiple databases on a single server.
Let's set SID for the new database to be: FYI.
How To Establish Administrator Authentication to the Server?
This is Step 2. There are two ways to establish administrator authentication to a new database.
• Use a password file.
• Use operating system (OS) authentication.
Using OS authentication is easier on Windows system. If you used your own Windows user account to install Oracle server, it will put your Windows user account into a special Window's user group called SYSDBA. This Window's user group will be fully trusted by Oracle server with SYSDBA privilege.
To continue with other steps, make sure you logged into the Windows system with a user account in the SYSDBA group.
How To Create an Initialization Parameter File?
This is Step 3. To run an Oracle database as an Oracle instance, you need to create an initialization parameter file, which contains a set of initialization parameters.
The easiest way to create an initialization parameter file to copy from the sample file provided by Oracle. You can do this in a command window as shown below:
>cd $ORACLE_HOME
>copy .\config\scripts\init.ora .\database\initFYI_ini.ora
>edit .\database\initFYI_ini.ora
(replace XE by FYI)
In this example, only the SID is changed from XE to FYI. All other parameters are maintained as is.
How To Connect the Oracle Server as SYSDBA?
This is Step 4. The best way to connect to the Oracle server as an administrator is to use SQL*Plus. You need to run SQL*Plus with /nolog option and use the CONNECT with blank user name, blank password and AS SYSDBA option. Here is a sample session:
>cd $ORACLE_HOME
>.\bin\sqlplus /nolog
SQL> CONNECT / AS SYSDBA
Connected.
How To Create a Server Parameter File?
This is Step 5. The initialization parameter file is good to get an Oracle database instance started. But it is not ideal run an instance as production. You need to convert the initialization parameter file into a Server Parameter File (SPFile) using the CREATE SPFILE statement. The script below shows you how do this:
SQL> CREATE SPFILE=$ORACLE_HOME/dbs/SPFILEFYI.ora
2 FROM PFILE=$ORACLE_HOME/database/initFYI_ini.ora;
File created.
Note that $ORACLE_HOME should be replaced by the real path name where your Oracle server is intalled.
The SPFile should be located in the expected directory and named as SPFILE($SID).ora.
How To Start an Oracle Instance?
This is Step 6. Now you are ready to start the new Oracle Instance without any database. This instance will be used to create a database. Starting an instance without database can be done by using STARTUP NOMOUNT statement as shown below:
>.\bin\sqlplus /nolog
SQL> CONNECT / AS SYSDBA
Connected.
SQL> SHUTDOWN
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORA-00821: Specified value of sga_target 16M is too small,
needs to be at least 20M
The SHUTDOWN command is need to bring the default instance XE down.
The STARTUP NOMOUNT command failed because it tried to start the default instance XE, and there is a bad parameter in the XE instance SPFile.
See the next FAQ question to find another way to start the new instance FYI.
How To Start a Specific Oracle Instance?
A simple way to start a specific Oracle instance is to start the instance with the PFILE option as shown in the following example:
>.\bin\sqlplus /nolog
SQL> CONNECT / AS SYSDBA
Connected.
SQL> STARTUP NOMOUNT
PFILE=$ORACLE_HOME/database/initFYI_ini.ora
ORA-02778: Name given for the log directory is invalid
The PFILE option allows you to specify the initialization parameter file of a specific Oracle instance. But the initialization parameter file created in Step 3 has some problem with the log directory.
How To Start Instance with a Minimal Initialization Parameter File?
The sample initialization parameter file provided by Oracle seems to be not working. But we can try to start the new instance with a minimal initialization parameter file (PFile). First you can create another PFile, $ORACLE_HOME/database/initFYI_ini_min.ora, as shown below:
db_name=FYI
control_files=("\oraclexe\oradata\FYI\control.dbf")
undo_management=AUTO
Then start the FYI instance again:
SQL> CONNECT / AS SYSDBA
Connected.
SQL> STARTUP NOMOUNT
PFILE=$ORACLE_HOME/database/initFYI_ini_min.ora
ORACLE instance started.
Total System Global Area 113246208 bytes
Fixed Size 1286028 bytes
Variable Size 58720372 bytes
Database Buffers 50331648 bytes
Redo Buffers 2908160 bytes
(Continued on next part...)
How To Run CREATE DATABASE Statement?
This is Step 7. Oracle Administrator Guide provided a sample CREATE DATABASE statement. But it is a long statement. You can modify and same it in a file, $ORACLE_HOME/configscripts/create_database_fyi.sql, and run the file within SQL*Plus. Here is a copy of the modified CREATE DATABASE statement:
CREATE DATABASE FYI
USER SYS IDENTIFIED BY fyicenter
USER SYSTEM IDENTIFIED BY fyicenter
LOGFILE GROUP 1 ('/oraclexe/oradata/FYI/redo01.log') SIZE 10M,
GROUP 2 ('/oraclexe/oradata/FYI/redo02.log') SIZE 10M,
GROUP 3 ('/oraclexe/oradata/FYI/redo03.log') SIZE 10M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/oraclexe/oradata/FYI/system01.dbf' SIZE 32M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/oraclexe/oradata/FYI/sysaux01.dbf' SIZE 32M REUSE
DEFAULT TABLESPACE tbs_1
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/oraclexe/oradata/FYI/temp01.dbf'SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/oraclexe/oradata/FYI/undotbs01.dbf'
SIZE 20M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Here is how to run this long CREATE DATABASE statement:
SQL> @$ORACLE_HOME\config\scripts\create_database_fyi.sql;
CREATE DATABASE FYI
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-00200: control file could not be created
ORA-00202: control file: 'C:\ORACLEXE\ORADATA\FYI\CONTROL.DBF'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
Don't worry about the error messages. They are caused by the missing directory for data files in file system.
How To Do Clean Up If CREATE DATABASE Failed?
To better organize data files, you should create a dedicated directory for each Oracle database. This can be done by using Windows file explorer to create the \oraclexe\oradata\fyi\ directory. Try the CREATE DATABASE statement again, when you have the directory ready.
If your CREATE DATABASE statement failed half way again, you may have to clean up the partial result of the CREATE DATABASE statement. Here is a list of suggestions for you:
• Run SHUTDOWN command to stop the partial started database instance.
• Remove all files in FYI directory: \oraclexe\oradata\fyi\
• Run STARTUP NOMOUNT PFILE command to start the empty instance again to be ready for CREATE DATABASE statement.
How To Run CREATE DATABASE Statement Again?
After cleaning up the results of a previously failed CREATE DATABASE statement, you can run the CREATE DATABASE statement again as shown below:
SQL> @$ORACLE_HOME\config\scripts\create_database_fyi.sql;
CREATE DATABASE FYI
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
Something went wrong again. This time it might related the limitation that Oracle 10g XE only supports one database instance. With the default instance called "XE" already defined, creating another database instance might be not allowed.
How To Create Additional Tablespaces for an New Database?
This is Step 8. Creating additional tablespaces can be done by using the CREATE TABLESPACE statement as shown in the following sample script:
SQL> CREATE TABLESPACE users
2 DATAFILE '/oraclexe/oradata/FYI/users01.dbf' SIZE 10M;
SQL> CREATE TABLESPACE indx
2 DATAFILE '/oraclexe/oradata/FYI/indx01.dbf' SIZE 10M;
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
Oracle DBA FAQ - Oracle Basic Concepts
This is a collection of 17 FAQs for Oracle DBA on fundamental concepts. The clear answers and sample scripts provided can be used as learning tutorials or interview preparation guides.
What Is Oracle?
What Is an Oracle Database?
What Is an Oracle Instance?
What Is a Parameter File?
What Is a Server Parameter File?
What Is a Initialization Parameter File?
What is System Global Area (SGA)?
What is Program Global Area (PGA)?
What Is a User Account?
What Is the Relation of a User Account and a Schema?
What Is a User Role?
What is a Database Schema?
What Is a Database Table?
What Is a Table Index?
What Is an Oracle Tablespace?
What Is an Oracle Data File?
What Is a Static Data Dictionary?
What Is a Dynamic Performance View?
What Is a Recycle Bin?
What Is SQL*Plus?
What Is Transport Network Substrate (TNS)?
What Is Open Database Communication (ODBC)?
More...
Oracle DBA FAQ - Introduction to Oracle Database 10g Express Edition
This is a collection of 21 FAQs for Oracle DBA on Oracle 10g Express Edition with installation and basic introduction. The clear answers and sample scripts provided can be used as learning tutorials or interview preparation guides.
What is Oracle Database 10g Express Edition?
What Are the Limitations Oracle Database 10g XE?
What Operating Systems Are Supported by Oracle Database 10g XE?
How To Download Oracle Database 10g XE?
How To Install Oracle Database 10g XE?
How To Check Your Oracle Database 10g XE Installation?
How To Shutdown Your 10g XE Server?
How To Start Your 10g XE Server?
How Much Memory Your 10g XE Server Is Using?
How To Start Your 10g XE Server from Command Line?
How To Shutdown Your 10g XE Server from Command Line?
How To Unlock the Sample User Account?
How To Change System Global Area (SGA)?
How To Change Program Global Area (PGA)?
What Happens If You Set the SGA Too Low?
What To Do If the StartBD.bat Failed to Start the XE Instance?
How To Login to the Server without an Instance?
How To Use "startup" Command to Start Default Instance?
Where Are the Settings Stored for Each Instance?
What To Do If the Binary SPFile Is Wrong for the Default Instance?
How To Check the Server Version?
More...
Oracle DBA FAQ - Introduction to Command-Line SQL*Plus Client Tool
A collection of 25 FAQs on Oracle command-line SQL*Plus client tool. Clear answers are provided with tutorial exercises on creating tnsnames.ora and connecting to Oracle servers; SQL*Plus settings and environment variables; saving query output to files; getting query performance reports.
What Is SQL*Plus?
How To Start the Command-Line SQL*Plus?
How To Get Help at the SQL Prompt?
What Information Is Needed to Connect SQL*Plus an Oracle Server?
What Is a Connect Identifier?
How To Connect a SQL*Plus Session to an Oracle Server?
What Happens If You Use a Wrong Connect Identifier?
What To Do If DBA Lost the SYSTEM Password?
What Types of Commands Can Be Executed in SQL*Plus?
How To Run SQL Commands in SQL*Plus?
How To Run PL/SQL Statements in SQL*Plus?
How To Change SQL*Plus System Settings?
How To Look at the Current SQL*Plus System Settings?
What Are SQL*Plus Environment Variables?
How To Generate Query Output in HTML Format?
What Is Output Spooling in SQL*Plus?
How To Save Query Output to a Local File?
What Is Input Buffer in SQL*Plus?
How To Revise and Re-Run the Last SQL Command?
How Run SQL*Plus Commands That Are Stored in a Local File?
How To Use SQL*Plus Built-in Timers?
What Is Oracle Server Autotrace?
How To Set Up Autotrace for a User Account?
How To Get Execution Path Reports on Query Statements?
How To Get Execution Statistics Reports on Query Statements?
More...
Oracle DBA FAQ - Understanding SQL Basics
A collection of 28 FAQs on Oracle SQL language basics. Clear answers are provided with tutorial exercises on data types, data literals, date and time values, data and time intervals, converting to dates and times, NULL values, pattern matches.
What Is SQL?
How Many Categories of Data Types?
What Are the Oracle Built-in Data Types?
What Are the Differences between CHAR and NCHAR?
What Are the Differences between CHAR and VARCHAR2?
What Are the Differences between NUMBER and BINARY_FLOAT?
What Are the Differences between DATE and TIMESTAMP?
What Are the Differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND?
What Are the Differences between BLOB and CLOB?
What Are the ANSI Data Types Supported in Oracle?
How To Write Text Literals?
How To Write Numeric Literals?
How To Write Date and Time Literals?
How To Write Date and Time Interval Literals?
How To Convert Numbers to Characters?
How To Convert Characters to Numbers?
How To Convert Dates to Characters?
How To Convert Characters to Dates?
How To Convert Times to Characters?
How To Convert Characters to Times?
What Is NULL?
How To Use NULL as Conditions?
How To Concatenate Two Text Values?
How To Increment Dates by 1?
How To Calculate Date and Time Differences?
How To Use IN Conditions?
How To Use LIKE Conditions?
How To Use Regular Expression in Pattern Match Conditions?
More...
Oracle DBA FAQ - Understanding SQL DDL Statements
A collection of 11 FAQs on Oracle SQL DDL statements. Clear answers are provided with tutorial exercises on creating, altering and dropping tables, indexes, and views.
What Are DDL Statements?
How To Create a New Table?
How To Create a New Table by Selecting Rows from Another Table?
How To Add a New Column to an Existing Table?
How To Delete a Column in an Existing Table?
How To Drop an Existing Table?
How To Create a Table Index?
How To Rename an Index?
How To Drop an Index?
How To Create a New View?
How To Drop an Existing View?
More...
Oracle DBA FAQ - Understanding SQL DML Statements
A collection of 15 FAQs on Oracle SQL DML statements. Clear answers are provided with tutorial exercises on inserting, updating and deleting rows from database tables.
What Are DML Statements?
How To Create a Testing Table?
How To Set Up SQL*Plus Output Format?
How To Insert a New Row into a Table?
How To Specify Default Values in INSERT Statement?
How To Omit Columns with Default Values in INSERT Statement?
How To Insert Multiple Rows with One INSERT Statement?
How To Update Values in a Table?
How To Update Values on Multiple Rows?
How To Use Existing Values in UPDATE Statements?
How To Use Values from Other Tables in UPDATE Statements?
What Happens If the UPDATE Subquery Returns Multiple Rows?
How To Delete an Existing Row from a Table?
How To Delete Multiple Rows from a Table?
How To Delete All Rows a Table?
More...
Oracle DBA FAQ - Understanding SQL SELECT Query Statements
A collection of 33 FAQs on Oracle SQL SELECT query statements. Clear answers are provided with tutorial exercises on selecting rows and columns from tables and views, sorting and counting query outputs, grouping outputs and applying group functions, joining tables, using subqueries.
What Is a SELECT Query Statement?
How To Select All Columns of All Rows from a Table?
How To Select Some Columns from a Table?
How To Select Some Rows from a Table?
How To Sort the Query Output?
Can the Query Output Be Sorted by Multiple Columns?
How To Sort Output in Descending Order?
How To Use SELECT Statement to Count the Number of Rows?
Can SELECT Statements Be Used on Views?
How To Filter Out Duplications in the Returning Rows?
What Are Group Functions?
How To Use Group Functions in the SELECT Clause?
Can Group Functions Be Mixed with Non-group Selection Fields?
How To Divide Query Output into Groups?
How To Apply Filtering Criteria at Group Level?
How To Count Duplicated Values in a Column?
Can Multiple Columns Be Used in GROUP BY?
Can Group Functions Be Used in the ORDER BY Clause?
How To Join Two Tables in a Single Query?
How To Write a Query with an Inner Join?
How To Define and Use Table Alias Names?
How To Write a Query with a Left Outer Join?
How To Write a Query with a Right Outer Join?
How To Write a Query with a Full Outer Join?
How To Write an Inner Join with the WHERE Clause?
How To Write a Left Outer Join with the WHERE Clause?
How To Name Query Output Columns?
What Is a Subquery?
How To Use Subqueries with the IN Operator?
How To Use Subqueries with the EXISTS Operator?
How To Use Subqueries in the FROM clause?
How To Count Groups Returned with the GROUP BY Clause?
How To Return Top 5 Rows?
More...
Oracle DBA FAQ - Understanding SQL Transaction Management
A collection of 22 FAQs on Oracle SQL transaction management. Clear answers are provided with tutorial exercises on starting and ending transactions, committing and rolling back transactions, transaction/statement-level read consistency, read committed isolation level, locks and dead locks.
What Is a Transaction?
How To Start a New Transaction?
How To End the Current Transaction?
How To Create a Testing Table?
How To Commit the Current Transaction?
How To Rollback the Current Transaction?
What Happens to the Current Transaction If a DDL Statement Is Executed?
What Happens to the Current Transaction If the Session Is Ended?
What Happens to the Current Transaction If the Session Is Killed?
How Does Oracle Handle Read Consistency?
What Is a READ WRITE Transaction?
What Is a READ ONLY Transaction?
How To Set a Transaction To Be READ ONLY?
What Are the Restrictions in a READ ONLY Transaction?
What Are the General Rules on Data Consistency?
What Are Transaction Isolation Levels Supported by Oracle?
What Is a Data Lock?
How Data Locks Are Respected?
How To Experiment a Data Lock?
How To View Existing Locks on the Database?
What Is a Dead Lock?
How Oracle Handles Dead Locks?
More...
Oracle DBA FAQ - Managing Oracle User Accounts, Schema and Privileges
A collection of 23 FAQs on Oracle user account, schema and privileges. Clear answers are provided with tutorial exercises on creating user accounts, granting privileges for session connections, granting privileges for creating tables and inserting rows.
What Is a User Account?
What Is the Relation of a User Account and a Schema?
What Is a User Role?
What Are the System Predefined User Roles?
What Are Internal User Account?
How To Connect to the Server with User Account: SYS?
How To Use Windows User to Connect to the Server?
How To List All User Accounts?
How To Create a New User Account?
How To Change User Password?
How To Delete a User Account?
What Privilege Is Needed for a User to Connect to Oracle Server?
How To Grant CREATE SESSION Privilege to a User?
How To Revoke CREATE SESSION Privilege from a User?
How To Lock and Unlock a User Account?
What Privilege Is Needed for a User to Create Tables?
How To Assign a Tablespace to a Users?
What Privilege Is Needed for a User to Create Views?
What Privilege Is Needed for a User to Create Indexes?
What Privilege Is Needed for a User to Query Tables in Another Schema?
What Privilege Is Needed for a User to Insert Rows to Tables in Another Schema?
What Privilege Is Needed for a User to Delete Rows from Tables in Another Schema?
How To Find Out What Privileges a User Currently Has?
More...
Oracle DBA FAQ - Managing Oracle Database Tables
A collection of 18 FAQs on database tables for DBA and developers. Clear answers are provided together with tutorial exercises to help beginners on creating, altering and removing tables, adding, altering and removing columns, and working with recycle bin.
What Is a Database Table?
How Many Types of Tables Supported by Oracle?
How To Create a New Table in Your Schema?
How To Create a New Table by Selecting Rows from Another Table?
How To Rename an Existing Table?
How To Drop an Existing Table?
How To Add a New Column to an Existing Table?
How To Add a New Column to an Existing Table with a Default Value?
How To Rename a Column in an Existing Table?
How To Delete a Column in an Existing Table?
How To View All Columns in an Existing Table?
How To Recover a Dropped Table?
What Is a Recycle Bin?
How To Turn On or Off Recycle Bin for the Instance?
How To View the Dropped Tables in Your Recycle Bin?
How To Empty Your Recycle Bin?
How To Turn On or Off Recycle Bin for the Session?
How To List All Tables in Your Schema?
More...
Oracle DBA FAQ - Managing Oracle Table Indexes
This is a collection of 14 FAQs for Oracle DBA on creating, dropping, rebuilding and managing indexes. The clear answers and sample scripts provided can be used as learning tutorials or interview preparation guides.
What Is a Table Index?
How To Run SQL Statements through the Web Interface?
How To Create a Table Index?
How To List All Indexes in Your Schema?
What Is an Index Associated with a Constraint?
How To Rename an Index?
How To Drop an Index?
Can You Drop an Index Associated with a Unique or Primary Key Constraint?
What Happens to Indexes If You Drop a Table?
How To Recover a Dropped Index?
What Happens to the Indexes If a Table Is Recovered?
How To Rebuild an Index?
How To See the Table Columns Used in an Index?
How To Create a Single Index for Multiple Columns?
More...
Oracle DBA FAQ - Managing Oracle Tablespaces and Data Files
A collection of 19 FAQs on creating and managing tablespaces and data files. Clear answers are provided with tutorial exercises on creating and dropping tablespaces; listing available tablespaces; creating and dropping data files; setting tablespaces and data files offline; removing corrupted data files.
What Is an Oracle Tablespace?
What Is an Oracle Data File?
How a Tablespace Is Related to Data Files?
How a Database Is Related to Tablespaces?
How To View the Tablespaces in the Current Database?
What Are the Predefined Tablespaces in a Database?
How To View the Data Files in the Current Database?
How To Create a new Oracle Data File?
How To Create a New Tablespace?
How To Rename a Tablespace?
How To Drop a Tablespace?
What Happens to the Data Files If a Tablespace Is Dropped?
How To Create a Table in a Specific Tablespace?
How To See Free Space of Each Tablespace?
How To Bring a Tablespace Offline?
How To Bring a Tablespace Online?
How To Add Another Datafile to a Tablespace?
What Happens If You Lost a Data File?
How Remove Data Files befor opening a Database?
More...
Oracle DBA FAQ - Creating New Database Instance Manually
This is a collection of 15 FAQs for Oracle DBA on creating Oracle database instances manually using CREATE DATABASE statement. Items in this FAQ collection are organized together to form a complete tutorial guide on creating a new database instance manually.
How To Create an Oracle Database?
How To Create an Oracle Database Manually?
How To Select an Oracle System ID (SID)?
How To Establish Administrator Authentication to the Server?
How To Create an Initialization Parameter File?
How To Connect the Oracle Server as SYSDBA?
How To Create a Server Parameter File?
How To Start an Oracle Instance?
How To Start a Specific Oracle Instance?
How To Start Instance with a Minimal Initialization Parameter File?
How To Run CREATE DATABASE Statement?
How To Do Clean Up If CREATE DATABASE Failed?
How To Run CREATE DATABASE Statement Again?
How To Create Additional Tablespaces for an New Database?
How To Build Data Dictionary View an New Database?
More...
Oracle DBA FAQ - Introduction to PL/SQL
A collection of 17 FAQs to introduce PL/SQL language for DBA and developers. This FAQ can also be used as learning tutorials on creating procedures, executing procedures, using local variables, controlling execution flows, passing parameters and defining nested procedures.
What Is PL/SQL?
What Are the Types PL/SQL Code Blocks?
How To Define an Anonymous Block?
How Many Anonymous Blocks Can Be Defined?
How To Run the Anonymous Block Again?
What Is Stored Program Unit?
How To Create a Stored Program Unit?
How To Execute a Stored Program Unit?
How Many Data Types Are Supported?
What Are the Execution Control Statements?
How To Use SQL Statements in PL/SQL?
How To Process Query Result in PL/SQL?
How To Create an Array in PL/SQL?
How To Manage Transaction Isolation Level?
How To Pass Parameters to Procedures?
How To Define a Procedure inside Another Procedure?
What Do You Think about PL/SQL?
More...
Oracle DBA FAQ - Introduction to Oracle SQL Developer
A collection of 29 FAQs to introduce Oracle SQL Developer, the new free GUI client for DBA and developers. This FAQ can also be used as learning tutorials on SQL statement execution, data objects management, system views and reports, stored procedure debugging.
What Is Oracle SQL Developer?
What Operating Systems Are Supported by Oracle SQL Developer?
How To Download Oracle SQL Developer?
How To Install Oracle SQL Developer?
How To Start Oracle SQL Developer?
Is Oracel SQL Developer written in Java?
How To Connect to a Local Oracle 10g XE Server?
How To Connect to a Remote Server?
How To Run SQL Statements with Oracle SQL Developer?
How To Export Your Connection Information to a File?
How To Run SQL*Plus Commands in SQL Developer?
How To Work with Data Objects Interactively?
How To Get a CREATE Statement for an Existing Table?
How To Create a Table Interactively?
How To Enter a New Row into a Table Interactively?
What Is the Reports View in Oracle SQL Developer?
How To Get a List of All Tables in the Database?
How To Get a List of All User Accounts in the Database?
How To Get a List of All Background Sessions in the Database?
How To Create Your Own Reports in SQL Developer?
How Many File Formats Are Supported to Export Data?
How To Export Data to a CSV File?
How To Export Data to an XML File?
How To Create a Procedure Interactively?
How To Run a Stored Procedure Interactively?
How To Run Stored Procedures in Debug Mode?
How To Assign Debug Privileges to a User?
How To Set Up Breakpoints in Debug Mode?
What Do You Think about Oracle SQL Developer?
More...
Oracle DBA FAQ - Understanding PL/SQL Language Basics
A collection of 22 FAQs on PL/SQL language basics or DBA and developers. It can also be used as learning tutorials on defining variables, assigning values, using "loop" statements, setting "if" conditions, and working with null values.
What Is PL/SQL Language Case Sensitive?
How To Place Comments in PL/SQL?
What Are the Types PL/SQL Code Blocks?
What Is an Anonymous Block?
What Is a Named Program Unit?
What Is a Procedure?
What Is a Function?
How To Declare a Local Variable?
How To Initialize Variables with Default Values?
How To Assign Values to Variables?
What Are the Arithmetic Operations?
What Are the Numeric Comparison Operations?
What Are the Logical Operations?
How Many Categories of Data Types?
How Many Scalar Data Types Are Supported in PL/SQL?
How To Convert Character Types to Numeric Types?
What Are the Execution Control Statements?
How To Use "IF" Statements on Multiple Conditions?
How To Use "WHILE" Statements?
How To Use "FOR" Statements?
What Is NULL in PL/SQL?
How To Test NULL Values?
More...
Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions
A collection of 26 FAQs on PL/SQL managing our own procedures. It can also be used as learning tutorials on creating procedures and functions, executing and dropping procedures, passing actual parameters to formal parameters, making optional parameters.
What Is a Procedure?
What Is a Function?
How To Define an Anonymous Procedure without Variables?
How To Define an Anonymous Procedure with Variables?
How To Create a Stored Procedure?
How To Execute a Stored Procedure?
How To Drop a Stored Procedure?
How To Pass Parameters to Procedures?
How To Create a Stored Function?
How To Call a Stored Function?
How To Drop a Stored Function?
How To Call a Stored Function with Parameters?
How To Define a Sub Procedure?
How To Call a Sub Procedure?
How To Define a Sub Function?
Can Sub Procedure/Function Be Called Recursively?
What Happens If Recursive Calls Get Out of Control?
What Is the Order of Defining Local Variables and Sub Procedures/Functions?
What Is the Difference between Formal Parameters and Actual Parameters?
What Are the Parameter Modes Supported by PL/SQL?
How To Use "IN" Parameter Properly?
How To Use "OUT" Parameter Properly?
How To Use "IN OUT" Parameter Properly?
How To Define Default Values for Formal Parameters?
What Are Named Parameters?
What Is the Scope of a Local Variable?
More...
Oracle DBA FAQ - Working with Database Objects in PL/SQL
A collection of 23 FAQs on working with database objects in PL/SQL. Clear answers are provided with tutorial exercises on running DML statements, assign table data to variables, using the implicit cursor, defining and using RECORDs with table rows.
Can DML Statements Be Used in PL/SQL?
Can DDL Statements Be Used in PL/SQL?
Can Variables Be Used in SQL Statements?
What Happens If Variable Names Collide with Table/Column Names?
How To Resolve Name Conflicts between Variables and Columns?
How To Assign Query Results to Variables?
Can You Assign Multiple Query Result Rows To a Variable?
How To Run SQL Functions in PL/SQL?
How To Retrieve the Count of Updated Rows?
What Is the Implicit Cursor?
How To Assign Data of the Deleted Row to Variables?
What Is a RECORD in PL/SQL?
How To Define a Specific RECORD Type?
How To Define a Variable of a Specific RECORD Type?
How To Assign Values to Data Fields in RECORD Variables?
How To Retrieve Values from Data Fields in RECORD Variables?
How To Define a Data Field as NOT NULL?
How To Define a RECORD Variable to Store a Table Row?
How To Assign a Table Row to a RECORD Variable?
How To Insert a Record into a Table?
How To Update a Table Row with a Record?
How To Define a Variable to Match a Table Column Data Type?
More...
Oracle DBA FAQ - Working with Cursors in PL/SQL
A collection of 19 FAQs on working with database objects in PL/SQL. Clear answers are provided with tutorial exercises on defining, opening, and closing cursors, looping through cursors, defining and using cursor variables.
What Is a Cursor?
How Many Types of Cursors Supported in PL/SQL?
What Is the Implicit Cursor?
How To Use Attributes of the Implicit Cursor?
How To Loop through Data Rows in the Implicit Curosr?
How To Define an Explicit Cursor?
How To Open and Close an Explicit Cursor?
How To Retrieve Data from an Explicit Cursor?
How To Retrieve Data from an Cursor to a RECORD?
How To Use FETCH Statement in a Loop?
How To Use an Explicit Cursor without OPEN Statements?
Can Multiple Cursors Being Opened at the Same Time?
How To Pass a Parameter to a Cursor?
What Is a Cursor Variable?
How To Define a Cursor Variable?
How To Open a Cursor Variable?
How To Loop through a Cursor Variable?
How To Pass a Cursor Variable to a Procedure?
Why Cursor Variables Are Easier to Use than Cursors?
More...
Oracle DBA FAQ - Loading and Exporting Data
A collection of 27 FAQs on Oracle loading data and exporting data. Clear answers are provided with tutorial exercises on saving data as flat files, loading data from flat, exporting and importing database, schema and tables, creating external tables.
What Is the Simplest Tool to Run Commands on Oracle Servers?
What Is the Quickest Way to Export a Table to a Flat File?
How To Export Data with a Field Delimiter?
What Is SQL*Loader?
What Is a SQL*Loader Control File?
How To Load Data with SQL*Loader?
What Is an External Table?
How To Load Data through External Tables?
What Are the Restrictions on External Table Columns?
What Is a Directory Object?
How To Define an External Table with a Text File?
How To Run Queries on External Tables?
How To Load Data from External Tables to Regular Tables?
What Is the Data Pump Export Utility?
What Is the Data Pump Import Utility?
How To Invoke the Data Pump Export Utility?
How To Invoke the Data Pump Import Utitlity?
What Are Data Pump Export and Import Modes?
How To Estimate Disk Space Needed for an Export Job?
How To Do a Full Database Export?
Where Is the Export Dump File Located?
How To Export Your Own Schema?
How To Export Several Tables Together?
What Happens If the Imported Table Already Exists?
How To Import One Table Back from a Dump File?
What Are the Original Export and Import Utilities?
How To Invoke the Original Export Import Utilities?
More...
Oracle DBA FAQ - ODBC Drivers, DSN Configuration and ASP Connection
A collection of 9 FAQs on Oracle ODBC drivers and connections. Clear answers are provided with tutorial exercises on installing Oracle ODBC drivers; TNS settings; defining DSN entries; connecting MS Access or ASP pages to Oracle servers.
What Is Open Database Communication (ODBC)?
How To Install Oracle ODBC Drivers?
How To Find Out What Oracle ODBC Drivers Are Installed?
How Can Windows Applications Connect to Oracle Servers?
How To Create Tables for ODBC Connection Testing?
How To Check the Oracle TNS Settings?
How To Define a Data Source Name (DSN) in ODBC Manager?
How To Connect MS Access to Oracle Servers?
How To Connect ASP Pages to Oracle Servers?
This is a collection of 17 FAQs for Oracle DBA on fundamental concepts. The clear answers and sample scripts provided can be used as learning tutorials or interview preparation guides.
What Is Oracle?
What Is an Oracle Database?
What Is an Oracle Instance?
What Is a Parameter File?
What Is a Server Parameter File?
What Is a Initialization Parameter File?
What is System Global Area (SGA)?
What is Program Global Area (PGA)?
What Is a User Account?
What Is the Relation of a User Account and a Schema?
What Is a User Role?
What is a Database Schema?
What Is a Database Table?
What Is a Table Index?
What Is an Oracle Tablespace?
What Is an Oracle Data File?
What Is a Static Data Dictionary?
What Is a Dynamic Performance View?
What Is a Recycle Bin?
What Is SQL*Plus?
What Is Transport Network Substrate (TNS)?
What Is Open Database Communication (ODBC)?
More...
Oracle DBA FAQ - Introduction to Oracle Database 10g Express Edition
This is a collection of 21 FAQs for Oracle DBA on Oracle 10g Express Edition with installation and basic introduction. The clear answers and sample scripts provided can be used as learning tutorials or interview preparation guides.
What is Oracle Database 10g Express Edition?
What Are the Limitations Oracle Database 10g XE?
What Operating Systems Are Supported by Oracle Database 10g XE?
How To Download Oracle Database 10g XE?
How To Install Oracle Database 10g XE?
How To Check Your Oracle Database 10g XE Installation?
How To Shutdown Your 10g XE Server?
How To Start Your 10g XE Server?
How Much Memory Your 10g XE Server Is Using?
How To Start Your 10g XE Server from Command Line?
How To Shutdown Your 10g XE Server from Command Line?
How To Unlock the Sample User Account?
How To Change System Global Area (SGA)?
How To Change Program Global Area (PGA)?
What Happens If You Set the SGA Too Low?
What To Do If the StartBD.bat Failed to Start the XE Instance?
How To Login to the Server without an Instance?
How To Use "startup" Command to Start Default Instance?
Where Are the Settings Stored for Each Instance?
What To Do If the Binary SPFile Is Wrong for the Default Instance?
How To Check the Server Version?
More...
Oracle DBA FAQ - Introduction to Command-Line SQL*Plus Client Tool
A collection of 25 FAQs on Oracle command-line SQL*Plus client tool. Clear answers are provided with tutorial exercises on creating tnsnames.ora and connecting to Oracle servers; SQL*Plus settings and environment variables; saving query output to files; getting query performance reports.
What Is SQL*Plus?
How To Start the Command-Line SQL*Plus?
How To Get Help at the SQL Prompt?
What Information Is Needed to Connect SQL*Plus an Oracle Server?
What Is a Connect Identifier?
How To Connect a SQL*Plus Session to an Oracle Server?
What Happens If You Use a Wrong Connect Identifier?
What To Do If DBA Lost the SYSTEM Password?
What Types of Commands Can Be Executed in SQL*Plus?
How To Run SQL Commands in SQL*Plus?
How To Run PL/SQL Statements in SQL*Plus?
How To Change SQL*Plus System Settings?
How To Look at the Current SQL*Plus System Settings?
What Are SQL*Plus Environment Variables?
How To Generate Query Output in HTML Format?
What Is Output Spooling in SQL*Plus?
How To Save Query Output to a Local File?
What Is Input Buffer in SQL*Plus?
How To Revise and Re-Run the Last SQL Command?
How Run SQL*Plus Commands That Are Stored in a Local File?
How To Use SQL*Plus Built-in Timers?
What Is Oracle Server Autotrace?
How To Set Up Autotrace for a User Account?
How To Get Execution Path Reports on Query Statements?
How To Get Execution Statistics Reports on Query Statements?
More...
Oracle DBA FAQ - Understanding SQL Basics
A collection of 28 FAQs on Oracle SQL language basics. Clear answers are provided with tutorial exercises on data types, data literals, date and time values, data and time intervals, converting to dates and times, NULL values, pattern matches.
What Is SQL?
How Many Categories of Data Types?
What Are the Oracle Built-in Data Types?
What Are the Differences between CHAR and NCHAR?
What Are the Differences between CHAR and VARCHAR2?
What Are the Differences between NUMBER and BINARY_FLOAT?
What Are the Differences between DATE and TIMESTAMP?
What Are the Differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND?
What Are the Differences between BLOB and CLOB?
What Are the ANSI Data Types Supported in Oracle?
How To Write Text Literals?
How To Write Numeric Literals?
How To Write Date and Time Literals?
How To Write Date and Time Interval Literals?
How To Convert Numbers to Characters?
How To Convert Characters to Numbers?
How To Convert Dates to Characters?
How To Convert Characters to Dates?
How To Convert Times to Characters?
How To Convert Characters to Times?
What Is NULL?
How To Use NULL as Conditions?
How To Concatenate Two Text Values?
How To Increment Dates by 1?
How To Calculate Date and Time Differences?
How To Use IN Conditions?
How To Use LIKE Conditions?
How To Use Regular Expression in Pattern Match Conditions?
More...
Oracle DBA FAQ - Understanding SQL DDL Statements
A collection of 11 FAQs on Oracle SQL DDL statements. Clear answers are provided with tutorial exercises on creating, altering and dropping tables, indexes, and views.
What Are DDL Statements?
How To Create a New Table?
How To Create a New Table by Selecting Rows from Another Table?
How To Add a New Column to an Existing Table?
How To Delete a Column in an Existing Table?
How To Drop an Existing Table?
How To Create a Table Index?
How To Rename an Index?
How To Drop an Index?
How To Create a New View?
How To Drop an Existing View?
More...
Oracle DBA FAQ - Understanding SQL DML Statements
A collection of 15 FAQs on Oracle SQL DML statements. Clear answers are provided with tutorial exercises on inserting, updating and deleting rows from database tables.
What Are DML Statements?
How To Create a Testing Table?
How To Set Up SQL*Plus Output Format?
How To Insert a New Row into a Table?
How To Specify Default Values in INSERT Statement?
How To Omit Columns with Default Values in INSERT Statement?
How To Insert Multiple Rows with One INSERT Statement?
How To Update Values in a Table?
How To Update Values on Multiple Rows?
How To Use Existing Values in UPDATE Statements?
How To Use Values from Other Tables in UPDATE Statements?
What Happens If the UPDATE Subquery Returns Multiple Rows?
How To Delete an Existing Row from a Table?
How To Delete Multiple Rows from a Table?
How To Delete All Rows a Table?
More...
Oracle DBA FAQ - Understanding SQL SELECT Query Statements
A collection of 33 FAQs on Oracle SQL SELECT query statements. Clear answers are provided with tutorial exercises on selecting rows and columns from tables and views, sorting and counting query outputs, grouping outputs and applying group functions, joining tables, using subqueries.
What Is a SELECT Query Statement?
How To Select All Columns of All Rows from a Table?
How To Select Some Columns from a Table?
How To Select Some Rows from a Table?
How To Sort the Query Output?
Can the Query Output Be Sorted by Multiple Columns?
How To Sort Output in Descending Order?
How To Use SELECT Statement to Count the Number of Rows?
Can SELECT Statements Be Used on Views?
How To Filter Out Duplications in the Returning Rows?
What Are Group Functions?
How To Use Group Functions in the SELECT Clause?
Can Group Functions Be Mixed with Non-group Selection Fields?
How To Divide Query Output into Groups?
How To Apply Filtering Criteria at Group Level?
How To Count Duplicated Values in a Column?
Can Multiple Columns Be Used in GROUP BY?
Can Group Functions Be Used in the ORDER BY Clause?
How To Join Two Tables in a Single Query?
How To Write a Query with an Inner Join?
How To Define and Use Table Alias Names?
How To Write a Query with a Left Outer Join?
How To Write a Query with a Right Outer Join?
How To Write a Query with a Full Outer Join?
How To Write an Inner Join with the WHERE Clause?
How To Write a Left Outer Join with the WHERE Clause?
How To Name Query Output Columns?
What Is a Subquery?
How To Use Subqueries with the IN Operator?
How To Use Subqueries with the EXISTS Operator?
How To Use Subqueries in the FROM clause?
How To Count Groups Returned with the GROUP BY Clause?
How To Return Top 5 Rows?
More...
Oracle DBA FAQ - Understanding SQL Transaction Management
A collection of 22 FAQs on Oracle SQL transaction management. Clear answers are provided with tutorial exercises on starting and ending transactions, committing and rolling back transactions, transaction/statement-level read consistency, read committed isolation level, locks and dead locks.
What Is a Transaction?
How To Start a New Transaction?
How To End the Current Transaction?
How To Create a Testing Table?
How To Commit the Current Transaction?
How To Rollback the Current Transaction?
What Happens to the Current Transaction If a DDL Statement Is Executed?
What Happens to the Current Transaction If the Session Is Ended?
What Happens to the Current Transaction If the Session Is Killed?
How Does Oracle Handle Read Consistency?
What Is a READ WRITE Transaction?
What Is a READ ONLY Transaction?
How To Set a Transaction To Be READ ONLY?
What Are the Restrictions in a READ ONLY Transaction?
What Are the General Rules on Data Consistency?
What Are Transaction Isolation Levels Supported by Oracle?
What Is a Data Lock?
How Data Locks Are Respected?
How To Experiment a Data Lock?
How To View Existing Locks on the Database?
What Is a Dead Lock?
How Oracle Handles Dead Locks?
More...
Oracle DBA FAQ - Managing Oracle User Accounts, Schema and Privileges
A collection of 23 FAQs on Oracle user account, schema and privileges. Clear answers are provided with tutorial exercises on creating user accounts, granting privileges for session connections, granting privileges for creating tables and inserting rows.
What Is a User Account?
What Is the Relation of a User Account and a Schema?
What Is a User Role?
What Are the System Predefined User Roles?
What Are Internal User Account?
How To Connect to the Server with User Account: SYS?
How To Use Windows User to Connect to the Server?
How To List All User Accounts?
How To Create a New User Account?
How To Change User Password?
How To Delete a User Account?
What Privilege Is Needed for a User to Connect to Oracle Server?
How To Grant CREATE SESSION Privilege to a User?
How To Revoke CREATE SESSION Privilege from a User?
How To Lock and Unlock a User Account?
What Privilege Is Needed for a User to Create Tables?
How To Assign a Tablespace to a Users?
What Privilege Is Needed for a User to Create Views?
What Privilege Is Needed for a User to Create Indexes?
What Privilege Is Needed for a User to Query Tables in Another Schema?
What Privilege Is Needed for a User to Insert Rows to Tables in Another Schema?
What Privilege Is Needed for a User to Delete Rows from Tables in Another Schema?
How To Find Out What Privileges a User Currently Has?
More...
Oracle DBA FAQ - Managing Oracle Database Tables
A collection of 18 FAQs on database tables for DBA and developers. Clear answers are provided together with tutorial exercises to help beginners on creating, altering and removing tables, adding, altering and removing columns, and working with recycle bin.
What Is a Database Table?
How Many Types of Tables Supported by Oracle?
How To Create a New Table in Your Schema?
How To Create a New Table by Selecting Rows from Another Table?
How To Rename an Existing Table?
How To Drop an Existing Table?
How To Add a New Column to an Existing Table?
How To Add a New Column to an Existing Table with a Default Value?
How To Rename a Column in an Existing Table?
How To Delete a Column in an Existing Table?
How To View All Columns in an Existing Table?
How To Recover a Dropped Table?
What Is a Recycle Bin?
How To Turn On or Off Recycle Bin for the Instance?
How To View the Dropped Tables in Your Recycle Bin?
How To Empty Your Recycle Bin?
How To Turn On or Off Recycle Bin for the Session?
How To List All Tables in Your Schema?
More...
Oracle DBA FAQ - Managing Oracle Table Indexes
This is a collection of 14 FAQs for Oracle DBA on creating, dropping, rebuilding and managing indexes. The clear answers and sample scripts provided can be used as learning tutorials or interview preparation guides.
What Is a Table Index?
How To Run SQL Statements through the Web Interface?
How To Create a Table Index?
How To List All Indexes in Your Schema?
What Is an Index Associated with a Constraint?
How To Rename an Index?
How To Drop an Index?
Can You Drop an Index Associated with a Unique or Primary Key Constraint?
What Happens to Indexes If You Drop a Table?
How To Recover a Dropped Index?
What Happens to the Indexes If a Table Is Recovered?
How To Rebuild an Index?
How To See the Table Columns Used in an Index?
How To Create a Single Index for Multiple Columns?
More...
Oracle DBA FAQ - Managing Oracle Tablespaces and Data Files
A collection of 19 FAQs on creating and managing tablespaces and data files. Clear answers are provided with tutorial exercises on creating and dropping tablespaces; listing available tablespaces; creating and dropping data files; setting tablespaces and data files offline; removing corrupted data files.
What Is an Oracle Tablespace?
What Is an Oracle Data File?
How a Tablespace Is Related to Data Files?
How a Database Is Related to Tablespaces?
How To View the Tablespaces in the Current Database?
What Are the Predefined Tablespaces in a Database?
How To View the Data Files in the Current Database?
How To Create a new Oracle Data File?
How To Create a New Tablespace?
How To Rename a Tablespace?
How To Drop a Tablespace?
What Happens to the Data Files If a Tablespace Is Dropped?
How To Create a Table in a Specific Tablespace?
How To See Free Space of Each Tablespace?
How To Bring a Tablespace Offline?
How To Bring a Tablespace Online?
How To Add Another Datafile to a Tablespace?
What Happens If You Lost a Data File?
How Remove Data Files befor opening a Database?
More...
Oracle DBA FAQ - Creating New Database Instance Manually
This is a collection of 15 FAQs for Oracle DBA on creating Oracle database instances manually using CREATE DATABASE statement. Items in this FAQ collection are organized together to form a complete tutorial guide on creating a new database instance manually.
How To Create an Oracle Database?
How To Create an Oracle Database Manually?
How To Select an Oracle System ID (SID)?
How To Establish Administrator Authentication to the Server?
How To Create an Initialization Parameter File?
How To Connect the Oracle Server as SYSDBA?
How To Create a Server Parameter File?
How To Start an Oracle Instance?
How To Start a Specific Oracle Instance?
How To Start Instance with a Minimal Initialization Parameter File?
How To Run CREATE DATABASE Statement?
How To Do Clean Up If CREATE DATABASE Failed?
How To Run CREATE DATABASE Statement Again?
How To Create Additional Tablespaces for an New Database?
How To Build Data Dictionary View an New Database?
More...
Oracle DBA FAQ - Introduction to PL/SQL
A collection of 17 FAQs to introduce PL/SQL language for DBA and developers. This FAQ can also be used as learning tutorials on creating procedures, executing procedures, using local variables, controlling execution flows, passing parameters and defining nested procedures.
What Is PL/SQL?
What Are the Types PL/SQL Code Blocks?
How To Define an Anonymous Block?
How Many Anonymous Blocks Can Be Defined?
How To Run the Anonymous Block Again?
What Is Stored Program Unit?
How To Create a Stored Program Unit?
How To Execute a Stored Program Unit?
How Many Data Types Are Supported?
What Are the Execution Control Statements?
How To Use SQL Statements in PL/SQL?
How To Process Query Result in PL/SQL?
How To Create an Array in PL/SQL?
How To Manage Transaction Isolation Level?
How To Pass Parameters to Procedures?
How To Define a Procedure inside Another Procedure?
What Do You Think about PL/SQL?
More...
Oracle DBA FAQ - Introduction to Oracle SQL Developer
A collection of 29 FAQs to introduce Oracle SQL Developer, the new free GUI client for DBA and developers. This FAQ can also be used as learning tutorials on SQL statement execution, data objects management, system views and reports, stored procedure debugging.
What Is Oracle SQL Developer?
What Operating Systems Are Supported by Oracle SQL Developer?
How To Download Oracle SQL Developer?
How To Install Oracle SQL Developer?
How To Start Oracle SQL Developer?
Is Oracel SQL Developer written in Java?
How To Connect to a Local Oracle 10g XE Server?
How To Connect to a Remote Server?
How To Run SQL Statements with Oracle SQL Developer?
How To Export Your Connection Information to a File?
How To Run SQL*Plus Commands in SQL Developer?
How To Work with Data Objects Interactively?
How To Get a CREATE Statement for an Existing Table?
How To Create a Table Interactively?
How To Enter a New Row into a Table Interactively?
What Is the Reports View in Oracle SQL Developer?
How To Get a List of All Tables in the Database?
How To Get a List of All User Accounts in the Database?
How To Get a List of All Background Sessions in the Database?
How To Create Your Own Reports in SQL Developer?
How Many File Formats Are Supported to Export Data?
How To Export Data to a CSV File?
How To Export Data to an XML File?
How To Create a Procedure Interactively?
How To Run a Stored Procedure Interactively?
How To Run Stored Procedures in Debug Mode?
How To Assign Debug Privileges to a User?
How To Set Up Breakpoints in Debug Mode?
What Do You Think about Oracle SQL Developer?
More...
Oracle DBA FAQ - Understanding PL/SQL Language Basics
A collection of 22 FAQs on PL/SQL language basics or DBA and developers. It can also be used as learning tutorials on defining variables, assigning values, using "loop" statements, setting "if" conditions, and working with null values.
What Is PL/SQL Language Case Sensitive?
How To Place Comments in PL/SQL?
What Are the Types PL/SQL Code Blocks?
What Is an Anonymous Block?
What Is a Named Program Unit?
What Is a Procedure?
What Is a Function?
How To Declare a Local Variable?
How To Initialize Variables with Default Values?
How To Assign Values to Variables?
What Are the Arithmetic Operations?
What Are the Numeric Comparison Operations?
What Are the Logical Operations?
How Many Categories of Data Types?
How Many Scalar Data Types Are Supported in PL/SQL?
How To Convert Character Types to Numeric Types?
What Are the Execution Control Statements?
How To Use "IF" Statements on Multiple Conditions?
How To Use "WHILE" Statements?
How To Use "FOR" Statements?
What Is NULL in PL/SQL?
How To Test NULL Values?
More...
Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions
A collection of 26 FAQs on PL/SQL managing our own procedures. It can also be used as learning tutorials on creating procedures and functions, executing and dropping procedures, passing actual parameters to formal parameters, making optional parameters.
What Is a Procedure?
What Is a Function?
How To Define an Anonymous Procedure without Variables?
How To Define an Anonymous Procedure with Variables?
How To Create a Stored Procedure?
How To Execute a Stored Procedure?
How To Drop a Stored Procedure?
How To Pass Parameters to Procedures?
How To Create a Stored Function?
How To Call a Stored Function?
How To Drop a Stored Function?
How To Call a Stored Function with Parameters?
How To Define a Sub Procedure?
How To Call a Sub Procedure?
How To Define a Sub Function?
Can Sub Procedure/Function Be Called Recursively?
What Happens If Recursive Calls Get Out of Control?
What Is the Order of Defining Local Variables and Sub Procedures/Functions?
What Is the Difference between Formal Parameters and Actual Parameters?
What Are the Parameter Modes Supported by PL/SQL?
How To Use "IN" Parameter Properly?
How To Use "OUT" Parameter Properly?
How To Use "IN OUT" Parameter Properly?
How To Define Default Values for Formal Parameters?
What Are Named Parameters?
What Is the Scope of a Local Variable?
More...
Oracle DBA FAQ - Working with Database Objects in PL/SQL
A collection of 23 FAQs on working with database objects in PL/SQL. Clear answers are provided with tutorial exercises on running DML statements, assign table data to variables, using the implicit cursor, defining and using RECORDs with table rows.
Can DML Statements Be Used in PL/SQL?
Can DDL Statements Be Used in PL/SQL?
Can Variables Be Used in SQL Statements?
What Happens If Variable Names Collide with Table/Column Names?
How To Resolve Name Conflicts between Variables and Columns?
How To Assign Query Results to Variables?
Can You Assign Multiple Query Result Rows To a Variable?
How To Run SQL Functions in PL/SQL?
How To Retrieve the Count of Updated Rows?
What Is the Implicit Cursor?
How To Assign Data of the Deleted Row to Variables?
What Is a RECORD in PL/SQL?
How To Define a Specific RECORD Type?
How To Define a Variable of a Specific RECORD Type?
How To Assign Values to Data Fields in RECORD Variables?
How To Retrieve Values from Data Fields in RECORD Variables?
How To Define a Data Field as NOT NULL?
How To Define a RECORD Variable to Store a Table Row?
How To Assign a Table Row to a RECORD Variable?
How To Insert a Record into a Table?
How To Update a Table Row with a Record?
How To Define a Variable to Match a Table Column Data Type?
More...
Oracle DBA FAQ - Working with Cursors in PL/SQL
A collection of 19 FAQs on working with database objects in PL/SQL. Clear answers are provided with tutorial exercises on defining, opening, and closing cursors, looping through cursors, defining and using cursor variables.
What Is a Cursor?
How Many Types of Cursors Supported in PL/SQL?
What Is the Implicit Cursor?
How To Use Attributes of the Implicit Cursor?
How To Loop through Data Rows in the Implicit Curosr?
How To Define an Explicit Cursor?
How To Open and Close an Explicit Cursor?
How To Retrieve Data from an Explicit Cursor?
How To Retrieve Data from an Cursor to a RECORD?
How To Use FETCH Statement in a Loop?
How To Use an Explicit Cursor without OPEN Statements?
Can Multiple Cursors Being Opened at the Same Time?
How To Pass a Parameter to a Cursor?
What Is a Cursor Variable?
How To Define a Cursor Variable?
How To Open a Cursor Variable?
How To Loop through a Cursor Variable?
How To Pass a Cursor Variable to a Procedure?
Why Cursor Variables Are Easier to Use than Cursors?
More...
Oracle DBA FAQ - Loading and Exporting Data
A collection of 27 FAQs on Oracle loading data and exporting data. Clear answers are provided with tutorial exercises on saving data as flat files, loading data from flat, exporting and importing database, schema and tables, creating external tables.
What Is the Simplest Tool to Run Commands on Oracle Servers?
What Is the Quickest Way to Export a Table to a Flat File?
How To Export Data with a Field Delimiter?
What Is SQL*Loader?
What Is a SQL*Loader Control File?
How To Load Data with SQL*Loader?
What Is an External Table?
How To Load Data through External Tables?
What Are the Restrictions on External Table Columns?
What Is a Directory Object?
How To Define an External Table with a Text File?
How To Run Queries on External Tables?
How To Load Data from External Tables to Regular Tables?
What Is the Data Pump Export Utility?
What Is the Data Pump Import Utility?
How To Invoke the Data Pump Export Utility?
How To Invoke the Data Pump Import Utitlity?
What Are Data Pump Export and Import Modes?
How To Estimate Disk Space Needed for an Export Job?
How To Do a Full Database Export?
Where Is the Export Dump File Located?
How To Export Your Own Schema?
How To Export Several Tables Together?
What Happens If the Imported Table Already Exists?
How To Import One Table Back from a Dump File?
What Are the Original Export and Import Utilities?
How To Invoke the Original Export Import Utilities?
More...
Oracle DBA FAQ - ODBC Drivers, DSN Configuration and ASP Connection
A collection of 9 FAQs on Oracle ODBC drivers and connections. Clear answers are provided with tutorial exercises on installing Oracle ODBC drivers; TNS settings; defining DSN entries; connecting MS Access or ASP pages to Oracle servers.
What Is Open Database Communication (ODBC)?
How To Install Oracle ODBC Drivers?
How To Find Out What Oracle ODBC Drivers Are Installed?
How Can Windows Applications Connect to Oracle Servers?
How To Create Tables for ODBC Connection Testing?
How To Check the Oracle TNS Settings?
How To Define a Data Source Name (DSN) in ODBC Manager?
How To Connect MS Access to Oracle Servers?
How To Connect ASP Pages to Oracle Servers?
Create a Database Instance in Oracle
Before 8i there was no easy way to get the scripts to create databases in Oracle. Like it was a state secret or something. Larry, you are a moron.
Oracle seems to have a lack of basic utilities, like schema extraction, etc. Some of this DDL can be seen in a "full" database export, but gee whiz, Oracle seems to revel in making it difficult to use their expensively licensed product.
#################### cut here ##########################
REM * Database instance create script used with Oracle 8.0.5
REM * Start the instance (ORACLE_SID here must be set to ).
REM * Use svrmgrl so admin procedures get run correctly.
-- Shutdown instance, remove all datafiles, control files, lockfiles,
-- logfiles to rerun and recreate the same instance
set termout on
set echo on
spool create.log
connect internal
startup nomount pfile=/apps/oracle/product/8.0.5/dbs/initumnp.ora
create database "umnp"
maxinstances 8
maxlogfiles 32
maxdatafiles 400
character set "US7ASCII"
datafile
'/vol01/oradata/umnp/system01.dbf' size 100M
logfile
('/vol05/oradata/umnp/redo01.01',
'/vol06/oradata/umnp/redo01.02') size 20M,
group 2
('/vol05/oradata/umnp/redo02.01',
'/vol06/oradata/umnp/redo02.02') size 20M,
group 3
('/vol05/oradata/umnp/redo03.01',
'/vol06/oradata/umnp/redo03.02') size 20M,
group 4
('/vol05/oradata/umnp/redo04.01',
'/vol06/oradata/umnp/redo04.02') size 20M,
group 5
('/vol05/oradata/umnp/redo05.01',
'/vol06/oradata/umnp/redo05.02') size 20M ;
@/apps/oracle/product/8.0.5/rdbms/admin/catalog.sql
@/apps/oracle/product/8.0.5/rdbms/admin/catproc.sql
@/apps/oracle/product/8.0.5/rdbms/admin/catparr.sql
create rollback segment temprbs
tablespace system
storage (initial 25K next 25K minextents 2 maxextents 99);
alter rollback segment temprbs online;
drop tablespace rollback01;
create tablespace rollback01
datafile '/vol07/oradata/umnp/rollback01_01.dbf' size 128M reuse
default storage (initial 1m next 1m maxextents unlimited pctincrease 0);
drop tablespace temp01 ;
create tablespace temp01
datafile '/vol01/oradata/umnp/temp01_01.dbf' size 128M reuse
default storage (initial 2m next 2m maxextents unlimited pctincrease 0)
temporary;
drop tablespace user01 ;
create tablespace user01
datafile '/vol02/oradata/umnp/user01_01.dbf' size 100M reuse
default storage (initial 100k next 100k maxextents unlimited pctincrease 0);
create rollback segment R01
tablespace rollback01
storage (initial 1M next 1M optimal 10M
minextents 20 maxextents 250);
create rollback segment R02
tablespace rollback01
storage (initial 1M next 1M optimal 10M
minextents 20 maxextents 250);
create rollback segment R03
tablespace rollback01
storage (initial 1M next 1M optimal 10M
minextents 20 maxextents 250);
create rollback segment R04
tablespace rollback01
storage (initial 1M next 1M optimal 10M
minextents 20 maxextents 250);
create rollback segment R05
tablespace rollback01
storage (initial 1M next 1M optimal 10M
minextents 20 maxextents 250);
alter rollback segment r01 online;
alter rollback segment r02 online;
alter rollback segment r03 online;
alter rollback segment r04 online;
alter rollback segment r05 online;
alter rollback segment temprbs offline;
REM * Alter SYS and SYSTEM users, because Oracle will make SYSTEM
REM * the default and temporary tablespace by default, and we don't
REM * want that.
REM *
alter user sys temporary tablespace temp01;
alter user system default tablespace user01 temporary tablespace temp01;
REM * Now run the Oracle-supplied scripts we need for this DB
REM *
@/apps/oracle/product/8.0.5/rdbms/admin/catblock.sql
@/apps/oracle/product/8.0.5/rdbms/admin/catio.sql
@/apps/oracle/product/8.0.5/rdbms/admin/dbmspool.sql
REM * All done, so close the log file and exit.
REM *
spool off
exit
################## cut here ###################################
Before 8i there was no easy way to get the scripts to create databases in Oracle. Like it was a state secret or something. Larry, you are a moron.
Oracle seems to have a lack of basic utilities, like schema extraction, etc. Some of this DDL can be seen in a "full" database export, but gee whiz, Oracle seems to revel in making it difficult to use their expensively licensed product.
#################### cut here ##########################
REM * Database instance create script used with Oracle 8.0.5
REM * Start the instance (ORACLE_SID here must be set to ).
REM * Use svrmgrl so admin procedures get run correctly.
-- Shutdown instance, remove all datafiles, control files, lockfiles,
-- logfiles to rerun and recreate the same instance
set termout on
set echo on
spool create.log
connect internal
startup nomount pfile=/apps/oracle/product/8.0.5/dbs/initumnp.ora
create database "umnp"
maxinstances 8
maxlogfiles 32
maxdatafiles 400
character set "US7ASCII"
datafile
'/vol01/oradata/umnp/system01.dbf' size 100M
logfile
('/vol05/oradata/umnp/redo01.01',
'/vol06/oradata/umnp/redo01.02') size 20M,
group 2
('/vol05/oradata/umnp/redo02.01',
'/vol06/oradata/umnp/redo02.02') size 20M,
group 3
('/vol05/oradata/umnp/redo03.01',
'/vol06/oradata/umnp/redo03.02') size 20M,
group 4
('/vol05/oradata/umnp/redo04.01',
'/vol06/oradata/umnp/redo04.02') size 20M,
group 5
('/vol05/oradata/umnp/redo05.01',
'/vol06/oradata/umnp/redo05.02') size 20M ;
@/apps/oracle/product/8.0.5/rdbms/admin/catalog.sql
@/apps/oracle/product/8.0.5/rdbms/admin/catproc.sql
@/apps/oracle/product/8.0.5/rdbms/admin/catparr.sql
create rollback segment temprbs
tablespace system
storage (initial 25K next 25K minextents 2 maxextents 99);
alter rollback segment temprbs online;
drop tablespace rollback01;
create tablespace rollback01
datafile '/vol07/oradata/umnp/rollback01_01.dbf' size 128M reuse
default storage (initial 1m next 1m maxextents unlimited pctincrease 0);
drop tablespace temp01 ;
create tablespace temp01
datafile '/vol01/oradata/umnp/temp01_01.dbf' size 128M reuse
default storage (initial 2m next 2m maxextents unlimited pctincrease 0)
temporary;
drop tablespace user01 ;
create tablespace user01
datafile '/vol02/oradata/umnp/user01_01.dbf' size 100M reuse
default storage (initial 100k next 100k maxextents unlimited pctincrease 0);
create rollback segment R01
tablespace rollback01
storage (initial 1M next 1M optimal 10M
minextents 20 maxextents 250);
create rollback segment R02
tablespace rollback01
storage (initial 1M next 1M optimal 10M
minextents 20 maxextents 250);
create rollback segment R03
tablespace rollback01
storage (initial 1M next 1M optimal 10M
minextents 20 maxextents 250);
create rollback segment R04
tablespace rollback01
storage (initial 1M next 1M optimal 10M
minextents 20 maxextents 250);
create rollback segment R05
tablespace rollback01
storage (initial 1M next 1M optimal 10M
minextents 20 maxextents 250);
alter rollback segment r01 online;
alter rollback segment r02 online;
alter rollback segment r03 online;
alter rollback segment r04 online;
alter rollback segment r05 online;
alter rollback segment temprbs offline;
REM * Alter SYS and SYSTEM users, because Oracle will make SYSTEM
REM * the default and temporary tablespace by default, and we don't
REM * want that.
REM *
alter user sys temporary tablespace temp01;
alter user system default tablespace user01 temporary tablespace temp01;
REM * Now run the Oracle-supplied scripts we need for this DB
REM *
@/apps/oracle/product/8.0.5/rdbms/admin/catblock.sql
@/apps/oracle/product/8.0.5/rdbms/admin/catio.sql
@/apps/oracle/product/8.0.5/rdbms/admin/dbmspool.sql
REM * All done, so close the log file and exit.
REM *
spool off
exit
################## cut here ###################################
ORA-01017: invalid username/password; logon denied
Cause: An invalid username or password was entered in an attempt to log on to Oracle. The username and password must be the same as was specified in a GRANT CONNECT statement. If the username and password are entered together, the format is: username/password.
Action: Enter a valid username and password combination in the correct format.
http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authentication.htm
•Enforced case sensitivity for passwords. Passwords are case sensitive. For example, the password hPP5620qr fails if it is entered as hpp5620QR or hPp5620Qr. In previous releases, passwords were not case sensitive. See "Enabling or Disabling Password Case Sensitivity" for information about how case sensitivity works, and how it affects password files and database links.
Cause: An invalid username or password was entered in an attempt to log on to Oracle. The username and password must be the same as was specified in a GRANT CONNECT statement. If the username and password are entered together, the format is: username/password.
Action: Enter a valid username and password combination in the correct format.
http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authentication.htm
•Enforced case sensitivity for passwords. Passwords are case sensitive. For example, the password hPP5620qr fails if it is entered as hpp5620QR or hPp5620Qr. In previous releases, passwords were not case sensitive. See "Enabling or Disabling Password Case Sensitivity" for information about how case sensitivity works, and how it affects password files and database links.
Explore Oracle Database 11g
This section briefly describes a few of the new features of Oracle Database 11g. A detailed description of the new features is beyond the scope of this guide. For a more comprehensive list, see the Oracle Database New Features Guide 11g Release 1 (11.1).
Automatic Memory Management - With Oracle Database 11g, memory management is further automated with the use of the dynamic parameter, memory_target. You would just be required to specify the total instance memory size and the database will automatically manages the memory distribution between the SGA and the PGA. The view, v$memory_target_advice provides advice on memory tuning.
Interval Partitioning improves partition table manageability by creating new table partitions automatically when inserted rows exceed the partition ranges.
Partitioning by integer value
SQL> create table patients (
2 patientid number not null,name varchar2(10),address varchar2(15)
3 )
4 partition by range (patientid)
5 interval (100)
6 (partition p1 values less than (100))
7 /
Table created.
SQL> select partition_name,high_value
2 from user_tab_partitions
3 where table_name='PATIENTS';
PARTITION_NAME HIGH_VALUE
--------------- ---------------
P1 100
SQL> insert into patients values (100,'ROBERT','4 BORNE AVE');
1 row created.
SQL> select partition_name,high_value
2 from user_tab_partitions
3 where table_name='PATIENTS';
PARTITION_NAME HIGH_VALUE
--------------- ---------------
P1 100
SYS_P81 200
SQL> select count(*) from patients partition (SYS_P81);
COUNT(*)
----------
1
Partitioning by date
SQL> create table userlogs (
2 transid number,
3 transdt date,
4 terminal varchar2(10)
5 )
6 partition by range (transdt)
7 interval (numtoyminterval(1,'YEAR'))
8 (
9 partition p1 values less than (to_date('01-01-2007','mm-dd-yyyy'))
10 );
Table created.
SQL> select partition_name,high_value
2 from user_tab_partitions
3 where table_name='USERLOGS';
PARTITION_NAME HIGH_VALUE
-------------- --------------------------------------------------------------------------------
P1 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SQL> insert into userlogs values (1,'11-AUG-07','XAV0004');
1 row created.
SQL> select partition_name,high_value
2 from user_tab_partitions
3 where table_name='USERLOGS';
PARTITION_NAME HIGH_VALUE
-------------- --------------------------------------------------------------------------------
P1 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P42 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SQL> select count(*) from userlogs partition (sys_p42);
COUNT(*)
----------
1
Reference Partitioning partitions a child table based on the partitioning scheme of the parent table.
SQL> create table patients (
2 patientid number not null,name varchar2(10), address varchar2(15)
3 )
4 partition by range (patientid)
5 (partition p1 values less than (100),
6 partition p2 values less than (200))
7 /
Table created.
SQL> alter table patients
2 add constraint patients_pk primary key (patientid);
Table altered.
SQL> create table invoices (
2 invoiceno number,amount number, patientid number not null,
3 constraint invoices_fk
4 foreign key (patientid) references patients
5 )
6 partition by reference (invoices_fk);
Table created.
SQL> select dbms_metadata.get_ddl('TABLE','INVOICES','VCHAN') from dual;
DBMS_METADATA.GET_DDL('TABLE','INVOICES','VCHAN')
-----------------------------------------------------------------------
CREATE TABLE "VCHAN"."INVOICES"
( "INVOICENO" NUMBER,
"AMOUNT" NUMBER,
"PATIENTID" NUMBER NOT NULL ENABLE,
CONSTRAINT "INVOICES_FK" FOREIGN KEY ("PATIENTID")
REFERENCES "VCHAN"."PATIENTS" ("PATIENTID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
PARTITION BY REFERENCE ("INVOICES_FK")
(PARTITION "P1"
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "P2"
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS )
SQL> insert into patients values (1,'TOBY','88 Palace Ave');
1 row created.
SQL> insert into invoices values (150,262.12,1);
1 row created.
SQL> select count(*) from invoices partition (p1);
COUNT(*)
----------
1
SQL> select count(*) from invoices partition (p2);
COUNT(*)
----------
0
SQL> insert into patients values (110,'GILY','512 HILE STREET');
1 row created.
SQL> insert into invoices values (151,500.01,110);
1 row created.
SQL> select count(*) from invoices partition (p1);
COUNT(*)
----------
1
SQL> select count(*) from invoices partition (p2);
COUNT(*)
----------
1
Table Compression in Oracle Database 11g supports conventional DML and drop column operations. Compressed data are not uncompressed during reading and thus queries on compressed data are noticeably faster since there are fewer data block reads.
SQL> create tablespace tbs1 datafile '/u01/app/oracle/oradata/db11g/tbs1_01.dbf' size 500M;
Tablespace created.
SQL> create tablespace tbs2 datafile '/u01/app/oracle/oradata/db11g/tbs2_01.dbf' size 500M;
Tablespace created.
SQL> create table mytable_compress (col1 varchar2(26),col2 varchar2(26)) tablespace tbs1 compress for all operations;
Table created.
SQL> create table mytable_nocompress (col1 varchar2(26),col2 varchar2(26)) tablespace tbs2;
Table created.
SQL> alter system flush buffer_Cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> set timing on
SQL> insert into mytable_nocompress
2 select 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','ABCDEFGHIJKLMNOPQRSTUVWXYZ'
3 from (select 1 from dual connect by level <= 2000000);
2000000 rows created.
Elapsed: 00:00:8.07
SQL> commit;
Commit complete.
Elapsed: 00:00:00.07
SQL> alter system flush buffer_Cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> insert into mytable_compress
2 select 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','ABCDEFGHIJKLMNOPQRSTUVWXYZ'
3 from (select 1 from dual connect by level <= 2000000);
2000000 rows created.
Elapsed: 00:00:41.79
SQL> commit;
Commit complete.
Elapsed: 00:00:00.04
SQL> select segment_name,extents from user_segments where segment_name like 'MYTABLE%';
SEGMENT_NAME EXTENTS
------------------------------ ----------
MYTABLE_COMPRESS 53
MYTABLE_NOCOMPRESS 88
SQL> select tablespace_name,bytes/1024/1024 from dba_free_space where tablespace_name like 'TBS%';
TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
TBS1 461.9375
TBS2 363.9375
SQL> alter table mytable_compress drop column col2;
Table altered.
Elapsed: 00:00:21.04
This section briefly describes a few of the new features of Oracle Database 11g. A detailed description of the new features is beyond the scope of this guide. For a more comprehensive list, see the Oracle Database New Features Guide 11g Release 1 (11.1).
Automatic Memory Management - With Oracle Database 11g, memory management is further automated with the use of the dynamic parameter, memory_target. You would just be required to specify the total instance memory size and the database will automatically manages the memory distribution between the SGA and the PGA. The view, v$memory_target_advice provides advice on memory tuning.
Interval Partitioning improves partition table manageability by creating new table partitions automatically when inserted rows exceed the partition ranges.
Partitioning by integer value
SQL> create table patients (
2 patientid number not null,name varchar2(10),address varchar2(15)
3 )
4 partition by range (patientid)
5 interval (100)
6 (partition p1 values less than (100))
7 /
Table created.
SQL> select partition_name,high_value
2 from user_tab_partitions
3 where table_name='PATIENTS';
PARTITION_NAME HIGH_VALUE
--------------- ---------------
P1 100
SQL> insert into patients values (100,'ROBERT','4 BORNE AVE');
1 row created.
SQL> select partition_name,high_value
2 from user_tab_partitions
3 where table_name='PATIENTS';
PARTITION_NAME HIGH_VALUE
--------------- ---------------
P1 100
SYS_P81 200
SQL> select count(*) from patients partition (SYS_P81);
COUNT(*)
----------
1
Partitioning by date
SQL> create table userlogs (
2 transid number,
3 transdt date,
4 terminal varchar2(10)
5 )
6 partition by range (transdt)
7 interval (numtoyminterval(1,'YEAR'))
8 (
9 partition p1 values less than (to_date('01-01-2007','mm-dd-yyyy'))
10 );
Table created.
SQL> select partition_name,high_value
2 from user_tab_partitions
3 where table_name='USERLOGS';
PARTITION_NAME HIGH_VALUE
-------------- --------------------------------------------------------------------------------
P1 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SQL> insert into userlogs values (1,'11-AUG-07','XAV0004');
1 row created.
SQL> select partition_name,high_value
2 from user_tab_partitions
3 where table_name='USERLOGS';
PARTITION_NAME HIGH_VALUE
-------------- --------------------------------------------------------------------------------
P1 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P42 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SQL> select count(*) from userlogs partition (sys_p42);
COUNT(*)
----------
1
Reference Partitioning partitions a child table based on the partitioning scheme of the parent table.
SQL> create table patients (
2 patientid number not null,name varchar2(10), address varchar2(15)
3 )
4 partition by range (patientid)
5 (partition p1 values less than (100),
6 partition p2 values less than (200))
7 /
Table created.
SQL> alter table patients
2 add constraint patients_pk primary key (patientid);
Table altered.
SQL> create table invoices (
2 invoiceno number,amount number, patientid number not null,
3 constraint invoices_fk
4 foreign key (patientid) references patients
5 )
6 partition by reference (invoices_fk);
Table created.
SQL> select dbms_metadata.get_ddl('TABLE','INVOICES','VCHAN') from dual;
DBMS_METADATA.GET_DDL('TABLE','INVOICES','VCHAN')
-----------------------------------------------------------------------
CREATE TABLE "VCHAN"."INVOICES"
( "INVOICENO" NUMBER,
"AMOUNT" NUMBER,
"PATIENTID" NUMBER NOT NULL ENABLE,
CONSTRAINT "INVOICES_FK" FOREIGN KEY ("PATIENTID")
REFERENCES "VCHAN"."PATIENTS" ("PATIENTID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
PARTITION BY REFERENCE ("INVOICES_FK")
(PARTITION "P1"
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "P2"
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS )
SQL> insert into patients values (1,'TOBY','88 Palace Ave');
1 row created.
SQL> insert into invoices values (150,262.12,1);
1 row created.
SQL> select count(*) from invoices partition (p1);
COUNT(*)
----------
1
SQL> select count(*) from invoices partition (p2);
COUNT(*)
----------
0
SQL> insert into patients values (110,'GILY','512 HILE STREET');
1 row created.
SQL> insert into invoices values (151,500.01,110);
1 row created.
SQL> select count(*) from invoices partition (p1);
COUNT(*)
----------
1
SQL> select count(*) from invoices partition (p2);
COUNT(*)
----------
1
Table Compression in Oracle Database 11g supports conventional DML and drop column operations. Compressed data are not uncompressed during reading and thus queries on compressed data are noticeably faster since there are fewer data block reads.
SQL> create tablespace tbs1 datafile '/u01/app/oracle/oradata/db11g/tbs1_01.dbf' size 500M;
Tablespace created.
SQL> create tablespace tbs2 datafile '/u01/app/oracle/oradata/db11g/tbs2_01.dbf' size 500M;
Tablespace created.
SQL> create table mytable_compress (col1 varchar2(26),col2 varchar2(26)) tablespace tbs1 compress for all operations;
Table created.
SQL> create table mytable_nocompress (col1 varchar2(26),col2 varchar2(26)) tablespace tbs2;
Table created.
SQL> alter system flush buffer_Cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> set timing on
SQL> insert into mytable_nocompress
2 select 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','ABCDEFGHIJKLMNOPQRSTUVWXYZ'
3 from (select 1 from dual connect by level <= 2000000);
2000000 rows created.
Elapsed: 00:00:8.07
SQL> commit;
Commit complete.
Elapsed: 00:00:00.07
SQL> alter system flush buffer_Cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> insert into mytable_compress
2 select 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','ABCDEFGHIJKLMNOPQRSTUVWXYZ'
3 from (select 1 from dual connect by level <= 2000000);
2000000 rows created.
Elapsed: 00:00:41.79
SQL> commit;
Commit complete.
Elapsed: 00:00:00.04
SQL> select segment_name,extents from user_segments where segment_name like 'MYTABLE%';
SEGMENT_NAME EXTENTS
------------------------------ ----------
MYTABLE_COMPRESS 53
MYTABLE_NOCOMPRESS 88
SQL> select tablespace_name,bytes/1024/1024 from dba_free_space where tablespace_name like 'TBS%';
TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
TBS1 461.9375
TBS2 363.9375
SQL> alter table mytable_compress drop column col2;
Table altered.
Elapsed: 00:00:21.04
Obsolete/Deprecated Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
--> "background_dump_dest" replaced by "diagnostic_dest"
--> "user_dump_dest" replaced by "diagnostic_dest"
--> "core_dump_dest" replaced by "diagnostic_dest"
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name, value from v$parameter where name like '%dump_dest' or name like 'diag%';
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
background_dump_dest
c:\oracle\diag\rdbms\dbcse\dbcse\trace
user_dump_dest
c:\oracle\diag\rdbms\dbcse\dbcse\trace
core_dump_dest
c:\oracle\diag\rdbms\dbcse\dbcse\cdump
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
diagnostic_dest
C:\ORACLE
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.1.0
SQL>
**********************************************************************
--> "background_dump_dest" replaced by "diagnostic_dest"
--> "user_dump_dest" replaced by "diagnostic_dest"
--> "core_dump_dest" replaced by "diagnostic_dest"
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name, value from v$parameter where name like '%dump_dest' or name like 'diag%';
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
background_dump_dest
c:\oracle\diag\rdbms\dbcse\dbcse\trace
user_dump_dest
c:\oracle\diag\rdbms\dbcse\dbcse\trace
core_dump_dest
c:\oracle\diag\rdbms\dbcse\dbcse\cdump
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
diagnostic_dest
C:\ORACLE
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.1.0
SQL>
Thursday, January 24, 2008
Oracle
Database programs, with few exceptions, need to utilize both the computer's memory and permanent storage space such as the hard drive to operate. The drives provide both long-term storage and the necessary room for millions of records and gigabytes worth of information. However, accessing information from disks is much slower than retrieving the same information from memory. Therefore, database engines use memory to cache information, which speeds its retrieval.
The complexity of how the information is stored and where it is retrieved from is hidden from the casual user who queries the database. But if you plan on administering Oracle, you need to become familiar with how Oracle handles both resources. In this article, I discuss two basic but important concepts with regard to memory and disk: the database and the instance.
The Database
In Oracle, a database is used to describe the physical files used to store information. There are three types of physical files:
Data files store—you guessed it—all the data that caused us to get a database engine to begin with.
Control files store metadata about the rest of the database for use by the Oracle engine.
Redo-log files are used to record all changes made to the data for use in backup and recovery.
Regardless of how many files are used, they are all part of one database.
SQL Server uses the term database very differently. It's used to define a collection of objects such as tables. Each of these collections is stored in a separate set of files. One SQL Server installation typically contains many databases. In fact, the SQL Server installation process itself creates four databases.
Understanding how each vendor uses the term is critical to understanding the literature written about each of the products.
The Instance
Database files themselves are useless without the memory structures and processes to interact with the database. Oracle defines the term instance as the memory structure and the background processes used to access data from a database.
An instance has two major memory structures:
The System Global Area, also known as the Shared Global Area (SGA) stores information in memory that is shared by the various processes in Oracle.
The Program Global Area, also known as the Private Global Area (PGA) contains information that is private to a particular process.
The SGA contains, among other things, the database buffer cache that is used to cache information read from the data files, a data dictionary cache used to cache metadata information, and a library cache that caches recently used SQL and PL/SQL statements. The PGA is used to allocate memory for information such as sort space, variables, arrays, and cursor information that is private to each process. The instance also contains numerous background processes that cooperate to fulfill all the various functions needed. Some examples of these processes include the Database Writer, responsible for writing all changes to the database, and the Process Monitor, responsible for cleaning up after failed user processes.
In the SQL Server world, it has not been till the 2000 version that the word instance has had any practical significance. Up to that point, you could only have one installation of SQL Server on a machine. With the 2000 version, you can actually have many "instances" of SQL Server running at once. In the SQL Server world, an instance refers to both the memory and files used by that particular installation.
More Than Just a Name
Other than providing yet more acronyms to remember, is there any practical implication to the distinction between the disk files and memory in Oracle? The answer is a resounding yes. Let's look at a couple of Oracle features that illustrate this.
Oracle Parallel Server A single machine can contain only so many CPUs and so much memory. The separation between the memory structures and data files allows Oracle to scale beyond a single machine by allowing multiple instances—that is, multiple separate Oracle memory structures—on different machines to access the same database.
My understanding is that the configuration and use of Parallel Server is rather complicated. Additionally, single Oracle boxes (especially on Unix) can scale quite high with numerous CPUs and large amounts of memory. You may never need this feature, but it does illustrate one practical outcome of the division between the database and the instance.
Starting Oracle Server The division between the instance and the database can be seen by the various steps for starting Oracle. The first step is when the instance itself is started. Memory in the computer is allocated and the various background processes are started. The second step is when the instance then "mounts" the database—i.e., accesses the database files themselves. The last step is opening the database for access by users.
Although you normally make Oracle go through all three steps when starting, it is possible and sometimes necessary to make Oracle stop at a particular stage in the process. Let's look at some of the syntax involved. Use SQL*Plus to log in to Oracle and try these commands if you like. (Assuming Oracle has already started, you can issue the shutdown command to first shut Oracle down.)
STARTUP or STARTUP OPEN tells Oracle to go through all three stages of the startup process. If you want Oracle only to start up the instance, you can instead issue a STARTUP NOMOUNT instead. Oracle will start the instance but not touch any of the database files yet.
Why would you want to do this? Well, let's say you were creating new control files (the files used to store the metadata about the rest of the database) or a whole new database. Such operations need to occur before the database is accessed. You can also issue a STARTUP MOUNT (or if you've previously issued a STARTUP NOMOUNT you can issue an ALTER DATABASE MOUNT) to tell Oracle to mount the database files. In this condition, the instance itself has access to all the information regarding the database. However, it is not yet accessible to users. One example of an operation that must be done in this state is renaming the files used by the SYSTEM tablespace. Once you've finished your changes you can issue the ALTER DATABASE OPEN command to make the database accessible to the public.
As I said, in Oracle an instance refers to the memory and background processes. A database refers to the physical files that store the data. Both are needed to provide the user with the information he or she needs. But Oracle allows (and in some cases requires) you to deal with both parts separately.
The complexity of how the information is stored and where it is retrieved from is hidden from the casual user who queries the database. But if you plan on administering Oracle, you need to become familiar with how Oracle handles both resources. In this article, I discuss two basic but important concepts with regard to memory and disk: the database and the instance.
The Database
In Oracle, a database is used to describe the physical files used to store information. There are three types of physical files:
Data files store—you guessed it—all the data that caused us to get a database engine to begin with.
Control files store metadata about the rest of the database for use by the Oracle engine.
Redo-log files are used to record all changes made to the data for use in backup and recovery.
Regardless of how many files are used, they are all part of one database.
SQL Server uses the term database very differently. It's used to define a collection of objects such as tables. Each of these collections is stored in a separate set of files. One SQL Server installation typically contains many databases. In fact, the SQL Server installation process itself creates four databases.
Understanding how each vendor uses the term is critical to understanding the literature written about each of the products.
The Instance
Database files themselves are useless without the memory structures and processes to interact with the database. Oracle defines the term instance as the memory structure and the background processes used to access data from a database.
An instance has two major memory structures:
The System Global Area, also known as the Shared Global Area (SGA) stores information in memory that is shared by the various processes in Oracle.
The Program Global Area, also known as the Private Global Area (PGA) contains information that is private to a particular process.
The SGA contains, among other things, the database buffer cache that is used to cache information read from the data files, a data dictionary cache used to cache metadata information, and a library cache that caches recently used SQL and PL/SQL statements. The PGA is used to allocate memory for information such as sort space, variables, arrays, and cursor information that is private to each process. The instance also contains numerous background processes that cooperate to fulfill all the various functions needed. Some examples of these processes include the Database Writer, responsible for writing all changes to the database, and the Process Monitor, responsible for cleaning up after failed user processes.
In the SQL Server world, it has not been till the 2000 version that the word instance has had any practical significance. Up to that point, you could only have one installation of SQL Server on a machine. With the 2000 version, you can actually have many "instances" of SQL Server running at once. In the SQL Server world, an instance refers to both the memory and files used by that particular installation.
More Than Just a Name
Other than providing yet more acronyms to remember, is there any practical implication to the distinction between the disk files and memory in Oracle? The answer is a resounding yes. Let's look at a couple of Oracle features that illustrate this.
Oracle Parallel Server A single machine can contain only so many CPUs and so much memory. The separation between the memory structures and data files allows Oracle to scale beyond a single machine by allowing multiple instances—that is, multiple separate Oracle memory structures—on different machines to access the same database.
My understanding is that the configuration and use of Parallel Server is rather complicated. Additionally, single Oracle boxes (especially on Unix) can scale quite high with numerous CPUs and large amounts of memory. You may never need this feature, but it does illustrate one practical outcome of the division between the database and the instance.
Starting Oracle Server The division between the instance and the database can be seen by the various steps for starting Oracle. The first step is when the instance itself is started. Memory in the computer is allocated and the various background processes are started. The second step is when the instance then "mounts" the database—i.e., accesses the database files themselves. The last step is opening the database for access by users.
Although you normally make Oracle go through all three steps when starting, it is possible and sometimes necessary to make Oracle stop at a particular stage in the process. Let's look at some of the syntax involved. Use SQL*Plus to log in to Oracle and try these commands if you like. (Assuming Oracle has already started, you can issue the shutdown command to first shut Oracle down.)
STARTUP or STARTUP OPEN tells Oracle to go through all three stages of the startup process. If you want Oracle only to start up the instance, you can instead issue a STARTUP NOMOUNT instead. Oracle will start the instance but not touch any of the database files yet.
Why would you want to do this? Well, let's say you were creating new control files (the files used to store the metadata about the rest of the database) or a whole new database. Such operations need to occur before the database is accessed. You can also issue a STARTUP MOUNT (or if you've previously issued a STARTUP NOMOUNT you can issue an ALTER DATABASE MOUNT) to tell Oracle to mount the database files. In this condition, the instance itself has access to all the information regarding the database. However, it is not yet accessible to users. One example of an operation that must be done in this state is renaming the files used by the SYSTEM tablespace. Once you've finished your changes you can issue the ALTER DATABASE OPEN command to make the database accessible to the public.
As I said, in Oracle an instance refers to the memory and background processes. A database refers to the physical files that store the data. Both are needed to provide the user with the information he or she needs. But Oracle allows (and in some cases requires) you to deal with both parts separately.
Tuesday, January 22, 2008
Subscribe to:
Posts (Atom)