Exports/Imports IMPDP and EXPDP

For Particular Tables

expdp TEST/TEST@ORCL tables=EMP,DEPT directory=TEST_DIR dumpfile=test.dmp logfile=test.log

impdp TEST/TEST@ORCL tables=EMP,DEPT directory=TEST_DIR dumpfile=test.dmp logfile=test.log

For 

Schema Exports/Imports

expdp TEST/TEST@ORCL schemas=HR dumpfile=test.dmp logfile=test.log

impdp TEST/TEST@ORCL schemas=HR dumpfile=test.dmp logfile=test.log  

import recreate the new table as in the dumpfile. 

impdp  dumpfile=emp.dmp logfile=imp1.log table_exists_action=replace

Basic Oracle space monitoring scripts

select tbs.tablespace_name, tot.bytes/1024 total, tot.bytes/1024-sum(nvl(fre.bytes,0))/1024 used, sum(nvl(fre.bytes,0))/1024 free, (1-sum(nvl(fre.bytes,0))/tot.bytes)*100 pct, decode( greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90), 90, '', '*' ) pct_warn from dba_free_space fre, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) tot, dba_tablespaces tbs where tot.tablespace_name = tbs.tablespace_name and fre.tablespace_name(+) = tbs.tablespace_name group by tbs.tablespace_name, tot.bytes/1024, tot.bytes order by 5, 1 ;

What is Explain Plan in Oracle, & how do we use it?

An Explain Plan is a tool that you can use to have Oracle explain to you how it plans on executing your query. This is useful in tuning queries to the database to get them to perform better. Once you know how Oracle plans on executing your query, you can change your environment to run the query faster.
Before you can use the EXPLAIN PLAN command, you need to have a PLAN_TABLE installed. This can be done by simply running the $ORACLE_HOME/rdbms/admin/utlxplan.sql script in your schema. It creates the table for you. After you have the PLAN_TABLE created, you issue an EXPLAIN PLAN for the query you are interested in tuning. The command is of the form:

EXPLAIN PLAN SET STATEMENT_ID='somevalue' FOR some SQL statement;

You need to use a statement_id and then give your SQL statement. For instance, suppose I have a query to tune. How does it get executed? I issue the following in SQL*Plus:

SQL> explain plan set statement_id = 'q1' for 2 select object_name from test where object_name like 'T%'; Explained.

I used 'q1' for my statement id (short for query 1). But you can use anything you want. My SQL statement is the second line. Now I query the PLAN_TABLE to see how this statement is executed. This is done with the following query:

SQL> SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options || ' ' || 2 object_name || ' ' || DECODE(id,0,'Cost = ' || position) AS "Query Plan",other 3 FROM plan_table 4 START WITH id = 0 5 AND statement_id='q1' 6 CONNECT BY PRIOR ID = PARENT_ID 7* AND statement_id = 'q1' Query Plan OTHER -------------------------------------------------- -------------------------------------------------- SELECT STATEMENT Cost = TABLE ACCESS FULL TEST

This tells me that my SQL statement will perform a FULL table scan on the TEST table (TABLE ACCESS FULL TEST). Now let's add an index on that table!

SQL> create index test_name_idx on test(object_name); Index created. SQL> truncate table plan_table; Table truncated. SQL> explain plan set statement_id = 'q1' for 2 select object_name from test where object_name like 'T%'; Explained. SQL> SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options || ' ' || 2 object_name || ' ' || DECODE(id,0,'Cost = ' || position) AS "Query Plan",other 3 FROM plan_table 4 START WITH id = 0 5 AND statement_id='q1' 6 CONNECT BY PRIOR ID = PARENT_ID 7* AND statement_id = 'q1' Query Plan OTHER -------------------------------------------------- -------------------------------------------------- SELECT STATEMENT Cost = INDEX RANGE SCAN TEST_NAME_IDX

I added an index to the table. Before I issue another EXPLAIN PLAN, I truncate the contents of my PLAN_TABLE to prepare for the new plan. Then I query the PLAN TABLE. Notice that this time I'm using an index (TEST_NAME_IDX) that I created!! Hopefully, this query will run faster now that it has an index to use. But this may not always be the case.

Oracle Database Performance Tuning Steps.

Using Baselines

Comparative Performance Analysis with AWR Baselines
Automatic Workload Repository Baselines
Moving Window Baseline
Baselines in Performance Page Settings & Baseline Templates
AWR Baselines & Creating AWR Baselines
Managing Baselines with PL/SQL & Baseline Views
Performance Monitoring and Baselines & Defining Alert Thresholds Using a Static Baseline

Using AWR Based Tools

Automatic Maintenance Tasks
ADDM Performance Monitoring

Identifying Problem SQL Statements

SQL Statement Processing Phases & Role of the Oracle Optimizer
Identifying Bad SQL, Real Time SQL Monitoring (a 11.1 feature new lesson in NF L-15) & TOP SQL Reports
What Is an Execution Plan? Methods for Viewing Execution Plans & Uses of Execution Plans
DBMS_XPLAN Package: Overview & EXPLAIN PLAN Command
Reading an Execution Plan, Using the V$SQL_PLAN View & Querying the AWR
SQL*Plus AUTOTRACE & SQL Trace Facility
How to Use the SQL Trace Facility
Generate an Optimizer Trace

Influencing the Optimizer

Functions of the Query Optimizer, Selectivity, Cardinality and Cost & Changing Optimizer Behavior
Using Hints, Optimizer Statistics & Extended Statistics
Controlling the Behavior of the Optimizer with Parameters
Enabling Query Optimizer Features & Influencing the Optimizer Approach
Optimizing SQL Statements, Access Paths & Choosing an Access Path
Join & Sort Operations
How the Query Optimizer Chooses Execution Plans for Joins
Reducing the Cost

Using SQL Performance Analyzer

Real Application Testing: Overview & Use Cases
SQL Performance Analyzer: Process & Capturing the SQL Workload
Creating a SQL Performance Analyzer Task & SPA (NF Lesson 9) DBMS_SQLTUNE.CREATE_TUNING_TASK
Optimizer Upgrade Simulation & SQL Performance Analyzer Task Page
Comparison Report & Comparison Report SQL Detail
Tuning Regressing Statements & Preventing Regressions
Parameter Change Analysis & Guided Workflow Analysis
SQL Performance Analyzer: PL/SQL Example & Data Dictionary Views

SQL Performance Management

Maintaining SQL Performance and Optimizer Statistics & Automated Maintenance Tasks
Statistic Gathering Options & Setting Statistic Preferences
Restore Statistics
Deferred Statistics Publishing: Overview & Example
Automatic SQL Tuning: Overview
SQL Tuning Advisor: Overview
Using the SQL Access Advisor
SQL Plan Management: Overview

Tuning PGA and Temporary Space
SQL Memory Usage & Performance Impact
SQL Memory Manager
Configuring Automatic PGA Memory & Setting PGA_AGGREGATE_TARGET Initially
Monitoring & Tuning SQL Memory Usage
PGA Target Advice Statistics & Histograms
Automatic PGA and Enterprise Manager & Automatic PGA and AWR Reports
Temporary Tablespace Management: Overview & Monitoring Temporary Tablespace
Temporary Tablespace Shrink & Tablespace Option for Creating Temporary Table

DBA_TABLESPACES

DBA_TABLESPACES and USER_TABLESPACES views indicates if the tablespace is encrypted or not.

SELECT tablespace_name, encrypted FROM dba_tablespaces;

TABLESPACE_NAME                ENC
------------------------------ ---
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       NO
TEMP                           NO
USERS                          NO

 

Listing Tablespaces and Default Storage Parameters

 

SELECT TABLESPACE_NAME "TABLESPACE",INITIAL_EXTENT, NEXT_EXTENT ,MIN_EXTENTS,

MAX_EXTENTS ,PCT_INCREASEFROM DBA_TABLESPACES;

 

How to check tablespace usage in Oracle

select sysdate, a.owner username, a.tablespace_name, round(b.total_space/1024/1024,2) "Total (MB)", round(sum(a.bytes)/1024/1024,2) "Used (MB)", round(sum(a.bytes/b.total_space)*100,2) "% Used"

from dba_segments a, (select tablespace_name, sum(bytes) total_space

                      from dba_data_files

                      group by tablespace_name) b

where a.tablespace_name not in ('SYSAUX', 'SYSTEM', 'UNDOTBS1', 'UNDOTBS2')

and a.tablespace_name = b.tablespace_name

group by a.tablespace_name, a.owner, b.total_space/1024/1024

order by a.tablespace_name, a.owner;

 

Tablespace Data Dictionary Views

View	Description
V$TABLESPACE	Name and number of all tablespaces from the control file.
V$ENCRYPTED_TABLESPACES	Name and encryption algorithm of all encrypted tablespaces.
DBA_TABLESPACES, USER_TABLESPACES	Descriptions of all (or user accessible) tablespaces.
DBA_TABLESPACE_GROUPS	Displays the tablespace groups and the tablespaces that belong to them.
DBA_SEGMENTS, USER_SEGMENTS	Information about segments within all (or user accessible) tablespaces.
DBA_EXTENTS, USER_EXTENTS	Information about data extents within all (or user accessible) tablespaces.
DBA_FREE_SPACE, USER_FREE_SPACE	Information about free extents within all (or user accessible) tablespaces.
DBA_TEMP_FREE_SPACE	Displays the total allocated and free space in each temporary tablespace.
V$DATAFILE	Information about all datafiles, including tablespace number of owning tablespace.
V$TEMPFILE	Information about all tempfiles, including tablespace number of owning tablespace.
DBA_DATA_FILES	Shows files (datafiles) belonging to tablespaces.
DBA_TEMP_FILES	Shows files (tempfiles) belonging to temporary tablespaces.
V$TEMP_EXTENT_MAP	Information for all extents in all locally managed temporary tablespaces.
V$TEMP_EXTENT_POOL	For locally managed temporary tablespaces: the state of temporary space cached and used for by each instance.
V$TEMP_SPACE_HEADER	Shows space used/free for each tempfile.
DBA_USERS	Default and temporary tablespaces for all users.
DBA_TS_QUOTAS	Lists tablespace quotas for all users.
V$SORT_SEGMENT	Information about every sort segment in a given instance. The view is only updated when the tablespace is of the TEMPORARY type.
V$TEMPSEG_USAGE	Describes temporary (sort) segment usage by user for temporary or permanent tablespaces.

Statistics on Tables, Indexes and Columns

• DBA_TABLES
• DBA_OBJECT_TABLES
• DBA_TAB_STATISTICS
• DBA_TAB_COL_STATISTICS
• DBA_TAB_HISTOGRAMS
• DBA_TAB_COLS
• DBA_COL_GROUP_COLUMNS
• DBA_INDEXES
• DBA_IND_STATISTICS
• DBA_CLUSTERS
• DBA_TAB_PARTITIONS
• DBA_TAB_SUBPARTITIONS
• DBA_IND_PARTITIONS
• DBA_IND_SUBPARTITIONS
• DBA_PART_COL_STATISTICS
• DBA_PART_HISTOGRAMS
• DBA_SUBPART_COL_STATISTICS
• DBA_SUBPART_HISTOGRAMS

Oracle System Tables

System Table	Description
ALL_ARGUMENTS	Arguments in object accessible to the user
ALL_CATALOG	All tables, views, synonyms, sequences accessible to the user
ALL_COL_COMMENTS	Comments on columns of accessible tables and views
ALL_CONSTRAINTS	Constraint definitions on accessible tables
ALL_CONS_COLUMNS	Information about accessible columns in constraint definitions
ALL_DB_LINKS	Database links accessible to the user
ALL_ERRORS	Current errors on stored objects that user is allowed to create
ALL_INDEXES	Descriptions of indexes on tables accessible to the user
ALL_IND_COLUMNS	COLUMNs comprising INDEXes on accessible TABLES
ALL_LOBS	Description of LOBs contained in tables accessible to the user
ALL_OBJECTS	Objects accessible to the user
ALL_OBJECT_TABLES	Description of all object tables accessible to the user
ALL_SEQUENCES	Description of SEQUENCEs accessible to the user
ALL_SNAPSHOTS	Snapshots the user can access
ALL_SOURCE	Current source on stored objects that user is allowed to create
ALL_SYNONYMS	All synonyms accessible to the user
ALL_TABLES	Description of relational tables accessible to the user
ALL_TAB_COLUMNS	Columns of user's tables, views and clusters
ALL_TAB_COL_STATISTICS	Columns of user's tables, views and clusters
ALL_TAB_COMMENTS	Comments on tables and views accessible to the user
ALL_TRIGGERS	Triggers accessible to the current user
ALL_TRIGGER_COLS	Column usage in user's triggers or in triggers on user's tables
ALL_TYPES	Description of types accessible to the user
ALL_UPDATABLE_COLUMNS	Description of all updatable columns
ALL_USERS	Information about all users of the database
ALL_VIEWS	Description of views accessible to the user
DATABASE_COMPATIBLE_LEVEL	Database compatible parameter set via init.ora
DBA_DB_LINKS	All database links in the database
DBA_ERRORS	Current errors on all stored objects in the database
DBA_OBJECTS	All objects in the database
DBA_ROLES	All Roles which exist in the database
DBA_ROLE_PRIVS	Roles granted to users and roles
DBA_SOURCE	Source of all stored objects in the database
DBA_TABLESPACES	Description of all tablespaces
DBA_TAB_PRIVS	All grants on objects in the database
DBA_TRIGGERS	All triggers in the database
DBA_TS_QUOTAS	Tablespace quotas for all users
DBA_USERS	Information about all users of the database
DBA_VIEWS	Description of all views in the database
DICTIONARY	Description of data dictionary tables and views
DICT_COLUMNS	Description of columns in data dictionary tables and views
GLOBAL_NAME	global database name
NLS_DATABASE_PARAMETERS	Permanent NLS parameters of the database
NLS_INSTANCE_PARAMETERS	NLS parameters of the instance
NLS_SESSION_PARAMETERS	NLS parameters of the user session
PRODUCT_COMPONENT_VERSION	version and status information for component products
ROLE_TAB_PRIVS	Table privileges granted to roles
SESSION_PRIVS	Privileges which the user currently has set
SESSION_ROLES	Roles which the user currently has enabled.
SYSTEM_PRIVILEGE_MAP	Description table for privilege type codes. Maps privilege type numbers to type names
TABLE_PRIVILEGES	Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
TABLE_PRIVILEGE_MAP	Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names

how to create sql profile

DECLARE
 my_task_name VARCHAR2(30);
 my_sqltext CLOB;
 Begin
 my_sqltext := 'select * from emp where empno=66';
 My_Task_Name := Dbms_Sqltune.Create_Tuning_Task(
 Sql_Text=> My_Sqltext,
 user_name => 'SCOTT',
 scope => 'COMPREHENSIVE',
Time_Limit => 60,
 task_name => 'sql_tuning_task_11',
 Description => 'Task to tune a query on a specified table');
 END;

  Exec Dbms_Sqltune.Execute_Tuning_Task( Task_Name => 'my_sql_tuning_task_11');
  SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_11') from DUAL;



check sql profile 

select * from dba_Sql_Profiles


delete  sql profile

EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('NAME');

Show hidden files windows 7

Follow these steps to display hidden files and folders.

1. Open Folder Options by clicking the Start button , clicking Control Panel, clicking Appearance and Personalization, and then clicking Folder Options.
2. Click the View tab.
3. Under Advanced settings, click Show hidden files and folders, and then click OK.

Documents required for marriage certificate in India

You need the below documents for getting marriage certificate in India

• Marriage Invitation card
  
• 2 Photos of Bride and Groom in marriage dress taken during marriage ritual which clearly show they are taking part in marriage cermony.
  
• Documents which serve as Proof of Age
  Preferably  10th class marks memo which has your photo and also your date of Birth on it.
  
• Address proof of current residing address which should be on Bride or Grooms name.
  
  • Some of the accepted documents are Passport,Drivers Lisence, Voter ID card, GAS card, Eletricity Bill on the name of the applicant. Atleast one of the applicant should have government issue Residence proof if not booth the applicants.

And 3 witnesses to certify your marriage.

You should go to the sub registrar office of the locality for which you have address proof. They differ based on your plot number/Door number of your address proof.

ORA-00257: archiver error tips

Increase the free space where archiver archives the archivelog. The location where archiver archives the log is determined by parameter file pfile or spfile.


SQL> show parameter log_archive_dest
2.) In case it is not possible to increase free space at the same location but if free space is available at other location then the parameter log_archive_dest (or log_archive_dest_1 in some cases) can be changed so that the new archives are produced at new location specified which has free space.
this can be done by modify init.ora file or using alter system if spfile is present
SQL> alter system set log_archive_dest_1=’
3.) The option which is often used is to take a backup of the archives from the existing place and delete those archives from that place so that new archives can generated at that place .
the backup can be OS level backup and OS level del;etion but the recommended method which is compulsory to be used with ASM in place is taking any RMAN backup and delete using RMAN. as shown
rman target sys/sys
RMAN> backup archive log all device type disk format ‘/oracle/arch_%U’;
RMAN> delete archive until time ‘trunc(sysdate)’;
This will delete all the archive logs until today and space will freed and the archiver will start archiving redo logs

What are joins..how many types of joins are there?

A join is a query that extracts data from two or more tables, views or snapshots.

1. Equi Join
2.Non Equijoin
3.outer joins
4.self joins 

To get second highest age from the student table 

SELECT * FROM (SELECT s.*, rownum age_rank

 FROM (SELECT * FROM student ORDER BY age DESC) s) WHERE age_rank=2; 

Write a query to display alternate records from the employee table?

SELECT * FROM emp WHERE ROWID IN 

(SELECT DECODE(MOD(ROWNUM,2),0,ROWID, NULL) FROM emp);

 

What is difference between DELETE & TRUNCATE commands?

Delete command removes the rows from a table based on the condition that we provide with a WHERE
clause. Truncate will actually remove all the rows from a table and there will be no data in the table
after we run the truncate command.

TRUNCATE
TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the
page deallocations are recorded in the transaction log.
TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes
and so on remain. The counter used by an identity for new rows is reset to the seed for the column.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE can not be Rolled back.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table.

DELETE
DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
If you want to retain the identity counter, use DELETE instead. If you want to remove table definition
and its data, use the DROP TABLE statement.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE Can be Rolled back.
DELETE is DML Command.
DELETE does not reset identity of the table.

What are different normalization forms?

1NF: Eliminate Repeating Groups
Make a separate table for each set of related attributes, and give each table a primary key. Each field
contains at most one value from its attribute domain.
2NF: Eliminate Redundant Data
If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF: Eliminate Columns Not Dependent On Key
If attributes do not contribute to a description of the key, remove them to a separate table. All
attributes must be directly dependent on the primary key
BCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes, separate them out into distinct
tables.
4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF: Isolate Semantically Related Multiple Relationships
There may be practical constrains on information that justify separating logically related many-to-many
relationships.
ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF: Domain-Key Normal Form
A model free from all modification anomalies.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first
fulfill all the criteria of a 2NF and 1NF database.

ORACLE DBA

Q: What are the Oracle Architectural components?
A: The Oracle Architectural components are:
Memory (SGA) such as Buffer Cache, Shared Pool, Redo Log Buffer, Large Pool, Java Pool, etc.
Background Processes such as Log Writer, DB Writer, Checkpoint, Archiver, SMON, etc.
Oracle Physical Layout such as Datafiles, Controlfiles, Online Redo log files, Parameter file, Password file, etc.

Q: What are the Oracle Memory Components?
A: All components such as Shared Pool (Library Cache , Dictionary Cache ), Buffer Cache , Online Redo Log file , Large Pool , Java Pool as well as a few other items are referred to as the System Global Area (SGA ). And the place stores information like bind variable values , sort areas, cursor handling, etc for a specific user is called Program Global Area (PGA ). The PGA is used to store only real values in place of bind variables for executing SQL statements. The combination of these two memories structure while they are running is called Oracle Instance.

Q: What is the Server Parameter File?
A: The Server Parameter File is a binary file and Oracle uses it to change the most of its system parameters dynamically.

Q: What is the Parameter File?
A: The Parameter file is a configuration file and it contains all the Oracle instance and database configuration parameters. When you change any parameter using this file, you should shutdown and startup the Oracle Database.

Q: How do you use the init.ora file?
A: The init.ora file is called initialized or parameter file. It is a configuration file.

Q: What is the System Global Area (SGA)?
A: The SGA contains of Shared Pool (Library Cache , Dictionary Cache ), Buffer Cache , Online Redo Log file , Large Pool , Java Pool as well as a few other items.

Q: What is the Shared Pool in SGA?
A: The Shared Pool contains the Library Cache and the Dictionary Cache as well as a few other items, which are not in the scope of this section. The Library Cache holds all users’ SQL statements, Functions, Procedures, and Packages. It stores parsed SQL statement with its execution plan for reuse. The Dictionary Cache, sometimes also referred to as the Row Cache , holds the Oracle repository data information such as tables, indexes, and columns definitions, usernames, passwords, synonyms, views, procedures, functions, packages, and privileges information.

Q: What does the Buffer Cache hold in SGA?
A: The Buffer Cache holds users’ data. Users query their data while they are in the Buffer Cache. If user’s request is not in the Buffer Cache then server process has to bring it from disk. The smallest unit in the buffer cache is an Oracle block. The buffer cache can be increased or decreased by granule unit. The smallest Granule Unit is 4Meg if the SGA size is less than 128Meg and the smallest Granule Unit become 16Meg is the SGA size is more than 128Meg.

. Notice that when you use dedicated servers, user session information is housed in the PGA .

Q: Describe the Multi-threaded Server process.
A: The Multi-threaded Server process will be used when a user send his/her request by using a shared server. A user’s request will be assigned to a dispatcher based on the availability of dispatchers. Then the dispatcher will send or receive request from an assigned shared server.

Q: What are PGA and UGA?
A: When you are running dedicated servers then the session information can be stored inside the process global area (PGA). The UGA is the user global area, which holds session-based information. When you are running shared servers then the session information can be stored inside the user global area (UGA).

Q: Describe the log writer background process (LGWR).
A: The LGWR’s job is to write the redo user’s entries from the Redo Log Buffer .

Q: How often does LGWR write user’s entries to the Online Redo Log Buffer files?
A: It writes user’s entries when the buffer exceeds one third of the Redo Log Buffer, every 3 seconds, or when a user executes the commit SQL statement.

Q: Describe the Checkpoint process.
A: The Checkpoint signals DB writers to write all dirty blocks into the disk. The Checkpoint will occurred either by a specific defined time, size of the Online Redo Log file used by DBA, or when an Online Redo log file will be switched from on log file to another.

Q: How do you automatically force the Oracle to perform a checkpoint?
A: The following are the parameters that will be used by a DBA to adjust time or interval of how frequently its checkpoint should occur on its database.
LOG_CHECKPOINT_TIMEOUT = 3600 # every one hour
LOG_CHECKPOINT_INTERVAL =1000 # number of OS blocks

Q: What is the Recovery Process?
A: The RECO will be used only if you have a distributed database. You use this process to recover a database if a failure occurs due to physical server problem or communication problem.

Q: What is the Lock Background Process?
A: The LCKn background process will be used if you have multiple instances accessing to only one database. An example of that is a Parallel Server or a Real Application Clusters.

Q: How does the Archive Process work?
A: This background process archives the Online Redo Log file when you are manually or automatically switching an Online Redo Log file. An example of manually switching is: ALTER SYSTEM SWITCH LOGFILE or ALTER SYSTEM ARCHIVE LOG CURRENT.

Q: How do you configure your database to do an automatic archiving?
A: SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

Q: What is the System Monitor Process?
A: The SMON job is: when you start your database, it will make sure that all datafiles, controlfiles, and log files are synchronized before opening a database. If they are no, it will perform an instance recovery. It will check the last SCN that was performed against the datafiles. If it finds that there are transactions that were not applied against the datafile, then it will recover or synchronize the datafile from either the Online Redo Log files or the Archive Log files. The smaller Online Redo log files will bring a faster database recovery.

Q: Describe the Program Monitor Process Job.
A: A user may be disconnected either by canceling its session or by communication link. In either act, the PMON will start and perform an action to clean the reminding memory allocation that was assigned to the user.

Q: What are the differences between the SPFILE and PFILE startup?
A: You can read or change the init.ora file (PFILE) for startup of the Oracle database. It contains all Oracle parameters file to configure a database instance. In this file, you can reset and change the Buffer Cache size, Shared Pool size, Redo Log Buffer size, etc. You also can change the location of your control files, mode of a database such as archivelog mode or noarchivelog mode, and many other parameter options that you will learn them in the course of this book.
But using Server Parameter File-SPFILE, you can not read the file. It is in a binary format. If you want to change database parameters dynamically, you should create the Server Parameter file (CREATE SPFILE FROM PFILE ) and startup your database using the SPFILE file. There are some few parameters that you still need to shutdown and startup the database, if you want to make the parameter in effect.

Q: What is the controlfile?
A: You cannot read this file and it is in a binary format. If you want to see the content of control file or the layout of your database, you should use the ALTER DATABASE BACKUP CONTROLFILE TO TRACE statement. It writes a trace file into the %ORACLE_BASE\admin\\UDUMP directory.

Q: How do you backup your database controlfiles?
A: SQL> ALTER DATABASE BACKUP CONTROLFILE TO c:\ctnlrfl.bk;

Q: What does a controlfile contain?
A: It contains information the structure of your database layout, database name, last System Change Number (SCN) number, your database mode (archivelog mode or noarchivelog mode), maximum number of log files, maximum number of log members, maximum number of instances, maximum of number of datafiles, the location of the database Online Redo Log files , and backup information.

Q: Describe the password file.
A: The password file is an external file that contains the password of sysdba or sysoper. To use the password file you should set the REMOTE_LOGIN_PASSWORD parameter to exclusive or shared mode in the Parameter File (Example: REMOTE_LOGIN_PASSWORD=EXCLUSIVE).

Q: How do you create a password file?
A: To create the password file, you should run the ORAPWD utility from operating system.
For example:
MS-DOS> ORAPWD FILE=%ORACLE_HOME\dbs\orapw.pwd \
PASSWORD=mypass ENTRIES=3
The ENTRIES parameter specifying the number of user entries allowed for the password file. Now, the DBA can be connected to the database as a user with sysdba privilege.

Q: Describe the Online Redo Log file.
A: The Online Redo Log files hold the Redo Entries. You should have at least two or more Redo Log Groups. Each group may have more than one member. It is a good practice to multiplex Online Redo Log members. The Redo Entries are information that will be used to reconstruct, or redo, changes made to a database. The Log Writer writes the entries into the Online Redo Log files when a COMMIT occurs, every 3 seconds, or when one third of the Redo Log Buffer is full. That will guarantee a database recovery to a point of failure if an Oracle database failure occurred.

Q: How do you start up an instance with the MOUNT option?
A: SQL> CONNECT / AS SYSDBA
SQL> STARTUP MOUNT
--OR--
SQL> STARTUP NOMOUNT
SQL> ALTER DATABASE MOUNT;

Q: Describe the IMMEDIATE option in the SHUTDOWN command.
A: The IMMEDIATE option means not to wait for a user to log off and roll back uncommitted transactions, then shut down the instance and close the database.
Q: Describe the ABORT option in the SHUTDOWN command.
A: The ABORT option tells Oracle not to wait for a user and do not roll back for any transaction and shutdown the instance. If you SHUTDOWN with the ABORT option and then you start your database, the SMON will perform an instance recovery automatically.

Q: Describe the PFILE parameter in the STARTUP command.
A: It tells the Oracle to use the specific parameter file that is in the PFILE parameter.

Q: What does the following SQL statement?
SQL> ALTER DATABASE BACKUP CONTROLFILE TO 'c:\backupcontrolfile\control_ddmmyyyy.ctl' /A: It will backup a controlfile.

Q: What is the ALERT file in an Oracle database?
A: It is a log file that any unknown problems with the database such as, not enough space in the rollback segment or the maximum extent reached in a table.
Q: How many different types of database mode can you change your database to?
A: Six! ARCHIVELOG, NOARCHIVELOG, SUSPEND, RESUME, RESTRICTED SESSION, and QUIESCE RESTRICTED mode.

Q: What does the following statement do?
SQL> CREATE SPFILE
FROM
PFILE='%ORACLE_HOME%\admin\school\pfile\init.ora' /A: It creates a Server Parameter File using an specific parameter file.

Q: How do you configure a database to an archive mode?
A: First you should change the following parameters in the parameter file.
log_archive_dest = /u01/app/oracle/admin//arch
log_archive_start = true
log_archive_format = log%s.arc
Then do the following in the SQLPLUS utility.
SQL> SHUTDOWN IMMEDIATE SQL> STARTUP MOUNT
PFILE=%ORACLE_HOME%\admin\school\pfile\init.ora SQL> ALTER DATABASE ARCHIVELOG ; SQL> ALTER DATABASE OPEN;
Q: What does the following SQL statement?
SQL> ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\SCHOOL\REDO04.LOG' TO
'C:\ORACLE\ORADATA\redo04a.log' /
A: Assuming that Online Redo Log file is offline, it relocates it to different location.

Q: What are the differences between an Oracle-Managed and User-Managed files?
A: A User-Managed file will be defined by an Oracle user. If you drop the tablespace that was using the file, you should physically delete it from the disk. But an Oracle-Managed file will be created and defined by Oracle. If you drop the tablespace that was using the file, oracle will physically deletes the file from the disk. It knows where the file is located.

Q: How do you maintain a tablespace using the Oracle-Managed file technique?
A: You should tell Oracle where it should locate and create datafiles.
SQL> ALTER SYSTEM SET db_create_file_dest='c:\newfolder'; Q: What does the following SQL statement do?
SQL> CREATE TEMPORARY TABLESPACE mytemp TEMPFILE 'mytemp_01.tmp' SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M /
A: It creates locally managed temporary tablespace with uniform size option.
Q: What are the PCTFREE and PCTUSED space allocations in the CREATE TABLE statement?
A: The PCTFREE parameter means that an Oracle user can add records to a block until the unused space block reaches to the PCTFREE value. When a block uses all space up to the “1-PCTFREE” percentage, it stops adding records to the block. Oracle takes that block out of the Freelist. It means that records can not be added to the block any more unless you delete records from the block till it reaches to the PCTUSED value. Then Oracle will add the block in the Freelist again and records can be added to the block. And this process continues to determine when to add records in or stop adding records from the block.

Q: How do you create an UNDO tablespace?
A: SQL> CREATE UNDO TABLESPACE my_undo_tablespace DATAFILE SIZE 100K /
Q: What are the differences between a DICTIONARY-MANAGED and LOCALLY managed tablespace?
A: The difference between the DICTIONALY-MANAGED and LOCALLY MANAGED tablespace is: In the locally managed tablespace all information about the datafiles such as the last performed checkpoint, etc are stored locally in the datafiles of the tablespace but in the DICTIONAY-MANAGED tablespace all such information would be stored in the Oracle repository in the SYSTEM tablespace.

Q: How do you create a TRANSACTION temporary table?
A: 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 /
Q: What are the differences between a row migration and chained record?
A: A “Chained Record” happens when a user updates a record and the new value can not fit in the existing allocated location. So, Oracle stores the value in a space that allocated for them (PCTFREE) and add a pointer to the row so that it knows where the rest of the record is. This process calls row Chaining. Now, if there was no space in the PCTFREE area, then Oracle will take the whole record and migrate it in a different block that has space. This process calls row Migration.

Q: How do you monitor a usage of an index table?
A: SQL> ALTER INDEX uk_emp MONITORING USAGE /
Q: What does the EXCEPTIONS INTO EXCEPTIONS clause perform in the ALTER TABLE statement?
A: It will insert all exceptions that were found during the ALTER TABLE command into the EXCEPTIONS table.

Q: How do you create a user account?
A: 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 /
Q: How do you assign a default tablespace to a user?
A: SQL> ALTER USER developer
DEFAULT TABLESPACE iself_data
/

Q: How do you lock a user?
A: SQL> ALTER USER DEVELOPER ACCOUNT LOCK /
Q: What are the Oracle database triggers?
A: The Oracle database triggers are triggers that will occur when an Oracle event happens either by the DML statements such as UPDATE, DELETE , INSERT, etc; the DDL statements such as DROP, CREATE, etc; the Database events such as SHUTDOWN, STARTUP, etc; or events in a schema such as dropping a table in an specific schema.

Q: How do you start or stop auditing?
A: SQL> ALTER SYSTEM SET audit_trail=db SCOPE=spfile /and you should shutdown and startup the database.

Then start auditing by using the AUDIT command.
Ex: SQL> AUDIT DELETE ON iself.emp BY ACCESS WHENEVER SUCCESSFUL /

And stop auditing by using the NOAUDIT command.
Ex: SQL> NOAUDIT ALL /
Q: What is a cluster table in the Oracle database?
A: A cluster is a schema object that contains one or more tables that all have one or more columns in common. Rows of one or more tables that share the same value in these common columns are physically stored together within the database. Generally, you should only cluster tables that are frequently joined on the cluster key columns in SQL statements. Clustering multiple tables improves the performance of joins, but it is likely to reduce the performance of full table scans, INSERT statements, and UPDATE statements that modify cluster key values.

Q: What are the differences between the Library Cache and Dictionary Cache?
A: The Library Cache holds user’s SQL statements, PL/SQL programs, but the Dictionary Cache holds only repository information such as user’s table name, its access privileges, and etc.

Q: What is the Redo Log Buffer in SGA?
A: The Redo Log Buffer holds users’ entries such as INSERT, UPDATE, DELETE, etc (DML) and CREATE TABLE, DROP TABLE (DDL). The Redo Entries are information that will be used to reconstruct, or redo, changes made to a database. The Log Writer writes the entries into the Online Redo Log files when a COMMIT occurs, every 3 seconds, or when one third of the Redo Log Buffer is full. That will guarantee a database recovery to a point of failure if an Oracle database failure occurred.

Q: Describe the Large Pool component in SGA.
A: The Large Pool holds information about the Recovery Manager (RMAN) utility when RMAN is running. If you use the Multi-threaded Server (MTS) process, you may allocate the Oracle Memory structure such that you can get advantage of using Large Pool instead of the Shared Pool

ORACLE DBA QUESTIONS AND ANSWERS.....

Q: What are the Oracle Architectural components?
A: The Oracle Architectural components are:
Memory (SGA) such as Buffer Cache, Shared Pool, Redo Log Buffer, Large Pool, Java Pool, etc.
Background Processes such as Log Writer, DB Writer, Checkpoint, Archiver, SMON, etc.
Oracle Physical Layout such as Datafiles, Controlfiles, Online Redo log files, Parameter file, Password file, etc.

Q: What are the Oracle Memory Components?
A: All components such as Shared Pool (Library Cache , Dictionary Cache ), Buffer Cache , Online Redo Log file , Large Pool , Java Pool as well as a few other items are referred to as the System Global Area (SGA ). And the place stores information like bind variable values , sort areas, cursor handling, etc for a specific user is called Program Global Area (PGA ). The PGA is used to store only real values in place of bind variables for executing SQL statements. The combination of these two memories structure while they are running is called Oracle Instance.

Q: What is the Server Parameter File?
A: The Server Parameter File is a binary file and Oracle uses it to change the most of its system parameters dynamically.

Q: What is the Parameter File?
A: The Parameter file is a configuration file and it contains all the Oracle instance and database configuration parameters. When you change any parameter using this file, you should shutdown and startup the Oracle Database.

Q: How do you use the init.ora file?
A: The init.ora file is called initialized or parameter file. It is a configuration file.

Q: What is the System Global Area (SGA)?
A: The SGA contains of Shared Pool (Library Cache , Dictionary Cache ), Buffer Cache , Online Redo Log file , Large Pool , Java Pool as well as a few other items.

Q: What is the Shared Pool in SGA?
A: The Shared Pool contains the Library Cache and the Dictionary Cache as well as a few other items, which are not in the scope of this section. The Library Cache holds all users’ SQL statements, Functions, Procedures, and Packages. It stores parsed SQL statement with its execution plan for reuse. The Dictionary Cache, sometimes also referred to as the Row Cache , holds the Oracle repository data information such as tables, indexes, and columns definitions, usernames, passwords, synonyms, views, procedures, functions, packages, and privileges information.

Q: What does the Buffer Cache hold in SGA?
A: The Buffer Cache holds users’ data. Users query their data while they are in the Buffer Cache. If user’s request is not in the Buffer Cache then server process has to bring it from disk. The smallest unit in the buffer cache is an Oracle block. The buffer cache can be increased or decreased by granule unit. The smallest Granule Unit is 4Meg if the SGA size is less than 128Meg and the smallest Granule Unit become 16Meg is the SGA size is more than 128Meg.

. Notice that when you use dedicated servers, user session information is housed in the PGA .

Q: Describe the Multi-threaded Server process.
A: The Multi-threaded Server process will be used when a user send his/her request by using a shared server. A user’s request will be assigned to a dispatcher based on the availability of dispatchers. Then the dispatcher will send or receive request from an assigned shared server.

Q: What are PGA and UGA?
A: When you are running dedicated servers then the session information can be stored inside the process global area (PGA). The UGA is the user global area, which holds session-based information. When you are running shared servers then the session information can be stored inside the user global area (UGA).

Q: Describe the log writer background process (LGWR).
A: The LGWR’s job is to write the redo user’s entries from the Redo Log Buffer .

Q: How often does LGWR write user’s entries to the Online Redo Log Buffer files?
A: It writes user’s entries when the buffer exceeds one third of the Redo Log Buffer, every 3 seconds, or when a user executes the commit SQL statement.

Q: Describe the Checkpoint process.
A: The Checkpoint signals DB writers to write all dirty blocks into the disk. The Checkpoint will occurred either by a specific defined time, size of the Online Redo Log file used by DBA, or when an Online Redo log file will be switched from on log file to another.

Q: How do you automatically force the Oracle to perform a checkpoint?
A: The following are the parameters that will be used by a DBA to adjust time or interval of how frequently its checkpoint should occur on its database.
LOG_CHECKPOINT_TIMEOUT = 3600 # every one hour
LOG_CHECKPOINT_INTERVAL =1000 # number of OS blocks

Q: What is the Recovery Process?
A: The RECO will be used only if you have a distributed database. You use this process to recover a database if a failure occurs due to physical server problem or communication problem.

Q: What is the Lock Background Process?
A: The LCKn background process will be used if you have multiple instances accessing to only one database. An example of that is a Parallel Server or a Real Application Clusters.

Q: How does the Archive Process work?
A: This background process archives the Online Redo Log file when you are manually or automatically switching an Online Redo Log file. An example of manually switching is: ALTER SYSTEM SWITCH LOGFILE or ALTER SYSTEM ARCHIVE LOG CURRENT.

Q: How do you configure your database to do an automatic archiving?
A: SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

Q: What is the System Monitor Process?
A: The SMON job is: when you start your database, it will make sure that all datafiles, controlfiles, and log files are synchronized before opening a database. If they are no, it will perform an instance recovery. It will check the last SCN that was performed against the datafiles. If it finds that there are transactions that were not applied against the datafile, then it will recover or synchronize the datafile from either the Online Redo Log files or the Archive Log files. The smaller Online Redo log files will bring a faster database recovery.

Q: Describe the Program Monitor Process Job.
A: A user may be disconnected either by canceling its session or by communication link. In either act, the PMON will start and perform an action to clean the reminding memory allocation that was assigned to the user.

Q: What are the differences between the SPFILE and PFILE startup?
A: You can read or change the init.ora file (PFILE) for startup of the Oracle database. It contains all Oracle parameters file to configure a database instance. In this file, you can reset and change the Buffer Cache size, Shared Pool size, Redo Log Buffer size, etc. You also can change the location of your control files, mode of a database such as archivelog mode or noarchivelog mode, and many other parameter options that you will learn them in the course of this book.
But using Server Parameter File-SPFILE, you can not read the file. It is in a binary format. If you want to change database parameters dynamically, you should create the Server Parameter file (CREATE SPFILE FROM PFILE ) and startup your database using the SPFILE file. There are some few parameters that you still need to shutdown and startup the database, if you want to make the parameter in effect.

Q: What is the controlfile?
A: You cannot read this file and it is in a binary format. If you want to see the content of control file or the layout of your database, you should use the ALTER DATABASE BACKUP CONTROLFILE TO TRACE statement. It writes a trace file into the %ORACLE_BASE\admin\\UDUMP directory.

Q: How do you backup your database controlfiles?
A: SQL> ALTER DATABASE BACKUP CONTROLFILE TO c:\ctnlrfl.bk;

Q: What does a controlfile contain?
A: It contains information the structure of your database layout, database name, last System Change Number (SCN) number, your database mode (archivelog mode or noarchivelog mode), maximum number of log files, maximum number of log members, maximum number of instances, maximum of number of datafiles, the location of the database Online Redo Log files , and backup information.

Q: Describe the password file.
A: The password file is an external file that contains the password of sysdba or sysoper. To use the password file you should set the REMOTE_LOGIN_PASSWORD parameter to exclusive or shared mode in the Parameter File (Example: REMOTE_LOGIN_PASSWORD=EXCLUSIVE).

Q: How do you create a password file?
A: To create the password file, you should run the ORAPWD utility from operating system.
For example:
MS-DOS> ORAPWD FILE=%ORACLE_HOME\dbs\orapw.pwd \
PASSWORD=mypass ENTRIES=3
The ENTRIES parameter specifying the number of user entries allowed for the password file. Now, the DBA can be connected to the database as a user with sysdba privilege.

Q: Describe the Online Redo Log file.
A: The Online Redo Log files hold the Redo Entries. You should have at least two or more Redo Log Groups. Each group may have more than one member. It is a good practice to multiplex Online Redo Log members. The Redo Entries are information that will be used to reconstruct, or redo, changes made to a database. The Log Writer writes the entries into the Online Redo Log files when a COMMIT occurs, every 3 seconds, or when one third of the Redo Log Buffer is full. That will guarantee a database recovery to a point of failure if an Oracle database failure occurred.

Q: How do you start up an instance with the MOUNT option?
A: SQL> CONNECT / AS SYSDBA
SQL> STARTUP MOUNT
--OR--
SQL> STARTUP NOMOUNT
SQL> ALTER DATABASE MOUNT;

Q: Describe the IMMEDIATE option in the SHUTDOWN command.
A: The IMMEDIATE option means not to wait for a user to log off and roll back uncommitted transactions, then shut down the instance and close the database.
Q: Describe the ABORT option in the SHUTDOWN command.
A: The ABORT option tells Oracle not to wait for a user and do not roll back for any transaction and shutdown the instance. If you SHUTDOWN with the ABORT option and then you start your database, the SMON will perform an instance recovery automatically.

Q: Describe the PFILE parameter in the STARTUP command.
A: It tells the Oracle to use the specific parameter file that is in the PFILE parameter.

Q: What does the following SQL statement?
SQL> ALTER DATABASE BACKUP CONTROLFILE TO 'c:\backupcontrolfile\control_ddmmyyyy.ctl' /A: It will backup a controlfile.

Q: What is the ALERT file in an Oracle database?
A: It is a log file that any unknown problems with the database such as, not enough space in the rollback segment or the maximum extent reached in a table.
Q: How many different types of database mode can you change your database to?
A: Six! ARCHIVELOG, NOARCHIVELOG, SUSPEND, RESUME, RESTRICTED SESSION, and QUIESCE RESTRICTED mode.

Q: What does the following statement do?
SQL> CREATE SPFILE
FROM
PFILE='%ORACLE_HOME%\admin\school\pfile\init.ora' /A: It creates a Server Parameter File using an specific parameter file.

Q: How do you configure a database to an archive mode?
A: First you should change the following parameters in the parameter file.
log_archive_dest = /u01/app/oracle/admin//arch
log_archive_start = true
log_archive_format = log%s.arc
Then do the following in the SQLPLUS utility.
SQL> SHUTDOWN IMMEDIATE SQL> STARTUP MOUNT
PFILE=%ORACLE_HOME%\admin\school\pfile\init.ora SQL> ALTER DATABASE ARCHIVELOG ; SQL> ALTER DATABASE OPEN;
Q: What does the following SQL statement?
SQL> ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\SCHOOL\REDO04.LOG' TO
'C:\ORACLE\ORADATA\redo04a.log' /
A: Assuming that Online Redo Log file is offline, it relocates it to different location.

Q: What are the differences between an Oracle-Managed and User-Managed files?
A: A User-Managed file will be defined by an Oracle user. If you drop the tablespace that was using the file, you should physically delete it from the disk. But an Oracle-Managed file will be created and defined by Oracle. If you drop the tablespace that was using the file, oracle will physically deletes the file from the disk. It knows where the file is located.

Q: How do you maintain a tablespace using the Oracle-Managed file technique?
A: You should tell Oracle where it should locate and create datafiles.
SQL> ALTER SYSTEM SET db_create_file_dest='c:\newfolder'; Q: What does the following SQL statement do?
SQL> CREATE TEMPORARY TABLESPACE mytemp TEMPFILE 'mytemp_01.tmp' SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M /
A: It creates locally managed temporary tablespace with uniform size option.
Q: What are the PCTFREE and PCTUSED space allocations in the CREATE TABLE statement?
A: The PCTFREE parameter means that an Oracle user can add records to a block until the unused space block reaches to the PCTFREE value. When a block uses all space up to the “1-PCTFREE” percentage, it stops adding records to the block. Oracle takes that block out of the Freelist. It means that records can not be added to the block any more unless you delete records from the block till it reaches to the PCTUSED value. Then Oracle will add the block in the Freelist again and records can be added to the block. And this process continues to determine when to add records in or stop adding records from the block.

Q: How do you create an UNDO tablespace?
A: SQL> CREATE UNDO TABLESPACE my_undo_tablespace DATAFILE SIZE 100K /
Q: What are the differences between a DICTIONARY-MANAGED and LOCALLY managed tablespace?
A: The difference between the DICTIONALY-MANAGED and LOCALLY MANAGED tablespace is: In the locally managed tablespace all information about the datafiles such as the last performed checkpoint, etc are stored locally in the datafiles of the tablespace but in the DICTIONAY-MANAGED tablespace all such information would be stored in the Oracle repository in the SYSTEM tablespace.

Q: How do you create a TRANSACTION temporary table?
A: 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 /
Q: What are the differences between a row migration and chained record?
A: A “Chained Record” happens when a user updates a record and the new value can not fit in the existing allocated location. So, Oracle stores the value in a space that allocated for them (PCTFREE) and add a pointer to the row so that it knows where the rest of the record is. This process calls row Chaining. Now, if there was no space in the PCTFREE area, then Oracle will take the whole record and migrate it in a different block that has space. This process calls row Migration.

Q: How do you monitor a usage of an index table?
A: SQL> ALTER INDEX uk_emp MONITORING USAGE /
Q: What does the EXCEPTIONS INTO EXCEPTIONS clause perform in the ALTER TABLE statement?
A: It will insert all exceptions that were found during the ALTER TABLE command into the EXCEPTIONS table.

Q: How do you create a user account?
A: 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 /
Q: How do you assign a default tablespace to a user?
A: SQL> ALTER USER developer
DEFAULT TABLESPACE iself_data
/

Q: How do you lock a user?
A: SQL> ALTER USER DEVELOPER ACCOUNT LOCK /
Q: What are the Oracle database triggers?
A: The Oracle database triggers are triggers that will occur when an Oracle event happens either by the DML statements such as UPDATE, DELETE , INSERT, etc; the DDL statements such as DROP, CREATE, etc; the Database events such as SHUTDOWN, STARTUP, etc; or events in a schema such as dropping a table in an specific schema.

Q: How do you start or stop auditing?
A: SQL> ALTER SYSTEM SET audit_trail=db SCOPE=spfile /and you should shutdown and startup the database.

Then start auditing by using the AUDIT command.
Ex: SQL> AUDIT DELETE ON iself.emp BY ACCESS WHENEVER SUCCESSFUL /

And stop auditing by using the NOAUDIT command.
Ex: SQL> NOAUDIT ALL /
Q: What is a cluster table in the Oracle database?
A: A cluster is a schema object that contains one or more tables that all have one or more columns in common. Rows of one or more tables that share the same value in these common columns are physically stored together within the database. Generally, you should only cluster tables that are frequently joined on the cluster key columns in SQL statements. Clustering multiple tables improves the performance of joins, but it is likely to reduce the performance of full table scans, INSERT statements, and UPDATE statements that modify cluster key values.

Q: What are the differences between the Library Cache and Dictionary Cache?
A: The Library Cache holds user’s SQL statements, PL/SQL programs, but the Dictionary Cache holds only repository information such as user’s table name, its access privileges, and etc.

Q: What is the Redo Log Buffer in SGA?
A: The Redo Log Buffer holds users’ entries such as INSERT, UPDATE, DELETE, etc (DML) and CREATE TABLE, DROP TABLE (DDL). The Redo Entries are information that will be used to reconstruct, or redo, changes made to a database. The Log Writer writes the entries into the Online Redo Log files when a COMMIT occurs, every 3 seconds, or when one third of the Redo Log Buffer is full. That will guarantee a database recovery to a point of failure if an Oracle database failure occurred.

Q: Describe the Large Pool component in SGA.
A: The Large Pool holds information about the Recovery Manager (RMAN) utility when RMAN is running. If you use the Multi-threaded Server (MTS) process, you may allocate the Oracle Memory structure such that you can get advantage of using Large Pool instead of the Shared Pool