This is my personal notes of the different commands I use from time to time for normal database administration. Use this for your reference.it will help you a lot if you are a new dba.

In the index section you will see the overview of the kind of topics that are covered.

Index

tablespace and datafiles management

ora-00020 max processes reached error.

file system 100% full.

find pending transactions in database

find if spfile is used.

Find size of table space

Find free space in tablespace

find last analyzed time of the schema,table,indexes etc

How to find users passwords in the database?

9i how to delete and compute statistics

find all indexes on a table

find all indexed columns

get all session info

get sql info for a session

setting identifiers for different application modules

drop tablespace including all constraints,datafiles,objects

describe the different user privileges views here

resize data files

kill session

change sys password

more info on ORAPWD file

find out if a server is shared or dedicated

when you have conversion errors in toad

find out whether datafiles are online

maximum cursors

modify primary key structure

find table and index storage parameters

change initial extent of table or any storage parameters

handle database recovery when datafile gets corrupted,no backup files available, non system datafile.

find out export size before exporting.

compiling packages in sys schema

find deprecated parameters in 10g

ORA-00376: file 40 cannot be read at this time .

syntax for doing secure copy to different server.

difference between oracle 10g db control and grid control

how to get a formatted explain plan?

find out oracle version on the server using unix commands

all faqs about sqlplus errors can be found here

find out long running job processes and the work progress.

Tablespace administration sql statements

Finding out the historical tablespace usage

select name , tablespace_size*8192/(1024*1024*1024) , tablespace_maxsize*8192/(1024*1024*1024) , tablespace_usedsize*8192/(1024*1024*1024) , rtime from DBA_HIST_TBSPC_SPACE_USAGE a , v$tablespace b where tablespace_id = 175 and a.tablespace_id = b.TS# order by rtime desc;

find out free space on the mentioned tablespace.

select tablespace_name, sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

select tablespace_name, sum(bytes)/1024/1024 from dba_free_space where tablespace_name = ‘TS_RBS’ group by tablespace_name;

find out existing size of the tablespaces by using the following command

select file_name, t.tablespace_name tablespace, bytes/1024/1024 , d.autoextensible autoextend , d.increment_by, d.maxbytes from dba_data_files d, dba_tablespaces t where t.tablespace_name = d.tablespace_name and t.tablespace_name = ‘TS_TRAINENGINE_FACT_DATA’

select t.tablespace_name tablespace, sum(bytes/1024/1024) from dba_data_files d, dba_tablespaces t where t.tablespace_name = d.tablespace_name group by t.tablespace_name

find out the tablespaces whose size is greater than 3 GB

select file_name, bytes/1024/1024 from dba_data_files d, dba_tablespaces t where t.tablespace_name = d.tablespace_name and bytes/1024/1024 > 3000 ;

select t.tablespace_name tablespace, sum(bytes/1024/1024) from dba_data_files d,

dba_tablespaces t where t.tablespace_name = d.tablespace_name and d.tablespace_name = ‘TS_GFD_DATA’ group by t.tablespace_name

creating a temporary tablespace

CREATE TEMPORARY TABLESPACE TS_TEMP TEMPFILE ‘/global/reuter-db1/oracle/reutertp/data1/reutertp_temp01.dbf’ SIZE 20000M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

Command to rename a datafile name.This is normally used when you previously copied the datafile from one location to another location and you want to now update the control file .Be careful that you have to move the datafiles only when the database is completely shutdown.

alter database rename file ‘/global/reuter-db1/oracle/reutertp/data6/reutertp_GFD_data11.dbf’ to ‘/global/reuter-db1/oracle/reutertp/data3/reutertp_GFD_data11.dbf’ ;

select file_name, bytes from dba_data_files d, dba_tablespaces t where t.tablespace_name = d.tablespace_name and bytes/1024/1024 > 3000 ;

select d.status, sum(bytes/1024/1024)/count(*) from dba_data_files d, dba_tablespaces t where t.tablespace_name = d.tablespace_name group by d.status;

select file_name, t.tablespace_name tablespace, bytes from dba_data_files d, dba_tablespaces t where t.tablespace_name = d.tablespace_name and d.tablespace_name like ‘UN%’ ;

if problem with tablespace size then increase the tablespace size.

find out datafile path and name for the specified tablespace

select file_name,tablespace_name from dba_data_files;

Resize the data file with the following command

alter database datafile ‘/u10/oradata/RSOD/RSODundo_02_02.dbf’

resize 1000M;

resizing a datafile

alter database datafile ‘ /global/mrt-db/oracle/mrt/data1/ppmrtint_tools01.dbf

‘ resize 680M

adding datafile to a tablespace

alter tablespace INDEX_MED_OPG ADD DATAFILE ‘/var/opt/oracle/DSS1/data04/INMED_OPGDSS105.dbf’ size 1000M;

to change the maxbytes of a datafile do the below command

ALTER DATABASE DATAFILE ‘ /global/riske-b/oracle/redb/data2/redb_TRAINENGINE_interm_data01.dbf’ AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

Adding datafile to a tablespace

alter tablespace ts_GFD_indx add datafile ‘/global/reuter-db1/oracle/reutertp/data6/reutertp_GFD_indx25.dbf’ SIZE 2048M AUTOEXTEND ON NEXT 1024M MAXSIZE 32000M;

select file_name, t.tablespace_name tablespace, bytes from dba_data_files d, dba_tablespaces t where t.tablespace_name = d.tablespace_name and d.tablespace_name like ‘BRAINLOBZIP’;

changing a users quota on a tablespace to unlimited

alter user ims quota unlimited on ts_data_1;

DBA_USERS Describes all users of the database

ALL_USERS Lists users visible to the current user, but does not describe

them

USER_USERS Describes only the current user

DBA_TS_QUOTAS Describes tablespace quotas for all users

USER_TS_QUOTAS Describes tablespace quotas for current user

USER_PASSWORD_LIMITS Describes the password profile parameters that are assigned to the user

USER_RESOURCE_LIMITS Displays the resource limits for the current user

DBA_PROFILES Displays all profiles and their limits

RESOURCE_COST Lists the cost for each resource

Syntax for creating a user

ALTER USER avyrros

IDENTIFIED by ‘password’

DEFAULT TABLESPACE data_ts

TEMPORARY TABLESPACE temp_ts

QUOTA 100M ON data_ts

QUOTA 0 ON test_ts

PROFILE clerk;

Defining user quotas on tablespaces

Alter user listmgmt quota 1024M on ts_data01 quota 1024M on ts_index01

Finding out tablespace quotas for an user.

SQL> select * from dba_ts_quotas where username like ‘%LISTMGMT%’;

TABLESPACE_NAME USERNAME BYTES

—————————— —————————— ———-

MAX_BYTES BLOCKS MAX_BLOCKS DRO

———- ———- ———- —

TS_DATA01 LISTMGMT 661651456

1048576000 80768 128000 NO

TS_INDEX01 LISTMGMT 1048576000

1048576000 128000 128000 NO

SQL> Alter user listmgmt quota 1024M on ts_data01 quota 1024M on ts_index01;

User altered.

The following query lists all tablespace quotas specifically assigned to each user:

SELECT * FROM DBA_TS_QUOTAS;

TABLESPACE USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS

———- ——— ——– ———- ——- ———-

USERS JFEE 0 512000 0 250

USERS DCRANNEY 0 -1 0 -1

When specific quotas are assigned, the exact number is indicated in the MAX_BYTES column. Note that this number is always a multiple of the database block size, so if you specify a tablespace quota that is not a multiple of the database block size, then it is rounded up accordingly. Unlimited quotas are indicated by -1

Users having both zero quota and zero bytes used in a tablespace are not

listed in the report. TFSTSUSR reports on DBA_TS_QUOTAS, which holds

resource privilege information only at the tablespace level. A user can have

an overriding database resource privilege that can be seen by querying DBA_USERS.

V$SESSION Lists session information for each current session, includes

user name

V$SESSTAT Lists user session statistics

V$STATNAME Displays decoded statistic names for the statistics shown in

the V$SESSTAT view

PROXY_USERS Describes users who can assume the identity of other users

See Also: Oracle Database SQL Reference for complete descriptions

of the preceding data dictionary and dynamic performance views

2)WHEN MAX ERRORS REACHED OCCURS

the error ORA-00020: maximum number of processes (%s) exceeded is because of the following reasons.

1)when the number of processes accessing oracle database exceeds specified limit.

One day we got the above error on our development server.

On our development server the processes parameter is set to 100.

and we know that we dont have 100 users at the moment on dev, so the only reason could be that somebody has opened a connection in front end and then not closing it. or they might have a loop and then opening the connection. So asked the app team to check their front end code.

Edit the init<sid>.ora file and increase the default max no.

of processes form 50 to 200 or 300. Be careful to take memory

availabily into account. This will give you a temporary solution

only.

If you are using web-application. Restart the application on the MMS – console.

The processes that are active take up some of the system memory in what is called the PGA, which is not part of the shared memory parameters. There is also some SGA space for all processes (active or not – but it is small). Generally, if a process is not active, it is not using any resources to maintain a “place” for that process. So, a change in the processes parameter from 75 to 150 will not have much of an impact on the system’s oracle-dedicated memory (the SGA).

oracle error no of sessions exceeded

reason is the processes are created from asp.net or whatever and then not closed.oracle has a limit on no of processes specified in the init.ora file.

select value, name from v$parameter where name=’processes’;

select substr( b.owner||’.’||b.object_name, 1, 30) “TABLE”

, substr( SID||’,’||SERIAL#, 1, 10) “SESSION_ID”

, substr( lower(USERNAME), 1, 10) “USERNAME”

, lower(OSUSER) “OSUSER”

, lower(TERMINAL) “TERMINAL”

, substr( PROGRAM , instr( PROGRAM , ‘’, -1)+ 1) “PROGRAM”

from V$LOCKED_OBJECT a, all_objects b, v$session c

where a.OBJECT_ID = b.OBJECT_ID and a.SESSION_ID = c.SID

;

select username,TERMINAL, COUNT(*) from v$session GROUP BY TERMINAL,username;

select TERMINAL, COUNT(*) from v$session where schemaname = ‘KEYSTONE’ GROUP BY TERMINAL

select TERMINAL, COUNT(*) from v$session GROUP BY TERMINAL

select * from v$session where schemaname = ‘KEYSTONE’ and terminal = ‘SPL1W082’ GROUP BY TERMINAL

when filesystem is 100% full, sometimes we need to move the datafiles to another filesystem temporarily so the filesstem can be extended by the unix team.non system datafiles can be moved by the following way.

This method has the advantage that it doesn’t require

shutting down the instance, but it only works with

non-SYSTEM tablespaces. Further, it can’t be used for

tablespaces that contain active rollback segments or

temporary segments.

1. Take the tablespace offline.

2. Rename and/or move the datafile using operating

system commands.

3. Use the alter tablespace command to rename the file

in the database.

4. Bring the tablespace back online.

sql> connect sys/oracle as sysdba

sql> alter tablespace app_data offline;

sql> alter tablespace app_date rename

datafile ‘/u01/oracle/U1/data01.dbf ‘ TO

‘/u02/oracle/U1/data04.dbf ‘ ;

sql> alter tablespace app_data online;

The tablespace will be back online using the new name

and/or location of the datafile.

How to find pending transactions in the database?

set lines 250

column start_time format a20

column sid format 999

column serial# format 999999

column username format a10

column status format a10

column schemaname format a10

column osuser format a10

column process format a10

column machine format a15

column terminal format a10

column program format a25

column module format a10

column logon format a20

prompt ####################################################

prompt # current transactions:

prompt ####################################################

select t.start_time,s.sid,s.serial#,s.username,s.status,s.schemaname,

s.osuser,s.process,s.machine,s.terminal,s.program,s.module,to_char(s.logon_time,’DD/MON/YY HH24:MI:SS’) logon_time

from v$transaction t, v$session s

where s.saddr = t.ses_addr

order by start_time;

How to find if spfile is used?

this is the correct way:

select distinct isspecified from v$spparameter

if you see ‘TRUE’ there, spfile is used otherwise pfile.

select value from v$parameter where name = ‘spfile’; if this comes back with a value other than null, you are using an spfile.

if you are using a pfile, you can shutdown the database, and startup open spfile=’/path/to/spfile’;

what about if i just want to use pfile.. should i just delete the spfile?

you will need to shutdown and

startup open pfile=’/path/to/initSID.ora’

then you can delete the spfile

Find size of table space

select file_name, t.tablespace_name tablespace, bytes from dba_data_files d, dba_tablespaces t where t.tablespace_name = d.tablespace_name

Find free space in tablespace

select tablespace_name, sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

Find last analyzed time for the schema, tables etc .

SELECT table_name, last_analyzed FROM user_tables ORDERBY

last_analyzed DESC

select table_name,last_analyzed from dba_tables where owner like ‘SUP%’ and rownum < 4;

How to find users passwords in the database?

yeah you can connect to the user schema without knowing the user’s actual password.

here are the steps (It will work only if you have DBA privilege ie. access to dba_users and other dba_ views)

1. select username, password from dba_users;s

suppose there is an user by name user1 and you want to log into his schema. save the value in the password column for this user retrieved from query in step 1 (say the encrypted value is abcdefgh1234)

change the password of the user

alter user user1 indetified by temppass;

4. now you can connect as user1 by using the new password.

5. then restore the original password (either connected as user1 or through your own user).

alter user user1 identified by values ‘abcdefgh1234’;

10)9i how to delete and compute statistics

Analyze table test compute statistics; — this will analyze the indexes as well.—This is to update statistics.

analyze table test delete statistics; — delete the existing stats first and then

analyze table test compute statistics; — compute new stats

The above commands are 9i .

If you are using 10g then don’t use compute statistics. Use dbms_stats.gather_table_stats procedure.

find all indexes on a table

select index_name

from all_indexes

where owner = ‘SCOTT’

and table_name = ‘TEST’; — this is to find the indexes for a particular table

find all indexed columns

select index_name, column_name, column_position

from all_ind_cols

where owner = ‘SCOTT’

and table_name = ‘TEST’; — this is to help you find any columns being not added in the multi column index

get all session info

Detailed sessions informations are returned by :

SELECT sid, schemaname, terminal, osuser, program

FROM v$session

ORDER BY schemaname, program, terminal, osuser;

get sql info for a session

SELECT sql_text

FROM v$sqltext

WHERE concat(hash_value, address)=(SELECT concat(sql_hash_value, sql_address) FROM v$session WHERE sid=?????)

ORDER BY piece;

15) setting identifiers for different application modules

There is a way for programs to “identify themselves”. DBMS_SESSION.SET_IDENTIFIER (client_id VARCHAR2)

If you are working with Application Developers, they could use this to set the identifier differently for different application modules “New Client Entry”, “Invoicing”, “Daily Reports”, etc. Then v$session would show this information for the session running the SQL in question in the CLIENT_IDENTIFIER field.

That would be the easiest way. Other ways will take more work.

drop tablespaces incuding objects,constraints,datafiles . this drops the whole tablespace. Be very careful.

drop tablespace data_med_lcp including contents and datafiles cascade constraints

describe the different user privileges views here

granting privileges to users -> some examples

grant CREATE SYNONYM to price_dev

grant CREATE SESSION to price_dev

select grantor,privilege from dba_tab_privs where owner in (‘SYS’, ‘SYSTEM’)

You can query the user_tab_privs_recd and the user_sys_privs:

Select grantee, privilege from dba_tab_privs where owner = ‘SIEBEL’ and table_name= ’S_SRC’

select * from user_sys_privs where username=user;

select * from dba_sys_privs where grantee=’KEYSTONE’;

select ‘grant ‘ || privilege || ‘ to keystone ‘ from dba_sys_privs where grantee=’KEYSTONE’

select * from dba_role_privs where grantee=’SUPPDB’;

select * from user_tab_privs_recd ; select * from user_tab_privs_recd where owner in (‘SYS’, ‘SYSTEM’); select * from dba_role_privs where grantee = [user];

select ‘grant ‘ || granted_role || ‘ to keystone ‘ from dba_role_privs where grantee = ‘KEYSTONE’;

select * from role_role_privs;

role_sys_privs

role_tab_privs

(1) How can I select the role(s) that a user has been assigned….?

(A) select * from dba_role_privs where GRANTEE=’RKSREPGEN’

(2) How can I see all the ‘grants’ that have been assigned to a role….?

(A) select * from dba_sys_privs where GRANTEE=<ROLE_NAME>

(3) How can I see all the ‘grants’ that have been assigned to a user….?

(A) select * from dba_sys_privs where grantee = ‘RKSREPGEN’

Oracle provides several roles that are built into the database. Some of them are DBA, RESOURCE, and CONNECT. Most DBAs use them to make their tasks easier and simpler, but each of them is a security nightmare.

Let’s examine RESOURCE. This is generally given to schema owners. Did you know that it has UNLIMITED TABLESPACE system privilege, making it able to create any table anywhere in the database – including the SYSTEM tablespace? Obviously, this is not what you want. You would want to restrict the tablespaces to specific users only.

Similarly the role CONNECT, by default, has the CREATE TABLE/SEQUENCE/SYNONYM and a few more options. The name CONNECT somehow conveys the impression of the ability to connect only, not anything else. As you can see, however, the ability is much more than that. Another privilege, ALTER SESSION system privilege, allows the grantee to issue sql_trace = TRUE in their session. This can have far reaching consequences.

Therefore, it is not prudent to use built-in roles. Rather, identify the privileges users will need, put them in the appropriate roles which you have created, and use them to control authorization.

If possible, try not to use the Oracle built-in roles like RESOURCE and CONNECT. Create your own roles.

Try these

For system privileges

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_912a.htm#2073689

For role privileges

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_912a.htm#2063331

For object privileges

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_912a.htm#2063440

In Oracle 10gR2 things are fairly sane:

CONNECT role has only CREATE SESSION

RESOURCE has CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER and CREATE TYPE

In Oracle 9iR2 things get a little scary:

CONNECT has ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE and CREATE VIEW. Rather a scary lot for a role called ‘connect’

RESOURCE has CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER and CREATE TYPE

Resize datafiles

alter database datafile ‘/var/opt/oracle/DSS1/data04/INDEX_MED_OPGDSS102.dbf’ resize 2000M

Kill session

The SQL*Plus Approach

Sessions can be killed from within oracle using the ALTER SYSTEM KILL SESSION syntax.

First identify the offending session as follows:

SELECT s.sid,

s.serial#,

s.osuser,

s.program

FROM v$session s;

SID SERIAL# OSUSER PROGRAM

———- ———- —————————— —————

1 1 SYSTEM ORACLE.EXE

2 1 SYSTEM ORACLE.EXE

3 1 SYSTEM ORACLE.EXE

4 1 SYSTEM ORACLE.EXE

5 1 SYSTEM ORACLE.EXE

6 1 SYSTEM ORACLE.EXE

20 60 SYSTEM DBSNMP.EXE

43 11215 USER1 SQLPLUSW.EXE

33 5337 USER2 SQLPLUSW.EXE

The SID and SERIAL# values of the relevant session can then be substituted into the following statement:

SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#’;

In some situations the Oracle.exe is not able to kill the session immediately. In these cases the session will be “marked for kill”. It will then be killed as soon as possible.

Issuing the ALTER SYSTEM KILL SESSION command is the only safe way to kill an Oracle session. If the marked session persists for some time you may consider killing the process at the operating system level, as explained below. Killing OS processes is dangerous and can lead to instance failures, so do this at your own peril.

It is possible to force the kill by adding the IMMEDIATE keyword:

SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#’ IMMEDIATE;

This should prevent you ever needing to use the orakill.exe in Windows, or the kill command in UNIX/Linux

change sys password

There is no difference. But if your server and database are set up to allow you to connect as SYS without explicitly entering a password, you can then change the password for SYS (and any other user also, like: SYSTEM) if you want to. The syntax is simply:

alter user [username] identified by [new_password];

So, to change the password for SYSTEM to be “”secret”, just enter this:

alter user system identified by secret;

No, usernames and passwords in Oracle not case-sensitive, so this also works:

alter user SYSTEM identified by SECRET

But in 11g passwords are case-sensitive.so be careful.

more info on orapwd file.

REMOTE_LOGIN_PASSWORDFILE

Parameter type = String

Syntax REMOTE_LOGIN_PASSWORDFILE=

{NONE | SHARED | EXCLUSIVE}

Default value

NONE

REMOTE_LOGIN_PASSWORDFILE specifies whether Oracle checks for a password file and how many databases can use the password file.

NONE >> Oracle ignores any password file. Therefore, privileged users must be authenticated by the operating system.

SHARED >>More than one database can use a password file. Only user recognized by the password file is SYS.

EXCLUSIVE >> The password file can be used by only one database and the password file can contain names other than SYS.

you can query view

sql> select * from V$PWFILE_USERS;

to get listing of user enabled to connect to the database using ORAPWD file.

Using Password File Authentication

This section describes how to authenticate an administrative user using password file authentication.

Preparing to Use Password File Authentication

To enable authentication of an administrative user using password file authentication you must do the following:

Create an operating system account for the user.

If not already created, Create the password file using the ORAPWD utility:

ORAPWD FILE=filename PASSWORD=password ENTRIES=max_users

Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE.

Connect to the database as user SYS (or as another user with the administrative privilege).

If the user does not already exist in the database, create the user. Grant the SYSDBA or SYSOPER system privilege to the user:

GRANT SYSDBA to scott;

This statement adds the user to the password file, thereby enabling connection AS SYSDBA

As soon as Oracle sees ‘as sysdba’ it checks to see if you’re allowed to connect. If you are, you are connected as the SYS user. It doesn’t make any difference what username and password is used.

Give it a try with a username you know doesn’t exist: conn fred/flintstone as sysdba.

Unless you create the password file, the default password of SYS and SYSTEM is “change_on_install” and “system” respectively when you create a new database. Otherwise, the password for sys is sepcified when you create the password file:

orapwd file=orapw<sid> password=<sys’s password> entries=<max # user’s>

with REMOTE_LOGIN_PASSWORDFILE=exclusive in the init<SID>.ora

Logging as sysdba to the database

sqlplus ‘/as sysdba’

This will let you login to Oracle as sys using OS authentication (must be login as Oracle user in Unix/Windows).

find out whether a server is dedicated or shared.

you can use lsnrctl status or lsnrctl services to see if your doing shared or dedicated (or just

query SERVER from v$session)

I ran the select…

SQL> set echo on

SQL> select server from v$session where sid=(select sid from v$mystat where rownum=1);

DEDICATED

SQL> spool off

that shows a single dedicated server session.

select server, count(*) from v$session group by server;

when you have conversion errors in toad

Change the nls_lang in the registry editor of the client to .UTF8 or whichever character set you want.be careful about including the dot before the character set.

find out whether datafiles are online

select FILE#,STATUS from v$datafile;

Maximum cursors

To check how many cursors are in use run this sql statement:

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

Modify primary key columns

If foreign key constraints are referencing this primary key then find them by using query

SELECT A.TABLE_NAME PARENT, A.CONSTRAINT_NAME, B.TABLE_NAME CHILD, B.CONSTRAINT_NAME

FROM USER_CONSTRAINTS A, USER_CONSTRAINTS B

WHERE A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME

AND A.CONSTRAINT_NAME = ‘PK_RECEIVING_CENTER_MATRIX’;

After spooling above information and after confirmation from application team use below command to drop primary key and cascade all constraints.

ALTER TABLE TAB_PK DROP PRIMARY KEY CASCADE;

CASCADE option drops the foreign key constraints on any table referencing the PK being dropped.

Dropped the primary key using

alter table surveyor.RECEIVING_CENTER_MATRIX drop primary key ;

observed that the index was not dropped.

So In toad I had to manually drop the index.

Then I recreated primary key with

ALTER TABLE surveyor.RECEIVING_CENTER_MATRIX

ADD CONSTRAINT pk_RECEIVING_CENTER_MATRIX1

PRIMARY KEY (PDA_TYPE_ID,BUSINESS_UNIT,ISO_COUNTRY_CODE,RECEIVING_CENTER_ID) USING INDEX TABLESPACE CLM_DATA;

find table and index storage parameters

select OWNER,

TABLE_NAME, TABLESPACE_NAME,PCT_FREE, PCT_USED, INI_TRANS,

MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS,

MAX_EXTENTS, PCT_INCREASE, FREELISTS, FREELIST_GROUPS

from dba_tables where owner = ‘SURVEYOR’ and table_name = ‘RECEIVING_CENTER_MATRIX’

select OWNER,

INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME,

TABLE_TYPE,TABLESPACE_NAME, INI_TRANS, MAX_TRANS, INITIAL_EXTENT,

NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE,

PCT_THRESHOLD, INCLUDE_COLUMN, FREELISTS, FREELIST_GROUPS,

PCT_FREE

from dba_indexes where owner = ‘SURVEYOR’

and index_name = ‘PK_RECEIVING_CENTER_MATRIX’

change initial extent of table or any storage parameters

1) Pre-Oracle 8i — drop an recreate the table and then reload (probably best done with export/import)

2) 8i and beyond — use the new “alter table …. move tablespace …. storage..;’ feature.

there is a new function under the alter tablecommand called MOVE TABLE clause.

The move_table_clause lets you relocate data of a nonpartitioned table into a new segment,

optionally in a different tablespace, and optionally modify any of its storage attributes.

This can be done without taking down the database (however, I recommend that you take an

export for safety)…

The syntax is:

alter table <TABLE_NAME> move tablespace <NEW_TBSP> storage( <STORAGE_CLAUSE>);

One restriction, you must rebuild all associated indexes after moving a table because the move invalidates

them. You rebuild indexes with the following command;

alter index <INDEX_NAME> rebuild tablespace <NEW_TBSP> storage( <STORAGE_CLAUSE>);

get table_size

1 select a.owner, a.table_name,

2 a.initial_extent,a.next_extent,

3 a.MIN_EXTENTS, a.MAX_EXTENTS, a.TABLESPACE_NAME,

4 a.NUM_ROWS, b.extents NUM_EXTENTS

5 from dba_tables a, dba_segments b

6 where a.owner=’SCOTT’

7 and a.owner=b.owner

8 and a.table_name =b.segment_name

9* and a.table_name =’EMP’

alter table scott.emp

2 move tablespace DATA

3 storage (initial 200K next 200K minextents 1 maxextents 299)

4 /

Note: there is one catch—the table cannot contain a column of the long datatype

3) Random advice:

Don’t go crazy about compressing tables into a single extent (or just a few extents). There was

According to presentations at IOUG2000 — from Oracle’s Performance Group, TUSC, & others (Mike Ault, Dave Ensor…), the number of outstanding extents that a table has DOES NOT IMPACT performance until a high number is reached — they have noticed performance slowdowns after 1000 extents—even with Locally managed Tablespaces. I have tested this in Oracle 8 and have confirmed this.

Follow the traditional rules, such as one of mine is to size growth rates for apx 1 extent per month

When allocating storage for an Object (table or index), I like to set the initial extent size = next

extent size, plus I place objects of the same extent sizes in the same tablespace: Why?

This reduces the fragmentation of the extents and makes maximum efficient use of the tablespace’s space

(even with tables that grow and shrink dynamically) while reducing the possibility of creating dead

space fragments. And while extents do grow, In theory I shouldn’t have to touch them for a long period

of time.

So, you must should know you database & data Thus plan in advance—do calculations of the growth

and set storage sizes appropriately — while the new tools allow you to do storage management on the

fly, you do not want to be babysitting extent managment all the time—takes away from the really

challenging DBA tasks!

There are two good papers to read about extent sizing and fragmentation & Storage Management.

1) “How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation” from Oracle

presented at OracleWorld 1999

2) I cannot remember the name—but the is a great paper on www.orapub.com

handle database recovery when datafile gets corrupted,no backup files available, non system datafile

shutdown the database and Copy all the database files into some safe place !!

2. startup mount;

3. alter database datafile ‘D:oracleoradataABCDevOEM_Repository.dbf’ offline drop;

4. alter database open;

5. shutdown immediate;

6. Copy all files, here is the chance to make your first consistent cold backup

7. startup

8. Delete ‘D:oracleoradataABCDevOEM_Repository.dbf’ with explorer

9. Recreate your repository

find out export size before exporting.

select owner,sum(bytes)/1024/1024 from dba_segments where owner = ‘SURVEYOR’ group by owner ;

select owner,segment_type,sum(bytes)/1024/1024 from dba_segments where owner = ‘SURVEYOR’ group by owner,segment_type ;

select owner,segment_type,sum(bytes)/1024/1024 from dba_segments where owner = ‘DSADM’ group by owner,segment_type ;

select owner,sum(bytes) from dba_segments group by owner order by owner;

in 10g you can use expdp and find out the expected size of the export dump without the above sql queres.

compiling packages in sys schema

This script will give you a list of invalid objects. You can recompile invalid packages with the utlrp.sql SQL script that is supplied with Oracle. This script is maintained in the $ORACLE_HOME/rdbms/admin directory on your database server. You can also manually recompile packages if you prefer. Here is an example of calling the utlrp.sql script to recompile invalid database objects:

@?/rdbms/admin/utlrp.sql

31)find deprecated parameters in 10g

select name from V$PARAMETER where ISDEPRECATED = ‘TRUE’;

ORA-00376: file 40 cannot be read at this time .

If the tablespace datafiles exist and are online and checked three

times! check the following:

Find the status of the tablespaces:

SQL> select tablespace_name,status from dba_tablespaces;

Use the following query to find out the status of the datafiles.

SQL> select file#,name,status,enabled from v$datafile;

If the status says ‘recover’ then media recovery must be done by bringing the datafile online. Else, ORA-1113 will be encountered:

Make sure that the archived log destination has all required archived files because when you execute recover command it will prompt you for exact archived log files that are required.Oracle will automatically suggest the required file names and you just have to type enter if you agree.

To recovery the datafile try using the command:

SQL> recover datafile ‘<full_path_of_datafile>’;

SQL> alter database datafile <full_path_datafile_name> online;

ORA-00376: file %s cannot be read at this time

There was a problem accessing the datafile.

The datafile or tablespace the datafile belongs to is either offline or the datafile is gone.

eg:

SQL> select name, status from v$datafile where status not in (‘ONLINE’, ‘SYSTEM’) ;

alter database datafile ‘C:ORACLEORADATAOFFLINE.DBF’ online;

alter tablespace offline_ts online;

select * from v$recover_file;

Either set the tablespace or datafile back online or – if the datafile is gone – restore the datafile.

syntax for doing secure copy to different server

scp filename oracleuser@servername:path

difference between oracle 10g db control and grid control

Database Control is the HTTP Management environment and comes installed with the 10g Database. It can be used to manage one database (one target) at a time (standalone). To monitor more than one database, you must create a new console on a different port for each database.

10g Grid Control is the Enterprise version in that you can monitor different Target from different operationg systems at the same time. These include Application Servers, Listeners, Operation Systems, Non Oracle Database Systems using plugins (from 10gR2).

So, for someone who was using OEM 9i, there was connection in to Standalone Console (Java) and Oracle Management Server (Java and HTML). In 10g, they are replaced with Database Control (Java and HTML versions) and Grid Control respectively.

To administer multiple database 10g grid control is better.

OEM Database Control comes with Oracle Enterprise Edition and does 10g Grid control is a separate software or added with Oracle Enterprise Edition .

Among the both which will be good in terms of functionality.

You will need a license for GC. It’s not free whereas DB Console is. You go to OTN to download it or edelivery.

That’s correct. You need an additional license from that of the Database Server for Grid Control

How to get a formatted explain plan?

select plan_table_output from table(dbms_xplan.display(‘PLAN_TABLE’,null,’ALL’));

find out oracle version on the server using unix commands

sqlplus –version

but this command may not tell you the correct version when there are multiple oracle homes. Then you have to set more oracle environment variables.

So export ORACLE_HOME=/opt/oracle/product/10.2.0

And then

Export LD_LIBRARY_PATH=/opt/oracle/product/10.2.0/lib:$LD_LIBRARY_PATH

Then sqlplus –version gives the right version

all faqs about sqlplus errors can be found here

http://www.oracle.com/technology/support/tech/sql_plus/htdocs/faq101.html#A4828

find out long running job processes and the work progress.

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,

ROUND(SOFAR/TOTALWORK*100,2) “%_COMPLETE”

FROM V$SESSION_LONGOPS

WHERE

OPNAME NOT LIKE ‘%aggregate%’

AND TOTALWORK != 0

AND SOFAR <> TOTALWORK

invalid objects

1. @?/rdbms/admin/utlrp.sql

3. Verify that all expected packages and classes are valid:

4. SQL> SELECT count(*) FROM dba_objects WHERE status=’INVALID’;

SQL> SELECT distinct object_name FROM dba_objects WHERE status=’INVALID’

when an object in oracle get’s invalid, all the dependent objects get invalid also.

this is not a problem usually, because when you try to use an objects that is marked invalid, oracle will try to compile it first, and if it’s ok, it will work. if the compilation fails, the application running the sql with the invalid object will then get the relevant error message.

unless you get anything in the view DBA_ERRORS which will contain the compilation errors, you don’t need to worry.

real life new oracle dba scenarios – common database administration commands