 |
|
Oracle Tips by Burleson |
Define and Query an External
Table
This exercise is in defining an external table
and running SQL against the external table.
Below we have a comma-delimited flat file
showing employee information (figure 1). We have the employee
ID, the employee last name, the job description, the employee ID of
their manager, their hire date, salary, commission and department, all
in a nice comma-delimited format.
7369,SMITH,CLERK,7902,17-DEC-80,800,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,BURLESON,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20
Figure 1 – A comma-delimited flat file
So, how do we define this file to Oracle?
First, we must create an Oracle directory entry in the data
dictionary, pointing to the Windows directory where the flat file
resides. In this example, we name the directory testdir, and it
points to c:\Burleson\queries:
Execute this command on your Oracle database.
SQL> create
directory testdir as ‘c:\Burleson\queries’;
Directory
Created.
Now that we have the directory, we can define
the structure of the external file to Oracle.
Copy this script and execute it on your Oracle
database.
create table
emp_ext
(
empno number(4),
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2)
)
organization external
(
type oracle_loader
default directory testdir
access parameters
(
records delimited by newline
fields terminated by ‘,’
)
location (‘emp_ext.csv’)
)
reject limit 1000;
In the syntax, we define the column of the
external table, much the same as an internal Oracle table. The
external definitions occur in the organization external clause:
|
default directory testdir |
The directory where the file resides |
|
records delimited by newline |
The new line character |
|
fields terminated by ‘,’ |
The column termination character |
|
Location (‘emp_ext.csv’) |
The name of the external file |
Now that we have defined the external table,
we can use it just like any internal Oracle table. We can:
* View and change the data from inside a
MS-Excel spreadsheet.
* Run reports against the external table using
SQL, just as if the table resided inside the Oracle database.
In MS-Excel, you can load the external table,
just like any spreadsheet. Go into Excel and open
c:\Burleson\queries\enp_ext.csv.
Even though this is a table to Oracle, you can
view and change the data, just like any other spreadsheet. As
soon as you change and save the spreadsheet data, the Oracle SQL will
change to show your changes.
We can also run queries against this flat
file, just like any other Oracle table.
Run the following queries on your Oracle
database:
select ename
from emp_ext where job = 'MANAGER';
ENAME
----------
BURLESON
BLAKE
CLARK
select
deptno,
count(job)
from
emp_ext
group by
deptno;
DEPTNO COUNT(JOB)
---------- ----------
10
2
20
3
30
5
The main points of this tutorial include:
* All Oracle database use online programs with
in-memory arrays to track their behavior. In Oracle these memory
frames are called x$ fixed tables.
* Oracle v$ views are built upon the x$
tables.
* Oracle has over 350 hidden parameters that
are used to fine-tune the Oracle database.
* Oracle recommends that a DBA should never
change a hidden parameter, but experienced DBAs find hidden parameters
very useful.
* Oracle 10g has external tables that allow
standard flat files to participate in the Oracle database.
* Oracle has implemented an object-relational
layer to support Oracle database objects. The Oracle object
features include nested tables, VARRAY tables (non-first-normal form
tables), abstract data types, and support for methods, polymorphism
and inheritance.
* Oracle 10g has an online table
reorganization utility that allows the DBA to reorganize a table while
it remains available for update.
Oracle test questions
1. Fragmented object queries – Assume that
your supervisor just finished reading an article about the benefits of
reorganizing tables and indexes, and has come to you seeking advice
about whether or not to reorganize your Oracle database. To
answer your boss’s question, write two data dictionary queries to see
if you have tables or indexes that will benefit from reorganization.
2. Continuous availability options – Your boss
has approached you with the requirement that your Oracle database be
protected 24x7. The downtime costs for the Oracle database is
$80,000 per minute, and your boss wants to know all of the options for
Oracle database failover. Prepare an executive summary listing
at least THREE failover options within Oracle, and discuss the merits
of each approach.
3. Oracle Managed Files – Your shop sells a
billing system product that uses the Oracle database. Your job is to
write an install routine that ensures that the tablespaces will be
created successfully, regardless of the platform and configuration of
the client. This billing database has two tablespaces TS_TABLE
at 20 megabytes and TS_INDEX at 25 megabytes. Write an install
script that uses OMF to allocate your tablespaces. The script
has accept the directory name as input, or you can write your SQL to
create the OMF directory in the same Oracle database that is used by
the SYSTEM tablespace.
4. Create an Abstract Data Type – You have
been asked to create an abstract data type called person_address.
This ADT will contain the street_address, city_address, state, and
zip_code. Write the DDL to create this ADT, and create a table
called test_table to prove that the ADT was properly created.
5. Backup & Recovery – Your shop has asked you
to write a hot backup script for your Oracle database. Write a
query against the data dictionary to alter each tablespace into BEGIN
BACKUP mode, and another script to END backup after the hot backup is
completed.
ANSWERS
Fragmented object queries – Assume that your
supervisor just finished reading an article about the benefits of
reorganizing tables and indexes, and has come to you seeking advice
about whether or not to reorganize your Oracle database. To
answer your boss’s question, write two data dictionary queries to see
if you have tables or indexes that will benefit from reorganization.
1. Fragmented objects – The correct answer
will have a query against DBA_TABLES to check chained rows and
optinally dba_segments.extents. The index query against
DBA_INDEXES (or a listing of alter index xxx validate structure) to
check clustering_factor, deleted_leaf_blocks and height.
select
owner
c1,
table_name c2,
pct_free
c3,
pct_used c4,
avg_row_len c5,
num_rows
c6,
chain_cnt c7,
chain_cnt/num_rows c8
from dba_tables
where
owner not in ('SYS','SYSTEM')
and
(chain_cnt/num_rows > .1 or chain_cnt > 1000)
and
table_name not in
(select table_name from dba_tab_columns
where
data_type in ('RAW','LONG RAW')
)
and
chain_cnt > 0
order by chain_cnt desc
;
Continuous availability options – Your boss
has approached you with the requirement that your Oracle database be
protected 24x7. The downtime costs for the database is $80,000
per minute, and your boss wants to know all of the options for
database failover. Prepare an executive summary listing at least
THREE failover options within Oracle, and discuss the merits of each
approach.
Answer – The correct answer will mention RAC
(OPS), Oracle replication, Oracle failsafe, and optionally Quest
shareplex. The answer should have points from this article:
http://www.dba-oracle.com/art_dbazine_rac_taf.htm
Oracle Managed Files – Your shop sells a
billing system product that uses the Oracle database. Your job is to
write an install routine that ensures that the tablespaces will be
created successfully, regardless of the platform and configuration of
the client. This billing database has two tablespaces TS_TABLE
and TS_INDEX. Write an install script that uses OMF to allocate
your tablespaces. The script has accept the directory name as
input, or you can write your SQL to create the OMF directory in the
same database that is used by the SYSTEM tablespace.
Answer – The script can prompt for the file
location.
select
'alter system set db_create_file_dest='''||
substr(file_name,1,length(file_name)-12)||
''';'
from
dba_data_files
where
tablespace_name = 'SYSTEM';
create
tablespace ts_table;
create tablespace ts_index;
Create an Abstract Data Type – You have been
asked to create an abstract data type called person_address.
This ADT will contain the street_address, city_address, state, and
zip_code. Write the DDL to create this ADT, and create a table
called test_table to prove that the ADT was properly created.
create or
replace type person_address
as object
(
street_address char(20),
city_address char(20),
state
char(2),
zip_code char(5)
);
/
create table
test_table
(
first_name char(20),
last_name char(20),
full_address person_address
);
Backup & Recovery – Your shop has asked you to
write a hot backup script for your Oracle database. Write a
query against the data dictionary to alter each tablespace into BEGIN
BACKUP mode, and another script to END backup after the hot backup is
completed.
select
'alter tablespace '||tablespace_name||' begin backup;'
from
dba_tablespaces
where
tablespace_name <> 'SYSTEM';
select
'alter tablespace '||tablespace_name||' end backup;'
from
dba_tablespaces
where
tablespace_name <> 'SYSTEM';
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
