Create a new schema in Oracle

Here we will first create a tablespace and then we will create a user for that tablespace.

create tablespace ahsan
add datafile 'e:\ahsan.dbf' size 100 m;

create user ahsan identified by welcome
default tablespace ahsan temporary tablespace temp;

grant create session, connect, resource to ahsan;

Hence now you are ready with the new tablespace and a new user that belongs to the ahsan tablespace;

Sphere: Related Content

Installing oracle 10g database on parallels desktop

Recently, I switched to Mac from Windows machine. I had a challenge to make Mac development environment as similar to Windows development environment. Being a java developer I usually use Oracle database. So I tried to install it first on my mac, then I found somehow that Oracle does not support my mac version. Then I had to install Parallels Desktop to make it work.
I proceeded this way

1. Install Parallels Desktop, You can find how to do it on parallels support center.

2. Install Oracle 10g database on Parallels Desktop, It is smooth, so no problem.

3. Steps to install InstantClient and Sqlplus package on mac to access Parallel's oracle DB.

4. Download InstantClient and SqlPlus packages from Oracle download site.

5. Extract these two packages to a single directory like I chose /Applications/OracleClient

6. Copy tnsnames.ora file from parallels oracle installation to the Mac's /Applications/OracleClient directory you chose for installing InstantClient and SqlPlus.

7. Modify your .bash_profile to indicate these entries

PATH=/Applications/OracleClient:${PATH}
export PATH
ORACLE_HOME=/Applications/OracleClient
export ORACLE_HOME
DYLD_LIBRARY_PATH=/Applications/OracleClient
export DYLD_LIBRARY_PATH
TNS_ADMIN=/Applications/OracleClient
export TNS_ADMIN

8. Check for the IP of the Parallel's Desktop machine and note it.

9. Check for the IP of the Mac machine and note it.

10. open vi editor and edit /etc/hosts file for Mac machine to reflect the IP obtained from parallels desktop machine(Step 7)

192.168.128.82 ahsan-javed.mydomain.com
(Note:ahsan-javed.mydomain.com is the machine name that was given in my office network.)

11. open notepad and edit C:/Windows/System32/drivers/etc/hosts file for Parallels Desktop to reflect the IP obtained from Mac machine (step 8)

192.168.128.132 ahsanjaved
(Note: ahsanjaved is the machine name I assigned to parallels desktop while installing Windows XP on it.)

12. Go to Windows Control Center and disable firewall/enable 1521,1158 and 5506 ports for communication between Parallels Oracle and Mac machine.

13. Now go to shell and type sqlplus. it should ask you for username and password. Here you go connected.

Sphere: Related Content

Oracle cursors parameters

To view open cursors on oracle

No of Open cursors on Oracle(Admin permission is required)
select count(*) from v$open_cursor;

To see open_cursors parameter on oracle db.
show parameter open_cursors;

To set open_cursors parameter on oracle db.

alter system set open_cursors=700 scope=memory;

To see the sql query associated with cursor

select sid, sql_text from v$Open_cursor order by sid, sql_text;

To select number of open cursors, machine name, operating system and user name executing the query, run the following

SELECT v.value as numopencursors ,s.machine ,s.osuser,s.username
FROM V$SESSTAT v, V$SESSION s
WHERE v.statistic# = 3 and v.sid = s.sid ;

Sphere: Related Content

Rebuild unusable index on a column of a table in Oracle schema

To rebuild unusable indexes on Oracle schema we can use this simple script.
This script first checks for all unusable indexes in the entire schema and then rebuild them to VALID state.

set serverout on
set head on
set echo on
declare
str varchar2(500);
begin for i in (select index_name,status from user_indexes where Status='UNUSABLE')
loop
str:='alter index ' || i.index_name || ' rebuild unrecoverable' ;
execute immediate str;
dbms_output.put_line('rebuilt index ' || i.index_name);
end loop;
end;
/

Sphere: Related Content