1 / 11

SQL Workshop

SQL Workshop. Day 4. Day 4 – SQL Agenda. SQL Plus How to connect Running a script Spooling ouptut Practice Oracle Flashback technology Oracle RIB - architecture Practice. SQL*Plus Overview.

nevaeh
Download Presentation

SQL Workshop

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Workshop Day 4

  2. Day 4– SQLAgenda SQL Plus How to connect Running a script Spooling ouptut Practice Oracle Flashback technology Oracle RIB - architecture Practice

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

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

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

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

  7. 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: Prepare SQL statments which will list the objects (tables and indexes) Spool the output as a script to a temporary file Execut the spooled script

  8. 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 employeesAS OF TIMESTAMP (sysdate – 1/24); Size of the flashback chache is limited. It can be adjusted by undo_retention database parameter.

  9. Oracle RIB architecture

  10. Laboratory database architecture APP_OWNER MESSAGE_QUEUE CONSUMER PUBLISHER

  11. Practice 2 • ?Question • What is throughoutput of the queue? • How many messages are being published per minute ? • How many messages being consumed per minute? APP_OWNER MESSAGE_QUEUE

More Related