INIT PARAMETERS NEEDED FOR RAC

INIT PARAMETERS NEEDED FOR RAC

The following Init parameters are needed in a RAC database.

1)  active_instance_count:-

Property	Description
Parameter type	Integer
Default value	There is no default value.
Modifiable	No
Range of values	1 or >= the number of instances in the cluster. (Values other than 1 have no effect on the active or standby status of any instances.)
Basic	No
Real Application Clusters	You must set this parameter for every instance, and multiple instances must have the same value.

ACTIVE_INSTANCE_COUNT enables you to designate one instance in a two-instance cluster as the primary instance and the other instance as the secondary instance. This parameter has no functionality in a cluster with more than two instances.

When you set this parameter to 1, the first instance you start up becomes the primary instance and accepts client connections. The second instance starts up as a secondary instance and can accept client connections only if the first instance fails. In such an event, the secondary instance becomes the primary instance.

When the failed instance can once again be started up, it starts up as the secondary instance, and will not accept client connections unless the current primary instance fails.

2)      cluster_database:-

Property	Description
Parameter type	Boolean
Default value	false
Modifiable	No
Range of values	true | false
Basic	Yes
Real Application Clusters	For all instances, the value must be set to TRUE.

CLUSTER_DATABASE is a Real Application Clusters parameter that specifies whether or not Real Application Clusters is enabled.

3)      cluster_database_instances:-

Property	Description
Parameter type	Integer
Default value	1
Modifiable	No
Range of values	Any nonzero value
Basic	No
Real Application Clusters	Multiple instances must have the same value.

CLUSTER_DATABASE_INSTANCES is a Real Application Clusters parameter that specifies the number of instances that are configured as part of your cluster database. You must set this parameter for every instance. Normally you should set this parameter to the number of instances in your Real Application Clusters environment. A proper setting for this parameter can improve memory use.

Oracle uses the value of this parameter to compute the default value of the LARGE_POOL_SIZE parameter when the PARALLEL_AUTOMATIC_TUNING parameter is set to true. Note that the PARALLEL_AUTOMATIC_TUNIG parameter has been deprecated.

4)      cluster_interconnects:-

Property	Description
Parameter type	String
Syntax	CLUSTER_INTERCONNECTS = ifn [: ifn ] ...
Default value	There is no default value.
Modifiable	No
Range of values	One or more IP addresses, separated by colons
Basic	No

CLUSTER_INTERCONNECTS provides Oracle with information about additional cluster interconnects available for use in Real Application Clusters environments.

The CLUSTER_INTERCONNECTS parameter can be used to override the default interconnect with a preferred cluster traffic network. This parameter is useful in Data Warehouse systems that have reduced availability requirements and high interconnect bandwidth demands. You can also use CLUSTER_INTERCONNECTS to override the default interconnect chosen by Oracle.

For example, if you are running two instances of Oracle for two databases on the same machine, then you can load balance the interconnect traffic to different physical interconnects. This does not reduce Oracle availability.

CLUSTER_INTERCONNECTS can be used in Oracle Real Application Clusters environments to indicate cluster interconnects available for use for the database traffic. Use this parameter if you need to override the default interconnect configured for the database traffic, which is stored in the cluster registry. This procedure also may be useful with Data Warehouse systems that have reduced availability requirements and high interconnect bandwidth demands.

CLUSTER_INTERCONNECTS specifically overrides the following:

• Network classifications stored by oifcfg in the OCR.
• The default interconnect chosen by Oracle.

If you want to load-balance the interconnect, then Oracle recommends that you use link-bonding at the operating system level, even if you have two databases on the same server, so that multiple interconnects use the same address. Note that multiple private addresses provide load balancing, but do not provide failover unless bonded. If you specify multiple addresses in init.ora using CLUSTER_INTERCONNECTS, instead of bonding multiple addresses at the operating system level, then typically availability is reduced, because each network interface card failure will take down that instance.

Refer to your vendor documentation for information about bonding interfaces. Some vendor bonding architectures may require the use of this parameter.

If you have multiple database instances on Oracle Real Application Clusters nodes and want to use a specific interface for each instance, then you can set theCLUSTER_INTERCONNECTS initialization parameter to the IP address for each database instance. For example:

hr1.init.ora.cluster_interconnects="192.0.2.111"

oltp3.init.ora.cluster_interconnects="192.0.2.112"

If the Oracle RAC interconnect is configured to run on a different interface than the Oracle Clusterware interconnect, then this configuration can cause reduced availability, as failovers or instance evictions can be delayed if the Oracle RAC interconnect fails while the Oracle Clusterware NIC remains up.

5)      instance_groups:-

Property	Description
Parameter type	String
Syntax	INSTANCE_GROUPS = group_name [, group_name ] ...
Default value	There is no default value.
Modifiable	No
Range of values	One or more instance group names, separated by commas
Basic	No
Real Application Clusters	Multiple instances can have different values.

INSTANCE_GROUPS is a Real Application Clusters parameter that you can specify only in parallel mode. Used in conjunction with the PARALLEL_INSTANCE_GROUP parameter, it lets you restrict parallel query operations to a limited number of instances.

This parameter specifies one or more instance groups and assigns the current instance to those groups. If one of the specified groups is also specified in the PARALLEL_INSTANCE_GROUP parameter, then Oracle allocates query processes for a parallel operation from this instance.

6)      instance_number:-

Property	Description
Parameter type	Integer
Default value	Lowest available number; derived from instance start up order and INSTANCE_NUMBER value of other instances. If not configured for Real Application Clusters, then 0.
Modifiable	No
Range of values	1 to maximum number of instances specified when the database was created
Basic	Yes
Real Application Clusters	You must set this parameter for every instance, and all instances must have different values.

INSTANCE_NUMBER is a Real Application Clusters parameter that can be specified in parallel mode or exclusive mode. It specifies a unique number that maps the instance to one free list group for each database object created with storage parameter FREELIST GROUPS.

The INSTANCE parameter of the ALTER TABLE ... ALLOCATE EXTENT statement assigns an extent to a particular free list group. If you set INSTANCE_NUMBER to the value specified for the INSTANCE parameter, the instance uses that extent for inserts and for updates that expand rows.

The practical maximum value of this parameter is the maximum number of instances specified in the CREATE DATABASE statement. The absolute maximum is operating system-dependent.

7)      lock_name_space:-

Property	Description
Parameter type	String
Syntax	LOCK_NAME_SPACE = namespace
Default value	There is no default value.
Modifiable	No
Range of values	Up to 8 alphanumeric characters. No special characters allowed.
Basic	No

LOCK_NAME_SPACE specifies the namespace that the distributed lock manager (DLM) uses to generate lock names. Consider setting this parameter if a standby or clone database has the same database name on the same cluster as the primary database.

If the standby database resides on the same file system as the primary database, then set LOCK_NAME_SPACE in the standby parameter file to a distinct value such as the following:

LOCK_NAME_SPACE = standby

8)      parallel_instance_group:-

Property	Description
Parameter type	String
Syntax	PARALLEL_INSTANCE_GROUP = group_name
Default value	A group consisting of all instances currently active
Modifiable	ALTER SESSION, ALTER SYSTEM
Range of values	Any group name specified in the INSTANCE_GROUPS parameter of any active instance
Real Application Clusters	Multiple instances can have different values.

PARALLEL_INSTANCE_GROUP is a Real Application Clusters parameter that you can specify in parallel mode only. Used in conjunction with the INSTANCE_GROUPS parameter, it lets you restrict parallel query operations to a limited number of instances.

This parameter identifies the parallel instance group Oracle will use for spawning parallel execution processes. Parallel operations will spawn parallel execution processes only on instances that specify a matching group in their INSTANCE_GROUPS parameter.

If the value of PARALLEL_INSTANCE_GROUP does not correspond to an instance group name specified for an active instance, Oracle returns an error.

9)      parallel_server:-

Parameter type:	Boolean
Parameter class:	Static
Default value:	FALSE
Range of values:	TRUE | FALSE
Oracle Parallel Server:	Multiple instances must have the same value.

PARALLEL_SERVER is an Oracle Parallel Server parameter that specifies whether Oracle Parallel Server is enabled. 

10)  parallel_server_instances:-

Parameter type:	Integer
Parameter class:	Static
Default value:	1
Range of values:	Any non-zero value

PARALLEL_SERVER_INSTANCES is an Oracle Parallel Server parameter that specifies the number of instances currently configured. You must set this parameter for every instance. Normally you should set this parameter to the number of instances in your Oracle Parallel Server environment. A proper setting for this parameter can improve memory use.

Oracle uses the value of this parameter to compute the default value of the LARGE_POOL_SIZE parameter when the PARALLEL_AUTOMATIC_TUNING parameter is set to TRUE.  

11)  thread:-

Property	Description
Parameter type	Integer
Default value	0
Modifiable	No
Range of values	0 to the maximum number of enabled threads
Real Application Clusters	If specified, multiple instances must have different values.

THREAD is a Real Application Clusters parameter that specifies the number of the redo thread to be used by an instance.
When you create a database, Oracle creates and enables thread 1 as a public thread (one that can be used by any instance). You must create and enable subsequent threads using the ADD LOGFILE THREAD clause andENABLE THREAD clause of the ALTER DATABASE statement. The number of threads you create is limited by the MAXINSTANCES parameter specified in the CREATE DATABASE statement.
In exclusive mode, thread 1 is the default thread. However, you can specify THREAD for an instance running in exclusive mode if you want to use the redo log files in a thread other than thread 1.In parallel mode, you can specify any available redo thread number, as long as that thread number is enabled and is not in use by another instance.
A value of zero specifies that this instance can use any available, enabled public thread.

EXPORTING AND IMPORTING A VIEW AS A TABLE IN ORACLE DATABASE 12c

Oracle have finally launched the much awaited Oracle database 12c which have about 500 new features,one of the interesting feature is the Export of a View and the Importing the same as a Table,so lets do it

STEP 1) Create a table in any of the PDB,i have created a view EMP_VW in scott schema

STEP 2) Create a directory for expdp and granting permissions to scott

STEP 3) Expdp the view using views_as_tables parameter

You have successfully exported the view,now lets import the same

STEP 4) First drop the existing view

STEP 5) Iimpdb the view using views_as_tables parameter

You have successfully imported the view,but the view would get imported as a table but not as a view so lets check it

STEP 5) The view will be imported in the schema as a table but not as a view

CONGRATULATIONS!!!!!!!
You have successfully Exported and then Imported a view as a table in new Oracle Database 12c.................

Keep watching for more new features of Oracle Database 12c

NEW FEATURES OF ORACLE DATABASE 12cR1

Finally the wait is over,Oracle database 12cR1 is out now and here are the various new features of 12cR1....welcome to the cloud

https://docs.google.com/file/d/0BwFkcPmksh3uZEVMNDBueUVvd2M/edit?usp=sharing

ALL ABOUT SERVER MONITORING

ALL ABOUT SERVER MONITORING

This post is about all the various things you can or you have to check while monitoring your database server,you can use this post to chech various different information related to your database server.

SERVER MONITORING

       1)      CHECKING FREE SPACE IN TABLESPACES

SELECT /* + RULE */  df.tablespace_name "Tablespace",

       df.bytes / (1024 * 1024) "Size (MB)",

       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",

       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",

       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"

  FROM dba_free_space fs,

       (SELECT tablespace_name,SUM(bytes) bytes

          FROM dba_data_files

         GROUP BY tablespace_name) df

 WHERE fs.tablespace_name (+)  = df.tablespace_name

 GROUP BY df.tablespace_name,df.bytes

UNION ALL

SELECT /* + RULE */ df.tablespace_name tspace,

       fs.bytes / (1024 * 1024),

       SUM(df.bytes_free) / (1024 * 1024),

       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),

       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)

  FROM dba_temp_files fs,

       (SELECT tablespace_name,bytes_free,bytes_used

          FROM v$temp_space_header

         GROUP BY tablespace_name,bytes_free,bytes_used) df

 WHERE fs.tablespace_name (+)  = df.tablespace_name

 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used

 ORDER BY 4 DESC;

2)      CHECKING TABLEPACES FOR AUTOEXTENSIBLE MODE

select file_name,autoextensible from dba_data_files where autoextensible='NO';

3)      CHECKING TEMPORARY TABLEPACES FOR AUTOEXTENSIBLE 

MODE

select file_name,autoextensible from dba_temp_files;

      4)  CHECKING THE DATABASE  FOR 32 AND 64 BIT

 select
   length(addr)*4 || '-bits' word_length
from
   v$process
where
   ROWNUM =1;

      

      5)      CHECKING THE DATABASE SIZE

       select sum(bytes / (1024*1024*1024)) "DB Size in GB" from dba_data_files;

6)     CHECKING DATAFILES IN PARTICULAR TABLESPACE

       select tablespace_name,file_name from dba_data_files;

7)    CHECKING SEGMENT SIZE IN ORACLE

       select sum(bytes/1024/1024/1024) "SEGMENT Size in GB"  from dba_segments;

8)      CHECKING LOG SWITCH INTERVAL FOR THE DAY

         SELECT

    SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)                          DAY

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23

  , COUNT(*)                                                                      TOTAL

FROM

  v$log_history  a

WHERE

    (TO_DATE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR')

     >=

     TO_DATE('&startDate', 'DD-MON-YYYY')

     )

     AND

    (TO_DATE(substr(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR')

     <=

     TO_DATE('&endDate', 'DD-MON-YYYY')

     )

GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)

/

     9)    CHECKING FOR CHAINED ROWS

       select * from v$sysstat where name like 'table scan rows gotten';

       select * from v$sysstat where name like 'table fetch by rowid';

       select * from v$sysstat where name like 'table scan blocks gotten';

       select * from v$sysstat where name like 'table fetch continued row';

    10)   CHECKING TOTAL I/O PER USER

 

         select

   sid,
   username,
   round(100 * total_user_io/total_io,2) tot_io_pct
from
(select

     b.sid sid,
     nvl(b.username,p.name) username,
     sum(value) total_user_io
 from

     sys.v_$statname c, 
     sys.v_$sesstat a,
     sys.v_$session b,
     sys.v_$bgprocess p
 where

      a.statistic#=c.statistic# and
      p.paddr (+) = b.paddr and
      b.sid=a.sid and
      c.name in ('physical reads',
                 'physical writes',
                 'physical writes direct',
                 'physical reads direct',
                 'physical writes direct (lob)',
                 'physical reads direct (lob)')
group by

      b.sid, nvl(b.username,p.name)),
(select

      sum(value) total_io
 from

      sys.v_$statname c,
      sys.v_$sesstat a
 where

      a.statistic#=c.statistic# and
      c.name in ('physical reads',
                 'physical writes',
                 'physical writes direct',
                 'physical reads direct',
                 'physical writes direct (lob)',
                 'physical reads direct (lob)'))
order by

      3 desc;

 

              

11)   CHECKING DATABASE FOR VALID AND INVALID OBJECTS

 

 select count(*)  from dba_objects where status='VALID';

 select count(*)  from dba_objects where status='INVALID';

 

 

 

              12)   CHECK THE DATABASE WETHER IT IS BEING UP BY PFILE OR SPFILE

                  

 select decode(count(*), 1, 'spfile', 'pfile' ) from v$spparameter where rownum=1 and  isspecified='TRUE';

                               

              13)   CHECKING THE CONTENT OF THE CONTROLFILE

                       SELECT * FROM v$controlfile_record_section;

              14)   CREATING PFILE FROM SPFILE

        

            create pfile='/oracle/oracle/newpfile.ora' from spfile;

             15)   CHECKING FREE SPACE IN TEMPORARY TABLESPACE

select TABLESPACE_NAME, sum(BYTES_USED/1024/1024/1024)  from V$TEMP_SPACE_HEADER group by tablespace_name;

select TABLESPACE_NAME, sum(BYTES_FREE/1024/1024/1024) from V$TEMP_SPACE_HEADER group by tablespace_name;

           

16)  VIEWING  TOTAL SIZE OF TEMPORARY TABLESPACE

               select sum(bytes / (1024*1024*1024)) "TEMPFILE Size in GB" from dba_temp_files;

17)    FOR CHECKING THE GETS AND WAITS OF THE ROLLBACK SEGMENTS

                 SELECT rn.Name "Rollback Segment", rs.RSSize/1024 "Size (KB)", rs.Gets "Gets",

                rs.waits "Waits", (rs.Waits/rs.Gets)*100 "% Waits",

                rs.Shrinks "# Shrinks", rs.Extends "# Extends"

               FROM   sys.v_$rollName rn, sys.v_$rollStat rs

               WHERE  rn.usn = rs.usn;

18)    FOR CHECKING THE TOTAL NUMBER OF ROLLBACK SEGMENTS 

REQUIRED

show parameter transaction;

NOTE:- now divide the total number of transactions by the transaction_per_rollback_segments then you will get the total number of  rollback segments required

              19)   CHECKING THE ONLINE STATUS FOR THE DATAFILE

select status,tablespace_name from v$datafile_header;

select tablespace_name,online_status from dba_data_files;

    20)  CHECKING THE MAXIMUM NUMBER OF ROLES ENABLED IN THE DATABASE

SELECT grantee, count(*)

FROM (SELECT grantee, granted_role

FROM dba_role_privs

CONNECT BY PRIOR grantee = granted_role)

GROUP BY grantee

HAVING count(*) = (SELECT max(count(*))

FROM (SELECT grantee, granted_role

FROM dba_role_privs

CONNECT BY PRIOR grantee=granted_role)

GROUP BY grantee);

show parameter max_enabled_roles;

21)  CHECKING THE TOTAL NUMBER OF ROLES GRANTED TO USERS

select "Grantee", count(*) "Role Number" from
(
select distinct connect_by_root grantee "Grantee", granted_role
from dba_role_privs
connect by prior granted_role=grantee
)
group by "Grantee"
order by "Role Number"
/

22)  CHECKING THE LOG GENERATION TIME

select SEQUENCE#,to_char(COMPLETION_TIME,'dd-mon-yy hh24:mi:ss') from v$archived_log;

23)  CHECKING LOCKING STATUS

      select OBJECT_ID,SESSION_ID,ORACLE_USERNAME from  v$locked_object;

     

24)  FOR GETTING RESORCE USAGE STATUS

select * from v$resource_limit;

25)   CALCULATING BUFFER CACHE HIT RATIO

select 'BUFFER CACHE HIT RATIO -- '|| ROUND((1 - (sum(decode(name,'physical reads',value,0) )/(sum(decode(name,'consistent gets',value,0))+sum(decode(name,'db block gets',value,0)))) )*100,2) HIT_RATIO

from v$sysstat

where name in ('physical reads','consistent gets','db block gets');

26)  CALCULATING SQL AREA HIT RATIO

SELECT 'SQL AREA HIT RATIO  -- '||ROUND((1 - SUM(RELOADS)/(SUM(PINS)+ SUM(RELOADS)))*100,2) FROM V$LIBRARYCACHE;

27)  CALCULATING DB CACHE HIT RATIO

SELECT 'DB CACHE HIT RATIO -- '|| ROUND((1 - SUM(GETMISSES)/(SUM(GETS) + Sum(getmisses)))*100,2) FROM V$ROWCACHE;

28)  CALCULATING LATCH HIT RATIO

SELECT 'LATCH HIT RATIO -- ' || ROUND((1 - (Sum(misses) / Sum(gets))) * 100,2) FROM   v$latch;

29)  CHECKING THE LIBRARY CACHE HIT RATIO

select sum(pins),sum(reloads),sum(reloads)/sum(pins) from v$librarycache;

30)  DETERMINE CURRENT ONGOING TRANSACTIONS

SELECT a.name,b.status

FROM v$rollname a, v$rollstat b

WHERE a.name IN ( SELECT segment_name

FROM dba_segments

WHERE tablespace_name = 'UNDOTBS1'

)

AND a.usn = b.usn;

31)   CHECKING LOCKING TABLES

select
  object_name,
  object_type,
  session_id,
  type,                 -- Type or system/user lock
  lmode,        -- lock mode in which session holds lock
  request,
  block,
  ctime                 -- Time since current mode was granted
from
  v$locked_object, all_objects, v$lock
where
  v$locked_object.object_id = all_objects.object_id AND
  v$lock.id1 = all_objects.object_id AND
  v$lock.sid = v$locked_object.session_id
order by
  session_id, ctime desc, object_name
/

32)  CHEKING THE SPEED OF IMPORT

SELECT

        SUBSTR(sql_text, INSTR(sql_text,'INTO "'),30) table_name

      , rows_processed

      , ROUND( (sysdate-TO_DATE(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes

      , TRUNC(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute

    FROM

        sys.v_$sqlarea

    WHERE

          sql_text like 'INSERT %INTO "%'

      AND command_type = 2

      AND open_versions > 0;

33)   CHECKING THE SIZE NEEDED FOR UNDO TABLESPACE

SELECT (UR * (UPS * DBS)) + (DBS * 24) AS " Bytes"

FROM (SELECT value AS UR

FROM v$parameter

WHERE name = 'undo_retention'),

(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS

FROM v$undostat),

(SELECT value AS DBS

FROM v$parameter

WHERE name = 'db_block_size');

34)   CHECKING FREE SPACE IN TABLESPACES

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files 
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments 
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;

35)  CHEKING SIZE OF CONTROLFILE

select name,BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024 as MB from v$controlfile;