Circle Gallery

Wednesday, 1 July 2015

Srinivasan Software Solutions

Srinivasan Software Solutions

Don't miss
Showing posts with label Oracle Administrator. Show all posts
Showing posts with label Oracle Administrator. Show all posts

Saturday, 12 October 2013

Oracle Database 11G Installation in Windows




Follow this document for 11g R2 version….. if difference found please let me know

Click on the setup of the 11g R2





Here below screenshot username : orcl password : orcl.



Give same path for installing all the software on one folder itself











Uncheck enable oracle configuration manager







Please let your administrator know if status fails













Enterprise Manager Database Control URL - (orcl) :

https://sal-dev-wtcor3.salzertech.com:1158/em



Your database configuration files have been installed in D:\app\amolp while other components selected for installation have been installed in D:\app\amolp\product\11.1.0\db_1. Be cautious not to accidentally delete these configuration files.

Thursday, 12 September 2013

Kill session in Oracle



SQL Error: ORA-01940: cannot drop a user that is currently connected
01940. 00000 -  "cannot drop a user that is currently connected"
*Cause:    Attempt was made to drop a user that is currently logged in.
*Action:   Make sure user is logged off, then repeat command.


When you planned to drop user in oracle.  sometime you will get error like this
Kill session in Oracle


Solution:

 Login with sys user of your database, and enter this query.

 select sid,serial# from v$session where username ='BAW'
It will display you the list of sid and serial#, which your user is using.

 Then use this query

 Alter system kill session '143','500';

 were 143 is sid and 500 is serial#

once you kill all the session related to this schema, drop this user now.

 Now you have successfully dropped your schema. 

Saturday, 7 September 2013

Check contrains in oracle



How to handle case senstive data in oracle by check contrains

I have a table which insert both upper and lower case data get inserted in my table

So, Now I need to insert only upper case in my table..

we have acheive it by the following query as mention below.

Alter Table Xxbkpi_Lookup
Add Constraint Xxbkpi_Lookup_Unq_Ck   
Check (Lookup_Type = Upper(Lookup_Type) And 
Lookup_Code= Upper(Lookup_Code))

Monday, 2 September 2013

PRAGMA AUTONOMOUS_TRANSACTION in ORACLE


We can't give TCL statement (commit,rollback) in oracle trigger..

So what is the solution???


Write a procedure with commit and PRAGMA AUTONOMOUS_TRANSACTION and call this procedure inside the trigger as mention below.

Write procedure as follow..

create or replace
Procedure Test
As
PRAGMA AUTONOMOUS_TRANSACTION;
Begin
Insert Into Emp_Test Values (600,'Rama2',1000,'chennai');
commit;
END;


And fire a trigger as follows

create or replace
TRIGGER TRG_TEST
After Insert On Emp
FOR EACH ROW

Declare
  Begin
test();

End;


So in this way, you can write a TCL (commit,rollback) inside a trigger

Fine Grained Auditing in Oracle


Fine Grained Auditing


The Fine Grained Auditing feature allows monitoring of a SQL statement in greater detail. It compliments the regular AUDIT feature provided by Oracle for years. Auditing provides the basic information about access. The Fine Grained Auditing feature provides even more detailed information. The level of detail provided by the Fine Grained Auditing feature includes, but is not limited to:

    The Complete SQL Statement executed on the object.
   
    Bind values and the user executing the statement
   
    System Change Number (SCN) information

In previous versions, only SELECTs can be audited using Fine Grained Auditing. Oracle10g extends the Fine Grained Auditing to Data Manipulation Languages. The following Data Manipulation commands can be audited using Fine Grained Auditing.

  •     INSERT
  •     DELETE
  •     UPDATE
  •     MERGE

If any of the audit columns is present in the DML statement, the column is audited by default. However, there is an option provided to audit based on ANY or ALL of the columns that are used in the statement.

Newly improved audit trial supports the SQLTEXT and SQLBIND values. The AUDIT_TRIAL parameter now takes the additional value DB_EXTENDED and when setting this parameter it captures the SQLTEXT and SQLBIND values. The dictionary table AUD$ has two additional clob columns to capture and store the information.

SYS> desc aud$;

 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 SESSIONID                                 NOT NULL NUMBER
 ENTRYID                                   NOT NULL NUMBER
 <Input.. Truncated>
 SQLBIND                                            CLOB
 SQLTEXT                                            CLOB

Audit table SYS.AUD$ is stored in the SYSAUX tablespace. In previous versions this table was stored in the SYSTEM tablespace.

Monday, 5 August 2013

How to Decrypt Oracle Apps Password

SELECT *FROM FND_USER WHERE USER_NAME='SEVENTH'

CREATE OR REPLACE PACKAGE XXARTO_GET_PWD
AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2;
END XXARTO_GET_PWD;

CREATE OR REPLACE PACKAGE BODY XXARTO_GET_PWD
AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2
AS
LANGUAGE JAVA
NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt
(java.lang.String,java.lang.String) return java.lang.String';
END XXARTO_GET_PWD;

SELECT Usr.User_Name, Usr.Description, XXARTO_GET_PWD.Decrypt (
(SELECT (SELECT XXARTO_GET_PWD.Decrypt(Fnd_Web_Sec.Get_Guest_Username_Pwd,
Usertable.Encrypted_Foundation_Password) FROM DUAL)AS Apps_Password
FROM applsys.Fnd_User Usertable WHERE Usertable.User_Name =(SELECT SUBSTR (
Fnd_Web_Sec.Get_Guest_Username_Pwd,1,INSTR
(Fnd_Web_Sec.Get_Guest_Username_Pwd,'/')- 1)
FROM DUAL)),Usr.Encrypted_User_Password)Password FROM applsys.Fnd_User Usr
WHERE Usr.User_Name = 'SEVENTH';



by
Ramya.P

Friday, 2 August 2013

Change default language in oracle database


How to insert multiple language in oracle



In database configuration assistance, select Unicode (AL32UTF8)

how to insert arabic data in oracle


Go to database configuration assistance 

select create database and give next


 click next
 mention your instance name

 click next with default

                                               
fill your password


go with file system

click next with default


click next with default

click next with default


select AR8MSWIN1256 - MS Windows code page 1256 8-Bit Latin/Arabic


click next with default


click next with default



click next with default


check on create database and give next



go to select sql developer and create connection



create table for sample

insert some data with arabic


data got inserted with arabic


Keep rocking

Wednesday, 31 July 2013

Command to find lock in oracle table


SELECT statment SELECT SID,SERIAL# FROM V$SESSION WHERE SID IN (SELECT SESSION_ID FROM DBA_DML_LOCKS WHERE NAME = Table Name)

Friday, 26 July 2013

To Drop Tablespace in oracle


SQL> drop tablespace kk including contents;
Tablespace dropped.

SQL>
SQL> select username from dba_users:
It will display all the username in the DB

SQL> select name from v$database;
NAME
ORCL
It will display the DB name


Sql> select count(*) from user_objects;
It will display all the objects in that schema.

Thursday, 25 July 2013

Full Database Export in oracle


C:\>exp system/<system pwd> file=<filename.dmp log=logfile.log full=y

C :\> exp system/system file=fulldump.dmp log=fulldump.log full=y

Export command in oracle


C:\> exp <username>/<password> file=<filename.dmp> log=<logname.log>

C:\>exp kk/kk file=kk.dmp log=kk.log

Export: Release 9.2.0.1.0 - Production on Thu Oct 20 20:50:23 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user KK
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user KK
About to export KK's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export KK's tables via Conventional Path ...
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

Import command in oracle



C:\>imp <username>/<password> file=<dumpname.dmp> log=<logfilename.log> full=y

C:\>imp kk/kk file=kk.dmp log=kkimp.log full=y

• C:\>imp <username>/<password> file=<dumpname.dmp> log=<logfilename.log> fromuser=<fromusername> touser=<tousername>

• Before import we need to check the tablespace free space.

SYSTEM -200M

INDX -100M

DEFAULT TABLESPACE (ORION) -

C:\>imp kk/kk file=kk.dmp log=kkimp.log fromuser=surya touser=kk
Imp……
Imp…….

Import completed successfully with warnings.

After Import Connect to the Respective DB user

C:\>sqlplus

Enter username: kk/kk

SQL>@c:\compile – it will compile all the Invalid objects run the compile script atlease 4 times.

To create Database user in Oracle


To create DB user

SQL> create user <username> identified by <password> default tablespace <Tablespace Name>
Temporary tablespace <Temporary tablespace name>;

SQL> create user kk identified by kk default tablespace kk temporary tablespace
temp;

User created.

SQL>

SQL> show user;
USER is "SYS"

SQL>

Sql> grant create session,imp_full_database to <user name>

SQL> grant create session,imp_full_database to kk;

Grant succeeded.

SQL> alter user kk quota unlimited on <username>;

SQL> alter user kk quota unlimited on kk;
User altered.

SQL>exit

Increase the Tablespace Size in oracle



Increasing the Existing Datafile

SQL> ALTER DATABASE DATAFILE 'E:\ORION_DBF\KK1.DBF' RESIZE 100M;

 Database altered.

SQL>

• Appending additional DATA file to the Tablespace

SQL> alter tablespace kk add datafile 'e:\orion_dbf\kk2.dbf' size 20m;

Tablespace altered.

To Create Table Space in Oracle



To Create Table Space:

Create a folder in local drive like ORION_DBF

Goto sqlplus enter userid/pwd
SQL>


SQL> CREATE TABLESPACE KK DATAFILE 'E:\ORION_DBF\KK1.DBF' SIZE 50M;

Tablespace created.

Tuesday, 16 July 2013

Increase temporary tablespace size in oracle


If you want to know the location of temporary tablespace:

 SELECT tablespace_name, file_name, bytes
FROM dba_temp_files WHERE tablespace_name = 'TEMP';

 If you want to delete the temporary tablespace:

 ALTER DATABASE 
TEMPFILE 'C:\APP\BI\ORADATA\ORCL\TEMP01.DBF' 
DROP INCLUDING DATAFILES;
Recreate your tablespace with maxsize:

 ALTER TABLESPACE temp ADD TEMPFILE
'C:\APP\BI\ORADATA\ORCL\TEMP01.DBF' SIZE 512m
AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;


Monday, 15 July 2013

Oracle Tablespace in LINUX



Want to create tablespace in linux which handle any sort of data?

 If you creating any Production environment, Please do create your own tablespace and assign users for that tablespace
Here is the query for you 


 CREATE TABLESPACE oracle DATAFILE
'/oracle/obi_tablespace/oracle.dbf' SIZE 31G AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
LOGGING
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

 
at Posted by No comments:
Subscribe to: Posts (Atom)