Transcript lecture5

Chapters 21, 22, 23, 37
 Ch. 21: SQL*Loader
 Ch. 22: Database Links, Oracle Net
 Ch. 23: Materialized Views (aka Snapshots)
 Ch. 37: Data Dictionary
1
Ch. 21: SQL*Loader

SQL*Loader loads data from external files into tables in Oracle.

Usually requires 2 primary files:
•
•
•

Datafile(s), data to be loaded.
Control file, describes how to load.
these two files can be combined into the control file.
When executing, SQL*Loader creates:
•
•
bad file, shows rejected rows
log file, shows load status
2
Ch. 21: SQL*Loader Control File Examples

Variable field control file

Fixed field control file
3
Ch. 22: Database Links



Database links allow you to access data in another Oracle database.
Requires that Oracle Net (aka Oracle SQL*Net or Net8) be running on
both database servers.
Links specify:
•
•
•
•
Protocol (eg. TCP/IP)
Host name of remote server.
Database name on the remote server
The database account and password to access the data.
4
Ch. 22: Database Links

Create Database Link syntax:
CREATE DATABASE LINK MYLINK
CONNECT TO ATEAL IDENTIFIED BY PASSWORD
USING ‘PHOENIX’;

Using a database link:
SELECT COUNT(*) FROM ATEAL.MYTABLE@MYLINK;

Using a synonym with a database link:
CREATE SYNONYM MYTABLE FOR ATEAL.MYTABLE@MYLINK;
SELECT COUNT(*) FROM MYTABLE;

“Dynamic” links using SQL*Plus’s copy command:
COPY FROM ATEAL/PASSWORD@MYLINK
CREATE NEWTABLECOPY
USING
SELECT * FROM MYTABLE;
5
Ch. 22: Oracle Net



Oracle Net is Oracle’s networking software. Formerly called SQL*Net (7.x) and
Net8 (8.0.x and 8i).
Oracle Net’s local configuration files are sqlnet.ora and tnsnames.ora. These
are located in ORACLE_HOME\network\admin.
Example tnsnames.ora entries below.
LOCAL =
(DESCRIPTION =
(SOURCE_ROUTE = OFF)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = TONYTEAL)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = ORACLE)
)
)
PHOENIX =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = phoenix.cs.iupui.edu)(PORT = 1521))
)
(CONNECT_DATA = (SID = OS80)
)
)
6
Ch. 23: Materialized Views

Materialized Views (formerly called Snapshots) pre-aggregate data,
index the data, and therefore improve query performance. Oracle
creates a table that holds the data that otherwise might be accessed
from queries/views.

Materialized views are commonly used in reporting applications.

When you create a materialized view, you must specify:
• The query the materialized view is based on.
• The refresh schedule.
• How the update is performed (update vs. complete refresh)
• The key type (rowid vs. primary key)
7
Ch. 37: Data Dictionary

Oracle’s Data Dictionary views stores all the information about what is
stored in the database.

Three types of data dictionary views:
• DBA_% views are only available to DBAs (granted DBA role) .
• ALL_% views show all objects the user has privileges on.
• USER_% views show only objects in your schema.

The Road Map views: DICT and DICT_COLUMNS
• DICTIONARY (DICT) – describes other dictionary views
• DICT_COLUMNS – describes columns of DICT views
8
Ch. 37: Data Dictionary

Important Data Dictionary views:
• USER_TABLES (TABS) – tables
• USER_TAB_COLUMNS (COLS) – columns of tables
• USER_VIEWS – views
• USER_SYNONYMS (SYN) – synonyms
• USER_SEQUENCES (SEQ) – sequences
• USER_CONSTRAINTS – constraints
• USER_CONS_COLUMNS – columns of the constraints
• USER_INDEXES (IND) – indexes
• USER_IND_COLUMNS – columns of indexes
9