Oracle Database Tutorials

Q

Where to find Oracle database server tutorials?

✍: FYIcenter.com

A

Here is a collection of tutorials, tips and FAQs for Oracle DBAs and Oracle database application developers. Clear explanations and simple examples provided can be used as learning tutorials or interview preparation guides.

It doesn't matter whether you are a beginner or an experienced Oracle DBA or developer, browse through our Oracle DBA FAQ and tips. They will always help you to improve your skills and find some good ideas to solve problems in your daily tasks.

So far, our FYIcenter.com DBA team have written 400 questions and answers to share with you. Each one of them answers one commonly asked Oracle question with a short, but precise and simple example.

Oracle Database Basic Concepts

What Is Oracle in Oracle

What Is an Oracle Database in Oracle

What Is an Oracle Instance in Oracle

What Is a Parameter File in Oracle

What Is a Server Parameter File in Oracle

What Is an Initialization Parameter File in Oracle

What Is System Global Area in Oracle

What Is Program Global Area (PGA) in Oracle

What Is a User Account in Oracle

Relations of a User Account and a Schema in Oracle

What Is a User Role in Oracle

What Is a Database Schema in Oracle

What Is a Database Table in Oracle

What Is a Table Index in Oracle

What Is an Oracle Tablespace in Oracle

What Is an Oracle Data File in Oracle

What Is a Static Data Dictionary in Oracle

What Is a Dynamic Performance View in Oracle

What Is a Recycle Bin in Oracle

What Is SQL*Plus in Oracle

What Is Transport Network Substrate in Oracle

What Is ODBC in Oracle

Introduction to Oracle Database 10g Express Edition

What Is Oracle Database 10g Express Edition in Oracle

Limitations of Oracle Database 10g XE in Oracle

Systems Supported by 10g XE Server in Oracle

Download Oracle Database 10g XE in Oracle

Install Oracle Database 10g XE in Oracle

Verify 10g XE Server Installation in Oracle

Shutdown 10g XE Server in Oracle

Start 10g XE Server in Oracle

Memory Usage of 10g XE Server in Oracle

Start 10g XE Server from Command Line in Oracle

Shutdown 10g XE Server from Commmand Line in Oracle

Unlock the Sample User Account in Oracle

Change System Global Area (SGA) in Oracle

Change Program Global Area (PGA) in Oracle

SGA Setting Is Too Low in Oracle

StartDB.bat Failed to Start the XE Instance in Oracle

Login to Server without an Instance in Oracle

Start the Default Instance in Oracle

Instance Settings Stored in SPFile in Oracle

Change Settings in Binary SPFile in Oracle

Show the Server Version in Oracle

Introduction to Command-Line SQL*Plus Client Tool

Main Features of SQL*Plus in Oracle

Start the Command-Line SQL*Plus in Oracle

Help at the SQL Prompt in Oracle

Requirements for SQL*Plus Connection in Oracle

What Is a Connect Identifier in Oracle

Connect SQL*Plus Session to Oracle Server in Oracle

Error: Could not Resolve the Connect Identifier in Oracle

Reset Lost SYSTEM Password in Oracle

Types of Commands Executed in SQL*Plus in Oracle

Run SQL Commands in SQL*Plus in Oracle

Run PL/SQL Statements in SQL*Plus in Oracle

Change SQL*Plus System Settings in Oracle

Show Current SQL*Plus System Settings in Oracle

SQL*Plus Environment Variables in Oracle

Generate Query Output in HTML Format in Oracle

Output Spooling in SQL*Plus in Oracle

Save Query Output to a Local File in Oracle

Input Buffer in SQL*Plus in Oracle

Revise and Rerun the Last SQL Command in Oracle

SQL*Plus Commands Stored in a File in Oracle

Use SQL*Plus Built-in Timer in Oracle

What Is Oracle Server Autotrace in Oracle

Setup Autotrace for a User Account in Oracle

Show Execution Path Reports in Oracle

Show Execution Statistics Reports in Oracle

Understanding SQL Language Basics for Oracle

What Is SQL Standard in Oracle

Categories of Data Types in Oracle

Oracle Built-in Data Types in Oracle

Differences between CHAR and NCHAR in Oracle

Differences between CHAR and VARCHAR in Oracle

Differences between NUMBER and BINARY_FLOAT in Oracle

Differences between DATE and TIMESTAMP in Oracle

Differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND in Oracle

Differences between BLOB and CLOB in Oracle

ANSI Data Types in Oracle

Text Literals in Oracle

Numeric Literals in Oracle

Date and Time Literals in Oracle

Date and Time Interval Literals in Oracle

Convert Numbers to Character Strings in Oracle

Convert Character Strings to Numbers in Oracle

Convert Dates to Character Strings in Oracle

Convert Character Strings to Dates in Oracle

Convert Times to Character Strings in Oracle

Convert Character Strings to Times in Oracle

What Is NULL Value in Oracle

Use NULL as Conditions in Oracle

Concatenate Two Text Values in Oracle

Increment Dates by 1 in Oracle

Date and Time Differences in Oracle

Use IN Conditions in Oracle

Use LIKE Conditions in Oracle

Use of Regular Expression in Oracle

Understanding SQL DDL Statements for Oracle

What Are DDL Statements in Oracle

Create a New Table in Oracle

Create a New Table with SELECT Statements in Oracle

ALTER TABLE - Add a New Column in Oracle

ALTER TABLE - Delete a Column in Oracle

DROP TABLE Statement in Oracle

CREATE INDEX - Create a Table Index in Oracle

ALTER INDEX - Rename an Index in Oracle

Drop an Existing Index in Oracle

Create a New View in Oracle

Drop an Existing View in Oracle

Understanding SQL DML Statements for Oracle

What Are DML Statements in Oracle

Create a Test Table for DML Testing in Oracle

Set Up SQL*Plus Output Format in Oracle

Insert a New Row into a Table in Oracle

Specify Default Values in INSERT Statements in Oracle

Omit Columns in INSERT Statements in Oracle

Insert Multiple Rows with 1 INSERT Statement in Oracle

Update Values in a Table in Oracle

Update Values on Multiple Rows in Oracle

Use Existing Column Values in SET Clause in Oracle

Use Values from Other Tables in UPDATE in Oracle

Error: Single-Row Subquery Returns More Than One Row in Oracle

Delete an Existing Row from a Table in Oracle

Delete Multiple Rows from a Table in Oracle

in Oracle

Understanding SQL SELECT Query Statements in Oracle

What Is a SELECT Query Statement in Oracle

Select All Columns of All Rows in Oracle

Select Some Columns from a Table in Oracle

Select Some Rows from a Table in Oracle

Sort the Query Output in Oracle

Query Output Sorted by Multiple Columns in Oracle

Sort Query Output in Descending Order in Oracle

Count the Number of Rows with SELECT Statements in Oracle

Use of SELECT Statements in Views in Oracle

Filter Out Duplications in Returning Rows in Oracle

What Are Group Functions in Oracle

Use Group Functions in the SELECT Clause in Oracle

Group Functions Used with Non-group Selection Fields in Oracle

Divide Query Output into Groups in Oracle

Apply Filtering Criteria at Group Level in Oracle

Count Duplicated Values in a Column in Oracle

Use Multiple Columns in GROUP BY in Oracle

Use Group Functions in ORDER BY Clause in Oracle

Ways to Join Two Tables in a Single Query in Oracle

Query with an Inner Join in Oracle

Define and Use Table Alias Names in Oracle

Query with a Left Outer Join in Oracle

Query with a Right Outer Join in Oracle

Query with a Full Outer Join in Oracle

Inner Join with the WHERE Clause in Oracle

Left Outer Join with the WHERE Clause in Oracle

Name Query Output Columns in Oracle

What Is a Subquery in Oracle

Use Subqueries with the IN Operator in Oracle

Use Subqueries with the EXISTS Operator in Oracle

Use Subqueries in the FROM Clause in Oracle

Counting Groups Returned with the GROUP BY Clause in Oracle

Return Top 5 Rows in Oracle

Understanding SQL Transaction Management in Oracle

What Is a Transaction in Oracle

Ways to Start a New Transaction in Oracle

Ways to End the Current Transaction in Oracle

Create a Test Table for Transaction Testing in Oracle

Commit the Current Transaction in Oracle

Rollback the Current Transaction in Oracle

Transaction Commit When DDL Statement Executed in Oracle

Transaction Commit When Session Ended in Oracle

Transaction Rollback When Session Killed in Oracle

Read Consistency in Oracle in Oracle

What Is a READ WRITE Transaction in Oracle

What Is a READ ONLY Transaction in Oracle

Set a READ ONLY Transaction in Oracle

Restrictions in a READ ONLY Transaction in Oracle

General Rules on Data Consistency in Oracle

Supported Transaction Isolation Levels in Oracle

What Is a Data Lock in Oracle

How Data Locks Are Respected in Oracle

Experiments of Data Locks in Oracle

Show Existing Locks on the Database in Oracle

What Is a Dead Lock in Oracle

How Oracle Handles Dead Locks in Oracle

Managing Oracle User Accounts, Schema and Privileges

User Account and Attributes in Oracle

User Account and Schema: One-to-One Relation in Oracle

User Role: Group of Privileges in Oracle

System Defined User Roles in Oracle

Built-in Internal User Accounts in Oracle

Use SYS Account to Connect to the Server in Oracle

Use Windows User to Connect to the Server in Oracle

Show All User Accounts in Oracle

Create a New User Account in Oracle

Change User Password in Oracle

Delete a User Account in Oracle

Privilege Needed to Connect to Oracle Server in Oracle

Grant CREATE SESSION Privilege in Oracle

Revoke CREATE SESSION Privilege in Oracle

Lock and Unlock a User Account in Oracle

Privilege to Create Tables in Oracle

Assign a Tablespace to a User in Oracle

Privilege to Create Views in Oracle

Privilege to Create Indexes in Oracle

Privilege to Query Tables in Another Schema in Oracle

Privilege to Insert Rows in Another Schema in Oracle

Privilege to Delete Rows in Another Schema in Oracle

Show Privileges of the Current User in Oracle

Managing Oracle Database Tables

Database Table: Rows of Columns in Oracle

Types of Tables Supported by Oracle in Oracle

Create a New Table in Your Schema in Oracle

Create a New Table by Selecting Rows from Another Table in Oracle

Rename an Existing Table in Oracle

Drop an Existing Table in Oracle

Add a New Column to an Existing Table in Oracle

Add a New Column to an Existing Table with a Default Value in Oracle

Rename a Column in an Existing Table in Oracle

Delete a Column in an Existing Table in Oracle

Show All Columns in an Existing Table in Oracle

Recover a Dropped Table in Oracle

Recycle Bin - Storage to Hold Dropped Tables in Oracle

Turn On and Off Recycle Bin for the Instance in Oracle

Show Dropped Tables in Recycle Bin in Oracle

Empty Your Recycle Bin in Oracle

Turn On and Off Recycle Bin for the Session in Oracle

Show All Tables in Your Schema in Oracle

Managing Oracle Table Indexes

Index - Data Structure for Query Performance in Oracle

Run SQL Statement through Web UI in Oracle

Create a Table Index in Oracle

Show All Indexes in Your Schema in Oracle

Indexes for the PRIMARY KEY Column in Oracle

Rename an Index in Oracle

Drop an Index in Oracle

Error: Cannot Drop Index on Primary Key in Oracle

Dropped Tables with Indexes in Oracle

Recover a Dropped Index in Oracle

Recovered Tables with Indexes in Oracle

Rebuild an Index in Oracle

Show Table Columns Used in an Index in Oracle

Create a Single Index for Multiple Columns in Oracle

Managing Oracle Tablespaces and Data Files

Oracle Tablespace - Unit of Logical Storage in Oracle

Oracle Data File - Unit of Physical Storage in Oracle

Relations between a Tablespace and Data Files in Oracle

Relation between Database and Tablespaces in Oracle

Show All Tablespaces in the Current Database in Oracle

Built-in Tablespaces in a Database in Oracle

Show All Data Files in the Current Database in Oracle

Create a New Data File in Oracle

Create a New Tablespace in Oracle

Rename a Tablespace in Oracle

Drop a Tablespace in Oracle

Keep Data Files When Tablespace Dropped in Oracle

Create a Table in a Given Tablespace in Oracle

Show Free Space in a Tablespace in Oracle

Bring a Tablespace Offline in Oracle

Bring a Tablespace Online in Oracle

Add Another Datafile to a Tablespace in Oracle

Error: Cannot Identify/Lock Data File in Oracle

Remove Data Files before Opening a Database in Oracle

Creating New Database Instance Manually

Ways to Create an Oracle Database in Oracle

Create an Oracle Database Manually in Oracle

Select an Oracle System ID (SID) in Oracle

Establish Administrator Authentication to the Server in Oracle

Create an Initialization Parameter File in Oracle

Connect to Oracle Server as SYSDBA in Oracle

Create a Server Parameter File in Oracle

Start an Oracle Instance in Oracle

Start a Specific Oracle Instance in Oracle

Start with a Minimum Initialization Parameter File in Oracle

Run CREATE DATABASE Statement in Oracle

Clean Up When CREATE DATABASE Failed in Oracle

Running CREATE DATABASE Statement Again in Oracle

Create Additional Tablespaces in Oracle

Build Data Dictionary View in Oracle

Introduction to Oracle PL/SQL

What Is PL/SQL in Oracle

Types of PL/SQL Code Blocks in Oracle

Define an Anonymous Block in Oracle

How Many Anonymous Blocks Can Be Defined in Oracle

Run the Anonymous Block Again in Oracle

What Is a Stored Procedure in Oracle

Create a Simple Stored Procedure in Oracle

Execute a Simple Stored Procedure in Oracle

Data Types Supported in PL/SQL in Oracle

Execution Flow Control Statements in Oracle

Use SQL Statements in PL/SQL in Oracle

Process Query Result in PL/SQL in Oracle

Create an Array in PL/SQL in Oracle

Manage Transaction Isolation Levels in Oracle

Example of Passing Parameters to Procedures in Oracle

Define a Procedure inside another Procedure in Oracle

What Do You Think about PL/SQL in Oracle

Introduction to Oracle SQL Developer

What Is Oracle SQL Developer in Oracle

Systems Supported by Oracle SQL Developer in Oracle

Download-Oracle-SQL-Developer in Oracle

Install Oracle SQL Developer in Oracle

Start Oracle SQL Developer in Oracle

Oracle SQL Developer Written in Java in Oracle

Connect to Local 10g XE Server in Oracle

Connect to a Remote Server in Oracle

Run SQL Statements with SQL Developer in Oracle

Export Connection Information to a File in Oracle

Run SQL*Plus Commands in SQL Developer in Oracle

Work with Data Objects Interactively in Oracle

Retrieve CREATE TABLE Statement with SQL Developer in Oracle

Create a Table Interactively in Oracle

Enter a New Row Interactively in Oracle

Report View in SQL Developer in Oracle

Show All Tables in the Database in Oracle

Show All User Accounts in the Database in Oracle

Show All Background Sessions in the Database in Oracle

Create Your Own Reports in SQL Developer in Oracle

File Formats Supported on Export Data in Oracle

Export Data to a CSV File in Oracle

Export Data to an XML File in Oracle

Create a Stored Procedure Interactively in Oracle

Run Stored Procedures Interactively in Oracle

Run Stored Procedures in Debug Mode in Oracle

Assign Debug Privilege to a User in Oracle

Set Up Breakpoints in Debug Mode in Oracle

What Do You Think about Oracle SQL Developer in Oracle

Understanding PL/SQL Language Basics

PL/SQL Language Case Insensitive in Oracle

Enter Comments in PL/SQL in Oracle

Different Types of PL/SQL Code Blocks in Oracle

PL/SQL Anonymous Block in Oracle

PL/SQL Named Program Unit in Oracle

PL/SQL Procedure in Oracle

PL/SQL Functions in Oracle

Declare Local Variables in Oracle

Initialize Variables with Default Values in Oracle

Assign Values to Variables in Oracle

Arithmetic Operations in Pl/SQL in Oracle

Numeric Comparison Operations in Oracle

Logical Operations in PL/SQL in Oracle

Categories of Data Types in PL/SQL in Oracle

Scalar Data Types Supported in PL/SQL in Oracle

Convert Character Types to Numeric Types in Oracle

Types of Execution Flow Control Statements in Oracle

Use "IF" Statements with Multiple Conditions in Oracle

Use "WHILE" Loop Statements in Oracle

Use "FOR" Loop Statements in Oracle

What Is NULL in PL/SQL in Oracle

Test NULL Values in Oracle

Creating Oracle PL/SQL Procedures and Functions

What Is a Procedure in Oracle

What Is a Function in Oracle

Define Anonymous Procedures without Variables in Oracle

Define Anonymous Procedures with Variables in Oracle

Create a Stored Procedure in Oracle

Execute a Stored Procedure in Oracle

Drop a Stored Procedure in Oracle

Pass Parameters to Procedures in Oracle

Create a Stored Function in Oracle

Call a Stored Function in Oracle

Drop a Stored Function in Oracle

Call a Stored Functoin with Parameters in Oracle

Define a Sub Procedure in Oracle

Call a Sub Procedure in Oracle

Define a Sub Function in Oracle

Call Procedure or Function Recursively in Oracle

Run-Away Recursive Calls in Oracle

Define Variables before Procedures and Functions in Oracle

Formal Parameters and Actual Parameters in Oracle

Parameter Modes Supported by PL/SQL in Oracle

Use "IN" Parameters in Oracle

Use "OUT" Parameters in Oracle

Use "IN OUT" Parameters in Oracle

Define Default Values for Formal Parameters in Oracle

What Are Named Parameters in Oracle

Scope of Local Variables in Oracle

Working with Database Objects in Oracle PL/SQL

Use DML Statements in PL/SQL in Oracle

Cannot Use DDL Statements in PL/SQL in Oracle

Use Variables in SQL Statements in Oracle

Variable Names Collide with Column Names in Oracle

Name Conflicts between Variables and Columns in Oracle

Assign Query Results to Variables in Oracle

Error: Exact Fetch Returns More Rows in Oracle

Invoke Built-in Functions in PL/SQL in Oracle

Retrieve the Count of Updated Rows in Oracle

Implicit Cursor in Oracle

Assign Data from a Deleted Row to Variables in Oracle

What Is a RECORD in PL/SQL in Oracle

Define a Specific RECORD Type in Oracle

Define a Variable for a Specific RECORD Type in Oracle

Assign Field Values into RECORD Variables in Oracle

Retrieve Field Values from RECORD Variables in Oracle

Define a Data Field as NOT NULL in Oracle

Define a RECORD Variable for a Table Row in Oracle

Assign a Table Row to RECORD Variable in Oracle

Insert a RECORD into a Table in Oracle

Update a Table Row with a RECORD in Oracle

Define a Variable to Match Column Data Type in Oracle

Working with Cursors in Oracle PL/SQL

What Is a Cursor in Oracle

Types of Cursors Supported in PL/SQL in Oracle

What Is the Implicit Cursor in Oracle

Attributes of the Implicit Cursor in Oracle

Loop through the Implicit Cursor in Oracle

Define an Explicit Cursor in Oracle

Open and Close an Explicit Cursor in Oracle

Retrieve Data from an Explicit Cursor in Oracle

Retrieve Data from a Cursor to a RECORD in Oracle

Use FETCH Statement in a Loop in Oracle

Use an Explicit Cursor without OPEN Statements in Oracle

Open Multiple Cursors at the Same Time in Oracle

Pass a Parameter to a Cursor in Oracle

What Is a Cursor Variable in Oracle

Define a Cursor Variable in Oracle

Open a Cursor Variable in Oracle

Loop through a Cursor Variable in Oracle

Pass a Cursor Variable to a Procedure in Oracle

Cursor Variable Easier to Use than Cursor in Oracle

Loading and Exporting Data in Oracle

Simplest Tool to Run Commands on Oracle Server in Oracle

Quickest Way to Export Data to a Flat File in Oracle

Export Data with Field Delimiters in Oracle

What Is SQL*Loader in Oracle

What Is a SQL*Loader Control File in Oracle

Load Data with SQL*Loader in Oracle

What Is an External Table in Oracle

Load Data through External Tables in Oracle

Restrictions on External Table Columns in Oracle

What Is a Directory Object in Oracle

Define External Table in a Text File in Oracle

Run Queries on External Tables in Oracle

Load Data from External Tables in Oracle

Data Pump Export Utility in Oracle

Data Pump Import Utility in Oracle

Invoke the Data Pump Export Utility in Oracle

Invoke the Data Pump Import Utility in Oracle

Modes of Data Dump Export and Import in Oracle

Disk Space Needed for an Export Job in Oracle

Perform a Full Database Export in Oracle

Where Is the Export Dump File Located in Oracle

Export Your Own Schema in Oracle

Export Several Tables Together in Oracle

Error: Imported Table Already Exists in Oracle

Import One Table back from a Dump File in Oracle

Original Export/Import Utilities in Oracle

Invoke Original Export/Import Utiities in Oracle

ODBC Drivers and DSN Configuration for Oracle

What Is Open Database Communication (ODBC) in Oracle

Install Oracle ODBC Drivers in Oracle

Show Installed Oracle ODBC Drivers in Oracle

Windows Applications Connect to Oracle Servers in Oracle

Create Tables for ODBC Connection Testing in Oracle

Verify Oracle TNS Settings in Oracle

Define DSN with ODBC Manager in Oracle

Connect MS Access to Oracle Servers in Oracle

Connect ASP Pages to Oracle Servers in Oracle

Copyright © FYIcenter.com. All rights reserved. Oracle v1.00

 

Oracle Database Basic Concepts

2020-12-02, 23527🔥, 1💬