WELCOME

Oracle

Checking Last executed query in database

 
select *
from v$sql sq, v$session se, v$open_cursor oc
where sq.sql_id = oc.sql_id
and se.saddr = oc.saddr
and se.sid = oc.sid AND user_name = ‘INTOUCH2’
— and se.audsid = SYS_CONTEXT(‘userenv’, ‘sessionid’)
order by oc.LAST_SQL_ACTIVE_TIME desc;

Killing Oracle Session

 

Select * from v$session;

ALTER SYSTEM KILL SESSION ‘sid,serial#’;

 

Pinning Oracle Object

execute dbms_shared_pool.keep(‘kccbseed.sp_mig_clients’);

Export/Import

Here is the simple command to export and import the data from oracle

Normal Export and Import

exp user/password@schema file=path\filename.dmp  log=path\filename.log

imp user/password@schema file=path\filename.dmp full=yes

Data Pump Export and Import

expdp userid/password@schema directory = directoryname dumpfile=filename.dmp logfile=logfilename.dmp full=y

impdp userid/password@database directory = directoryname dumpfile=filename.dmp logfile=logfilename.dmp remap_schema=oldusername:newusername

Character Set

Before exporting and importing the dump from oracle we need to check the character set of the installed oracle version. I used some special character in my procedure for  internal validation it was working in my existing Database. After import to other schema  same procedure not working. Then i sat more than one hour to identify the problem .

The query to check the characterset

SELECT  *  FROM  NLS_DATABASE_PARAMETERS;

Oracle Directory

create or replace directory foo_dir as '/tmp';
grant read, write on directory foo_dir

File Creation

Declare
f utl_file.file_type;
s varchar2(200) := ‘this is some info’;

Begin
f := utl_file.fopen(‘TEST’,’sample2.txt’,’W’);
utl_file.put_line(f,s);
utl_file.fclose(f);
End;

Increasing the Oracle Connection

conn sys/sysnsdb@nsdbpr as sysdba
create pfile=’C:\initnsdbpr_220211.ora’ from spfile;
alter system set sessions=1000 scope=spfile;
alter system set processes=1000 scope=spfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
shutdown immediate;
startup;
select open_mode from v$database;
show parameter;

Creating a tablespace

create tablespace sampletablespace
logging
datafile ‘C:\oraclexe\oradata\XE\ts_sth.dbf’
size 32m
autoextend on
next 32m maxsize 2048m
extent management local

SQLPLUS TIPS

1.  Compiling Invalid Objects in SQLPLUS

EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => ‘USER’);

Grant Permissions

1. grant execute on dbms_lock to public;
2. grant execute on dbms_sqlhash to public;

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: