Transcript Oracle SQL and PL/SQL

SQL Workshop
Day 4
Copyright © 2011 Accenture All Rights Reserved. Accenture, its logo, and High Performance Delivered are trademarks of Accenture.
Day 4 – SQL
Agenda
• SQL Plus
• How to connect
• Running a script
• Spooling ouptut
– Practice
• Oracle Flashback technology
• Oracle RIB - architecture
– Practice
© Accenture 2011. All Rights Reserved.
2
SQL*Plus Overview
SQL*Plus is an interactive and batch query tool that is installed with every
Oracle Database installation. It has a command-line user interface, a
Windows Graphical User Interface (GUI) and the iSQL*Plus web-based
user interface.
SQL*Plus has its own commands and environment, and it provides access
to the Oracle Database. It enables you to enter and execute SQL, PL/SQL,
SQL*Plus and operating system commands to perform the following:
•Format, perform calculations on, store, and print from query results
•Examine table and object definitions
•Develop and run batch scripts
•Perform database administration
You can use SQL*Plus to generate reports interactively, to generate reports
as batch processes, and to output the results to text file, to screen, or to
HTML file for browsing on the Internet. You can generate reports
dynamically using the HTML output facility of SQL*Plus, or using the
dynamic reporting capability of iSQL*Plus to run a script from a web page.
© Accenture 2011. All Rights Reserved.
Execute script in SQL Plus
The START command retrieves a script and runs the command(s) it
contains. Use START to run a script containing SQL commands,
PL/SQL blocks, and SQL*Plus commands. You can have many
commands in the file. Follow the START command with the name of
the file:
START [file_name]
© Accenture 2011. All Rights Reserved.
SQL Plus SPOOL command
In order to redirect sql commands result to output file use:
SPOOL [filename]
The file will be populated tilll
SPOOL OFF
command is issued.
© Accenture 2011. All Rights Reserved.
Practice
• Create table with four fields:
• Employee id
• Name – first_name and last_name
• Department name
• Salary
• Using one SQL query populate this table with 10
employees with highest salary
Spool employee table to file
Write a query that will show number of employees
hired during each year
© Accenture 2011. All Rights Reserved.
Practice 1
Some tables may allocate large space despite it
contains a few rows. In order to solve the issue it is
required to rebuild its structure.
Prepare script which will:
- Coalesce tablespace for all tables in given schema
- Use ALTER TABLE [table] MOVE; command
- Rebuild all intexes in the schema
- Use ALTER INDEX [index] REBUILD; command
In order to realize that task you should:
1. Prepare SQL statments which will list the objects
(tables and indexes)
2. Spool the output as a script to a temporary file
3. Execut the spooled script
© Accenture 2011. All Rights Reserved.
Oracle Flashback
Flashback query allows a user to view the data quickly and easily
the way it was at a particular time in the past, even when it is
modified and committed, be it a single row or the whole table.
SELECT job_id, first_name
FROM employees AS OF TIMESTAMP (sysdate – 1/24);
Size of the flashback chache is limited. It can be adjusted by
undo_retention database parameter.
© Accenture 2011. All Rights Reserved.
Oracle RIB architecture
© Accenture 2011. All Rights Reserved.
Laboratory database architecture
APP_OWNER
MESSAGE_QUEUE
CONSUMER
© Accenture 2011. All Rights Reserved.
PUBLISHER
Practice 2
?Question
•What is throughoutput of the queue?
APP_OWNER
MESSAGE_QUEUE
© Accenture 2011. All Rights Reserved.
•How many messages are being published
per minute ?
•How many messages being consumed per
minute?