Miscellaneous

1) Installation of JVM (JAVA) in oracle database

To create object containing a Java source, class, or resource, first you need

to install JVM support in oracle database, for that execute the following script

@?/javavm/install/initjvm.sql

if for any reason the above installation does not complete successfully, you

could remove the installation using @?/javavm/install/ rmjvm.sql

also make sure to increase the java pool size upto 80mb

If installation is successfully check the db registry

SQL> select comp_id from dba_registry

javavm // Indicates javavm installed

Creating simple object, below is the example to create

object using java

CREATE JAVA SOURCE NAMED "Hello world" AS

public class Welcome {

public static String welcome() {

return "Hello World"; } }

/

To drop the above created procedure

drop java source "Welcome";

2) Exporting Sequences From production database onto UAT

Its not possible to exprot sequences object from production to uat directly, there is working

using software of DDL Wizard. After taking export dump of the database structure

we use this software to extract the DDL create commands to get the create structure of required

objects

Here are the detailed steps

1) First take a structural dump of the Database or required schemna, using export utility

exp file=full_db.dmp log=full_db.log full=y rows=N statistics=none direct=y

For exporting structure of schema

exp file=schema.dmp log=schema.log owner='<schema_owner>' rows=n statistics=none direct=y

2) Using the DDL Wizard, open the dmp file generated above, the software reads the binary file

and gives us the DDL scripts of the objects in that database or schema,

3) Copy those scripts and its done, now you have the export of the sequence objects

Alternative for above is using PLSQLDEV, through the plsqldev connect to the production db

using the username to whom the sequences belongs and you will get the sql ddl script of

the objects required, however you need to check whether the

username you are using to connect have required permission to access the objects required.

Also you could use the dbms_metadata to genereate the create scripts for any of the database objects

more infomation here

3) Grant select on v$views, V$session,v$instance etc

uv$session is synonym so you cannot give privilige on sysnome, its underlying view is v_$session, you have to grant privilege on it.

For example

If user 'ron' wants acces on v$session

You have to grant acces on v_$session view

Command : Grant select on v_$session to ron;

4) Changing init parameters

SPFILE :

When you have started your database with spfile you could modify the initialization

parameters using "ALTER SYSTEM " command, however you need to be aware

of the argument "scope" with the ccommand

First Check the various states for the initialization parameters

select disctint issys_modifiable from v$parameter

ISSYS_MOD

------------

DEFFERED

FALSE

IMMEDIATE

Above

DEFFERED : Bounce is required for the modification to take effect

FALSE : Means the parameter cannot be modified

IMMEDIATE : Parameter could be changed instantly

Various scope i.e

ALTER SYSTEM <parameter>=<value> scope=spfile

means it makes modification in spfile however changes come in affect at next startup

ALTER SYSTEM <parameter>=<value> scope=memory

means changes take effect instantly and are there till the instance is on and are gone

when it is restarted

For parameter whose ISSYS_MOD=false, you cannot modify the parameter online,

you have to bounce the database and make changes to pfile,

So the inference is that

if the parameter shows issys_modifiable=IMMEDIATE, you could use scope=both to get

it done immediately and last forever

4) Altering Profile, disable password_verify_function.

ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL;

change the password of the user as required , then enable the function again

ALTER PROFILE DEFAULT limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION;

Retaining Existing password

alter user <username> identified by values '<xxxxxxxxxxxxxxxxxxxxx>';

5) Compiling Invalid objects.

Compiling invalid objects

Identifying invalid objects ;

SELECT owner,object_type,object_name,status

FROM dba_objects

WHERE status = 'INVALID'

ORDER BY owner, object_type, object_name;

Now after getting the list we could compile the objecs manually

ALTER <OBJECT_TYPE> <OWNER.OBJECT_NAME> COMPILE;

ALTER PACKAGE <package_name> COMPILE;

ALTER PACKAGE <package_name> COMPILE BODY;

ALTER PROCEDURE <procedure_name> COMPILE;

ALTER FUNCTION <function_name> COMPILE;

ALTER TRIGGER <trigger_name> COMPILE;

ALTER VIEW <view_name> COMPILE;

The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database

Execute the above through sys/system user

6) UTL_file, dba_directories

UTL_FILE_DIR : Specifies location where file can be written and

from where files can be read using utl_file which is

a package provided by oracle to read and write files

that are located on the server.

Coming of 9i, we could create new directories to which we could

provide privilege so as to be accessed by Oracle database users

for read and write operations.

To create directories

CREATE DIRECTORY <alias> AS '<path to the directory on server>';

After which we could grant privilege to required users.

GRANT READ ON DIRECTORY <directory_alias> to <username>;

GRANT WRITE ON DIRECTORY <directory_alias> to <username>;

For checking the privilge granted on directories

select * from dba_tab_privs where table_name='<directory_alias>';

CREATE OR REPLACE directory prod_idr as '/d01/prod_folder';

7) Creating RnD tables and insert random data

CREATE TABLE Emp_details(empname char(20),empid number, emplocation char(15));

To Insert Data into the table

begin

for i in 1 .. 30000 loop

insert into Emp_details values

(dbms_random.string('X',20),i,dbms_random.string('X',15));

end loop;

end;

/

8) Checking locks on table

SET LINESIZE 500

SET PAGESIZE 1000

SET VERIFY OFF

COLUMN owner FORMAT A20

COLUMN username FORMAT A20

COLUMN object_owner FORMAT A20

COLUMN object_name FORMAT A30

COLUMN locked_mode FORMAT A15

SELECT b.session_id AS sid,

NVL(b.oracle_username, '(oracle)') AS username,

a.owner AS object_owner,

a.object_name,

Decode(b.locked_mode, 0, 'None',

1, 'Null (NULL)',

2, 'Row-S (SS)',

3, 'Row-X (SX)',

4, 'Share (S)',

5, 'S/Row-X (SSX)',

6, 'Exclusive (X)',

b.locked_mode) locked_mode,

b.os_user_name

FROM dba_objects a,

v$locked_object b

WHERE a.object_id = b.object_id

ORDER BY 1, 2, 3, 4;

8) Tracing User Session

To enable

select 'exec dbms_system.set_ev(' ||sid||','||serial#||',10046,12,'''');' from v$session where username='<username>'

To Disable

select 'exec dbms_system.set_ev(' ||sid||','||serial#||',10046,0,'''');' from v$session where username='<username>';

9) Datafile, checks.

Tablespace Free Space Check.

select tbs.tablespace_name,

tot.bytes/1024/1024 total,

tot.bytes/1024/1024-sum(nvl(fre.bytes,0))/1024/1024 used,

sum(nvl(fre.bytes,0))/1024/1024 free,

max(fre.bytes/1024/1024) Largest_Space,

(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/1024, tot.bytes

order by 6 desc;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YYYY HH24:MI:SS';

SELECT A.FILE_NAME,

A.BYTES/1024/1024 "MB",

A.TABLESPACE_NAME,

B.CREATION_TIME

FROM DBA_DATA_FILES A,V$DATAFILE B WHERE A.FILE_ID=B.FILE# AND TABLESPACE_NAME='&d' ORDER BY 4

/

10) SQL Loader, importing data from csv file

Import Data from csv file into oracle database table

1) Create table with same structure as the data to be imported for example emp ( empno, empname, sal, deptno )

2) Create a control file having following contents and save the file as "loader.ctl"

load data

infile 'c:\path\to\csv\file.csv'

into table emp

fields terminated by ","

( empno, empname, sal, deptno )

3) sqlldr username/password@server control=loader.ctl

11)

When you move RMAN backup to some other server for restoration or drive fails and

you loose tempfile and the status in v$tempfile continue to show as ONLINE:

SQL> select file#,status,name from v$tempfile;

FILE# STATUS NAME

---------- ------- ---------------------------------------------

1 ONLINE /u01/oradata/oradb/temp01.dbf

2 ONLINE /u02/oradata/oradb/temp02.dbf

Here you should check the dba_temp_files views and ....

SQL> select file_name,status from dba_temp_files;

select file_name,status from dba_temp_files

*

ERROR at line 1:

ORA-01187: cannot read from file 201 because it failed verification tests

ORA-01110: data file 201: '/u01/oradata/oradb/temp01.dbf'

to resolve this issue you can simply drop the missing tempfile without causing

any trouble:

SQL> alter database tempfile '/u01/oradata/oradb/temp01.dbf' drop;

Database altered.

SQL> select file_name,status from dba_temp_files;

FILE_NAME

--------------------------------- STATUS ---------

/u02/oradata/SINAME/temp02.dbf AVAILABLE

Add the file back as below

ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/oradb/temp01.dbf' SIZE 500m;

12) Archive log Analysis

Generated Day wise

SELECT TRUNC(COMPLETION_TIME) ARCHIVED_DATE,

SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1023 SIZE_IN_GB

FROM V$ARCHIVED_LOG

GROUP BY TRUNC(COMPLETION_TIME)

ORDER BY 1;

Generated hourly

alter session set nls_date_format = 'YYYY-MM-DD HH24';

SELECT TRUNC(COMPLETION_TIME, 'HH') ARCHIVED_DATE_HOUR,

SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB

FROM V$ARCHIVED_LOG

GROUP BY TRUNC(COMPLETION_TIME, 'HH')

ORDER BY 1;

Columnar view of hourly log day wise

set linesize 200 pagesize 1000 column day format a3 column total format 9999 column h00 format 999 column h01 format 999 column h02 format 999 column h03 format 999 column h04 format 999 column h04 format 999 column h05 format 999 column h06 format 999 column h07 format 999 column h08 format 999 column h09 format 999 column h10 format 999 column h11 format 999 column h12 format 999 column h13 format 999 column h14 format 999 column h15 format 999 column h16 format 999 column h17 format 999 column h18 format 999 column h19 format 999 column h20 format 999 column h21 format 999 column h22 format 999 column h23 format 999 column h24 format 999 break on report compute max of "total" on report compute max of "h01" on report compute max of "h02" on report compute max of "h03" on report compute max of "h04" on report compute max of "h05" on report compute max of "h06" on report compute max of "h07" on report compute max of "h08" on report compute max of "h09" on report compute max of "h10" on report compute max of "h11" on report compute max of "h12" on report compute max of "h13" on report compute max of "h14" on report compute max of "h15" on report compute max of "h16" on report compute max of "h17" on report compute max of "h18" on report compute max of "h19" on report compute max of "h20" on report compute max of "h21" on report compute max of "h22" on report compute max of "h23" on report compute sum of NUM on report compute sum of GB on report compute sum of MB on report compute sum of KB on report /* Report the Redo Log Switch History */ alter session set nls_date_format='DD MON YYYY'; select thread#, trunc(completion_time) as "date", to_char(completion_time,'Dy') as "Day", count(1) as "total", sum(decode(to_char(completion_time,'HH24'),'00',1,0)) as "h00", sum(decode(to_char(completion_time,'HH24'),'01',1,0)) as "h01", sum(decode(to_char(completion_time,'HH24'),'02',1,0)) as "h02", sum(decode(to_char(completion_time,'HH24'),'03',1,0)) as "h03", sum(decode(to_char(completion_time,'HH24'),'04',1,0)) as "h04", sum(decode(to_char(completion_time,'HH24'),'05',1,0)) as "h05", sum(decode(to_char(completion_time,'HH24'),'06',1,0)) as "h06", sum(decode(to_char(completion_time,'HH24'),'07',1,0)) as "h07", sum(decode(to_char(completion_time,'HH24'),'08',1,0)) as "h08", sum(decode(to_char(completion_time,'HH24'),'09',1,0)) as "h09", sum(decode(to_char(completion_time,'HH24'),'10',1,0)) as "h10", sum(decode(to_char(completion_time,'HH24'),'11',1,0)) as "h11", sum(decode(to_char(completion_time,'HH24'),'12',1,0)) as "h12", sum(decode(to_char(completion_time,'HH24'),'13',1,0)) as "h13", sum(decode(to_char(completion_time,'HH24'),'14',1,0)) as "h14", sum(decode(to_char(completion_time,'HH24'),'15',1,0)) as "h15", sum(decode(to_char(completion_time,'HH24'),'16',1,0)) as "h16", sum(decode(to_char(completion_time,'HH24'),'17',1,0)) as "h17", sum(decode(to_char(completion_time,'HH24'),'18',1,0)) as "h18", sum(decode(to_char(completion_time,'HH24'),'19',1,0)) as "h19", sum(decode(to_char(completion_time,'HH24'),'20',1,0)) as "h20", sum(decode(to_char(completion_time,'HH24'),'21',1,0)) as "h21", sum(decode(to_char(completion_time,'HH24'),'22',1,0)) as "h22", sum(decode(to_char(completion_time,'HH24'),'23',1,0)) as "h23" from v$archived_log where first_time > trunc(sysdate-10) and dest_id = (select dest_id from V$ARCHIVE_DEST_STATUS where status='VALID' and type='LOCAL') group by thread#, trunc(completion_time), to_char(completion_time, 'Dy') order by 2,1;