|
Oracle DBA #1 Fundamental Exam Questions
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.
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?
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.
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?
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.
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?
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'
/
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?
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'
/
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'
/
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?
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
/
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'
/
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> /
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
/
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
/
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
/
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
/
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
/
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
/
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
/
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?
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
/
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)
/
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
|