Chapters 21, 22, 23, 37

1
Chapters 21, 22, 23, 37

• Ch. 21 SQLLoader
• Ch. 22 Database Links, Oracle Net
• Ch. 23 Materialized Views (aka Snapshots)
• Ch. 37 Data Dictionary

2
Ch. 21 SQLLoader

• SQLLoader 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, SQLLoader creates
• bad file, shows rejected rows
• log file, shows load status

3
Ch. 21 SQLLoader Control File Examples

• Variable field control file
• Fixed field control file

4
Ch. 22 Database Links

• Database links allow you to access data in
  another Oracle database.
• Requires that Oracle Net (aka Oracle SQLNet 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.

5
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_at_MYLINK
• Using a synonym with a database link
• CREATE SYNONYM MYTABLE FOR ATEAL.MYTABLE_at_MYLINK
• SELECT COUNT() FROM MYTABLE
• Dynamic links using SQLPluss copy command
• COPY FROM ATEAL/PASSWORD_at_MYLINK
• CREATE NEWTABLECOPY
• USING
• SELECT FROM MYTABLE

6
Ch. 22 Oracle Net

• Oracle Net is Oracles networking software.
  Formerly called SQLNet (7.x) and Net8 (8.0.x and
  8i).
• Oracle Nets 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)

7
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)

8
Ch. 37 Data Dictionary

• Oracles 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

9
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