Transcript SQL Utilities - Department of Computer and Information Science

Department of Computer and Information Science,
School of Science, IUPUI
SQL Utilities
Dale Roberts, Lecturer
Computer Science, IUPUI
E-mail: [email protected]
Dale Roberts
1
SQL Utilities
1. SQL*Loader
2. Data Pump Export and Import
3. Accessing Remote Data (Database Links, Oracle
Net)
4. Materialized Views (aka Snapshots)
5. Data Dictionary Views
Dale Roberts
2
SQL*Loader – Bulk Load
SQL*Loader, executed as sqlldr, loads data from external files into tables
in Oracle.
Very common utility used to process external input interfaces from other
applications.
Uses a control file to describe the input data files.
Control file is flexible and supports the following features:
Variable and fixed length record formats
Many-to-one physical to logical records
One-to-many physical to logical records
Data transformations
NULLIF
Functions – UPPER(:Authorname)
Expressions - :taxamount / 100
Formatting – DATE(:birthdate, ‘MM-DD-YYYY’)
Default column values – SYSDATE
Row selection - WHEN
Batch and interactive interfaces
Supports partial loads set error and discard tolerance levels
Dale Roberts
3
SQL*Loader Architecture
SQL*Loader runs locally on the
client, therefore all files resides on
the client except for the database
tables.
Input files contain data to be
loaded.
Control file contains instructions
to sqlldr, including describing the
input files.
Log file contains a detailed
summary of the load, including a
description of any errors that
occurred. Can set max allowed.
Discard file contains rows that are
filtered out by the control file and
did not make it to the database.
Can set max allowed.
Dale Roberts
4
SQL*Loader Control File Examples
Variable-length field control file
Fixed-length field control file
Dale Roberts
5
Data Pump Overview
Oracle Data Pump technology enables very high-speed
movement of data and metadata from one database to
another.
Data Pump Export is a utility for unloading data and
metadata into a set of operating system files called a
dump file set.
The dump file set can be moved to another system and
loaded by the Data Pump Import utility.
Data Pump Import is a utility for loading an export dump
file set into a target system. The dump file set is made
up of one or more disk files that contain table data,
database object metadata, and control information.
The files are written in a proprietary, binary format.
Dale Roberts
6
Data Pump Export Operation
expdp.exe
Network
Oracle
Database
Export
File(s)
Dale Roberts
7
Types of Exports
Table
Schema
Tablespace
Database
Transportable Tablespace
INCLUDE / EXCLUDE object filters
QUERY and SAMPLE data filters
CONTENTS = data | metadata | both
Dale Roberts
Directory Objects
Created as a database object
Requires CREATE_ANY_DIRECTORY privilege
Permissions (read, write) granted on the
object to specific user(s)
Not validated – existence, syntax, OS
privilege
Accessed as user “oracle” at the OS level
Default DATA_PUMP_DIR maps to …
Dale Roberts
Data Punp Interactive Mode
NOT the same as old imp/exp!
Default starts schema mode export
Use command line arguments or par file
“logging” vs “interactive command” mode
Default logging mode logs to terminal
Ctrl-C to enter interactive command mode
Job will continue to run even if client
disconnects!
expdp scott/tiger@fred parfile=myjob.par
Dale Roberts
Interactive Mode Commands
STATUS
CONTINUE_CLIENT
EXIT_CLIENT
STOP_JOB
START_JOB
KILL_JOB
PARALLEL
ADD_FILE
HELP
Status of current job
Resume “logging” mode
Exit client, leave job running
Stop current job, do not delete
Re-start current job
Delete current job
Add/remove worker processes
Add dump file during export
Get help
Dale Roberts
Data Pump SQL File
Import can generate an SQL file instead of
actually performing the import using SQLFILE
parameter
Contains DDL that would have been executed
based on job parameters
Passwords excluded
No change to target DB
Does not include DML for data. (i.e. no inserts)
Dale Roberts
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.
Database links expressed with @ sign: practice.ledger@firebird
Dale Roberts
13
Database Links
Create Database Link syntax:
CREATE DATABASE LINK MYLINK
CONNECT TO DALE IDENTIFIED BY PASSWORD
USING ‘FIREBIRD’;
Using a database link:
SELECT COUNT(*) FROM DALE.MYTABLE@MYLINK;
Using a synonym with a database link (location independence):
CREATE SYNONYM MYTABLE FOR DALE.MYTABLE@MYLINK;
SELECT COUNT(*) FROM MYTABLE;
“Dynamic” links using SQL*Plus’s copy command:
COPY FROM DALE/PASSWORD@MYLINK
CREATE NEWTABLECOPY
USING
SELECT * FROM MYTABLE;
Dale Roberts
14
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 = DALE)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = ORACLE)
)
)
FIREBIRD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = firebird.cs.iupui.edu)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = cs10gorc.cs.iupui.edu)
)
)
Dale Roberts
15
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.
•
Why materialized views instead of views?
•
Classic space vs time trade-off.

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)
Dale Roberts
16
Data Dictionary - Metadata

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 “meta metadata data”: DICT and
DICT_COLUMNS
• DICTIONARY (DICT) – describes other dictionary views
• DICT_COLUMNS – describes columns of DICT views

Data Dictionary views can be found in the SYS tablespace but have
public synonyms so no schema owner is necessary.

Access to production source code:
• Viewing production source code requires compilation privilege,
which is not normally allowed in production.
• Granting select access to DBA_SOURCE is one workaround.
Dale Roberts
17
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
Dale Roberts
18
Acknowledgements
Loney, Oracle Database 10g The Complete Reference
Calgary Oracle User‘s Group, www.coug.ab.ca.
Dale Roberts
19