“Great spirits have always found violent opposition from mediocrities. The latter cannot understand it when a man does not thoughtlessly submit to hereditary prejudices but honestly and courageously uses his intelligence.”

Albert Einstein

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.

“I never think of the future - it comes soon enough.”

Albert Einstein

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?

“Imagination is more important than knowledge...”

Albert Einstein

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.

“Laws alone can not secure freedom of expression; in order that every man present his views without penalty there must be spirit of tolerance in the entire population.”

Albert Einstein

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?

"My religion consists of a humble admiration of the illimitable superior spirit who reveals himself in the slight details we are able to perceive with our frail and feeble mind.”

Albert Einstein

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'
/

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”

Albert Einstein

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?

"Reading, after a certain age, diverts the mind too much from its creative pursuits. Any man who reads too much and uses his own brain too little falls into lazy habits of thinking.”

Albert Einstein

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'
/

"The important thing is not to stop questioning. Curiosity has its own reason for existing. One cannot help but be in awe when he contemplates the mysteries of eternity, of life, of the marvelous structure of reality. It is enough if one tries merely to comprehend a little of this mystery every day. Never lose a holy curiosity.”

Albert Einstein

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'
/

"Reality is merely an illusion, albeit a very persistent one.”

Albert Einstein

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?

"The ideals which have lighted my way, and time after time have given me new courage to face life cheerfully, have been Kindness, Beauty, and Truth. The trite subjects of human efforts, possessions, outward success, luxury have always seemed to me contemptible.”

Albert Einstein

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
/

"The important thing is not to stop questioning.”

Albert Einstein

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'
/

"The most incomprehensible thing about the world is that it is at all comprehensible.”

Albert Einstein

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> /

"The most beautiful thing we can experience is the mysterious. It is the source of all true art and science.”

Albert Einstein

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
/

“The secret to creativity is knowing how to hide your sources.”

Albert Einstein

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

“When you look at yourself from a universal standpoint, something inside always reminds or informs you that there are bigger and better things to worry about.”

Albert Einstein, The World as I See It.

Creating and Maintaining a TEMPORARY table

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
/

“At any rate, I am convinced that He [God] does not play dice.”

Albert Einstein, In a letter to Max Born, 1926

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
/

"The significant problems we face cannot be solved at the same level of thinking we were at when we created them."

- Albert Einstein (1879-1955)

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
/

"The hardest thing in the world to understand is the income tax.”

Albert Einstein

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
/

"You cannot simultaneously prevent and prepare for war.”

Albert Einstein

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
/

"Not everything that can be counted counts, and not everything that counts can be counted.”

Albert Einstein

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?

"It is the duty of every citizen according to his best capacities to give validity to his convictions in political affairs.”

Albert Einstein, 'Treasury for the Free World,' 1946

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
/

"Try not to become a man of success but rather to become a man of value.”

Albert Einstein

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)

"Science without religion is lame, religion without science is blind.”

Albert Einstein, "Science, Philosophy and Religion: a Symposium", 1941

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