Study your flashcards anywhere!

Download the official Cram app for free >

  • Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

A key: Read text to speech.a key

image

Play button

image

Play button

image

Progress

1/28

Click to flip

28 Cards in this Set

  • Front
  • Back
What initialization parameter defined at the creation time has the standard size of of DB Block for the SYSTEM and SYSAUX table spaces?
db_block_size
Larger block sizes results in what?
Shallower BTree indexes and thus better performance
What is the typical data block size range?
8KB TO 32KB
Define Table Spaces?
Tablespaces physically group schema objects for admin convenience.
They bridge physical structures such as datafiles or extents and logical structures such as tables and indexes.
They can store 0 or more segments.
Define Segments?
Segments are schema objects which require storage outside the data dictionary.
Tables and segments are examples of segments.
Constraints and sequeneces are the examples of schema objects that don't store data outside the data dictionary and are therefore not segments.
What tablespaces are always created at the time of database creation?
SYSTEM
SYSAUX(FOR an upgrade to 10g)
What Commands do you use for creating a table space?
CREATE DATABASE
CREATE TABLESPACE
choices are:
bigfile|smallfile
EXTENT MANAGEMENT = LOCAL|DATADICTIONARY
SEGMENT SPACE MANAGEMENT =AUTOMATIC|MANUAL
What is the benefit of using OMF(Oracle Managed File Tablespaces)
Admin does not explicitly specify the name of datafiles or temp files.
Admin specify operations in terms of tablespaces.
Oracle creates the unique names of datafiles such as:
01_hr_data_0dc3z9wl_.dbf
The file is autoextend enable and the default size will be 100MB unless a different size is specified.

SQL>Create BIGFILE TABLESPACE hr_data;
What are the types of extent management?
1- Local extent management
2- Dictionary extent management.
Describe Dictionary extent management?
With Dictionary extent management database tracks used and free extents in the data dictionary by changing FET$ AND UET$ tables by using recursive SQL.
Describe local extent management?
With Local Extent management database tracks extents through bitmaps.
This is the default and preferred technique.
What are the two options of local extent management?
1- UNIFORM
2- AUTOALLOCATE
Describe UNIFORM option of local extent management?
The uniform options tells the database to allocate and deallocate extents in the tablespace with the same unvarying size that you specify or (the default size is 1MB).
This option is default for temporary temp tablespaces and it can't be used in UNDO tablespace.
SQL>CREATE TABLESPACE hist DATAFILE 'C:\ORADATA\HIST.DBF' SIZE 25G EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M;
Describe AUTOALLOCATE option of local extent management?
With autoallocate option the database varies size for each extent of the segments.
On Windows and Linux with 8kb BLOCKS,each segments starts with 64 KB extent for the first 16 extents and then the extent size increases to 1MB for the next 63 extents.
SQL>CREATE TABLESPACE hist DATAFILE 'C:\ORADATA\HIST.DBF' SIZE 25G EXTENT MANAGEMENT LOCAL AUTOALLOCATE ;
What is the parameter for specifying OMF?
db_create_file_dest
ALTER SYSTEM SET db_create_file_dest = 'd:\oradata\OMF' SCOPE=BOTH;
What types of segment space management can be used in local extent management?
1-Manual(PCT_FREE and PCT_USED paramenters are used to find out whether a datablock is available for use)
2-Automatic(Bitmaps are used to identify free blocks)

SQL> CREATE TABLESPACE HIST DATAFILE 'C:\ORADATA\HIST.DBF' SIZE 25G EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENNT SPACE MANAGEMENT AUTO;
Why does db need temp tablespace?
DB often uses temp tablespace for:
1- ORDER BY
2- GROUP BY
3- CREATE INDEX
4- HASH JOINS
5- INSERT INTO TEMP TABLES.

SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'C:\ORADATA\TEMP01.DBF' SIZE 2G;

Tempfiles are always guaranteed to allocate the disk space.
Why does db need undo tablespace?
Undo table space contains undo segments which are used for:
1- Rolling back a transaction explicitly with a ROLLBACK statement.
2- Rolling back a transaction implicitly(recovery of the failed transaction)
3-Reconstructing a read consistent image of data.
4- Recovering from logical corruption.
What command is used to recursively dropping tablespace and all the contents?
SQL>DROP TABLESPACE dba_sandbox INCLUDING CONTENTS AND DATAFILES;
What command is used to modify a tablespace?
SQL>Alter tablespace fin rename to payables;

SQL>Alter tablespace fin READ ONLY;

SQL>Alter tablespace fin READ WRITE;

SQL>Alter tablespace fin BEGIN BACKUP;

SQL>Alter tablespace fin END BACKUP;
Why do we need to take tablespaces offline?
For maintenance
1- Recovering a tablespace
2- Moving the datafiles to a new location.
SQL>ALTER TABLESPACE FIN OFFLINE/ONLINE;
What DD VIEWS CONTAIN TABLESPACE INFORMATION?
1-DBA_TABLESPACES
2-DBA_DATA_FILES
3-DBA_TEMP_FILES
4-V$TABLESPACE
What operations can be performed on Data Files?
1-Resizing
2-Taking them offline or online
3-Moving (Renaming)
4-Recovering

SQL>ALTER DATABASE DATAFILE 'C:\ORADATA\DATA01.DBF' RESIZE 100M;

SQL>ALTER DATABASE DATAFILE 'C:\ORADATA\DATA01.DBF' AUTOEXTEND ON 100M MAXSIZE 8000M;

SQL>ALTER DATABASE DATAFILE 'C:\ORADATA\DATA01.DBF' OFFLINE/ONLINE;

SQL>RECOVER DATAFILE 'C:\ORADATA\DATA01.DBF';

SQL>ALTER DATABASE DATAFILE RENAME 'C:\ORADATA\DATA01.DBF' 'C:\ORADATA\DATA01A.DBF'
Define Schema?
A schema is a collection of objects owned by a specifi database user.

Schema objects could be segmented(table, index,) and nonsegment(views, synonyms, procedures) objects.
What are the major categories of datatype in 10g?
1-Character
2-Numeric
3-Datetime
4-LOB(Large Object)
5-ROWID
6-Binary
What are the types of Character data?
1-Fixed Width(the column is padded with spaces): - CHAR(BYTE|CHAR), NCHAR(Size, it uses Unicode character set)
2-Variable Width:- VARCHAR (byte|char), VARCHAR2(size|byte), NVARCHAR2(size, used for unicode)
3-Legacy datatype:- LONG it stores variable length alpha numeric data up to 2GB.
What are the types of Numeric data?
Numeric datatypes can store
1-Positive and Negative fixed.
2-Floating point
3-zero
4-Infinity
5-Special values (NOT A NUMBER)

NUMBER[(Precision[,Scale])]
BINARY_FLOAT
BINARY_DOUBLE
What types of data a DATETIME type can store?
1-DATE
2-TIMESTAMP[(precision)]
3-TIMESTAMP[(precision)] with TIMEZONE
4-TIMESTAMP[(precision)] with LOCAL TIMEZONE
5-INTERVAL YEAR [(precision)] TO MONTH
6- INTERVAL DAY[(d_precision)] TO SECOND [(s_precision)]