Oracle: Usefull Commands

1.EXPORT:
exp system@test owner=test123 file=d:oracleuser.dmp log=d:oracleuserdump.log
rows=y statistics=none feedback=100 indexes=y consistent=y compress=n;
(for individual tables=table1,table2,…..)

2.IMPORT:
imp system@test fromuser=test123 touser=test123 file=d:oracleuser.dmp log=d:oracleuser11.log
ignore=y rows=y indexes=y feedback=100;

3.ADDIND DATAFILE INTO A TABLE SPACE:
alter tablespace {tablespace name}add datafile ‘PATH’ size 200m;

4.TO CHECK THE USERS IN A PARTICULAR SCHEMA:
select username,default_tablespace from dba_users;

5.TO CREATE TABLESAPCE:
create tablespace {tablespace name} datafile ‘c:—–.dbf’ size 200m;

6.TO CREATE TEMPORARY TABLE SAPCE:
create temporary tablesapce {tablespace name} tempfile ‘c:——.dbf’ size 200m;

7.TO RESIZE THE DATAFILE:
alter database datafile ‘c:—–.dbf’ resize 1024m;

8.TO CHECK THE SPACE ON TABLESPACE:
select tablespace_name,sum(bytes/1024/1024/1024) from dba_free_space group by tablespace_name

9.TO GET TEMFILE INFORMATION:
desc dba_temp_files or desc v$tempfile;

10.TO GET INFORMATION REGARDING TABLESPACE:

desc dba_tablespaces or desc v$tablespace;

11.TO ALTER THE SPFILE TO INCLUDE CONTROL FILES:
alter system set control_files=’path.ctl’,’path.ctl’ scope=spfile;

12.TO CREATE USER N GRANTS:
create user {username} identified by {password} default tablespace
{tablespace name which has free space acc to statement 8} quota unlimited on {same tablespace name};

13.TO SELECT USERNAME N CHECK THERE STATUS:
select username,created,account_status from dba_users where username like ‘—–%’ order by created

14.TO ALTER USER ACCOUNT STATUS:
select ‘alter user ‘||username||’ account unlock;’created,account_status from dba_users where username like ‘—–%’ order by created

15.TO GIVE SYSTEM GRANTS TO USER(IN CASE IMPORT ERROR:ORA-00001: unique constraint (SYS.I_JOB_JOB) violated ORA-06512: at “SYS.DBMS_JOB”, line 97)
-grant dba to {username}
-grant create public database link to {username}
-grant create database link to {username}

16.TO CHECK THE ACCESS ON A PARTICULAR OBJECT:
select * from v$access; (*=sid,owner,object,type)

17.TO CHECK THE O.S USER CONNECTED TO A PARTICULAR USER:
select osuser from v$session where username=’username’;

18.TO CHECK THE FOLLWING INFORMATION FOR THE SESSIONS:
select p.spid,s.sid,S.SERIAL#,s.username,s.osuser,STATUS,TO_CHAR(LOGON_TIME,’DD-MON-YY HH24:MI’)
from v$process p, v$session s
where p.addr = s.paddr and s.username=’&USERNAME’
ORDER BY LOGON_TIME;
SKUMAR:
select s.sid,p.spid,s.osuser,s.program,s.username
from v$process p,v$session s
where p.addr=s.paddr and
s.username=’&username’;

19.TO CHECK THE FOLLOWING INFORMATION FOR THE SESSIONS PARTICULAR OBLECT:
select p.spid,s.sid,S.SERIAL#,s.username,STATUS,TO_CHAR(LOGON_TIME,’DD-MON-YY HH24:MI’)
from v$process p, v$session s
where p.addr = s.paddr and
s.sid in(select sid from v$access where object=’WEB_LEA_COLLECTION_REPORT’)

20.TO KILL SESSION
alter system kill session ‘sid,’serial#’;

21.TO KILL SESSION FOR UNIX,SOLARIS,LINUX:

#kill -9 spid
#bash
#kill -9 spid

22.TO CHECK TABLESPSACE FOR PATICULAR TABLE:

select table spacename from dba_tables where table_name=’tablename’ and owner=’ownername’;

23.SK

C:oracleora10gasr1infra1jre1.1.8bin/jrew.exe -DORACLE_HOME=”C:oracleora10gasr1infra1″ -DJDBC_PROTOCOL=”thin” -classpath “;C:oracleora10gasr1infra1jre1.1.8libi18n.jar;C:oracleora10gasr1infra1jre1.1.8librt.jar;C:oracleora10gasr1infra1jlibewt3.jar;C:oracleora10gasr1infra1jlibkodiak.jar;C:oracleora10gasr1infra1jlibewtcompat-3_3_15.jar;C:oracleora10gasr1infra1libxmlparserv2.jar;C:oracleora10gasr1infra1jdbclibclasses111.zip;C:oracleora10gasr1infra1classes;C:oracleora10gasr1infra1jlibgss-1_1.zip;C:oracleora10gasr1infra1jliboembase-9_0_2.jar;C:oracleora10gasr1infra1jlibsrvm.jar;C:oracleora10gasr1infra1assistantsjlibassistantsCommon.jar;C:oracleora10gasr1infra1jlibshare.jar;C:oracleora10gasr1infra1jlibswingall-1_1_1.jar;C:oracleora10gasr1infra1assistantsdbcajlibdbca.jar;C:oracleora10gasr1infra1jlibhelp3.jar;C:oracleora10gasr1infra1jliboracle_ice5.jar;C:oracleora10gasr1infra1jlibnetcfg.jar;C:oracleora10gasr1 infra1jlibojmisc118.jar;C:oracleora10gasr1infra1libvbjorb.jar;C:oracleora10gasr1infra1libvbjtools.jar;C:oracleora10gasr1infra1libvbjapp.jar;” oracle.sysman.assistants.dbca.Dbca -progress_only -createDatabase -templateName Oracle9iAS_Repository_Database.dbc -gdbname asdb.nucleussoftware.com -sid asdb -datafileJarLocation C:oracleora10gasr1infra1assistantsdbcatemplates -datafileDestination C:oracleoradata -responseFile NO_VALUE -characterset WE8MSWIN1252 -passwordDialog false -obfuscatedPasswords true -sysPassword 0502aad3dc284aa810274115a30b615130 -systemPassword 05aac032baab5bc04d2e9ec91a6bf38b9f

24.TO CHECK THE PATH FOR DATAFILES:
select name from v$datafile;

25.STEPS FOR CREATING DATABASE MANUALLY:
There are two ways that you can create a database in Oracle 9i. One way is to use the Oracle Database Configuration Assistant. Using the Oracle Database Configuration Assistant makes sure that the new database is optimized with Oracle 9i’s latest features. Simply read the instruction and follow the prompts. The other way to create a database is to do it manually. This will now be demonstrated.

Create a suitable directory structure for the new database. Once this is done, copy the init.ora file into a new file named init{SID}.ora into new directory.

Now you have created a parameter file, you’ll need to declare an Oracle SID name. Keep the SID consistent with the parameter filename. The command to do this action will depend on the platform you have the DBMS installed. If you are using Windows, simply type,

Set ORACLE_SID = {SID}

Alternatively, on the a unix platform, the DBA must type,

Export ORACLE_SID = {SID}

Use SQL*Plus under / as sysdba and set the database to nomount mode.

So, if we wish to override the default spfile to start the new instance in nomount mode then type,

Startup nomount pfile = ‘{directory}init{SID}.ora’

Now we are about to create the database. When creating a new database, write the code into an sql file so that your have a copy of the database features as well as the ability to fix any mistakes in the create database command. Here is the code,

CREATE DATABASE ShepIT
Maxinstances 1
Maxloghistory 1
Maxlogfiles 10
Maxlogmembers 15
Maxdatafiles 100
Datafile ‘{directory}datafile_01.dbf’
size 300M reuse autoextend on next 15M maxsize unlimited
character set WE8MSWIN1252
national character set AL16UFT16
logfile
group 1 (‘{directory}redo_01.log’) size 75M
group 2 (‘{directory}redo_02.log’) size 75M
group 3 (‘{directory}redo_03.log’) size 75M
default temporary tablespace TEMP
tempfile ‘{directory}temp_01.dbf’
extent management local uniform size 1M
undo tablespace UNDO_TS datafile ‘{directory}datafile_01.dbf’
size 125M reuse autoextend on next 15M maxsize unlimited;

Oracle, should respond by saying that the database is created. Once this is done you are ready to load scripts that are needed to support your Oracle products by typing the following commands

@{directory}catalog.sql
@{directory}catproc.sql
@{directory}catexp.sql

Ok, the database is configured. Change the SYSTEM and SYS passwords, to ensure better security for your database.

26.TO CHECK THE SAPCE IN TEMP FILE:
select bytes_free from v$temp_space_header

27.TO SELECT THE TEMP FILENAME AND SPACE:
select filename,bytes from dba_temp_files;

28.TO ADD THE TEMP FILE:
alter tablespace {tablespace name} add temp file ‘PATH’ size;

29.TO KILL THE SESSION FROM WINDOWS:
orakill {database name} spid;

30.TO SELECT THE SPID:
select spid from v$session s,v$process p where s.paddr=p.addr and sid in(_,_,…);

31.TO CHECK IF THE OBJECT IS BEING DROPPED OR NOT:
select object_type,count(*) from dba_objects where owner=’NAME’ group by object_type;

32. select
a.grantee,
a.role_priv,
a.admn,
a.df,
b.account_status
from (SELECT GRANTEE,ROLE_PRIV,ADMN,DF FROM (
SELECT GRANTEE,GRANTED_ROLE ROLE_PRIV,ADMIN_OPTION ADMN,DEFAULT_ROLE DF
FROM DBA_ROLE_PRIVS
UNION ALL
SELECT GRANTEE,PRIVILEGE ROLE_PRIV,ADMIN_OPTION ADMN,’ ‘ DF FROM
DBA_SYS_PRIVS)) a,
dba_users b
where
a.grantee=b.username AND
B.USERNAME=’&username’

33.THIS COMMAND WILL INSTALL REPORT SERVER ON THE ALREADY INSTALLED DS

rwserver server={server-name} auto=y batch=yes

34.WHEN THIS ERROR COME RESTART THE OC4J_BI_FORMS

REP-52266: The in-process Reports Server rep_localhost_mid failed to
start.oracle.reports.RWException: IDL:oracle/reports/RWException:1.0

35.TO CALCULATE AT THE END OF REPORT
COMPUTE SUM OF COUNT(*) BREAK ON REPORT
BREAK ON REPORT
.
/

36.GRANT DEBUG ANY PROCEDURE TO ICICI_QCT;
GRANT DEBUG CONNECT SESSION TO ICICI_QCT;

37.HOW TO TRACE THE SESSION
dbms_system.sql_trace_in_session(‘SID’,’SERIAL#’,true)

38. script for monitoring the indexes, which are not being used
alter index {{index_name}} monitoring usage;

39. select count(*) from ods.ods_process;

Leave a Comment

Your email address will not be published. Required fields are marked *

CAPTCHA * Time limit is exhausted. Please reload the CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top