Department of Computer and Information Science, School of Science, IUPUI

1. Department of Computer and Information Science,School of Science, IUPUI SQL Utilities Dale Roberts, Lecturer Computer Science, IUPUI E-mail: droberts@cs.iupui.edu

2. SQL Utilities • SQL*Loader • Data Pump Export and Import • Accessing Remote Data (Database Links, Oracle Net) • Materialized Views (aka Snapshots) • Data Dictionary Views 2

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

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

5. SQL*Loader Control File Examples • Variable-length field control file • Fixed-length field control file 5

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

7. Network Data Pump Export Operation Database Server Client Machine expdp.exe Oracle Database Export File(s)

8. Types of Exports • Table • Schema • Tablespace • Database • Transportable Tablespace • INCLUDE / EXCLUDE object filters • QUERY and SAMPLE data filters • CONTENTS = data | metadata | both

9. 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 …

10. 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! expdpscott/tiger@fredparfile=myjob.par

11. Interactive Mode Commands

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

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

14. 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; 14

15. 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) • ) • ) 15

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

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

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

19. Acknowledgements • Loney, Oracle Database 10g The Complete Reference • Calgary Oracle User‘s Group, www.coug.ab.ca.