Scribd
Importer
4 vues7 pages

CH 5

Ch 5 oracle

Transféré par

shyamfaldu000
Copyright
© All Rights Reserved
Nous prenons très au sérieux les droits relatifs au contenu. Si vous pensez qu’il s’agit de votre contenu, signalez une atteinte au droit d’auteur ici.
Formats disponibles
Téléchargez aux formats PDF ou lisez en ligne sur Scribd
4 vues7 pages

CH 5

Ch 5 oracle

Transféré par

shyamfaldu000
Copyright
© All Rights Reserved
Nous prenons très au sérieux les droits relatifs au contenu. Si vous pensez qu’il s’agit de votre contenu, signalez une atteinte au droit d’auteur ici.
Formats disponibles
Téléchargez aux formats PDF ou lisez en ligne sur Scribd
Sub: RDBMS using Oracle Unit~ $ Oracle Databare Structure and Storage Database, Resource Management B. C.A. Sem3 { 5 I ‘Oracle Database Structure and Storage Database, Resource Management J ‘Topics Covered Instance Architecture (Database Processes, Memory Structure) Creating & Altering Database ‘Opening & shutdown Database Control Files, Redo Logs files ‘Tablespace (Create, Alter, Drop), types of tablesplace (system, read only, temporary, tool, user, data and index, rollback) * Rollback Segment (Create, Alter) (System & Transaction RBS) Oracle Blocks Import + Export © SQL*Loader * Oracle Scheduler Concepts ‘© Managing Resources with Oracle Database Resource Manager Instance Architecture (Database Processes, Memory Structure) — |__| NJ pedcacdner | Shared ever \ \ —< | —_— Za FMON Databace buffer |[ Redo log Shared pool cache butter Sow RECO, LCKs ARCH Page 1 of 14 Sub: RDBMS using Oracle Unit~ $ Oracle Databare Structure and Storage Database, Resource Management B. C.A. Sem3 Database Processes ‘The oracle uses two types of process. 1. Userprovess 2. Background process 1. User Process: + User or client, processes are the user's connections to the RDBMS system, + Iestarts atthe time when a oracle server starts + The user process manipulator the user's input and communicates with the oracle server. 2. Background process: + Itstats when an oracle instance is started, + They are used to perform various tasks within the RDBMS system. + They perform system maintenance. 1) DWBR(Database Writer): ‘* Itisresponsiblefor writing dirty data blocks from database block. When a transaction changes data in a data block need not be immediately written to disk. 2) “LGWR(Log Writer): + The LGWR process is responsible for writing data from the log buffer to the redo log files. 3) CKPT(Check Point): ‘© The CKPT process is responsible for signaling the DBWR process to perform a checkpoint ‘and to update all the data files and control files. 4) PMON(Process Monitor): ‘* Itisresponsible for keeping track of database processes and cleaning up. It contains data and control information beld by a single server process. It is created when a user process is created and is released when the user process is terminated. 5) SMON(System Monitor): * SMON performs instance recovery at instance startup, ‘This includes cleaning temporary segments and recovering transactions. 6) RECOReovery): Ibis used to clean transaction that were pending in a distributed database. * _ Itisresponsible for committing or rolling back. 7) ARCH(Archiver): + Iis responsible for copying the online redo log files. Memory Structure ‘The oracle basic memory structure consists of two memory ares, System Global Area (SGA) 2. Program Global Area (PGA) > System Global Area: + Oracle use SGA to store data and control information for oracle instance. + The SGA consists of the following elements. 1) The database butfer cache: Page 2 of 14 Sub: RDBMS using Oracle Unit~ $ Oracle Databare Structure and Storage Database, Resource Management B. C.A. Sem3 * Itis an area in memory that holds all the blocks read from disk for query or modification, ‘© This buffer is managed in a manner that free blocks are always made available for new blocks. The contents of the database butter cache are written to the data file on disk by the database writer background process, 2). The redo log buffer: ‘* Itisa circular buffer that stores all changes made in the database. It contains periodically modification. It done by the log writer background process 3) The shared Pool: Itis the area of SGA that stores shared memory structures such as shared SQL. areas. # Itis used to store most recently executed SQL. statements. > Program Global Area: + The PGA is a memory area that contains data and control information for the oracle server processes, + PGAis allocated when a process is created and de-allocated when the process is terminated. + -consists of the following components 1) Stack space: ‘© The memory that holds the session’s variables, arrays and so om. 2) Ss If you are not running the multithreaded Seryer the session information is stored in PGA. 3) Private SQL Area: Thisis an area where binding variable and runtime buffers are store. jon information Creating & Altering Database Creating Database: * The CREATE DATABASE statement is used to create a new SQL database. Syntax Create database [datafile] [max logfiles n} [max datafile n} [archivelog/noarchive log] [maxlogmemebers 1] Altering Database: ‘Syntax ‘Alter database Opening & shutdown Database © ADBA can perform a startup & shutdown database. Qnenine Database When a database started three important steps are executed. 1) Instance Creation: This is the first step of starting database. Page 3 of 14 Sub: RDBMS using Oracle Unit~ $ Oracle Databare Structure and Storage Database, Resource Management B. C.A. Sem3 Data dictionary “The Oracle data dictionary is one of the most important components of the Oracle DBMS. I: contains all information about the structures and objects of the database such as + tables, *columns, susers, + data files ete. ‘The data stored in the data dictionary are also often called metadata, Althoughit is usvally the domain of database administrators (DBAs), the data dictionary is a valuable source of information for end users and developers. ‘The data dictionary consists of two levels: 1. The internal level contains all base tables that are used by the various DBMS software components and they are normally not accessible by end users. 2. The external level provides numerous views on these base tables to access information about objects and structures at different levels of detail Data Dictionary Tables ‘An install jon of an Oracle database always includes the creation of three standard Oracle users: SYS This is the owner of all data dictionary bles and views. This user has the highest privileges to manage objects and structures of an Oracle database such as creating ew users, ‘SYSTEM ‘This is the owner of tables used by different tools such SQL*Forms, ‘SQL *Reports ete. This user has less pr jeges than SYS. PUBLIC Thi ‘a “dummy” user in an Oracle database. All privileges assigned fo this user are automatically assigned to all users known in the database. Data Dictionary Contains. The tables and vio provided by the data dictionary contain information about ‘users and their privileges, ‘ tables, table columns and their data types, integrity constraints, indexes, ‘ statistics about tables and indexes used by the optimizer, « privileges granted on database obj « storage structures of the database. Viewing the data dictionary The SQL command select * from DICTIIOARY]: select * from DICT; Page 5 of 14 Sub: RDBMS using Oracle Unit~ $ Oracle Databare Structure and Storage Database, Resource Management B. C.A. Sem3 6) Rollback : + Contains the private rolback segments; its size will depend on number of rollback segments and expected transaction size Rollback Segment (Create, Alter) (System & Transaction RBS) © Arollback segmentis an Oracle database structure that stores undo information for transactions. Undo informationis the original information that was changed curing a transaction. It restores the changed database information back to what it was before a transaction changed it. © When a transaction changes data, the Oracle server assigns the transaction to the next available segment and stores the undo information there. What are the uses of Rollback Segment? = Use the CREATE ROLLBACK SEGMENT statement to create a rollback segment, which is an object that Oracle Database uses to store data necessary to reverse, or undo, changes made by transactions ‘If the database has a locally managed SYSTEM tablespace, then rollback segments cannot creste in any dictionary-managed tablespace ‘Instead, either use the automatic undo management feature or create locally managed tablespaces to hold the rollback segments, Create Rollback Segment ‘Syntax: CREATE [PUBLIC] ROLLBACK SEGMENT rbs-name [TABLESPACE tbs-name} (INITIAL 20K NEXT 40K MINEXTENTS 2 MAXEXTENTS 50); "ALTER ROLLBACK SEGMENT rbs-name STORAGE storage-option Oracle Bl ‘© Oracle data blocks are the smallest units of storage that Oracle ean use or allocate. ‘* Itcontain header information concerning the block itself. as well as the data, ‘© They are physically store on disk. © Oracle stores and retrieves data on disk using data blocks, + Attime of creation of database the block size can be specified, Import ‘* Itallows you to restore the database information that held in previously created export files. ‘* Import loads data in the following order. ‘Table defi ‘Table data ‘Table indexes “Triggervindex/eonstaint ‘First of all new tables are created. then datais imported and indexes are built. © Then triggers are enabled. © There are 3 important modes:- 1. Usee: - import all objects owned by wer. 2. Table:- import all tables owned by user. 3. Full database: - import all objects of the database. Page9 of 14 Sub: RDBMS using Oracle Unit~ $ Oracle Databare Structure and Storage Database, Resource Management B. C.A. Sem3 Sunt IMP file= from user= tables= Export Ttis a logical backup of database. Itcopies the data and database 10 a binary OS file in a special format. ‘We can take backup database while itis open and available for use. Data should not be changed while export take place ‘There are 3 export modes. 1. User: ~ export all objects owned by user. 2. Table:- export all tables owned by user. 3. Full database: - export all objects of the database. SOL*Loader SQL*Loader loals data from external files into tables of an Oracle database. It has a powerful data parsing engine that puts little limitation on the format of the data in the datafile. You can use SQL*Loader to do the following: © Load data across a network. This means that you ean sun the SQL*Loader el system from the one that is running the SQL*Loader server. Load data from multiple datafiles during the same load session. Load data into multiple tables during the satne load session. Specity the character set of the data Selectively load data (you ean load records based on the records values). Manipulate the data before loading it, using SQL functions, Generate unique sequential key values in specified columns. Use the operating system's file system to access the datafiles, Following ae SQL. *Loaier file control file bog file bad file Gscard file 5. data file ‘A typical SQL*Loader session takes as input a control file, which controls the behavior of SQL*Loader, and one or more datafiles. The output of SQL*Loader is an Oracle database (where the data is loaded), a log file, a bod file, and potentially, a discard file, An example of the flow of a SQL*Loader session is shown in figure. Page 10 of 14 Sub: RDBMS using Oracle Unit~ $ Oracle Databare Structure and Storage Database, Resource Management B. C.A. Sem3 Cz] sat-Lowder = Oracle Scheduler Concepts Oracle Database includes Oracle Scheduler, an enterprise job scheduler to help you simplify the scheduling of hundreds or even thousands of tasks. Oracle Scheduler (the Scheduler) is implemented by the procedures and functions in the DBMS_SCHEDULER PL/SQL package. ‘The Scheduler enables you to control when and where various computifig. tasks tke place in the enterprise environment. The Scheduler helps you effectively manage and plan these tasks. By ensuring that many routine computing tasks occur without manual intervention, you can lower operating costs, implement more reliable routines, minimize human error, and shorten the time windows needed. ‘The Scheduler provides sophisticated, flexible enterprise scheduling functionality, which you can use to: + Run database program units You can run program units, that is, PL/SQL anonymous blocks, PL/SQL stored procedures, and Java stored procedures on the local database or on one or more remote Oracle databases. + Run external executables, (executables that are external to the database) You can run external executables, such as applications, shell scripts, and batch files, on fhe local system or on one or more remote systems. Remote systems do not require an Oracle Database installation; they require only a Scheduler agent. Scheduler agents are available for all platforms supported by Oracle Database and some additional platforms. + Schedule job execution using the following methods: © Time-based scheduling You can schedule a job to run at a particular date andl time, either once or on a repeating basis. You can define complex repeat intervals, such as "every Monday and Thursday at 3:00 Page 11 of 14

Vous aimerez peut-être aussi