1 / 13

Data Base Administrator Responsibility

Installing & Upgrading R.D.B.M.S. + Application Tools. Creating Primary DB Structure & Primary Objects. Planning Storage for User Objects. Modifying DB Structure. Enrolling Users. Monitoring & Controlling User Access. Backing Up & Recovering . Maintaining & Security .

bonner
Télécharger la présentation

Data Base Administrator Responsibility

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. Installing & Upgrading R.D.B.M.S. + Application Tools Creating Primary DB Structure & Primary Objects Planning Storage for User Objects Modifying DB Structure Enrolling Users Monitoring & Controlling User Access Backing Up & Recovering Maintaining & Security Tuning (Optimizing Performances) Consulting (Development) Data Base Administrator Responsibility

  2. Tablespace DataFile Oracle Block Segment Extent O.S. Block Owner Schema Partition Oracle Architectural Components (Logical) DataBase Table Index Cluster Snapshot

  3. Row header Rows Area Block header (F/V) Column length Database block Column value Structure of a Block / Row

  4. CREATE TABLE TEST1 ( campo1 number (9) not null primary key, campo2 varchar2(32) not null , campo3 date , campo4 number (7,2) ) TABLESPACE USER_DATA STORAGE ( initial xxx M next yyy M minextents 1 maxextens 121 pctincrease 0 ) PCTFREE XX%

  5. 80 Bytes c.a. 4 + 2R Bytes c.a. 3 + num. col. Bytes c.a. Num. Rows x Block Struttura Oggetto PCTFREE DB_BLOCK_SIZE DB_BLOCK_SIZE - Fixed Block Header - (DB_BLOCK_SIZE * PCTFREE/100) - Variable B.Header R= -------------------------------------------------------------------------------------------------------------- Row Header + Sum (field - length) Date 7 Bytes Char (n) n Bytes Varchar2 (n) n* %occ Bytes Number (n) int( n/2 + 0,5) + 1 Bytes Number (n,m) int( (n-m)/2 + 0,5) + int( m/2 + 0,5) + 1 Bytes

  6. Syntax Validation Parse Bind Terminology Validation Grant / User Validation Execution Plan Implicit Cursor OK Q Q Describe KO Execute OK Fetch KO Statement SQL

  7. System Undo Tools Data Dictionary Tables Undo Segments for Transactional Consistency Meta Data Integration Tools Temp U_Data U_Idx Dynamic Objects for Sort / Group By / Union …….. User Tables User Indexes O.F.A Oracle Flexible Architecture

  8. System tablespace Other database files Database Data Dictionary The data dictionary provides information about: • Logical and physical database structure • Definitions and space allocations of objects • Integrity constraints • Users • Roles • Privileges • Auditing • Other information • Central to every Oracle database • Describes the database and its objects • Contains read-only tables and views • Updated by SQL commands: • DDL • Some DML • Owned by the user SYS • Stored in the SYSTEM tablespace • Accessed with SELECT statements

  9. DBA_xxx All of the objects in the database ALL_xxx Objects accessible by the current user USER_xxx Objects owned by the current user Data Dictionary Views

  10. User-defined Built-in Scalar Collection Relationship CHAR(N), NCHAR(N)VARCHAR2(N),NVARCHAR2(N) NUMBER(P,S) DATERAW(N)BLOB, CLOB, NCLOB, BFILE LONG, LONG RAW ROWID, UROWID VARRAYTABLE REF Oracle Data Types Data type

  11. Creazione Nuovo DB Organizzare il contenuto del DB attraverso Tablespace Disegnare la struttura fisica per ridurre Contention & Fragmentation Preparare l’Ambiente di Sistema Operativo – Variabili di Ambiente e Kernel Editare e personalizzare il Parameter File InitSID.ora StartUp Istanza in modalità NoMount Avvio dello Statement SQL di Create DataBase Creazione delle Strutture di MetaDati (script minimi catalog.sql & catproc.sql) Creazione delle TableSpace Addizionali (TEMP / RBS / TOOLS / USER_DATA) Creazione dei RollBack Segment Addizionali Creazione degli Utenti Applicativi

  12. CREATE DATABASE newtest CONTROLFILE REUSE LOGFILE GROUP 1 (’diskb:log1.log’, ’diskc:log1.log’) SIZE 50K, GROUP 2 (’diskb:log2.log’, ’diskc:log2.log’) SIZE 50K MAXLOGFILES 5 MAXLOGMEMBER 3 MAXDATAFILES 25 MAXINSTANCES 2 ARCHIVELOG CHARACTER SET US7ASCII NATIONAL CHARACTER SET JA16SJISFIXED DATAFILE ’disk1:df1.dbf’ SIZE 100M, ’disk2:df2.dbf’ SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

More Related