Some advance features in Oracle 9i
MATERIALIZED VIEW
A materialized view is a database object that contains the results of a query. The FROM clause
of the query can name tables, views, and other materialized views. Collectively these objects
are called master tables (a replication term) or detail tables (a data warehousing term).
This reference uses "master tables" for consistency. The databases containing the master
tables are called the master databases
CREATE MATERIALIZED VIEW sales_mv
REFRESH FAST ON COMMIT
AS SELECT t.calendar_year, p.prod_id,
SUM(s.amount_sold) AS sum_sales
FROM times t, products p, sales s
WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id
GROUP BY t.calendar_year, p.prod_id;
FLASHBACK TABLE
Purpose
Use the FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system. Also, Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table.
Oracle Utilities
Export
• A backup utility provided by Oracle
• Writes data from an Oracle database into Oracle binary-format files
• Exported data can only be read by the “Import” utility
• A User must have connect or DBA privilege to export table
• Exprt Modes:
o Table: The names of the tables to be exported need to be specified
o User: All objects belonging to the user are exported
o Database: All database objects are exported
• The command used is : exp
Table(s) Level Export
C:\> exp username/passward file=filename.dmp log=filename.log Tables=tablename,tablename
User Level Export
C:\> exp system/manager file=filename.dmp log=filename.log owner=username
Entire Database Level
C:\> exp system/manager file=filename.dmp log=filename.log full=y
Import
• Is used for importing data exported using the exp command into the Oracle database
Importing tables
C:\> Imp username/passward file=filename.dmp log=filename.log Tables=(tablename,tablename)
Importing User
C:\> Imp system/manager file=filename.dmp log=filename.log fromuser = username touser=username
Sql Loader
• This tool is used to move data from a Non-Oracle standard source into the Oracle Database
• Load data into multiple datafiles
by Sql Loader - sqlldr
step -1
- create a table
ex
create table person
(code number(2),
name varchar2(10));
setp - 2
- create a data file
ex
test.txt
10 MILLER
20 FORD
step - 3
- create a control file
load data
ex - ctrl.txt
infile
into table person
(code position (01:03) char,
name postion ( 04:10) char)
step - 4
c:\> sqlldr userid=sss/sss control=c:\ctrl.txt
To create multiple table in a SINGLE SQL Query
Create schema authorization sss
Create table a (a number)
Create table b (a number);
Where SSS is the User Name
MATERIALIZED VIEW
A materialized view is a database object that contains the results of a query. The FROM clause
of the query can name tables, views, and other materialized views. Collectively these objects
are called master tables (a replication term) or detail tables (a data warehousing term).
This reference uses "master tables" for consistency. The databases containing the master
tables are called the master databases
CREATE MATERIALIZED VIEW sales_mv
REFRESH FAST ON COMMIT
AS SELECT t.calendar_year, p.prod_id,
SUM(s.amount_sold) AS sum_sales
FROM times t, products p, sales s
WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id
GROUP BY t.calendar_year, p.prod_id;
FLASHBACK TABLE
Purpose
Use the FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system. Also, Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table.
Oracle Utilities
Export
• A backup utility provided by Oracle
• Writes data from an Oracle database into Oracle binary-format files
• Exported data can only be read by the “Import” utility
• A User must have connect or DBA privilege to export table
• Exprt Modes:
o Table: The names of the tables to be exported need to be specified
o User: All objects belonging to the user are exported
o Database: All database objects are exported
• The command used is : exp
Table(s) Level Export
C:\> exp username/passward file=filename.dmp log=filename.log Tables=tablename,tablename
User Level Export
C:\> exp system/manager file=filename.dmp log=filename.log owner=username
Entire Database Level
C:\> exp system/manager file=filename.dmp log=filename.log full=y
Import
• Is used for importing data exported using the exp command into the Oracle database
Importing tables
C:\> Imp username/passward file=filename.dmp log=filename.log Tables=(tablename,tablename)
Importing User
C:\> Imp system/manager file=filename.dmp log=filename.log fromuser = username touser=username
Sql Loader
• This tool is used to move data from a Non-Oracle standard source into the Oracle Database
• Load data into multiple datafiles
by Sql Loader - sqlldr
step -1
- create a table
ex
create table person
(code number(2),
name varchar2(10));
setp - 2
- create a data file
ex
test.txt
10 MILLER
20 FORD
step - 3
- create a control file
load data
ex - ctrl.txt
infile
into table person
(code position (01:03) char,
name postion ( 04:10) char)
step - 4
c:\> sqlldr userid=sss/sss control=c:\ctrl.txt
To create multiple table in a SINGLE SQL Query
Create schema authorization sss
Create table a (a number)
Create table b (a number);
Where SSS is the User Name
