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;