Resize/Add/Move/Rename/Drop datafile and temporary datafile in Oracle

Resize a datafile and tempfile:

SQL> alter database datafile '/u10/oradata/TEST/test.tst_tbspace.data.01.dbf' resize 50M;

SQL> alter database tempfile '/u09/oradata/TEST/test.tmp_tbspace.data.01.dbf' resize 50M;

Add a datafile:

SQL> alter tablespace tst_tbspace1 add datafile '/u10/oradata/TEST/test.tst_tbspace.data.02.dbf' size 2000M;

SQL> alter tablespace tmp_tbspace add tempfile '/u09/oradata/TEST/test.tmp_tbspace.data.02.dbf' size 2000M;


Steps to move and/or rename datafile in oracle:

1. Shutdown the database and exit out of the SQL prompt - Database level step

Ex: SQL> shutdown

2. At the unix prompt copy or move the datafile from current location to new location using the OS copy (cp) command - OS level step

Ex: $ mv /u10/oradata/TEST/test.tbspace.data.01.dbf /u01/oradata/TEST/test.tst_tbspace.data.01.dbf

you change the location and also rename the file at a time if you need to.

Ex: $ mv /u10/oradata/TEST/test.tst_tbspace.data.01.dbf /u01/oradata/TEST/test.tst_tbspace.datafile.02.dbf

3. Open a SQL session and startup mount the database - Database level step

Ex: SQL> startup mount

4. Rename the datafile using oracle rename command - Database level step

Ex: SQL> alter database rename file '/u10/oradata/TEST/test.tst_tbspace.data.01.dbf'
                 to '/u01/oradata/TEST/test.tbspace.data.01.dbf';

5. Open the database - Database level step

Ex: SQL> alter database open;

                       (or)

      SQL> shutdown

      SQL> startup

Drop datafile:

SQL>  alter database datafile '/u10/oradata/TEST/test.tst_tbspace.data.01.dbf' offline drop;

(or)

SQL> alter tablespace tst_tbspace drop datafile '/u10/oradata/TEST/test.tst_tbspace.data.01.dbf';

Rename/Copy a Table

A table in oracle can be renamed using the following syntax:

alter table table_name rename to new_table_name;

Ex: SQL> alter table dept rename to hist_dept;

Renaming a table doesn't update the oracle objects such as HTML DB, PL/SQL, Views some of which might be invalid based on their dependancy on the renamed object.

A copy of the original table can be created using the following syntax:

create table new_table_name as select * from table_name;

Ex: SQL> create hist_dept as select * from dept;

we need not necessarily need to include all the columns from the original table we can create a copy based on our choice of columns that we want to be included in the new table. The new table can be created either from one single table or multiple tables. The advantage of using CREATE TABLE....AS SELECT..(CTAS) it will create the new table with datatypes similar to old table(s).

A copy of the original table created including only the columns of our choice from a single table:

create table new_table_name as select column1, column2 from table_name;

Ex: SQL> create hist_dept as select dept_id, dept_name from dept;


A copy of the original table created including only the columns of our choice from a two tables:

create table new_table_name as select t1.column1, t2.column1, t2.column2 from table_name1 t1, table_name2 t2 where t1.column1 = t2.column1;

Ex: SQL> create hist_dept as select d.dept_id, e.emp_id, e.emp_anem from dept d, employee e where
                 d.dept_id = e.dept_id;

Oracle Background Processes

Oracle Background Porcesses: To maximize performance and accommodate many users, a multiprocess Oracle Database system uses background processes. Background processes consolidate functions that would otherwise be handled by multiple database programs running for each user process. Background processes asynchronously perform I/O and monitor other Oracle Database processes to provide increased parallelism for better performance and reliability.

Basic Oracle Background Processes:

• Database writer (DBWn): The database writer writes modified blocks from the database buffer cache to the datafiles. Oracle Database allows a maximum of 20 database writer processes (DBW0-DBW9 and DBWa-DBWj). The DB_WRITER_PROCESSES initialization parameter specifies the number of DBWn processes. The database selects an appropriate default setting for this initialization parameter or adjusts a user-specified setting based on the number of CPUs and the number of processor groups.

• Log writer (LGWR): The log writer process writes redo log entries to disk. Redo log entries are generated in the redo log buffer of the system global area (SGA). LGWR writes the redo log entries sequentially into a redo log file. If the database has a multiplexed redo log, then LGWR writes the redo log entries to a group of redo log files.

• Checkpoint (CKPT): At specific times, all modified database buffers in the system global area are written to the datafiles by DBWn. This event is called a checkpoint. The checkpoint process is responsible for signalling DBWn at checkpoints and updating all the datafiles and control files of the database to indicate the most recent checkpoint.

• System monitor (SMON): The system monitor performs recovery when a failed instance starts up again. In a Real Application Clusters database, the SMON process of one instance can perform instance recovery for other instances that have failed. SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during system failure and instance recovery because of file-read or offline errors. These transactions are eventually recovered by SMON when the tablespace or file is brought back online.

• Process monitor (PMON): The process monitor performs process recovery when a user process fails. PMON is responsible for cleaning up the cache and freeing resources that the process was using. PMON also checks on the dispatcher processes (described later in this table) and server processes and restarts them if they have failed.

• Archiver (ARCn): One or more archiver processes copy the redo log files to archival storage when they are full or a log switch occurs.

• Recoverer (RECO): The recoverer process is used to resolve distributed transactions that are pending because of a network or system failure in a distributed database. At timed intervals, the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions.

• Dispatcher (Dnnn): Dispatchers are optional background processes, present only when the shared server configuration is used.

• Global Cache Service (LMS): In a Real Application Clusters environment, this process manages resources and provides inter-instance resource control.

Source: Oracle Documentation

Oracle Dedicated and Shared Server Processes

Oracle creates server processes whenever a user tries to connect to a database for handling all user requestss. Below are the two ways a server process can be:

• A Dedicated Server Process, which serves only one user or one user connection
• A Shared Server Process, which server multiple users or multiple user connections

Dedicated Server Processes:

Oracle databases are by default configured for dedicated server process and if you need to enable Shared server process you need to configure it manually by setting one or more initialization parameters. However a Dedicated server process is always required  for:

• To submit a batch job (for example, when a job can allow little or no idle time for the server process)
• To use Recovery Manager (RMAN) to back up, restore, or recover a database

To request a dedicated server connection when Oracle Database is configured for shared server, users must connect using a net service name that is configured to use a dedicated server. Specifically, the net service name value should include the SERVER=DEDICATED clause in the connect descriptor.

Figure 1: Oracle Database Dedicated Server Processes

Shared Server Process:

Figure 2: Oracle Database Shared Server Processes

In Shared server configuration a client user process connect a dispatcher and the dispatcher then communicates with the oracle database for client request processing once the processing is done oracle then sends back the output or solution back to the dispatcher which in turn communicates it to the client user process. One dispatcher can serve multiple client user processes by connection pooling which can be enabled for shared server process. Further, shared server can be configured for session multiplexing, which combines multiple sessions for transmission over a single network connection in order to conserve the operating system's resources.

Initialization Parameters for Shared Server:

The following initialization parameters control shared server operation:

• SHARED_SERVERS: Specifies the initial number of shared servers to start and the minimum number of shared servers to keep. This is the only required parameter for using shared servers.
• MAX_SHARED_SERVERS: Specifies the maximum number of shared servers that can run simultaneously.
• SHARED_SERVER_SESSIONS: Specifies the total number of shared server user sessions that can run simultaneously. Setting this parameter enables you to reserve user sessions for dedicated servers.
• DISPATCHERS: Configures dispatcher processes in the shared server architecture.
• MAX_DISPATCHERS: Specifies the maximum number of dispatcher processes that can run simultaneously. This parameter can be ignored for now. It will only be useful in a future release when the number of dispatchers is auto-tuned according to the number of concurrent connections.
• CIRCUITS: Specifies the total number of virtual circuits that are available for inbound and outbound network sessions.

Enabling Shared Server:

Shared server is enabled by setting the SHARED_SERVERS initialization parameter to a value greater than 0. The other shared server initialization parameters need not be set. Because shared server requires at least one dispatcher in order to work, a dispatcher is brought up even if no dispatcher has been configured.

Determining a Value for SHARED_SERVERS and others:

The SHARED_SERVERS initialization parameter specifies the minimum number of shared servers that you want created when the instance is started. After instance startup, Oracle Database can dynamically adjust the number of shared servers based on how busy existing shared servers are and the length of the request queue.
You can limit the number of shared servers that can be created by setting MAX_SHARED_SERVERS parameter specifies the maximum number of shared servers that can be automatically created by PMON. After you set the SHARED_SERVERS parameter oracle decides the number of shares servers that needs to be created based on how busy the system is.

The SHARED_SERVER_SESSIONS initialization parameter specifies the maximum number of concurrent shared server user sessions. Setting this parameter, which is a dynamic parameter, lets you reserve database sessions for dedicated servers.
Configuring Dispatchers:

Ex: This is a typical example of setting the DISPATCHERS initialization parameter.

DISPATCHERS="(PROTOCOL=TCP)(DISPATCHERS=2)"

You can use multiple different protocols for different dispatchers within the same initialization parameter.

Ex: DISPATCHERS='(PROT=tcp)(DISP=5)', '(PROT-tcps)(DISP=3)'

Monitor the following views to determine the load on the dispatcher processes:

• V$QUEUE
• V$DISPATCHER
• V$DISPATCHER_RATE

Shutting down specific Dispatches process:
To identify the name of the specific dispatcher process to shut down, use the V$DISPATCHER dynamic performance view.

SQL> SELECT NAME, NETWORK FROM V$DISPATCHER;

Each dispatcher is uniquely identified by a name of the form Dnnn.To shut down dispatcher D002, issue the following statement:

SQL>ALTER SYSTEM SHUTDOWN IMMEDIATE 'D002';

Monitoring Shared Server:
The following views are useful for obtaining information about your shared server configuration and for monitoring performance.

• V$DISPATCHER: Provides information on the dispatcher processes, including name, network address, status, various usage statistics, and index number.
• V$DISPATCHER_CONFIG: Provides configuration information about the dispatchers.
• V$DISPATCHER_RATE: Provides rate statistics for the dispatcher processes.
• V$QUEUE: Contains information on the shared server message queues.
• V$SHARED_SERVER: Contains information on the shared servers.
• V$CIRCUIT: Contains information about virtual circuits, which are user connections to the database through dispatchers and servers.
• V$SHARED_SERVER_MONITOR: Contains information for tuning shared server.
• V$SGA: Contains size information about various system global area (SGA) groups. May be useful when tuning shared server.
• V$SGASTAT: Contains detailed statistical information about the SGA, useful for tuning.
• V$SHARED_POOL_RESERVED: Lists statistics to help tune the reserved pool and space within the shared pool.

Source: Oracle Documentation

All about startup migrate and startup upgrade

startup migrate:

=> Used to upgrade a database till 9i.
See the step 11 from the 9.2 manual upgrade guide :
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96530/upgrade.htm#1009472


=> Used to downgrade a database till 9i.
See the step 5 from the 9.2 downgrade guide :
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96530/downgrad.htm#248958


=> Used to downgrade a database since 10g.
See the step 18 from the 10.2 downgrade guide :
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14238/downgrade.htm#sthref415


 startup upgrade
=> Used to upgrade a database since 10g.
See the step 7 from the 10.2 manual upgrade guide :
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14238/upgrade.htm#CACGGHJC

Recompiling Invalid Schema Objects

Objects in a database tend to get invalid due to operation such as patches, DDL changes and upgrades etc. The invalid objects are to compiled to ensure proper health of the database and user's access to them. I will provide here the ways you can compile objects in Oracle.

Identifying Invalid Objects:

col format OBJECT_NAME a40

SQL> SELECT OBJECT_NAME,
                            OBJECT_TYPE,
                            OWNER,
                            STATUS
           FROM DBA_OBJECTS
           WHERE STATUS='INVALID'
           ORDER BY OBJECT_NAME, OBJECT_TYPE, OWNER;

to get invalid objects list for a particular schema below is the SQL:

col format OBJECT_NAME a40

SQL> SELECT OBJECT_NAME,
                          OBJECT_TYPE,
                          OWNER,
                          STATUS
           FROM DBA_OBJECTS
          WHERE OWNER='DEPT' AND STATUS='INVALID'
          ORDER BY OBJECT_NAME, OBJECT_TYPE, OWNER;

DBMS_UTILITY.COMPILE_SCHEMA:
DBMS_UTILITY package proides COMPILE_SCHEMA procedure to compile all the objects in a schema:

SQL> EXECUTE DBMS_UTILITY.COMPILE_SCHEMA ('SCHEMA_NAME');

UTLRP and UTLPRP:
UTLRP and UTLPRP are the oracle provided scripts to compile all invalid objects in oracle database.

Ex: SQL> ?/rdbms/admin/utlrp

Manual Approach:
Invalid Objects can be compiled individually after you have the list of invalid objects in your oracle database. Below are some of the examples:

SQL> ALTER PACKAGE package_name COMPILE;

SQL> ALTER PACKAGE package_name COMPILE BODY;

SQL> ALTER PROCEDURE procedure_name COMPILE;

SQL> ALTER FUNCTION function_name COMPILE;

SQL> ALTER TRIGGER trigger_name COMPILE;

SQL> ALTER VIEW view_name COMPILE;


An alternative approach is to use the DBMS_DDL package to perform the recompilations:

SQL> EXEC DBMS_DDL.alter_compile('PACKAGE', 'SCHEMA_NAME', 'PACKAGE_NAME');

SQL> EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'SCHEMA_NAME', 'PACKAGE_NAME');

SQL> EXEC DBMS_DDL.alter_compile('PROCEDURE', 'SCHEMA_NAME', 'PROCEDURE_NAME');

SQL> EXEC DBMS_DDL.alter_compile('FUNCTION', 'SCHEMA_NAME', 'FUNCTION_NAME');

SQL> EXEC DBMS_DDL.alter_compile('TRIGGER', 'SCHEMA_NAME', 'TRIGGER_NAME');
 
In addition to the above approaches you can write your own script to get the invalid objects and recompile them using the manual approach to recompile them all included in the script.

How to get DDL for an object: TABLE, INDEX, PACKAGE....

DBMS_METADATA is a package that can be used to get DDL for TABLE, INDEXES etc. Below is a quick view of how it works:


SQL> select dbms_metadata.get_ddl('TABLE','IDX3_TAB') from dual;

 The output would be:

CREATE TABLE "SCOTT"."IDX3_TAB"
( "NAME" VARCHAR2(30),
"ID" NUMBER,
"ADDR" VARCHAR2(100),
"PHONE" VARCHAR2(30)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"


To get the create table definition without the storage clause you could do as follows:

SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

The output should be PL/SQL procedure successfully completed.


And then if you run

set long 100000
select dbms_metadata.get_ddl('TABLE','IDX3_TAB') from dual;

would return

CREATE TABLE "SCOTT"."IDX3_TAB"
( "NAME" VARCHAR2(30),
"ID" NUMBER,
"ADDR" VARCHAR2(100),
"PHONE" VARCHAR2(30)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING TABLESPACE "USERS"

But the above package wasn't useful when I tried to get DDL information for a package body based on my experience and DBA_SOURCE from into help for that. Below is an example:
 
SQL> SELECT TEXT FROM DBA_SOURCE WHERE NAME='PKG_DEPT_PROCESS';

CURSOR_SHARING Parameter in Oracle

CURSOR_SHARING parameter is the one that oracle uses to control whether it will auto-bind a SQL statement or not.

For example:
SQL> select * from dept where dept_id = 'literal_value';

Oracle takes the above statement and replaces the predicate with something as below:

SQL> select * from dept where dept_id=:"SYS_B_0";

And now the oracle compares the above sql statement to the plans that it already has in the memory to see if the plan can be reused or not, perhaps leading to a better utilization of shared_pool and reduction in number hard parses performed by the system.

The cursor_sharing parameter can be set to three values:

• EXACT: This is the default setting. With this value in place, the query is not rewritten to use bind variables.
• FORCE: This setting rewrites the query, replacing all literals with bind values and setting up a one-size-fits-all plan—a single plan for the rewritten query. I'll demonstrate what that implies in a moment.
• SIMILAR: This setting also rewrites the query, replacing the literals with bind variables, but can set up different plans for different bind variable combinations. This last point is why CURSOR_SHARING=SIMILAR might reduce the number of plans generated. Because multiple plans may be generated, the setting of SIMILAR may or may not reduce the number of actual plans you observe in the shared pool.

An example to show how CURSOR_SHARING works with setting equal to EXACT, FORCE and SIMILAR:

SQL> alter session set cursor_sharing=EXACT;

Session altered.

SQL> select * from dual CS_EXACT where dummy='A';

no rows selected

SQL> select * from dual CS_EXACT where dummy='B';

no rows selected

SQL> alter session set cursor_sharing=FORCE;

Session altered.

SQL> select * from dual CS_FORCE where dummy='A';

no rows selected

SQL> select * from dual CS_FORCE where dummy='B';

no rows selected

SQL> alter session set cursor_sharing=SIMILAR;

Session altered.

SQL> select * from dual CS_SIMILAR where dummy='A';

no rows selected

SQL> select * from dual CS_SIMILAR where dummy='B';

no rows selected

SQL> select sql_text
2 from v$sql
3 where sql_text like 'select * from dual CS% where dummy=%'
4 order by sql_text;

SQL_TEXT
--------------------------------------------------------------------------------
select * from dual CS_EXACT where dummy='A'
select * from dual CS_EXACT where dummy='B'
select * from dual CS_FORCE where dummy=:"SYS_B_0"
select * from dual CS_SIMILAR where dummy=:"SYS_B_0"

CURSOR_SHARING=EXACT: From the above example as we can see oracle uses different plans for each of the select statements which include the word "CS_EXACT". With this setting every SQL statement you excute will be new and a new plan is generated for every query that we execute and the plans are not shared. And a new entry is created in V$SQL as you can see from above for every SQL Statement we execute.

CURSOR_SHARING=FORCE: When this is the setting for the parameter then for each statement that I have executed above which include the word "CS_FORCE" the literal values 'A' and 'B' are replaced by "SYS_B_0". The oracle uses the same plan for each of the SQL whether the predicate is either 'A' or 'B' since rest of the statement is similar and thus we see just one entry in V$SQL for the two SQL that we have executed.

CURSOR_SHARING=SIMILAR: When this is the setting for the parameter then for each statement that I have executed above which include the word "CS_FORCE" the literal values 'A' and 'B' are replaced by "SYS_B_0". The oracle uses the same plan for each of the SQL whether the predicate is either 'A' or 'B' since rest of the statement is similar and thus we see just one entry in V$SQL for the two SQL that we have executed. So, the settings FORCE and SIMILAR looks similar huh? But there is one difference between both i.e., when the setting is SIMILAR oracle not only checks for similar looking statements but also compares the plans. For example when I executed the SELECT statement with the literal value 'A' it generates a plan which will say a FULL TABLE SCAN for the statement. Now when I executed the same SELECT statement with the literal value 'B' the it also generates a plan for the statement and compares to the plan that is already stored i.e., when literal value is 'A'. If the explain plan for the SELECT statement when literal is 'A' a FULL TABLE SCAN and also a FULL TABLE SCAN (lets assume for now) when the literal value is 'B' then we see only one entry in the dynamic view V$SQL. If both the plans are different say a FULL TABLE SCAN when literal value is 'A' and an INDEX SCAN when literal value is 'B' which is not in this case as per our assumption then we will see two different entries in V$SQL for the SQL statements which contain the word "CS_SIMILAR" and you would see something as below (which is not with our example above): The results displayed shown below are with respect to an imaginary table "t" which is a big table of about atleast 100 rows and has an index on it and the data is skewed

SQL> alter session set cursor_sharing=FORCE;

Session altered.

SQL> select * from t CS_FORCE where t_id=1;

1 row selected.

SQL> select * from t CS_FORCE where t_id='99';

1 row selected.

SQL> alter session set cursor_sharing=SIMILAR;

Session altered.

SQL> select * from t CS_SIMILAR where t_id='1';

1 row selected.

SQL> select * from t CS_SIMILAR where t_id='99';

1 row selected.

SQL> select sql_text

2 from v$sql
3 where sql_text like 'select * from t CS% where t_id=%'
4 order by sql_text;

SQL_TEXT
--------------------------------------------------------------------------------
select * from t CS_FORCE where t_id=:"SYS_B_0"
select * from t CS_SIMILAR where t_id=:"SYS_B_0"
select * from t CS_SIMILAR where t_id=:"SYS_B_0"

From the above example we can see that the plans are different say a FULL TABLE SCAN when literal value is '1' and an INDEX SCAN when literal value is '99' we see two different entries in V$SQL for the SQL statements which contain the word "CS_SIMILAR".

Database Structures: Logical Structures

Oracle database logical structures mainly comprise of Tablespaces, Segements, Extents and Oracle Datablocks.

I will present the information starting with the finest logical structure Oracle Datablocks.

Oracle Datablocks: Oracle datablocks are at the finest level granularity, all of the oracle datatbase data is stored in oracle datablocks. One oracle datablock corresponds to specific number of bytes which occupy the same number of bytes on the physical disk space. The size of a datablock is determined by the initialization parameter DB_BLOCK_SIZE. In addition to the one specified already you can specify upto 5 additional datablock sizes

Extent: Extent is the next level of oracle logical database space. A extent is comprised specific number of contiguous datablocks, obtained in a single allocation, used to store a specific type of information.

Segment: A segment is a set of extents allocated for a certain logical structure. The segments can be of one of following type data segment,index segment,temporary segment,rollback segment.

Tablespace: Each database is logically divided into one or more tablespaces. One or more datafiles are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace. The combined size of the datafiles in a tablespace is the total storage capacity of the tablespace.

Database Structures: Physical Structures

A database consists of Physical Structures and Logical Structures in this post I will post information regarding the physical structures in simple terms:

Datafiles: Contain all of the database data; logical structures , such as tables, indexes, packages, procedures, functions, triggers and etc.

Redo Log Files: Hold records of all changes made to the database for recovery purposes.

Control Files: Record the physical structure and status of the database

Parameter File: Contain startup values for database parameters (often referred as the init.ora file )


Note: The explanation provided here is for understanding only.