1 / 9

Chapters 21, 22, 23, 37

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. Ch. 21: SQL*Loader. SQL*Loader loads data from external files into tables in Oracle. Usually requires 2 primary files:

cecile
Download Presentation

Chapters 21, 22, 23, 37

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related