iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

 

DBA Fundamental QUESTIONS

We are against multiple choice questions. 

Online Oracle Training

Oracle DBA #1 Fundamental Exam Questions

Intro to Oracle Database Architecture

Lesson 01

Q:   What are the Oracle Architectural components?

Q:   What are the Oracle Memory Components?

Q:   What is the Server Parameter File?

Q:   What is the Parameter File?

Q:   How do you use the init.ora file?

Q:   What is the System Global Area (SGA)?

Q:   What is the Shared Pool in SGA?

Q:   What is the Buffer Cache in SGA?

Q:   What does the Buffer Cache hold in SGA?

Q:   What are the differences between the Library Cache and Dictionary Cache?

Q:   What is the Redo Log Buffer in SGA?

Q:   Describe the Large Pool component in SGA?

Q:   Describe the Multi-threaded Server process?

Q:   What are PGA and UGA?

Q:   Describe the log writer background process (LGWR)?

Q:   How often LGWR writes userís entries to the Online Redo Log Buffer files?

Q:   Describe the Checkpoint process?

Q:   How do you automatically force the Oracle to perform a checkpoint?

Q:   What is the Recovery Process?

Q:   What is the Lock Background Process?

Q:   How does the Archive Process work?

Q:   How do you configure your database to do an automatic archiving?

Q:   What is the System Monitor Process?

Q:   Describe the Program Monitor Process Job?

Q:   What are the differences between the SPFILE and PFILE startup?

Q:   What is the controlfile?

Q:   How do you backup your database controlfiles?

Q:   What does a controlfile contain?

Q:   Describe the password file?

Q:  How do you create a password file?

Q:   Describe the Online Redo Log file.

 

The most important Dictionary Views

Lesson 02

Q:   Describe a data dictionary in the Oracle database.

Q:   Describe the CATALOG.SQL script.

Q:   What are the uses of ALL_, DBA_,  and USER_ dictionary views?

Q:   Describe the DBA_VIEWS dictionary view.

Q:   Describe the DBA_TABLES dictionary view.

Q:   Describe the V$PWFILE_USERS view.

Q:   Describe the V$PARAMETER view.

Q:   Describe the V$SYSTEM_PARAMETER view.

Q:   Describe the V$SGA view.

Q:   Describe the V$OPTION view.

Q:   Describe the V$PROCESS view.

Q:   Describe the V$SESSION view.

Q:   Describe the V$VERSION view.

Q:   Describe the V$INSTANCE view.

Q:   Describe the V$THREAD view.

Q:   Describe the V$CONTROLFILE view.

Q:   Describe the DBA_VIEWS dictionary view.

Q:   Describe the V$DATAFILE view.

Q:   Describe the V$DATABASE view.

Q:   Describe the V$LOGFILE view.

Q:   Describe the V$LOG view.

Q:   What do the following scripts create?

CATAUDIT.SQL

CATLDR.SQL

CATEXP.SQL

CATPART.SQL

CATADT.SQL

STANDARD.SQL

 

Q:   What does the SHOW PARAMETER command?

 

Startup and Shutdown the database

Lesson 03

Q:   Describe the NOMOUNT option in the STATUP commandí

Q:   How do you start up an instance with the NOMOUNT option?

Q:   Describe the MOUNT option in the STARTUP commandí

Q:   How do you start up an instance with the MOUNT option?

Q:   Describe the FORCE option in the STARTUP command.

Q:   How do you start up an instance using the FORCE option?

Q:   Describe the OPEN option in the STARTUP command.

Q:   How do you start up an instance using the OPEN option?

Q:   Describe the TRANSACTIONAL option in the SHUTDOWN command.

Q:   How do you shutdown an instance using the TRANSACTIONAL option?

Q:   Describe the IMMEDIATE option in the SHUTDOWN command.

Q:   How do you shutdown an instance using the IMMEDIATE option?

Q:   Describe the NORMAL option in the SHUTDOWN command.

Q:   How do you shutdown an instance using the NORMAL option?

Q:   Describe the ABORT option in the SHUTDOWN command.

Q:   How do you shutdown an instance using the ABORT option?

Q:   Describe the READ ONLY mode option.

Q:   How do you start up an instance with the READ ONLY mode option?

Q:   Describe the BACKGROUND_DUMP_DEST parameter.

Q:   Describe the PFILE parameter in the STARTUP command.

 

Multiplexing controlfiles

Lesson 04

Q:   Describe an Oracle database controlfile.

Q:   How do you multiplex a controlfile?

Q:   Describe the V$CONTROLFILE view.

Q:   Describe the V$CONTROLFILE_RECORD_SECTION view.

Q:   What does the ĎALTER DATABASE BACKUP CONTROLFILE TO TRACEí command?

Q:   What does the following SQL statement?

SQL> ALTER DATABASE BACKUP CONTROLFILE  
           TO 'c:\backupcontrolfile\control_ddmmyyyy.ctl'
           /

Q:   How do you view the divided sections of a controlfile?

 

Reading the ALERT file

Lesson 05

Q:   What is the ALERT file in an Oracle database?

Q:   What type of information does the ALERT file contain?

Q:   What does the Top Part of ALERT file contain?

Q:   What type of system parameters does the ALTERT file show?

Q:   Describe the background processes in the ALERT file.

Q:   Describe the archived information in the ALERT file.

Q:   Describe the checkpoint process activities messages in the ALERT file.

Q:   Describe the System Error Messages in the ALERT file.

 

Changing the database mode

Lesson 06

Q:   How many different types of database mode can you change your database to?

Q:   Describe the Oracle database SUSPEND mode.

Q:   Describe the Oracle database RESUME mode.

Q:   Describe the Oracle database RESTRICTED SESSION mode.

Q:   Describe the Oracle database QUIESCE RESTRICTED mode.

 Q:  Descript the RESOURCE_MANAGER_PLAN parameter.

Q:   How do you change a database mode to the SUSPEND mode?

Q:   How do you turn on the Resource Manager Plan?

 

Server Parameter File-SPFILE

Lesson 07

Q:   Describe the Server Parameter File.

Q:   Describe the MEMORY option in the ALTER SYSTEM statement.

Q:   Describe the SPFILE option in the ALTER SYSTEM statement.

Q:   Describe the BOTH option in the ALTER SYSTEM statement.

Q:   How can you create the Server Parameter File?

Q:   What are the differences between SPFILE and PFILE?

Q:   How do you change a database dynamically?

Q:   What does the following statement do?

SQL> CREATE SPFILE 

           FROM

            PFILE='%ORACLE_HOME%\admin\school\pfile\init.ora'
            /

 

Configure the database to the archive mode

Lesson 08

Q:   How do you configure a database to an archive mode?

Q:   What are the benefits of changing a database mode to the archive mode?

Q:   How do you maintain an Oracle Online redo log file?

Q:   How do you monitor and maintain the checkpoint process?

Q:   Describe the database NOARCHIVELOG mode.

Q:   Describe the DBA_USERS view.

Q:   Describe the V$LOG dictionary view.

Q:   What does the STATUS column indicate in the V$LOG view?

Q:   Describe the following parameters:

LOG_CHECKPOINT_INTERVAL  parameter

LOG_CHECKPOINT_TIMEOUT  parameter

LOG_CHECKPOINTS_TO_ALERT  parameter

Q:   Describe the ARCHIVE LOG LIST command.

Q:   What does the following statement do?

SQL> STARTUP MOUNT

            PFILE=%ORACLE_HOME%\admin\school\pfile\init.ora

           /

Q:   How do you switch an online redo log file?

Q:   How do you perform a checkpoint manually?

Q:   How do you perform a checkpoint automatically?

 

Maintaining and Relocating the Redo Log files

Lesson 09

Q:   How do you maintain and relocate a redo log file?

Q:   Describe the V$LOG view.

Q:   Describe the V$LOGFILE view.

Q:   How do you add an online redo log group?

Q:   How do you add an online redo log file member?

Q:   How do you relocate or rename an online redo log file?

Q:   How do you drop an online redo log file?

Q:   How do you drop an online redo log group?

Q:   How do you delete a physical online redo log file?

Q:   What does the following SQL statement?

SQL> ALTER DATABASE ADD LOGFILE GROUP 4

            'c:\oracle\oradata\school\redo04.log' size 500k
           /

Q:   What does the following SQL statement?

SQL> ALTER DATABASE RENAME FILE

'C:\ORACLE\ORADATA\SCHOOL\REDO04.LOG' TO

'C:\ORACLE\ORADATA\redo04a.log'
             /

 

Multiplexing and Maintaining the Online Redo Log files

Lesson 10

Q:   Why do you multiplex an online redo log file?

Q:   How do you multiplex an online redo log file?

Q:   Describe the Oracle-Managed files.

Q:   Describe the User-Managed files.

Q:   What are the differences between an Oracle-Managed and User-Managed files?

Q:   How do you create an online redo log member?

Q:   What does the following SQL statement?

SQL> ALTER SYSTEM SET  db_create_online_log_dest_4='c:\newfolder'
            /

 

Maintaining Tablespace and Datafiles Introduction

Lesson 11

Q:   Describe a tablespace.

Q:   How do you create a tablespace in a database?

Q:   How do you maintain a tablespace using the Oracle-Managed file technique?

Q:   How do you maintain a tablespace using the User-Managed file technique?

Q:   How do you maintain a datafile using the Oracle-Managed file technique?

Q:   How do you maintain a datafile using the User-Managed file technique?

Q:   Describe the AUTOEXTEND ON option in the CREATE TABLESPACE statement.

Q:   Describe the following storage options in the CREATE TABLESPCES statement.

INITIAL parameter

NEXT parameter

MINEXTENTS parameter

MAXEXTENTS parameter

Q:   Describe the PERMANENT ONLINE option.

Q:   What does it mean that a tablespace LOCALLY managed?

Q:   Describe the DBA_TABLESPACES view.

Q:   How do you add a datafile to an existing tablespace?

Q:   When do you use the DB_CREATE_FILE_DEST  parameter?

Q:   What do the following SQL statement do?  What are the differences between the first CREATE statement and the second CREATE statement?

SQL> CREATE TABLESPACE myfirst_tablespace 
           DATAFILE 'c:\oracle\oradata\school\myfirst_tablespace_01.dbf'

           SIZE 10M
           AUTOEXTEND ON
           DEFAULT STORAGE (INITIAL
100K NEXT 100K 
           MINEXTENTS 10 MAXEXTENTS
200)
           PERMANENT ONLINE
           /

SQL> CREATE TABLESPACE  my2nd_tablespace
           /

Q:   How do you drop a tablespace?

Q:   How do you drop a tablespace if it contains objects?

 

Maintaining a TEMPORARY tablespace

Lesson 12

Q:   How do you create a TEMPORARY tablespace using Oracle-Managed File (OMF) technique?

Q:   How do you create a TEMPORARY tablespace using User-Managed File (UMF) technique?

Q:   What is the DB_CREATE_FILE_DEST parameter?

Q:   Describe the DBA_TABLESPACES view.

Q:   Describe the DBA_DATA_FILES view.

Q:   Describe the following views.

DBA_DATA_FILES directory view

DBA_TEMP_FILES directory view

V$SESSION dictionary view

V$SORT_SEGMENT view

Q:   How do you drop a temporary tablespace?

Q:   Create a temporary tablespace using an Oracle original syntax.

Q:   Create a temporary tablespace using the TEMPFILE clause.

Q:   Describe the UNIFORM option.

Q:   What do the following SQL statements do?

 SQL> SELECT tablespace_name, initial_extent, max_extents, 
           contents, logging, status
           FROM dba_tablespaces
/

SQL> SELECT s.username, tablespace, contents, extents, blocks
           FROM v$session s, v$sort_usage
           WHERE s.saddr = session_addr
/

SQL> SELECT tablespace_name, extent_size,

           total_extents, max_sort_blocks
           FROM v$sort_segment
/

SQL> CREATE TEMPORARY TABLESPACE mytemp
           TEMPFILE 'mytemp_01.tmp' SIZE 20M
           EXTENT MANAGEMENT LOCAL
           UNIFORM SIZE 10M
/

 

Maintaining a Tablespace

Lesson 13

Q:   How do you change a tablespace mode to the READ ONLY mode?

Q:   How do you relocate a tablespace?

Q:   How do you change a tablelspace mode to the READ WRITE mode?

Q:   How do you OFFLINE a tablespace mode?

Q:   How do you ONLINE a tablespace mode?

Q:   When do you OFFLINE a tablespace mode?

Q:   When can you perform the following SQL statement?

SQL> ALTER DATABASE RENAME FILE
           'c:\oracle\oradata\school\users01.dbf'
           TO
            'c:\newfolder\users01.dbf'
/


Managing Storage Structures

Lesson 14

Q:   Describe a Segment in the Oracle database.

Q:   Describe the DBA_SEGMENTS view.

Q:   What are the PCTFREE and PCTUSED space allocations in the CREATE TABLE statement?

Q:   How many types of segment do you have in the Oracle database?

Q:   How do you create an INDEX_ORGANIZED table?

Q:   Describe the ORGANIZATION INDEX parameter.

Q:   Describe the PCTTHRESHOLD and OVERFLOW TABLESPACE parameters.

Q:   Describe the DBA_EXTENTS view.

Q:   What do the following SQL statements do?

SQL> SELECT segment_type, count(segment_type)
           FROM dba_segments
           GROUP BY segment_type
/

SQL> CREATE TABLE my_iot (
           partno NUMBER, 
           name VARCHAR2
(20),
           CONSTRAINT pk_my_iot PRIMARY KEY (partno))
           ORGANIZATION INDEX
           TABLESPACE users
           PCTTHRESHOLD
20
           OVERFLOW TABLESPACE
users
/
SQL> CREATE TABLE my_default_table
           (col1 CHAR
(2000))
           STORAGE (INITIAL
50k 
           NEXT 50k 
           PCTINCREASE 0
           MINEXTENTS 1 
           MAXEXTENTS
5)
/
Q:   How do you query the distribution of the segments in the database?

Q:   How do you the following PL/SQL statement?

SQL> BEGIN
SQL>     FOR i IN 1..100 LOOP
SQL>         INSERT INTO my_default_table VALUES ('Numbers: ' || i);
SQL>     END LOOP;
SQL>     COMMIT
;
SQL> END;
SQL> /

 

Maintaining and Configuring UNDO tablespace

Lesson 15

Q:   How do you create an UNDO tablespace?

Q:   How do you configure an UNDO tablespace?

Q:   How do you view the UNDO MANAGEMENT parameter?

Q:   How do you set the UNDO MANAGEMENT parameter?

Q:   Describe the DBA_ROLLBACK_SEGS view.

Q:   Create an UNDO tablespace using OMF.

Q:   Create an UNDO tablespace using UMF.

Q:   How do you set an UNDO segment status from OFFLINE to ONLINE?

Q:   How do you deactivate an UNDO tablespace?

Q:   How do you drop an UNDO tablespace?

Q:   How do you set an UNDO retention time?

Q:   What do the following SQL statements do?

SQL> ALTER SYSTEM SET  db_create_file_dest='c:\newfolder'
/
SQL> CREATE UNDO TABLESPACE my_undo_tablespace
           DATAFILE SIZE 100K
/
SQL> SELECT
segment_name, tablespace_name, status
           FROM dba_rollback_segs
           WHERE tablespace_name = 'MY_UNDO_TABLESPACE'
/
SQL> ALTER TABLESPACE my_undo_tablespace ONLINE
/
SQL> DROP TABLESPACE  my_undo_tablespace

           INCLUDING CONTENTS CASCADE CONSTRAINTS
/

 

Maintaining and Configuring an UNDO tablespace manually

Lesson 16

Q:   How do you create an UNDO tablespace manually?

Q:   What are the differences between an UNDO tablespace manually and automatically?

Q:   What is the DICTIONARY-MANAGED tablespace?

Q:   What are the differences between a DICTIONARY-MANAGED and LOCALLY managed tablespace?

Q:   Describe the OPTIMAL option.

Q:   How do you offline an UNDO tablespace?

Q:   How do you change an automatically UNDO tablespace to a manually UNDO tablespace?

Q:   What do the following SQL statements do?

SQL> CREATE TABLESPACE rbs 
           DATAFILE size 100k
           EXTENT MANAGEMENT DICTIONARY
/
SQL> CREATE ROLLBACK SEGMENT rbs01
           TABLESPACE rbs
           STORAGE (INITIAL 10k 
           NEXT 10k 
           MAXEXTENTS 100 
           OPTIMAL 100k)
/
SQL> ALTER SYSTEM 

           SET undo_management=MANUAL  SCOPE=spfile
/
SQL> ALTER ROLLBACK SEGMENT rbs01 ONLINE
/
SQL> DROP TABLESPACE rbs
           INCLUDING CONTENTS
           CASCADE CONSTRAINTS

/

Creating and Maintaining a TEMPORARY

Lesson 17

Q:   How do you create a TRANSACTION temporary table?

Q:   How do you create a SESSION temporary table?

Q:   Describe the ON COMMIT DELETE ROWS option.

Q:   Describe the ON COMMIT PRESERVE ROWS option.

Q:   How do you drop a transaction or session temporary table?

Q:   What do the following SQL statements do?

SQL> CREATE GLOBAL TEMPORARY TABLE test_temp
           (col1 NUMBER(5) PRIMARY KEY,
            col2 VARCHAR2
(10) check (col2 BETWEEN 'A' AND 'T'))
           ON COMMIT
DELETE ROWS
/
SQL> CREATE GLOBAL
TEMPORARY TABLE test_temp
            (col1 NUMBER(5) PRIMARY KEY,
             col2 VARCHAR2
(10) check (col2 BETWEEN 'A' AND 'T'))
             ON COMMIT
PRESERVE ROWS
/

Detecting ROW Migration and Chaining

Lesson 18

Q:   What is a ROW Migration?

Q:   What is a Chained record?

Q:  What are the differences between a row migration and chained record?

Q:   How do you detect a row migration and chaining?

Q:   How do you analyze a table?

Q:   Describe the following views.

USER_TABLES view

USER_INDEXES view

INDEX_STATS view

Q:   What do the following columns contain in the USER_TABLES view?

NUM_ROWS column

BLOCKS column

CHAIN_CNT column

Q:   How do you move a table?

Q:   How do you grant a system privilege to a user?

Q:   How do you reorganize an index table?

Q:   What does the DELETED ROWS RATIO value show?

Q:   What are the lf_rows and del_lf_rows columns in the INDEX_STAT table?

Q:  How do you revoke a system privilege from a user?

Q:   How do you create a table constraint?

Q:   What do the following SQL statements do?

SQL> CREATE TABLE test_migrate
           (col1 NUMBER,
            col2 VARCHAR2 (1000),
           CONSTRAINT pk_test_migrate PRIMARY KEY (col1)
)
/
SQL> SELECT table_name, num_rows, blocks, chain_cnt
           FROM user_tables
           WHERE table_name = 'TEST_MIGRATE'
/

SQL> ANALYZE TABLE test_migrate COMPUTE STATISTICS
/

SQL> ALTER TABLE test_migrate MOVE TABLESPACE data2move
/
SQL> ALTER INDEX pk_test_migrate

           REBUILD TABLESPACE index2move
/

SQL> SELECT name, lf_rows, del_lf_rows,

           del_lf_rows/lf_rows "Over 30%"
           FROM index_stats
           WHERE name = 'PK_TEST_MIGRATE'
/

SQL> DROP TABLESPACE index2move
           INCLUDING CONTENTS
           CASCADE CONSTRAINTS

 

Monitoring an object usage

Lesson 19

Q:   How do you monitor a usage of an index table?

Q:   How do you start monitoring a usage of an index table?

Q:   How do you stop monitoring a usage of an index table?

Q:   Describe the V$OBJECT_USAGE view.

Q:   What do the following columns indicate in the V$OBJECT_USAGE view?

USED column

MONITORING column

END_MONITORING column

Q:   What do the following SQL statements do?

SQL> ALTER INDEX uk_emp 
           MONITORING USAGE
/
SQL> SELECT * FROM v$object_usage
/

SQL> ALTER INDEX uk_emp NOMONITORING USAGE
/

 

EXCEPTIONS INTO EXCEPTIONS

Lesson 20

Q:   What does the EXCEPTIONS INTO EXCEPTIONS clause perform in the ALTER TABLE statement?

Q:   How do you disable a constraint?

Q:   How do you enable a constraint?

Q:   How do you create the EXCEPTIONS table?

Q:   Describe the UTLEXCPT.SQL script.

Q:   How do you find duplicate records using the EXCEPTIONS INTO EXCEPTIONS clause?

Q:   How do you drop a constraint?

Q:   What do the following SQL statements do?

SQL> ALTER TABLE emp 
           ENABLE VALIDATE CONSTRAINT ck_emp 
           EXCEPTIONS INTO EXCEPTIONS
/

 

Maintaining userís account and profile

Lesson 21

Q:   How do you create a user account?

Q:   How do you create a user profile?

Q:   How do you grant an object privilege to a user?

Q:   How do you assign a default tablespace to a user?

Q:   How do you assign a temporary tablespace to a user?

Q:   What is a quota in the Oracle database?

Q:   How do you assign a quota to a user?

Q:   How do you assign a profile to a user?

Q:   How does a user account expire?

Q:   How do you create an Oracle profile?

Q:   How do you change an Oracle userís password?

Q:   How do you allocate resource limitation to a profile?

Q:   How do you lock a user?

Q:   How do you unlock a user?

Q:   What is the DEFAULT profile?

Q:   Describe the DBA_USERS and ALL_OBJECTS views.

Q:   How do you activate the resource limit system parameter?

Q:   How do you drop a user?

Q:   Can you drop a user containing Oracle objects?

Q:   How do you maintain a profile?

Q:   How do you maintain a user assigned tablespaces?

Q:   What do the following SQL statement do?

SQL> SELECT username, password, account_status,
            default_tablespace, temporary_tablespace, 
             profile
           FROM dba_users
           WHERE username like 'D%'
/
SQL> CREATE USER developer
            IDENTIFIED BY developer
            DEFAULT TABLESPACE iself_data
            TEMPORARY
TABLESPACE temp
            QUOTA 10K ON iself_data
            QUOTA 0K ON SYSTEM
            PROFILE default
            PASSWORD EXPIRE
            ACCOUNT UNLOCK
/
SQL> ALTER USER DEVELOPER ACCOUNT LOCK
/
SQL> ALTER USER DEVELOPER ACCOUNT UNLOCK
/
SQL> CREATE PROFILE developer LIMIT
           SESSIONS_PER_USER 1
           CPU_PER_SESSION 1000
           CONNECT_TIME 4800
           IDLE_TIME 60
/
SQL> ALTER SYSTEM
SET  resource_limit=TRUE
/
SQL> DROP USER developer
           CASCADE
/
 

 

Database Triggers

Lesson 22

Q:   What are the Oracle database triggers?

Q:   What is an Oracle event trigger?

Q:   What is a DML trigger?

Q:   What is a DDL trigger?

Q:   What is a database event trigger?

Q:   What is an INSTEAD OF trigger?

Q:   What is a schema trigger?

 

Auditing a database

Lesson 23

Q:   How do you activate auditing a database?

Q:   How do you start auditing?

Q:   How do you stop auditing?

Q:   How do you read from the AUDIT_TRAIL table?

Q:   When and why do you truncate the AUD$ table?

Q:   How do you view the AUDIT_TRAIL parameter value?

Q:   How do you set the AUDIT_TRAIL parameter value?

Q:   How do you auditing an auditor?

Q:   Audit all the users who delete a record or records from the EMP table.

Q:   What do the following SQL statements do?

SQL> TRUNCATE TABLE aud$
/
SQL> ALTER SYSTEM
SET audit_trail=db SCOPE=spfile
/
SQL> AUDIT delete ON sys.aud$

/
SQL> AUDIT DELETE 
           ON iself.emp
           BY ACCESS
           WHENEVER SUCCESSFUL
/
SQL> SELECT

           TO_CHAR
(timestamp#,'DD-MON-YYYY HH24:MI:SS')

             as "Date and Time", userid, name "Action by user"
           FROM sys.aud$ JOIN sys.audit_actions
           ON action = action#
/
SQL> NOAUDIT ALL

/

 

Cluster table

Lesson 24

Q:   What is a cluster table in the Oracle database?

Q:   What is a cluster key?

Q:   What are the types of clusters?

Q:   Describe an indexed cluster.

Q:   Describe a hash cluster.

Q:   When do you use a hash cluster?

Q:   What is the Cluster Size parameter?

Q:   How do you add a table to a cluster table?

Q:  What do the following SQL statements do?

SQL> CREATE CLUSTER personnel

    ( department_number  NUMBER(2) )

    SIZE 512

    STORAGE (INITIAL 100K NEXT 50K PCTINCREASE 10)

/

SQL> CREATE TABLE emp

    (empno     NUMBER        PRIMARY KEY,

     ename     VARCHAR2 (10)  NOT NULL

                             CHECK (ename = UPPER(ename)),

     job       VARCHAR2 (9),

     mgr       NUMBER        REFERENCES scott.emp(empno),

     hiredate  DATE          CHECK (hiredate >= SYSDATE),

     sal       NUMBER(10,2)  CHECK (sal > 500),

     comm      NUMBER(9,0)   DEFAULT NULL,

     deptno   NUMBER(2)      NOT NULL )

     CLUSTER personnel (deptno)

/

SQL> CREATE TABLE dept

    (deptno  NUMBER(2),

     dname   VARCHAR2 (9),

     loc     VARCHAR2 (9))

     CLUSTER personnel (deptno)

/

SQL> CREATE INDEX idx_personnel ON CLUSTER personnel

/

SQL> CREATE CLUSTER personnel

           ( department_number  NUMBER )

           SIZE 512  HASHKEYS 500

               STORAGE (INITIAL 100K 

                                    NEXT 50K 

                                    PCTINCREASE 10)

/

 

Organizing tables and indexes

Lesson 25

Q:   How do you re-organize a table?

Q:   How do you re-organize a table when the table contains a LONG datatype?

Q:   Describe the DBMS_STATS package.

Q:   What does the GATHER_SCHEMA_STATS procedure in the DBMS_STATS package?

Q:   What do the following SQL and PL/SQL statements do?

SQL> EXECUTE dbms_stats.gather_schema_stats
                                 ('your_schema_name',cascade=>true);
SQL>

  1  BEGIN

  2     FOR this IN 1..100 LOOP

  3        INSERT INTO table_with_long

  4           VALUES

          ('PK_' || this, this, 'This is a very long long long data...');

  5        -- save transaction

  6        COMMIT ;

  7     END LOOP;

  8* END;

 

  1  DECLARE

  2     CURSOR c_table_with_long$$recovery

  3        IS SELECT * FROM table_with_long$$recovery;

  4  BEGIN

  5     FOR this IN c_table_with_long$$recovery LOOP

  6        INSERT INTO table_with_long

  7           VALUES (this.c1, this.c2, this.c3);

  8        COMMIT;

  9     END LOOP;

 10* END;

Answers

 

Google
 
Web web site