chapters 21 22 23 37 n.
Skip this Video
Loading SlideShow in 5 Seconds..
Chapters 21, 22, 23, 37 PowerPoint Presentation
Download Presentation
Chapters 21, 22, 23, 37

Loading in 2 Seconds...

  share
play fullscreen
1 / 9
Download Presentation

Chapters 21, 22, 23, 37 - PowerPoint PPT Presentation

cecile
110 Views
Download Presentation

Chapters 21, 22, 23, 37

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. 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

  2. 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

  3. Ch. 21: SQL*Loader Control File Examples • Variable field control file • Fixed field control file 3

  4. 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

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

  6. 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

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

  8. 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

  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 9