Database Technologies‎ > ‎Oracle‎ > ‎

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>';

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;

Comments