Friday 22 March 2013

Script DBA


ORACLE BASIC QURIES
================
current release number ::
SELECT * FROM PRODUCT_COMPONENT_VERSION;
col comp_name format a40
SELECT comp_name, version, status FROM dba_registry;

Password fifle: 
--------------
ORAPWD FILENAME="<feilename>" PASSWORD="<password>" ENTRIES="<Maximum number of users>"
follow name format: orapw$ORACLE_SID
when you grant sysdba or sysoptr privilage to a user automatically his entry will be added to
the password file.for enabling the remote login you have to set the initial parameter REMOTE_LOGIN_ PASSWORDFILE 
to EXCLUSIVE or sHARED
define _editor=vi
create user jobin identified by jobin123 default tablespace USERS temporary tablespace TEMP
grant connect,resource,dba to jobin;
or you can give only selected privilages like:
grant create session to jobin;
grant create table to jobin;
you can allocate quota on a tablespace for the user like:
alter user jobin quota unlimited on USERS;
or
alter user jobin quota 100M on USERS;
dba can view the allocated quotas:
select * from dba_ts_quotas;
--alter the password
alter user jobin identified by xxxxxx;
--directory information
select DIRECTORY_NAME,OWNER,DIRECTORY_PATH from dba_directories;
Setting up a oracle database.
-----------------------------
1)Deside on SID
>setenv ORACLE_SID <sid> //windows
or
>export ORACLE_SID=<sid> //*ix
this sid should match the DB_NAME initialization parameter.
Create a initialization parameter file
--------------------------------------
store this file in the default location, otherwise at the time of starting up the database, you may have to specify 
the PFILE= parameter
in unix platforms the filename will be init$ORACLE_SID.ora (ex. initdemodb.ora) and the default location will be $ORACLE_HOME/dbs
Connect to Instance
-------------------
Start SQL*Plus and connect to your Oracle instance AS SYSDBA.
$ SQLPLUS /nolog
CONNECT SYS/password AS SYSDBA
OS AUTHENTIFICATION
-------------------
create user "OPS$AMERICAS\JOBIN_AUGUSTINE" IDENTIFIED BY EXTERNAL;
grant connect to "OPS$AMERICAS\JOBIN_AUGUSTINE";
Startup the database without mounting the database
--------------------------------------------------
>STARTUP NOMOUNT
Installing Oracle Clusterware
------------------------------
important : A single Oracle Inventory group is requied for all installations of Oracle software on the system.
if this is not exisiting, that means you are fist time installing oracle software.
to check whether you have inventory (installed oracle software) in your system.
$>more /etc/oraInst.loc //this file contains the inventory location and inventory group
OSDBA group (typically, dba)
$>/usr/sbin/groupadd dba
OSOPER group (typically, oper) //this group is optional.
$>/usr/sbin/groupadd oper
unprivilaged user (typically, nobody) this "nobody" user owns the external jobs executables after installation
Oracle Invenentory group (typically, oinstall) //this group owns the oracle inventory
$>/usr/sbin/groupadd oinstall
Oracle Software owner user (typically, oracle) //this user owns all the oracle software, must have Oracle Inventory group as primary group and OSDBA and OSOPER groups as secondary groups.
$>id oracle //to check whether oracle user is already present
$>/usr/sbin/useradd -u 200 -g oinstall -G dba[,oper] oracle //-u specifies the user id for the user, if you did not specify this, system will generate one for you but you should be noteing it.
//-g specifies the primary group
//-G specifies the secondary group(s)
$>passwd oracle
if user is created with wrong settings, you can modify like:
$>/usr/sbin/usermod -g oinstall -G dba[,oper] oracle
$>id nobody //verify that nobody is existing
$>/usr/sbin/useradd nobody
Need to have user setup in other nodes also in the same way. even the IDs should match.
$>id oracle //find out the details of oracle user say, uid=440(oracle) gid=200(oinstall) groups=201(dba),2002(oper)
$>/usr/sbin/groupadd -g 200 oinstall
$>/usr/sbin/groupadd -g 201 dba
$>/usr/sbin/groupadd -g 202 oper
$>/usr/sbin/useradd -u 440 -g oinstall -G dba[,oper] oracle
$>passwd oracle


Create Database
----------------
Issue the create database statement.
since it is difficult to remember all the parameters of the create statment, it is better to copy form some standard files.
this statment specifies the following things:
1. Database name
2. the SYS and SYSTEM user passwords.
3. There control file locations.
4. Log files (Redo logs)
5. Default temporary table space.
6. Default undo tablespace.
7. and other limits as paramenters. (these are restricted by operating system. so you may not be able to reach the theoretical maximum
as you specified in the parameter file)
Creating additional table space
-------------------------------
You need to create additional table space for users.
fist connect as sysdba
>CONNECT SYS/password AS SYSDBA
then issue the create tablespace command
>CREATE TABLESPACE users LOGGING 
DATAFILE '/vobs/oracle/oradata/mynewdb/users01.dbf' 
SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED 
EXTENT MANAGEMENT LOCAL
\Run the scripts to Build Data Dictionary views
----------------------------------------------
CONNECT SYS/password AS SYSDBA
@/vobs/oracle/rdbms/admin/catalog.sql
@/vobs/oracle/rdbms/admin/catproc.sql
EXIT
Create server parameter file (recommended)
------------------------------------------
server parameter file is used for dynamic means of maintaining initialization parameter.
> CONN SYS/PASSWORD AS SYSDBA
> CREATE SPFILE='/vobs/oracle/dbs/spfilemynewdb.ora' FROM
PFILE='/vobs/oracle/admin/mynewdb/scripts/init.ora';
>SHUTDOWN
Next time you start up, using the server parameter file
Using automatic undo management
-------------------------------
oracle recomends to use undo tablespace instead of rollback segment.
Automatic Shared Memory Management-10g onwards
----------------------------------
Enabling:- specify the SGA_TARGET (cannot excced SGA_MAX_SIZE) and set inividual values to 0
>alter system set sga_target = 500M scope=both;
>alter system set shared_pool_size = 0 scope=both;
>alter system set large_pool_size = 0 scope=both
>alter system set java_pool_size = 0 scope=both
>alter system set db_cache_size = 0 scope=both
minimum size of that pool can be specified by values for the pool instead of 0.
alter system set large_pool_size = 16M;
On most OS (with rare exception) , Oracle will allocate memory from OS equal to the SGA_MAX_SIZE at startup.
SGA_TARGET is how much you want to assign to automatic memory management. 
The difference between SGA_TARGET and SGA_MAX_SIZE will be reported as Free SGA Memory Available from
New background process Memory manager (MMAN) does the job.
select * from v$sgainfo;


Tablespace.. Block information
------------------------------
-- space usage (per tablespace)
---------------------------------------------------------------------------
set pages 200
col tablespace format a30
col total_sp format 9999.9999
col free_sp format 9999.9999
col used_sp format 9999.9999
col max_sp format 9999.9999
SELECT total.tablespace_name tablespace,
total.total_sp total_sp,
free.free_sp free_sp,
(total.total_sp - free.free_sp) used_sp,
total.max_sp
FROM 
(
SELECT tablespace_name,
sum(bytes/1024/1024/1024) total_sp,
sum(MAXBYTES/1024/1024/1024) max_sp
FROM dba_data_files 
GROUP BY tablespace_name ) total,
(
SELECT tablespace_name,
sum(bytes/1024/1024/1024) free_sp
FROM dba_free_space 
GROUP BY tablespace_name ) free
WHERE free.tablespace_name = total.tablespace_name
/
==query under construction do for union all onwards
SET LINES 200
SET PAGES 2000
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024 * 1024) "TS Size GB",
df.max_bytes / (1024 * 1024 * 1024) "TS Max GB",
SUM(fs.bytes) / (1024 * 1024 * 1024) "TS Free (MB)",
(df.max_bytes - df.bytes)/ (1024 * 1024 * 1024) "can grow",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free in TS",
(SUM(fs.bytes) + (df.max_bytes - df.bytes)) / (1024 * 1024 * 1024) "TS Free + Can Grow",
Round((SUM(fs.bytes) + (df.max_bytes - df.bytes)) * 100 / df.bytes ) "Max Free %"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes, sum(MAXBYTES) max_bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes,df.max_bytes
UNION ALL
SELECT '**'|| fs.tablespace_name || '**' tspace,
CUR_SZ / (1024 * 1024 * 1024),
MAX_SZ / (1024 * 1024 * 1024),
sg.TOT_BK * dt.BLOCK_SIZE / (1024 * 1024 * 1024) - sg.USED_BK * dt.BLOCK_SIZE /(1024 * 1024 * 1024) ,
null , null, null, null
FROM (SELECT tablespace_name,SUM(BYTES) CUR_SZ,SUM(MAXBYTES) MAX_SZ FROM dba_temp_files group by tablespace_name) fs, 
(SELECT TABLESPACE_NAME,SUM(TOTAL_BLOCKS) TOT_BK,SUM(USED_BLOCKS) USED_BK from 
gv$sort_segment group by TABLESPACE_NAME) sg,
dba_tablespaces dt
WHERE fs.tablespace_name = sg.TABLESPACE_NAME
AND fs.tablespace_name = dt.tablespace_name
ORDER BY 5 DESC;

try this also:-
set lines 200
set pages 2000
col tablespace_name format a15 head Tablespace
col file_name format a60 head DataFileName
col cursize form 999.99 heading "CurrSize|GB"
col maxsize form 999.99 heading "MaxSize|GB"
col Autoextensible form a4 heading Auto
break on tablespace_name SKIP 1 ON REPORT
compute sum label Total of cursize maxsize on tablespace_name
COMPUTE SUM OF cursize maxsize ON REPORT
select tablespace_name, 
FILE_ID,file_name, 
bytes/1024/1024/1024 cursize, 
maxbytes/1024/1024/1024 maxsize, 
autoextensible 
from dba_data_files 
where tablespace_name in (select distinct tablespace_name from dba_data_files)
order by tablespace_name
/


--Total Database size
SELECT sum(total.total_sp) total_sp,
sum(free.free_sp) free_sp,
sum(total.total_sp - free.free_sp) used_sp
FROM 
(
SELECT tablespace_name,
sum(bytes/1024/1024/1024) total_sp
FROM dba_data_files 
GROUP BY tablespace_name ) total,
(
SELECT tablespace_name,
sum(bytes/1024/1024/1024) free_sp
FROM dba_free_space 
GROUP BY tablespace_name ) free
WHERE free.tablespace_name = total.tablespace_name
/
select df.file_id,df.file_name,df.bytes/1024/1024,df.autoextensible,fs.free_space from dba_data_files df,
(select file_id,sum(bytes)/1024/1024 free_space from dba_free_space group by file_id having sum(bytes)/1024/1024 >1000) fs
where df.file_id=fs.file_id;
--growth of database
select to_char(creation_time, 'RRRR Month') "Month", sum(bytes)/1024/1024/1024 "Growth in GB"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month')
order by (to_date(to_char(creation_time, 'RRRR Month'),'RRRR Month'))
--table growth
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
DEFINE table_nm = 'MLOG$_COR_IDF_MSG';
SELECT * FROM
(SELECT TO_CHAR(th.analyzetime, 'YYYY/MM/DD hh24:mi') ANALYZEDATE, th.rowcnt, th.blkcnt blocks, th.blkcnt*&&blksize/1024/1024 SIZE_MB
FROM sys.wri$_optstat_tab_history th
WHERE th.obj# = (SELECT o.object_id FROM dba_objects o WHERE o.object_name = '&table_nm' AND o.object_type = 'TABLE')
UNION
SELECT TO_CHAR(t.last_analyzed, 'YYYY/MM/DD hh24:mi') ANALYZEDATE,t.num_rows rowcnt,t.blocks, t.blocks*&&blksize/1024/1024 SIZE_MB
FROM dba_tables t
WHERE t.table_name = '&table_nm')
ORDER BY ANALYZEDATE
/

col file_name format a60
SELECT FILE_NAME,FILE_ID,BYTES/1024/1024 MB,AUTOEXTENSIBLE AS AUTO FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'DRM_DAT01';
--segments consuming space in a tablespace
select owner,segment_name,segment_type,sum(bytes)/1024/1024/1024 from dba_segments where TABLESPACE_NAME = 'GWAC_DATA03'
group by owner,segment_name,segment_type
order by 4 desc
/
select segment_name,segment_type,bytes/1024/1024 from dba_segments where segment_name in ('F01131M','F4311','F4301','F0101');
==BIGGEST OBJECTS
col segment_name for a35
col owner for a25
col SEGMENT_TYPE for a20
col tablespace_name for a15
SELECT * FROM 
(select OWNER,segment_name,segment_type,tablespace_name, bytes/1024/1024/1024 as sz from dba_segments where segment_type in ('TABLE','TABLE PARTITION')
order by sz desc)
WHERE ROWNUM < 200
/
==BIGGEST TABLES
col segment_name for a35
col owner for a25
col SEGMENT_TYPE for a20
col tablespace_name for a15
SELECT * FROM 
(select OWNER,segment_name,SUM(bytes)/1024/1024/1024 as sz from dba_segments where segment_type in ('TABLE','TABLE PARTITION')
GROUP BY OWNER,segment_name
order by sz desc)
WHERE ROWNUM < 200
/

==best candiates for table purging
col segment_name for a35
col owner for a25
col SEGMENT_TYPE for a20
col tablespace_name for a15
SELECT * FROM 
(select a.OWNER,a.segment_name, sum(a.bytes)/1024/1024/1024 as sz from dba_segments a, dba_hist_sql_plan b
where b.OBJECT_TYPE = 'TABLE' AND b.OBJECT_OWNER NOT IN ('SYS','SYSMAN','SYSTEM','WMSYS','SERVICE_ORAMAINT','OUTLN','EXFSYS','DBSNMP')
and a.segment_name = b.OBJECT_NAME and a.owner = b.OBJECT_OWNER
group by a.OWNER,a.segment_name
order by sz desc)
WHERE ROWNUM < 100
/
select SID,SERIAL#,username,program,terminal from gv$session where username = 'SYS';

TEMP USAGE
------------
--TEMPORARY TABLESPACE USAGE
COL FILE_NAME FOR A50
select A.TABLESPACE_NAME,A.FILE_ID,B.FILE_NAME,B.BYTES/1024/1024 MB_TOTAL, A.BYTES_FREE/1024/1024 MB_FREE from V$TEMP_SPACE_HEADER A, dba_temp_files b
where A.FILE_ID = B.FILE_ID;

select TABLESPACE_NAME,FILE_ID, BYTES_USED/1024/1024 MB_USED, BYTES_FREE/1024/1024 MB_FREE from V$TEMP_SPACE_HEADER;

SELECT s.sid "SID",s.username "User",s.program "Program", u.tablespace "Tablespace",
u.contents "Contents", u.extents "Extents", u.blocks*8/1024 "Used Space in MB", q.sql_text "SQL TEXT",
a.object "Object", k.bytes/1024/1024 "Temp File Size"
FROM gv$session s, gv$sort_usage u, gv$access a, dba_temp_files k, gv$sql q
WHERE s.saddr=u.session_addr
and s.sql_address=q.address
and s.inst_id = u.inst_id
and u.inst_id = a.inst_id
and a.inst_id = q.inst_id
and s.sid=a.sid and u.tablespace=k.tablespace_name;
--first findout the temporary block size
select block_size from dba_tablespaces where contents = 'TEMPORARY'
--assuming 8192 is the block size.
select sum(blocks * 8192 )/1024/1024/1024 "GB in sort segments" from gv$sort_usage;
--srikrishna uses this
select inst_id,tablespace_name,total_extents,used_extents,total_blocks,used_blocks,max_size,max_sort_size from gv$sort_segment;
--
select (u.tot/d.tot)*100 "pct_temp_used" FROM
(select sum(u.blocks) tot from gv$tempseg_usage u) u,
(select sum(d.blocks) tot from dba_temp_files d) d
/
SELECT tablespace_name, file_id, block_id, BYTES / 1024 / 1024, owner FROM v$temp_extent_map;
SELECT FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024/1024 SZ_GB FROM dba_temp_files;
or
SELECT tablespace_name, file_id,count(*),SUM(BYTES)/1024/1024/1024 from v$temp_extent_map group by tablespace_name,file_id;
Resizing Temporary Tablespace
http://www.idevelopment.info/data/Oracle/DBA_tips/Tablespaces/TBS_3.shtml
alter database tempfile 3 resize 5G;
alter database tempfile 3 autoextend on;
Resize datafile.
----------------
alter database datafile 7 resize 100M //here 7 is the file_id
alter database datafile 4 autoextend on;
--REM Size per datafile size (there are os level limitation like 32GB for a single file)
----------------------------------------------------------------------------------
col tablespace_name format a10 head Tablespace
col file_name format a20 head DataFileName
col cursize form 999.99 heading "CurrSize|GB"
col maxsize form 999.99 heading "MaxSize|GB"
col Autoextensible form a4 heading Auto
break on tablespace_name
compute sum label Total of cursize maxsize on tablespace_name
select tablespace_name, 
file_name, 
bytes/1024/1024/1024 cursize, 
maxbytes/1024/1024/1024 maxsize, 
autoextensible 
from dba_data_files 
where tablespace_name = '&NM'
/
clear columns
clear breaks

Automatic Segment Space Management(ASSM)-10g onwards
----------------------------------
you can shrink segments.
find out the candiate for shrink using the query.
--reclaimable space in objects
Set linesize 200
col owner for a30
col table_name for a30
col tablespace_name for a30
select t.owner,t.table_name, s.tablespace_name,
round(s.bytes/1024/1024) actual_MB,
round((t.num_rows+1) * t.avg_row_len/1024/1024) optimal_MB,
round(s.bytes/1024/1024) -
round((t.num_rows+1) * t.avg_row_len/1024/1024) CLAIMABLE_MB
from dba_tables t, dba_segments s
where t.owner = s.owner
and t.table_name = s.segment_name
and round(s.bytes/1024/1024) - round((t.num_rows+1) * t.avg_row_len/1024/1024) > 200
order by 6,1,3 desc
/
SQL> 
set serveroutput on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
v_block_size number := 8; -- change the block size according to the database block size
v_result number;
begin
DBMS_OUTPUT.ENABLE(1000000);
dbms_output.put_line('Name, Type, Unformatted, 0-25% free, 25-50% free, 50-75% free, 75-100% free, Full Blocks, Free KB');
for r in (select owner, table_name, tablespace_name from dba_tables where OWNER NOT IN ('SYS','SYSTEM') and PARTITIONED='NO' AND TABLESPACE_NAME IN (SELECT TABLESPACE_NAME FROM dba_tablespaces WHERE SEGMENT_SPACE_MANAGEMENT = 'AUTO') order by table_name) loop
--dbms_output.put_line(r.TABLE_NAME ||','|| r.TABLESPACE_NAME);
dbms_space.space_usage (r.OWNER, r.TABLE_NAME, 'TABLE', 
v_unformatted_blocks, v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
v_result := (v_unformatted_blocks * v_block_size) + (v_fs1_blocks * (v_block_size * 12/100)) + (v_fs2_blocks * (v_block_size * 36/100)) + (v_fs3_blocks * (v_block_size * 62/100)) + (v_fs4_blocks * (v_block_size * 87/100));
dbms_output.put_line(r.OWNER||'.'||r.TABLE_NAME||',TABLE,'||v_unformatted_blocks||','||v_fs1_blocks||','||v_fs2_blocks||','||v_fs3_blocks||','||v_fs4_blocks||','||v_full_blocks||','||v_result);
end loop;
end;
/
=============================
C:\Docs\Oracle\indexfragmentation.sql
C:\Docs\Oracle\partitionedTableFragmentation.sql
C:\Docs\Oracle\partitionedIndexFragmentation.sql
===space in table partitions.
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('ERLOAD', 'QUOTEDTL', 'TABLE PARTITION', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes, 'QUOTEDTL_P56');
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('Blocks with 0-25% free space = '||v_fs1_blocks);
dbms_output.put_line('Blocks with 25-50% free space = '||v_fs2_blocks);
dbms_output.put_line('Blocks with 50-75% free space = '||v_fs3_blocks);
dbms_output.put_line('Blocks with 75-100% free space = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/

declare
m_total_blocks number;
m_total_bytes number;
m_unused_blocks number;
m_unused_bytes number;
m_last_ext_file_id number;
m_last_ext_block_id number;
m_last_used_block number;
begin
dbms_space.unused_space(
segment_owner => 'REMAP',
segment_name => 'ORDER_DETAILS',
segment_type => 'table',
total_blocks => m_total_blocks,
total_bytes => m_total_bytes,
unused_blocks => m_unused_blocks,
unused_bytes => m_unused_bytes,
last_used_extent_file_id => m_last_ext_file_id,
last_used_extent_block_id => m_last_ext_block_id,
last_used_block => m_last_used_block,
partition_name => null
);
dbms_output.put_Line(
'Allocated blocks: ' || m_total_blocks || ' ' ||
'Used blocks: ' || (m_total_blocks - m_unused_blocks)
);
dbms_output.put_Line(
'Allocated Bytes: ' || m_total_bytes || ' ' ||
'Used Bytes: ' || (m_total_bytes - m_unused_bytes)
);
end;
/
/* another verificaion checking whether BOOKING table in ARUP schema can be shrinked to 1300000 bytes or not*/
begin
if (dbms_space.verify_shrink_candidate
('ARUP','BOOKINGS','TABLE',1300000)
) then
:x := 'T';
else
:x := 'F';
end if;
end;
/
shrinking tables after identifing the right candidate.
alter table <table_name> enable row movement;
in single step> alter table <table_name> shrink space;
in 2 steps > alter table <table_name> shrink space compact;
> alter table <table_name> shrink space;
Moving table to another TABLESPACE
----------------------------------
SQL>alter table ISIS_USR.YP_REMAPCTRLOG move tablespace ts_ISIS_DATA;
SQL>Alter table sample_regional_sales move partition q2_1999 tablespace TS_CDB; //MOVE TABLE PARTITON
SQL>Alter table sample_regional_sales move subpartition q2_1999 tablespace TS_CDB; //MOVE TABLE SUBPARTION

shrink datafile
----------------
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/
column cmd format a100 word_wrapped
select BYTES/1024/1024,'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/


KILL SESSIONS
----------------
--new single query to get details
select c.inst_id,a.object_name,a.object_id,a.object_type,'ALTER SYSTEM KILL SESSION '''||c.sid||','||c.serial#||''';',
c.username,c.osuser,c.Machine,c.program
from dba_objects a,gv$locked_object b,gv$session c
where a.object_id=b.object_id and
b.session_id = c.sid;
--same as above but with colum formated
set lines 200
set pages 2000
col OBJECT_NAME for a25
col MACHINE for a30
col PROGRAM for a20
col OSUSER for a20
col USERNAME FOR a20
col OBJECT_TYPE for a10
col SID for 9999
select a.object_name,a.object_type,c.sid,c.username,c.osuser,c.Machine,c.program
from dba_objects a,gv$locked_object b,gv$session c
where a.object_id=b.object_id and
b.session_id = c.sid;

then kill the session
blocking sessions
-----------------
select s1.INST_ID ||'.'|| s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
--from Rehan Valter
select INST_ID,blocking_session, sid blockedsid, serial# blockedserial#, wait_class, seconds_in_wait from gv$session
where blocking_session is not NULL order by SECONDS_IN_WAIT,blocking_session;
--finding out unused sessions (sessions which is not giving anything to database)
select nvl(username,'<Internal>') username,program, 
osuser, sid||', '||serial# "SS", to_char(logon_time, 'mm/dd/yyyy hh24:mi:ss') "LOGON_TIME",
to_char(trunc(last_call_et/3600,0))||' '||' HRS '|| 
to_char(trunc((last_call_et - trunc(last_call_et/3600,0)*3600) / 60,0))|| ' MINS' "LAST_CALL" 
from v$session;
--cas user pageserver
SELECT 'ALTER SYSTEM KILL SESSION'''||sid||','||serial#||''';' FROM V$SESSION WHERE USERNAME LIKE 'CAS_USR' and PROGRAM LIKE 'page%'
SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||',@'||INST_ID||''' IMMEDIATE;' FROM GV$SESSION WHERE USERNAME = 'SYNCHRO';
--kill process
SELECT s.INST_ID, 'kill -9 ' || p.spid FROM gv$session s,gv$process p WHERE
p.addr = s.paddr and p.inst_id = s.inst_id and s.sid = 108;
Finding out what are the databases running on the server
--------------------------------------------------------
ps -ef | grep pmon
this will give all process monitors running (one for each database).
Finding out a particular table/view/database object.
----------------------------------------------------
select * from all_objects where object_name like '%DBA%PRIV%'
Table Details
-------------
select CONSTRAINT_NAME from dba_constraints where CONSTRAINT_TYPE = 'P' AND table_name='SHIP_CODE_TRANSLATE';
select COLUMN_NAME from dba_cons_columns where CONSTRAINT_NAME = 'SYS_C008336';
select owner,table_name,constraint_name from dba_constraints where r_constraint_name = 'PK_BACKLOG';
select ca.owner,ca.table_name,ca.constraint_name
from dba_constraints ca,dba_cons_columns cb
where ca.r_constraint_name = cb.constraint_name
and ca.r_owner = cb.owner and ca.constraint_type = 'R'
and cb.owner = 'SHIP_USER' and cb.table_name = 'BACKLOG';
see all constraints that point to a table: parent and child
select a.owner,a.table_name,a.constraint_name,b.table_name from dba_constraints a,dba_constraints b
where a.R_CONSTRAINT_NAME=b.CONSTRAINT_NAME and 
a.r_owner = 'CORP' and b.owner='CORP' and a.CONSTRAINT_TYPE = 'R' and b.CONSTRAINT_TYPE = 'P'
and b.table_name in ('xxxx')
DISABLE AND ENABLE CONSTRAINT WITHOUT VALIDATIG
alter table GLOBAL_HRDB.DELL_PERS_KEYS modify constraint SYS_C0049668 DISABLE;
alter table GLOBAL_HRDB.DELL_PERS_KEYS modify constraint SYS_C0049668 ENABLE NOVALIDATE;
finding out user roles/previlages
---------------------------------
SELECT * FROM DBA_ROLE_PRIVS ORDER BY GRANTEE
creating keys and indexes
-------------------------
ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME>
ADD CONSTRAINT PK_SETTINGS_FC PRIMARY KEY (FC)
USING INDEX
INITRAS 20
TABLESPACE <TABLESPACENAME>;

index rebuild
-------------
ALTER INDEX <shema>.<index> REBUILD [PARTITION <partition>] [TABLESPACE <tablespace>];
ALTER INDEX cost_ix REBUILD PARTITION p2;
ALTER INDEX cost_ix REBUILD PARTITION p3 NOLOGGING;

disabling foreign key constaraints
----------------------
select all foreign key columns like
select constraint_name,status from dba_constraints
where table_name like '%DST'
and constraint_type='R'
form the alter table statment to disable the contraint
spool in to a file
and execute it.
in one step
-----------
set feedback off
set verify off
set echo off
prompt Finding constraints to disable...
set termout off
set pages 80
set heading off
set linesize 120
spool tmp_disable.sql
select 'spool igen_disable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||
' DISABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c, user_tables u
where c.table_name = u.table_name;
select 'exit;' from dual;
set termout on
prompt Disabling constraints now...
set termout off
@tmp_disable.sql;
exit

Execution plan of a query
--------------------------
>set autotrace on exp
Execution monitoring of a query progress.
----------------------------------------
set lines 200
set pages 2000
col sql_text for a100
col inst_id for 99
col username for a30
col sid for 9999999
select b.inst_id,a.username,a.SID,substr(sql_text, 1, 4000) sql_text
from gv$sqlarea b, gv$session a
where b.address=a.sql_address and users_executing > 0;

select sql_id,sql_text,users_executing from v$sqlarea where users_executing > 0;
select sql_text,address,hash_value,child_number from v$sql where users_executing > 0;
select inst_id,sql_id,sql_text from gv$sqlarea where users_executing > 0;
select PLAN_HASH_VALUE from v$sql where sql_id='brhp13utj5nr4';
--give only sub sql which is actually under exectution.
select a.sql_id,a.sql_text,b.sid 
FROM v$sqlarea a JOIN v$session b ON a.SQL_ID = B.SQL_ID 
where A.users_executing > 0;
--findout full sql statement
select SQL_TEXT from v$sqltext where sql_id = 'apwvsgy9nh16t' ORDER BY PIECE;
or
set head off
set pages 0
set long 9999999
SELECT SQL_TEXT FROM DBA_HIST_SQLTEXT WHERE sql_id = 'apwvsgy9nh16t';
select EXECUTIONS from v$sqlarea where sql_id= 'dwtt8fpn8ydb5';
select SID,username,program,MACHINE,TERMINAL from gv$session where sql_id= '4rf91db681tgw';
SELECT s.INST_ID,s.SID,s.serial#,SQL_CHILD_NUMBER,p.spid,s.program FROM gv$session s,gv$process p WHERE
p.addr = s.paddr and p.inst_id = s.inst_id;
SELECT s.INST_ID,s.SID,s.serial#,SQL_CHILD_NUMBER,p.spid,s.program FROM gv$session s,gv$process p WHERE
p.addr = s.paddr and p.inst_id = s.inst_id and SQL_ID='6bzp5y1nsr62j' and s.INST_ID=3
--OSUSER='ServicePayPlanner';
SELECT 'ALTER SYSTEM KILL SESSION '''||s.SID ||','||s.serial#||''';' FROM gv$session s,gv$process p WHERE
p.addr = s.paddr and p.inst_id = s.inst_id and SQL_ID='d5fr9h7zf4s67' and s.INST_ID=3
=================use below query to see what is happening for a statement================================================
set lines 200
set pages 2000
SELECT a.inst_id,a.sid,a.EVENT,a.TOTAL_WAITS,a.TIME_WAITED_MICRO FROM GV$SESSION_EVENT a,Gv$session b WHERE a.SID = b.SID
and a.inst_id = b.inst_id and b.SQL_ID='482ykvw64vq0t' order by 5;
OR
SELECT a.inst_id,a.sid,a.EVENT,a.TOTAL_WAITS,a.TIME_WAITED_MICRO FROM GV$SESSION_EVENT a WHERE A.SID = 1429 order by 5;
===========================SUMMARY INFO==================================================================
col CENTI_SECONDS for 999999999999999
col WAIT_CLASS for a25
SELECT C.WAIT_CLASS,SUM(a.TOTAL_WAITS) as EVENT_COUNT,SUM(a.TIME_WAITED_MICRO) as CENTI_SECONDS FROM GV$SESSION_EVENT a,Gv$session b,V$EVENT_NAME c WHERE a.SID = b.SID
and a.EVENT_ID = c.EVENT_ID and a.inst_id = b.inst_id and b.SQL_ID='5mhphw3t0g2vq' GROUP BY c.WAIT_CLASS order by 3;
==================================================================================================================
SELECT SID,EVENT,TOTAL_WAITS,TIME_WAITED_MICRO FROM V$SESSION_EVENT WHERE SID in (SELECT SID FROM v$session WHERE USERNAME = 'GRIDDBA') order by 4;

SELECT B.WAIT_CLASS,SUM(TOTAL_WAITS),SUM(TIME_WAITED_MICRO) FROM V$SESSION_EVENT a,V$EVENT_NAME b WHERE a.EVENT_ID=b.EVENT_ID AND a.SID in
(SELECT SID FROM v$session WHERE SQL_ID='cb5mk300khksv') GROUP BY B.WAIT_CLASS order by 3;
OR
SELECT B.WAIT_CLASS,SUM(TOTAL_WAITS),SUM(TIME_WAITED_MICRO) FROM V$SESSION_EVENT a,V$EVENT_NAME b WHERE a.EVENT_ID=b.EVENT_ID AND a.SID = 792 GROUP BY B.WAIT_CLASS order by 3;

set lines 200
set pages 2000
SELECT B.WAIT_CLASS,SUM(TOTAL_WAITS),SUM(TIME_WAITED_MICRO) FROM V$SESSION_EVENT a,V$EVENT_NAME b WHERE a.EVENT_ID=b.EVENT_ID AND a.SID in
(SELECT SID FROM v$session WHERE USERNAME = 'GRIDDBA') GROUP BY B.WAIT_CLASS order by 3;
-TABLE ANALYSIS HISTORY CAN BE OBTAINED FROM 
DBA_TAB_STATS_HISTORY
SELECT wri$_optstat_tab_history
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3xayqhv2a15wu',0,'ALL'));
session monitoring:
select inst_id,count(*) from gv$session group by inst_id;
set lines 200
set pages 2000
col USERNAME for a25
col OSUSER for a25
col Machine for a30
col program for a30
select INST_ID,SID,USERNAME,OSUSER,Machine,program from gv$session where USERNAME is not null;
select OSUSER,count(*) from gv$session where program not like 'oracle%' group by OSUSER;
Identifing the problem session from OS process
----------------------------------------------
SELECT s.INST_ID, p.spid,s.sid FROM gv$session s,gv$process p WHERE
p.addr = s.paddr and p.inst_id = s.inst_id and p.spid = 25572;
select username,serial# from v$session where sid=459;

set lines 200
set pages 2000
col sql_text for a120
select sql_id,sql_text,users_executing from v$sqlarea where users_executing > 0;
select sql_text,users_executing from v$sqlarea where sql_id = '26m6k9zw4k0t5';
SELECT SQL_ID FROM v$session WHERE SID=585;
-----------------------------------------------------
Queries executing by current sessions
------------------------------------
select sesion.sid, sesion.username,optimizer_mode,hash_value, address,cpu_time, elapsed_time, sql_text
from v$session sesion, v$sqlarea sqlarea
where sesion.sql_hash_value = sqlarea.hash_value and sesion.sql_address = sqlarea.address
and sqlarea.users_executing > 0
-- and sqlarea.sql_id = 'd5fr9h7zf4s67'
-- and sesion.username is not null
order by sesion.USERNAME;

the above query may not fetch full sql_text because of the length problem for that use the
following query to fetch full text.
select sesion.sid,sqltext.address,sesion.USERNAME,sql_text,sqltext.piece
from v$sqltext sqltext, v$session sesion
where sesion.sql_hash_value = sqltext.hash_value
and sesion.sql_address = sqltext.address
and sesion.username is not null
order by sesion.username,sesion.sid,sqltext.piece
Status of Session
-----------------
this query shows GETS,READS and CHANGES
if these values are changing the session is working (query is in progress)
select io.sid,s.USERNAME,io.block_gets,io.consistent_gets,io.physical_reads,io.block_changes,io.consistent_changes 
from v$sess_io io, v$session s where s.sid = io.sid and s.USERNAME = 'SERVICE_EXPORT';
CPU utilization
---------------
SELECT
s.sid sid
, s.serial# serial_id
, s.status session_status
, s.username oracle_username
, s.osuser os_username
, p.spid os_pid
, s.program session_program
, s.machine session_machine
, sstat.value cpu_value
FROM 
v$process p
, v$session s
, v$sesstat sstat
, v$statname statname
WHERE
p.addr = s.paddr
AND s.sid = sstat.sid
AND statname.statistic# = sstat.statistic#
AND statname.name = 'CPU used by this session'
ORDER BY cpu_value DESC

export and import databases.
----------------------------
export
>exp "'/ as sysdba'" file=metadata.dmp log=meta.log full=y CONSISTENT=Y statistics=none ROWS=N;
>exp "'/ as sysdba'" file=goal.dmp log=goal.log owner=('GOAL') CONSISTENT=Y statistics=none Feedback=10000 ROWS=N;
>exp "'/ as sysdba'" file=shp.dmp log=shpdt.log TABLES=('SHIPPING.CARRIER_MSG_TYPE','SHIPPING.COD_RATE','SHIPPING.USER_ACTION_CODE','SHIPPING.RS_LANGUAGE','SHIPPING.RS_LOCATION_COUNTRY','SHIPPING.RS_LOCATION_LANGUAGE') statistics=none Feedback=10000
>exp "'/ as sysdba'" file=payap.dmp log=payap.log owner='ADP_INTERFACE','BANKACT_ADMIN','INGRIAN' statistics=none
1) ADP_INTERFACE (Paybridge schema)
2) BANKACT_ADMIN (Bank Account schema)
3) INGRIAN (Ingrian schema)
--you can specify OWNER-- list of owner usernames
--better to specify Feedback=10000 for big tables
import
>imp system/password file=filename.dmp log=imp.dmp fromuser=xxx touser=xxx Feedback=10000 ignore=Y
>imp "'/ as sysdba'" file=remap.dmp log=remapimp.log fromuser=REMAP touser=REMAP Feedback=10000 ignore=Y
import only few tables
extract the dump
>gunzip -d 04Feb052130isistxd3.dmp.gz
you will get 04Feb052130isistxd3.dmp
>imp fordba@isistxd3 file=04Feb052130isistxd3.dmp log=imp.log tables=(fd_ctrdtls,fd_ctrothrdtls) fromuser=isis_usr touser=fordba
this will import all data into fordba.
datapump
expdp and impdp
---------------
expdp "'/ as sysdba'" DIRECTORY=DPDUMP_HRDB_DIR1 DUMPFILE=GHRDR_%U.dmp LOGFILE=GHRDR.log SCHEMAS=GHRDR_BI_PREP,GHRDR,GHRDR_PREP exclude=statistics PARALLEL=4
expdp "'/ as sysdba'" DIRECTORY=DPDUMP_HRDB_DIR1 DUMPFILE=TRANS_HRDB%U.dmp LOGFILE=TRANS_HRDB.log SCHEMAS=TRANS_HRDB exclude=statistics PARALLEL=4
expdp "'/ as sysdba'" DIRECTORY=DPDUMP_HRDB_DIR1 DUMPFILE=TRANS_HRDB%U.dmp LOGFILE=TRANS_HRDB.log FULL=Y "EXCLUDE=TABLESPACE,PROFILE,DIRECTORY,statistics,SCHEMA:\"IN ('ARCHIVE_USER','TRANS_HRDB','GLOBAL_HRDB','GLOBAL_INTERFACE_USER','TRANS_HRDD','PERF_DIRECT','GCM_DATA','GCM_APP','COMP','COMP_HIST','SYS','SYSTEM','SYSMAN','RMANUSER','RMAN','EXFSYS','WMSYS','DBSNMP')\"" PARALLEL=4
expdp "'/ as sysdba'" schemas=PDS directory=DATA_PUMP_DIR dumpfile=PCK_QTC.dmp logfile=PCK_QTC.log EXCLUDE=SEQUENCE,PROCEDURE,LOB,PACKAGE,PACKAGE BODY,PROGRAM,TYPE BODY,TRIGGER,TABLE,INDEX,FUNCTION,TYPE INCLUDE=PCK_QTC CONTENT=METADATA_ONLY
impdp
-----
impdp "'/ as sysdba'" SCHEMAS=TRANS_HRDB REMAP_SCHEMA=TRANS_HRDB:TRANS_HRDB_DA DIRECTORY=DPDUMP_HRDB_DIR1 DUMPFILE=TRANS_HRDB%U.dmp LOGFILE=import.log PARALLEL=4
impdp "'/ as sysdba'" SCHEMAS=GHRDR_BI_PREP DIRECTORY=DPDUMP_HRDB_DIR1 DUMPFILE=GHRDR_%U.dmp LOGFILE=import_GHRDR_BI_PREP.log PARALLEL=4
impdp "'/ as sysdba'" tables=DELL_PERS_KEYS,PERSON_KEYS DIRECTORY=DPDUMP_HRDB_DIR1 DUMPFILE=GHRDR_%U.dmp LOGFILE=import_GHRDR_BI_PREP.log PARALLEL=4
impdp "'/ as sysdba'" DIRECTORY=DPDUMP_HRDB_DIR1 DUMPFILE=META.dmp SQLFILE=views.sql INCLUDE=TABLE,INDEX
impdp "'/ as sysdba'" directory=DPDATA1 dumpfile=expprocs.dmp sqlfile=procs.sql
impdp griddba/'grid1234$mega1'
monitor datapump progress
-------------------------
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12 
COL operation LIKE owner_name
COL job_mode LIKE owner_name
SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs;
--do above column width settings
COL SID FORMAT 99999
SELECT A.OWNER_NAME,A.JOB_NAME,A.SESSION_TYPE,B.SID,B.SEQ# FROM DBA_DATAPUMP_SESSIONS A,V$SESSION B WHERE A.SADDR = B.SADDR;
select username,opname,target_desc,sofar,totalwork,message from GV$SESSION_LONGOPS where sofar<>totalwork;
or
select opname,sofar,totalwork from GV$SESSION_LONGOPS;
include /exclude objects
set lines 200
set pages 2000
col object_path for a50
col comments for a100
SELECT object_path, comments FROM table_export_objects;
resetting / clearing up the master table for the job
>drop table sys.SYS_EXPORT_FULL_01 purge;
in simple step
SELECT 'DROP TABLE '||OWNER_NAME||'.'||JOB_NAME ||' PURGE;' FROM dba_datapump_jobs WHERE STATE='NOT RUNNING';
for more info
http://arjudba.blogspot.com/2009/05/how-to-cleanup-orphaned-datapump-jobs.html
export to .gz and then import from .gz
--------------------------------------
$mknod unit_mod.dmp p
$gzip <unit_mod.dmp > unit_mod.dmp.gz &
$exp userid/password file=unit_mod.dmp log=unit_mod.log tables=unit_mod
If you want to import from the zip file follow bellow commands.

gunzip -c < unit_mod.gz > unit_mod.dmp &
imp username/password file=unit_mod.dmp log=impunit_mod.log fromuser=test touser=test
database link
--------------
select OWNER,DB_LINK from dba_db_links
/
create public database link ISISTXD3.WORLD
connect to FORDBA identified by <password>
using '<TNSENTRY>';
CREATE DATABASE LINK "DCLP.WORLD"
CONNECT TO "SABA_REPORT_54" IDENTIFIED BY VALUES '050F8FA9BE8B3CB7B4D84D5EA67E7D2F4AC5D75CD07DCE041A'
USING '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = aussabaprddb1.us.dell.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = sabap)
)
)'
update one table using data from another table.
---------------------------------------------
UPDATE ISIS_USR.ED_TIERDTLS SET INT_MSG_HDR_DTL_NO=(SELECT INT_MSG_HDR_DTL_NO FROM ISIS_USR.ED_WELLTYPEDTLS WHERE ISIS_USR.ED_WELLTYPEDTLS.INT_WELL_TYPE_NO = ISIS_USR.ED_TIERDTLS.INT_WELL_TYPE_NO)
WHERE ISIS_USR.ED_TIERDTLS.INT_WELL_TYPE_NO IN (SELECT INT_WELL_TYPE_NO FROM ISIS_USR.ED_WELLTYPEDTLS)

grant quota on tablespace
>alter user isis_usr quota unlimited on ts_isis_data;

rename the database;
-------------------
1. Shutdown database
2. Startup mount
(sid is set to old name)
3. $ nid target=/ dbname=<new database name>
4. Shutdown database
5. Change init.ora/spfile name, i.e.: initnewname.ora
6. Change db_name parameter in init.ora/spfile, i.e.: db_name='newname'
7. Change ORACLE_SID to point to new database name
create password file
>orapwd file='/opt/oracle/product/10.1.0/db_1/dbs/orapw.ora' password=isis
or/and
change the init file parameter to
remote_login_passwordfile=NONE
CREATE spfile FROM pfile='initorabase.ora'
8. Startup mount
9. Alter database open resetlogs
10. Take a backup
CREATE TEMPORARY TABLESPACE temp01 TEMPFILE '/oradata/mytemp_01.tmp' SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp01; //from 9i onwards
Move files from one location to another
---------------------------------------
shutdown immeidate;
startup mount;
alter database backup controlfile to trace;
shutdown immediate;
--copy all file to new location
--edit init.ora to change the location of the controlfiles
startup mount pfile='.....';
alter database rename <old files> to <new file>;
(except temporary files)
alter database open;
ALTER TABLESPACE TEMP ADD TEMPFILE <new temp file> REUSE;
alter database tempfile <new temp file> online;
alter database tempfile <old temp file> offline;
alter database tempfile <old temp file> drop;
create spfile from pfile='/opt/oracle/admin/isistxsg/pfile/initisistxsg.ora'
Recompile all invalid objects
-----------------------------
Following cammand is for compile all invalid objects for specific schema.
execute DBMS_UTILITY.compile_schema ('SP_USR', FALSE);
For seeing all invalid objects 
select 'alter ' || decode(object_type,'PACKAGE BODY','PACKAGE',object_type)|| ' ' || owner || '.'|| object_name || ' compile ' ||
decode(object_type,'PACKAGE BODY',' body;',';') 
from dba_objects
where object_type in ('FUNCTION','PACKAGE','PACKAGE BODY','PROCEDURE','TRIGGER','VIEW')
and status = 'INVALID'
and owner = 'SP_USR'
order by object_type , object_name;
There is also an Oracle supplied script that does this. 
The script can be run from an SQL prompt as the user sys. It's found at ORACLE_HOME\rdbms\admin\utlrp.sql
making a database into archivelog mode
--------------------------------------
backup (create pfile from spfile also, because we are going to change parameter)
>ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=/ora-home/app/oracle/product/10.1.0/Db_1/dbs/arch' SCOPE=BOTH;
or in case of asm
>ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='location=+ARCH_1' scope=both sid='*';
>SHUTDOWN IMMEDIATE;
>STARTUP MOUNT;
>ALTER DATABASE ARCHIVELOG;
>ALTER DATABASE OPEN;
or
SQL> CONNECT sys AS SYSDBA
SQL> STARTUP MOUNT EXCLUSEVE;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ARCHIVE LOG START;
SQL> ALTER DATABASE OPEN;
or
add following lines into init.ora and restart the database;
log_archive_start = TRUE
log_archive_dest_1 = 'LOCATION=/arch_dir_name'
log_archive_dest _state_1 = ENABLE
log_archive_format = %d_%t_%s.arc

making database noarchive log mode.
-----------------------------------
startup mount;
alter database noarchivelog;
alter database open;
checking
>archive log list;
switch logfile
>alter system switch logfile;
manually archive filled redologs.
>ALTER SYSTEM ARCHIVE LOG ALL;
changing the archive log destination
------------------------------------
alter system set log_archive_dest_1='location=dgarc2 mandatory valid_for=(online_logfile,all_roles)' scope=both sid='*';
location="+DATA_1", valid_for=(ONLINE_LOGFILE,ALL_ROLES)
location="+ARCH_1", valid_for=(ALL_LOGFILES,ALL_ROLES)

tables with row count
---------------------
select 'SELECT '''||TABLE_NAME||''',COUNT(*) FROM '||OWNER||'.'||TABLE_NAME||' HAVING COUNT(*) > 1 ;' from dba_tables where OWNER='ISIS_USR';
SET HEADING OFF
SPOOL C:\TEMP.TXT
--EXEUCTE THE RESULT FROM THE ABOVE RESULT
SPOOL OFF
starting problem
ORA-01102: cannot mount database in EXCLUSIVE mode
there are some oracle process running.
>ps -eaf | grep oracle
kill off of them
>make sure that ORACLE_SID is correct
now startup
--Active session history.
select a.sid,cnt,a.username,a.status,a.machine,a.machine,a.PROGRAM,a.osuser 
from v$session a,(select session_id,count(*) cnt from v$active_session_history group by session_id) b
where a.sid = b.session_id
order by cnt desc
SELECT DISTINCT B.USERNAME,C.SQL_TEXT FROM 
V$ACTIVE_SESSION_HISTORY A LEFT JOIN DBA_USERS B ON A.USER_ID= B.USER_ID
LEFT JOIN V$SQLAREA C ON A.SQL_ID = C.SQL_ID
WHERE A.USER_ID >25
select * from v$sgastat where name like 'ASH buffers';
select min(sample_time), max(sample_time) from v$active_session_history;
TOP SQL:- follwoing query returns the most active sql in the last minute.
select sql_id, count(*),round(count(*)/sum(count(*)) over (), 2)pctload
from v$active_session_history
where sample_time > sysdate -1/24/60 and session_type <> 'BACKGROUND'
group by sql_id
order by count(*) desc;
TOP IO SQL
select ash.sql_id,USERNAME, count(*) 
from v$active_session_history ash, v$event_name evt,
DBA_USERS USR
where ash.sample_time between to_date('12-MAY-2010 23:00:00','DD-Mon-YYYY HH24:MI:SS') AND to_date('13-MAY-2010 01:00:00','DD-Mon-YYYY HH24:MI:SS')
--sysdate - 1/24/60 
AND ash.USER_ID = USR.USER_ID
and ash.session_state = 'WAITING' and 
ash.event_id = evt.event_id and evt.wait_class = 'User I/O' 
group by sql_id,USERNAME order by count(*) desc;

TOP SESSION / USER:
SELECT B.USERNAME,A.CNT FROM
(select USER_ID, COUNT(*) AS CNT from v$active_session_history where sample_time > sysdate -1/24/60 and session_type <> 'BACKGROUND' GROUP BY USER_ID) A,
DBA_USERS B 
WHERE A.USER_ID=B.USER_ID
ORDER BY CNT;
BAD SQL: THIS QUERY NEED TO BE REWRITTEN;
select e.event, e.total_waits -nvl(b.total_waits,0) total_waits,e.time_waited -nvl(b.time_waited,0) time_waited
from v$active_session_history b, v$active_session_history e, stats$snapshot sn
Where snap_time >sysdate - 1/24/60 And e.event not like '%timer' And e.event not like '%message%'
And e.event not like '%slave wait%' And e.snap_id =sn.snap_id And b.snap_id = e.snap_id-1
And b.event = e.event And e.total_timeouts > 100 And (e.total_waits -b.total_waits > 100
or e.time_waited -b.time_waited > 100);
BAD SQL: query need to be rewritten.
select sess_id,username,program,wait_event,sess_time,
round(100*(sess_time/total_time),2) pct_time_waited
from
(select a.session_idsess_id,
decode(session_type,'background',session_type,c.username) username,
a.program program,
b.name wait_event,
sum(a.time_waited)sess_time
from sys.v_$active_session_history a,
sys.v_$event_name b,
sys.dba_users c
where a.event# = b.event# and
a.user_id = c.user_id and
sample_time > '21-NOV-04 12:00:00 AM' and
sample_time < '21-NOV-04 05:00:00 AM' and
b.wait_class = 'User I/O'
group by a.session_id,
decode(session_type,'background',session_type,c.username),
a.program,
b.name),
(select sum(a.time_waited) total_time
OMF
---
control files : set parameter : DB_CREATE_ONLINE_LOG_DEST_n
redo logs : set parameter : DB_CREAT_ONLINE_LOG_DEST_n
ex: if you specify
DB_CREATE_ONLINE_LOG_DEST_1 = c:\Oracle\Oradata\TSH1
DB_CREATE_ONLINE_LOG_DEST_2 = d:\Oracle\Oradata\TSH1 
2 memeber files will be created in these location if you issue
ALTER DATABASE ADD LOGFILE
remove redolog group
ALTER DATABASE DROP LOGFILE GROUP 3
Table spaces:
CREATE TABLESPACE tsh_data;
CREATE TABLESPACE COMP_DATA DATAFILE SIZE 150M;
ALTER TABLESPACE tsh_data ADD DATAFILE;
DROP TABLESPACE GHRDR_INDEX01 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE GHRDR_BI_INDEX01 INCLUDING CONTENTS AND DATAFILES;
purge recyclebin
>purge dba_recyclebin;
--select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin;
select OWNER,OBJECT_NAME,ORIGINAL_NAME,SPACE from dba_recyclebin;
purge table CUSTWATCH."BIN$HGnc55/7rRPgQPeM/qQoRw==$0" ;

statistics collection of a table
>exec DBMS_STATS.gather_schema_stats(ownname => 'SHIPPING', cascade => TRUE, estimate_percent => dbms_stats.auto_sample_size);
for histogram generation
>exec dbms_stats.gather_schema_stats( ownname => 'SCOTT', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'for all columns size auto', degree => DBMS_STATS.DEFAULT_DEGREE);
to specify auto as above the table must have monitoring enabled using "alter table <table_name> monitoring"

>exec DBMS_STATS.delete_schema_stats('SYS');
>exec DBMS_STATS.gather_schema_stats('SYS');
exec dbms_stats.gather_table_stats(ownname => 'SHIPPING', tabname => 'SALES_ORDER',cascade => TRUE, estimate_percent => dbms_stats.auto_sample_size);
--from koo
exec dbms_stats.gather_table_stats(ownname => 'ERLOAD', tabname => 'ODCNTAMT', estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE, block_sample => FALSE, degree => 4, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO', granularity => 'ALL', cascade => TRUE);
--full database stats
exec dbms_stats.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 100,cascade => TRUE);

>ANALYZE TABLE <TABLE_NAME> COMPUTE STATISTICS;
ANALYZE TABLE ERLOAD.ODCNTAMT COMPUTE STATISTICS;
ANALYZE TABLE ERLOAD.ORDDTL COMPUTE STATISTICS;
--to see when is the last analysed
SELECT TABLE_NAME,TO_CHAR(LAST_ANALYZED,'DD-MON-YYYY HH24:MI:SS') from dba_tables where owner='TRANS_HRDB'
--to see the startup time of the instance
SELECT INST_ID,TO_CHAR(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') FROM Gv$instance;
--to see the progress of analysis
SELECT OPNAME,SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS WHERE TOTALWORK != 0 AND SOFAR <> TOTALWORK;


Database startup in restrited session 
-------------------------------------
start sqlplus without loggin in to any database : >sqlplus /nolog
startup without mounting database > startup nomount
startup to mount state without opening database > startup mount
startup with restricted access (only dba) > STARTUP RESTRICT;
removet the restriction for users >ALTER SYSTEM DISABLE RESTRICTED SESSION
size estimation / space estimation
----------------------------------
>SELECT * FROM table(dbms_spae.OBJECT_GROWTH_TREND ('ARUP','BOOKINGS','TABLE'));
Enterprise Manger console and agent
-----------------------------------
set the SID
>emctl status dbconsole|agent
>emctl start dbcolsole|agent
>emctl stop dbconsole|agent
reconfiguring
>emca -r
Patching windows
----------------
during the patching process keep on checking tlist kill any instance. of following
petrol agent, omagent,oracle,sqlplus
>taskmgr (task manager)
make sure that you have the required perl
if not just copy the perl related files to a directory (no need of install)
let perl be there in the following directory.
E:\oracle9207cpupatch\perl\5.6.1\bin\MSWin32-x86\perl.exe (check the perl installation and version)
cd E:\oracle9207cpupatch\5064365 (the extracted patchs)
D:\oracle\ora92\OPatch\opatch.pl (perl script for patching)

set oracle_home=D:\ORACLE\ORA92
E:\oracle9207cpupatch\perl\5.6.1\bin\MSWin32-x86\perl.exe D:\oracle\ora92\OPatch\opatch.pl version
(make sure that the opatch version is 55)
shutdown database and other sevices.
1) shutdown the database.
2) shutdown the listener.
2) stop DTC.

d1ct10naryport
set oracle_home=D:\ORACLE\ORA92
E:\oracle9207cpupatch\perl\5.6.1\bin\MSWin32-x86\perl.exe D:\oracle\ora92\OPatch\opatch.pl lsinventory
set oracle_home=D:\ORACLE\ORA92
E:\oracle9207cpupatch\perl\5.6.1\bin\MSWin32-x86\perl.exe D:\oracle\ora92\OPatch\opatch.pl apply
execute the catcpu.sql in $ORACLE_HOME\cpu
DR out of sync
---------------------
--got from phani, execute in primary
select max(archived.sequence#) primary,
max(applied.sequence#) standby,
archived.thread# thread
from v$archived_log archived, v$archived_log applied
where archived.thread#=applied.thread#
and archived.archived='YES' and applied.applied='YES'
group by archived.thread#;

select abs(source.seq-dest.seq) numseq
from
(select max(a.sequence#) seq
from v$archived_log a, v$instance b
where a.standby_dest = 'NO'
and a.thread# = b.thread#) source,
(select max(a.sequence#) seq
from v$archived_log a, v$instance b
where a.applied = 'YES'
and a.standby_dest = 'YES'
and a.thread# = b.thread#) dest
/
Select * from v$archive_gap;
select thread#,max(sequence#) from v$loghist group by thread#;
standby> select archived_thread#,archived_seq#,applied_thread#,applied_seq# from v$archive_dest_status;
//findout applying node
>select instance_name,host_name from gv$managed_standby a,gv$instance b 
where a.inst_id = b.inst_id and a.process like 'MRP%' ;
>select THREAD#,max(SEQUENCE#) from v$archived_log where applied = 'YES' group by thread#;
>select process,status,sequence# from v$managed_standby; --last archive log
>select message from v$dataguard_status;
>select MESSAGE from v$dataguard_status where FACILITY = 'Log Apply Services';
select group#,THREAD#,members, a,archived,status from v$log;
col group# for 99
col member for a60
SELECT a.group#, a.member, b.bytes,a.type FROM v$logfile a, v$log b WHERE a.group# = b.group#;
select group#,thread#,bytes,status from v$standby_log;
ALTER DATABASE ADD LOGFILE SIZE 512M;
or
ALTER DATABASE ADD LOGFILE THREAD 3 SIZE 50M;
or
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 5 SIZE 512M;
alter system switch logfile;
alter database drop logfile group 16;
--other queries
select database_role,open_mode from v$database; //findout database role

ASM Disks
--------- 
col name for a10
SELECT NAME,STATE,TOTAL_MB/1024 TOT_GB,FREE_MB/1024 FREE_GB, FREE_MB/TOTAL_MB*100 AS PCT_FREE FROM v$asm_diskgroup;
col name format a15
col path format a50
set lines 200
select a.group_number, a.name, b.name,b.path,b.TOTAL_MB,b.FREE_MB
from v$asm_diskgroup a, v$asm_disk b
where a.group_number=b.group_number
order by 1,3;
select name,path,total_mb,free_mb from v$asm_disk;
col name format a15
col path format a50
select group_number, name,header_status, TOTAL_MB, FREE_MB,PATH from V$asm_disk_stat order by 1,2;

asm operation
set lines 200
select * from v$asm_operation;
OR
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a20 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a30 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "" of total_mb used_mb on disk_group_name
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY
a.name
/
add disk to asm diskgroup
-------------------------
The following SQL query shows candidates
SELECT name, header_status, path FROM V$ASM_DISK;

ALTER DISKGROUP DATA_1 ADD DISK
'/u02/oradata/asm/data_lunxx' NAME DATA_1_0015,
'/u02/oradata/asm/data_lunxx' NAME DATA_1_0016,
'/u02/oradata/asm/data_lunxx' NAME DATA_1_0017,
'/u02/oradata/asm/data_lunxx' NAME DATA_1_0018;
ALTER DISKGROUP DATA_1 REBALANCE POWER 7;
remove disk from asm diskgroup
-------------------------------
ALTER DISKGROUP asm1dg2 DROP DISK dbg2_5;

--files inside ASM..
select b.name gname, a.name aname,a.system_created, a.alias_directory,BYTES/1024/1024
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
and c.type = 'TEMPFILE'
/
other options are : ARCHIVELOG AUTOBACKUP CONTROLFILE DATAFILE DATAGUARDCONFIG FLASHBACK ONLINELOG PARAMETERFILE TEMPFILE
select * from v$flash_recovery_area_usage;
--disk io
col path format a50
select PATH,READ_ERRS,WRITE_ERRS,BYTES_READ/1024/1024 AS READ_MB,BYTES_WRITTEN/1024/1024 AS WRITTEN_MB ,BYTES_READ/READ_TIME/1024/10.24 AS READ_MB_PER_SEC,BYTES_WRITTEN/WRITE_TIME/1024/10.24 AS WRITE_MB_PER_SEC from V$asm_disk;
--drop diskgroup
DROP DISKGROUP DATA_1 INCLUDING CONTENTS;
======Display asm disk usage======================
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
,free_mb free_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name
/
==========================================================
check lun for readablity
dd if=/u02/oradata/asm/data_lun9 of=/dev/null bs=1M
dd if=/u02/oradata/asm/data_lun5 of=/tmp/disk.dd bs=1048576 count=300
===========================================================
you can get file size info from Database.
select file_name,bytes/1024/1024/1024 from dba_data_files
unable to connect ASM instance. (maximum number of session/process exceeded)
stop the database.
connect to asm using sqlplus.
>alter system set process=60 scope=spfile;
shutdown and startup.
to mount a diskgroup
>ALTER DISKGROUP dgroup_01 MOUNT;
ASM DISK Performance. 
=====================
(STILL WORKING ON Caliberation. read_sec and wirtes_sec will be around 200 maximum and mb_sec 2.2 to 22.2)
select group_number, disk_number, name, reads/read_time read_sec, writes/write_time write_sec, read_errs, write_errs, read_time, write_time, (bytes_read)/1024/1024/read_time read_mb_sec, bytes_written/1024/1024/write_time write_mb_per_sec from v$asm_disk_stat order by group_number, disk_number;
RMAN recovery catelog verification
----------------------------------
connect to rman catlog using sqlpus : sqlplus rman/namr@rman9i.world
select c.db_key, c.dbinc_key, c.db_name, c.file#, max(c.completion_time) latest_completion_time, backup_type
from rc_backup_datafile c,
rc_database a,
rc_database_incarnation b,
rc_datafile d
where a.db_key = b.db_key
and a.dbinc_key = b.dbinc_key
and b.current_incarnation = 'YES'
and c.db_key = a.db_key
and c.dbinc_key = a.dbinc_key
and d.db_key = a.db_key
and d.dbinc_key = a.dbinc_key
and d.file# = c.file#
and (d.drop_time is null or (d.drop_time = d.creation_time))
and (d.creation_time < c.completion_time)
and c.db_name = upper('&NM')
group by c.db_key, c.dbinc_key, c.db_name, c.file#, backup_type
order by 5;

select max(sequence#) from rc_archived_log where db_name='ESCP';
SELECT MAX(SEQUENCE#) FROM RC_BACKUP_ARCHIVELOG_DETAILS WHERE DB_NAME = 'ESCP'; (ONLY ON 10G catelog)
list backup of archivelog all; (all 9i and 10g)
LIST BACKUP OF ARCHIVELOG FROM TIME 'sysdate-2'; (last 2 days archive log backups)
connect to target database and execute:-
select sequence#,deleted ,backup_count,completion_time from v$archived_log;
archive logs that can be deleted from priamary because that are backed up atleaset onces
---------------------------------------------------------------------------------------
select * 
from v$archived_log 
where archived='YES' and deleted='NO' and backup_count > 0 order by first_change# desc
or
--this query given by srikanth
select
-- 'change archivelog '''||substr(name,1,100)||''' delete;'
'DELETE NOPROMPT ARCHIVELOG UNTIL SEQUENCE = ' || max(SEQUENCE#) ||' thread '||a.thread#||' backed up 1 times to devie type SBT ;'
from
v$archived_log a
where
archived='YES' and
deleted='NO' and
backup_count > 0 and
standby_dest = 'NO' and
exists (
select
sequence#
from
v$backup_redolog b
where
b.sequence# = a.sequence# and
b.first_change# = a.first_change#) and
a.first_change# <=
(select
max(first_change#)
from
v$archived_log c
where
c.standby_dest = 'YES' and
c.applied = 'YES')
group by a.thread#
;

RMAN Process running.
--------------------
select
p.spid "SPID_THREAD"
, substr(s.sid,1,4) "SID"
, s.serial# "SERIAL#"
, substr(NVL(S.USERNAME, 'SYS'),1,10) "USERNAME"
, substr(s.status,1,3) "STATUS"
, substr(s.osuser,1,7) "OSUSER"
, substr(s.program,1,15) "PROGRAM"
, substr(S.TYPE,1,6)
, T.VALUE "CPU"
, to_char(logon_time,'dd-mon-yy hh24:mi:ss')
from sys.v_$process p
, sys.v_$bgprocess b
, sys.v_$session s
, V$SESSTAT T,
V$STATNAME N
where N.NAME = 'CPU used by this session'
and s.paddr = p.addr
and b.paddr(+) = p.addr
AND T.STATISTIC# = N.STATISTIC#
AND S.SID = T.SID
and S.USERNAME IS NOT NULL
--and S.USERNAME <> 'SYS'
and s.program like 'RMAN%'
order by logon_time desc
--to check job from a session is in progress or not.
SELECT SEQ#,STATE,EVENT,WAIT_TIME,SECONDS_IN_WAIT FROM V$SESSION_WAIT WHERE SID = 48
here: 
1) WAIT_TIME = Length of most recent wait in centiseconds 
(if STATE = ‘WAITED KNOWN TIME’
2) SECONDS_IN_WAIT = How long current wait has been so far 
(if STATE = ‘WAITING’)

RMAN progress
-------------
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK;
RMAN commands:-
BACKUP ARCHIVELOG ALL not backed up 1 times; #or 2 times.
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'TRUNC(SYSDATE-1)'; 
delete archivelog all backed up 1 times to device type sbt; //delete all archive logs that are already backedup to tape 
DELETE ARCHIVELOG UNTIL SEQUENCE 1640 THREAD 3;

Media Manager Waits:
SELECT p.SPID, EVENT, SECONDS_IN_WAIT AS SEC_WAIT, 
sw.STATE, CLIENT_INFO
FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p
WHERE sw.EVENT LIKE 'sbt%'
AND s.SID=sw.SID
AND s.PADDR=p.ADDR
;
--channel and process information
COLUMN CLIENT_INFO FORMAT a60
COLUMN SID FORMAT 9999
COLUMN SPID FORMAT 99999
SELECT s.SID, p.SPID, s.CLIENT_INFO
FROM V$PROCESS p, V$SESSION s
WHERE p.ADDR = s.PADDR
AND CLIENT_INFO LIKE 'rman%'
/
--media manager events (found no rows returned)
COLUMN EVENT FORMAT a10
COLUMN SECONDS_IN_WAIT FORMAT 999
COLUMN STATE FORMAT a20
COLUMN CLIENT_INFO FORMAT a30
SELECT p.SPID, sw.EVENT, sw.SECONDS_IN_WAIT AS SEC_WAIT,
sw.STATE, CLIENT_INFO
FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p
WHERE sw.EVENT LIKE 's%bt%'
AND s.SID=sw.SID
AND s.PADDR=p.ADDR
;
--arhive logs bakedup.
select THREAD#,MAX(SEQUENCE#) FROM V$BACKUP_REDOLOG GROUP BY THREAD#;
backup Archivelogs
------------------
rman target rman/newrman@escp.world catalog rman/namr@rman9i.world --for escp
run {
allocate channel t1 type 'SBT_TAPE'
parms 'ENV=(NSR_DATA_VOLUME_POOL=fulldb,NSR_CLIENT=e3odscl1,NSR_SERVER=limbue101)';
allocate channel t2 type 'SBT_TAPE'
parms 'ENV=(NSR_DATA_VOLUME_POOL=fulldb,NSR_CLIENT=e3odscl1,NSR_SERVER=limbue101)';
backup
filesperset 4
format 'escp_al_t%t_s%s_p%p'
(archivelog until time 'SYSDATE -1' thread 1 delete input);
}
use DELETE ALL INPUT in case you have many archive log destinations and want to delete the file from all destinations.
syntax:-
BACKUP/RESTORE ARCHIVELOG [FROM TIME 'SYSDATE-30'] [UNTIL TIME 'SYSDATE-7']
BACKUP/RESTORE ARCHIVELOG SCN BETWEEN 94097 AND 106245 [THREAD 1];
or 
... SEQUENCE BETWEEN integer1 AND integer2 
... FROM SEQUENCE integer1 UNTIL SEQUENCE integer2
testing the tape server by saving a small file.
>save -b fulldb -s ausbups310 C:\temp\test.txt
sample restore script for archive log restore:
run {
set command id to 'archlog_backup';
allocate channel t1 type 'SBT_TAPE'
parms 'ENV=(NSR_SERVER=ausbups370,NSR_DATA_VOLUME_POOL=arclog,NSR_COMPRESSION=FALSE)';
allocate channel t2 type 'SBT_TAPE'
parms 'ENV=(NSR_SERVER=ausbups370,NSR_DATA_VOLUME_POOL=arclog,NSR_COMPRESSION=FALSE)';
allocate channel t3 type 'SBT_TAPE'
parms 'ENV=(NSR_SERVER=ausbups370,NSR_DATA_VOLUME_POOL=arclog,NSR_COMPRESSION=FALSE)';
restore archivelog from sequence 52383 until sequence 52393 thread 2;
}
select max(SEQUENCE#) from V$BACKUP_REDOLOG; //maximum sequence number already in backup
CRS in RAC Env.
---------------
start CRS:- run /etc/init.d/init.crs with root privilage.
start the crs-demon
$sudo /opt/Dell/Oracle/run_oracle_root /etc/init.d/init.crs start
sudo /misc/images/linux/OracleScripts/run_oracle_root /etc/init.d/init.cssd stop
or
sudo /misc/software/linux/OracleScripts/run_oracle_root /etc/init.d/init.cssd stop
single instance with ASM also requires this to stop the cssd.
start all CRS resource
./bin/crs_start -all
unregister listner from CRS
./bin/crs_stat (findout the names)
./bin/crs_unregister ora.e1dlfccdb02.LISTENER_E1DLFCCDB02.lsnr
stop CRS:-
stop all clusterware stack:-
need to execute as root
>crsctl stop crs 
>#<CRS_HOME>/bin/oprocd stop
make sure that nothing is running from clusterware
>ps -ef |egrep "crsd.bin|ocssd.bin|evmd.bin|oprocd"
set the diagwait for clusterware, this lets the clusterware to write traces in case of node eviction / reboot
>crsctl set css diagwait 13 -force

crs version:
crsctl query crs softwareversion
crsctl query crs activeversion
cluster name:- 
$ORA_CRS_HOME/bin/cemutlo -n 
or check the name of the directory under $ORA_CRS_HOME/cdata
findout the voting disk
>crsctl query css votedisk
ocr location
ocrcheck
cssd (Oracle Cluster Synchronization Services) Non-RAC Installation
-----------------------------------------------------------------
Metalink note : 314173.1
sudo /opt/Dell/Oracle/run_oracle_root /u01/app/oracle/product/10.1.0/db_1/bin/localconfig reset $ORACLE_HOME
sudo /u01/app/oracle/product/10.1.0/db_1/bin/localconfig delete
you need to start cssd before creating ASM. 
/bin/su -l oracle -c exec /u01/app/oracle/product/10.1.0/db_1/bin/ocssd
CRS trouble shooting
-----------------------
see the log
$ORA_CRS_HOME/log/auspdsprddb01
User Profiles
-------------
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE; (Make sure that the parameter is set to true)
1.Creation of profile
1.1) you can create a composite limit
ALTER RESOURCE COST
CPU_PER_SESSION 10
LOGICAL_READS_PER_SESSION 2
PRIVATE_SGA 6
CONNECT_TIME 1;
You can assign this composite limit to each profile
ALTER PROFILE <PROFILE NAME> LIMIT
COMPOSITE_LIMIT 500;
1.2) You can create a profile directly
create profile TEST limit
password_life_time unlimited
password_grace_time unlimited
password_reuse_time unlimited
password_reuse_max unlimited
failed_login_attempts unlimited
password_lock_time unlimited
password_verify_function NULL;

CREATE PROFILE TEST LIMIT
SESSIONS_PER_USER 2
CPU_PER_SESSION 10000
CPU_PER_CALL 20
CONNECT_TIME 240
IDLE_TIME 20
LOGICAL_READS_PER_SESSION 50000
LOGICAL_READS_PER_CALL 400
PRIVATE_SGA 1024;
CREATE PROFILE TEMP LIMIT PASSWORD_VERIFY_FUNCTION NULL;
2.Assigning the profile to a user.
2.1)at the user creation time
CREATE USER <USERNAME> IDENTIFIED BY <PASSWORD>
DEFAULT TABLESPACE <DEFAULT TABLESPACE>
TEMPORARY TABLESPACE <TEMP TABLESPACE>
QUOTA 5M ON ...
PROFILE <PROFILE NAME>
2.2) Assign the profile to an existing user
ALTER USER <USERNAME> PROFILE <PROFILENAME>
copying schema
--------------
SELECT 'CREATE USER '||USERNAME||' IDENTIFIED BY VALUES '''||PASSWORD||
''' DEFAULT TABLESPACE ' ||DEFAULT_TABLESPACE ||' PROFILE '|| PROFILE
FROM DBA_USERS WHERE USERNAME IN ('HRIM');
or
copy privilages
SELECT DBMS_METADATA.GET_GRANTED_DDL(’ROLE_GRANT’, USERNAME) || ‘/’ DDL
FROM DBA_USERS where exists (select ‘x’ from dba_role_privs drp where
drp.grantee = dba_users.username)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL(’SYSTEM_GRANT’, USERNAME) || ‘/’ DDL 
FROM DBA_USERS where exists (select ‘x’ from dba_role_privs drp where
drp.grantee = dba_users.username)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL(’OBJECT_GRANT’, USERNAME) || '/' DDL 
FROM DBA_USERS where exists (select 'x' from dba_tab_privs dtp where
dtp.grantee = dba_users.username);
--if requied tablespace need to be created. and export/import
GETTING SOURCE FROM DATABASE
----------------------------
SELECT OWNER,TRIGGER_NAME,TABLE_NAME FROM DBA_TRIGGERS WHERE TABLE_NAME LIKE 'M%_B%';
set linesize 1000
set pagesize 0
spool PCK_FCONLY_MERGE_HEADER.sql
SELECT TEXT FROM DBA_SOURCE WHERE NAME='PCK_FCONLY_MERGE' AND TYPE ='PACKAGE' ORDER BY LINE;
spool off
spool PCK_FCONLY_MERGE_BODY.sql
SELECT TEXT FROM DBA_SOURCE WHERE NAME='PCK_FCONLY_MERGE' AND TYPE ='PACKAGE BODY' ORDER BY LINE;
spool off
Getting time on a particular timezone
SQL> select current_timestamp at time zone 'GMT' from dual;

CHANGING TIMEZONE ON RAC ENV.
----------------------------
set the below settings in .profile or .bash_profile
export TZ=Europe/London 
or 
export TZ=Eire
srvctl getenv database -d ehfdm
srvctl setenv database -d ehfdm -t TZ=Eire
srvctl stop nodeapps -n aushfdmdb1
srvctl stop nodeapps -n aushfdmdb2
srvctl start nodeapps -n aushfdmdb1
srvctl start nodeapps -n aushfdmdb2
srvctl getenv database -d ehfdm
srvctl start instance -d racdb -i racdb1, racdb3
SRVCTL ADD DATABASE -d <db_unique_name> -o <$ORACLE_HOME> -s MOUNT //add database to configuration with start option as mount
SRVCTL MODIFY DATABASE -d <db_unique_name> -o <$ORACLE_HOME> -s MOUNT //modify the configured database to start option as mount
srvctl modify asm -n aushrdprddb01 -i +ASM1 -o /u01/app/oracle/product/11.1.0/asm_1 //move asm to another oracle home
srvctl setenv instance -d hrdbp -i hrdbp1 -t "TNS_ADMIN=/u01/app/oracle/product/11.1.0/db_1/network/admin/"
relocate the service to another node:
srvctl relocate service -d lmsp -s lmspapp -i lmsp2 -t lmsp1 
srvctl relocate service -d lmsp -s lmsprpt -i lmsp1 -t lmsp2
modify vip associated with nodeapps
srvctl modify nodeapps -n node1 -A 10.173.224.244/255.255.248.0/eth0
--export configuraiton
srvconfig -exp out.txt
--start service
srvctl start service -d ADSPRDP -s DSREPORTING
example
-------
srvctl add database –d v10g –o $ORACLE_HOME –s $ORACLE_HOME/dbs/v10g_spfile #adding new database to srvctl
srvctl add instance –d v10g –I v10g1 –n aultlinux1 #adding instances to srvctl
srvctl add instance –d v10g –I v10g2 –n aultlinux2
srvctl add instance –d v10g –I v10g3 –n aultlinux3
srvctl add instance –d v10g –I v10g4 –n aultlinux4
srvctl add service –d v10g –s CRM –r V10g1,v10g2 –a v10g3,v10g4 #adding services to specific nodes
srvctl add service –d v10g –s AR –r v10g3,v10g4 –a v10g1,v10g2 #adding
granting access to v$ views:-
grant select_catalog_role to xxx;
SRVCTL usages
-------------
List all configured databases 
$ srvctl config database
configuration of a database (nodes, instances and home)
$srvctl config database -d SHFCCDEV
Configuration of nodeapps
$srvctl config nodeapps -n node1 -a -g -s -l
Status of all instances and services
$ srvctl status database -d SHFCCDEV
Status of node applications on a particular node 
$srvctl status nodeapps -n linux1
ASM status on a node
$srvctl status asm -n linux1

ASMCMD
------
DISK GROUP INFORMATION
ASMCMD>lsdg
CONNECTED INSTANCE INFORMATION
ASMCMD> lsct
serching for a file in asm
ASMCMD>find . -t BACKUPSET.*
display current directory in the command prompt
$>asmcmd -p
copy file to asm
cp -ifr +DATA_1/PONCP/DATAFILE/DATA01.264.698338435 sys@ausponcdrdb01.1521.+ASM1:+DATA_1/PONCP/DATAFILE/DATA01
let OMF take care of the exact name of the file.


"connected to idle instance"
-----------------------------
if you see database is running and not able to connect to that. chances re there for multiple homes.
ps -eaf | grep oralce 
my list some of the home info
othewise seach in .bash_profile
dropping datafiles
>startup mount
>alter database drop datafile 23 offline drop
>alter database open

EXPLAN PLAN
------------
first execute:
explain plan for
<select statment>
then execute:
set head off
set pages 0
set lines 200
set long 9999999
@?/rdbms/admin/utlxpls.sql

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
catcpu.sql failing (ORA-04021: timeout occurred while waiting to loc)
--------------------------------------------------------------------
1.shutdown immediate
2. startup migrate
3. @catcpu.sql
4. shutdown immediate
5. startup
clean files older than 30 days
------------------------------
find . -ctime +30 -exec rm {} \;
tablespace quota
>grant unlimited tablespace to remap;
AWR
---
select snap_interval, retention from dba_hist_wr_control;
SNAP_INTERVAL RETENTION
------------------- -------------------
+00000 01:00:00.0 +00007 00:00:00.0
this says snapshot interval is 1hr and RETENTION time is 7 days.
for snapshot intervals of 20 minutes and a retention period of two days--you would issue the following. The parameters are specified in minutes.
begin
dbms_workload_repository.modify_snapshot_settings (interval => 20,retention => 2*24*60);
end;
begin
dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 7*24*60);
end;

All information AWR collects can be viewed from DBA_HIST_% views
>select view_name from dba_views where view_name like 'DBA_HIST%';
DBA_HIST_METRIC_NAME gives the different Metrics of AWR. each metric is identified by a METRIC_ID. 
name of the metric is METRIC_NAME.
for example you can get the cpu utilization info with following query
select begin_time, intsize, num_interval, minval, maxval, average, standard_deviation
from dba_hist_sysmetric_summary where metric_id = 2075 order by BEGIN_TIME;
/* findout the snap ids you want */
SELECT SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME FROM DBA_HIST_SNAPSHOT where BEGIN_INTERVAL_TIME BETWEEN '11-MAY-10 11.00.20.000 PM' AND '12-MAY-10 02.00.59.000 AM' order by BEGIN_INTERVAL_TIME; --where snap_id = 17139
select begin_time, intsize, num_interval, minval, maxval, average, standard_deviation
from dba_hist_sysmetric_summary where metric_id = 2075 order by BEGIN_TIME;
/*findout all the users logged in to the database over the time*/
SELECT DISTINCT USERNAME FROM dba_hist_active_sess_history A,DBA_USERS B WHERE A.USER_ID=B.USER_ID;
SELECT * FROM DBA_ADVISOR_TASKS ORDER BY CREATED
SELECT * FROM DBA_ADVISOR_TASKS WHERE DESCRIPTION LIKE '%17115%'
/* get the findings */
SELECT * FROM DBA_ADVISOR_FINDINGS WHERE TASK_NAME = 'ADDM:1159647078_1_17116' AND TYPE='PROBLEM'
unused indexes..
SELECT OWNER,INDEX_NAME FROM DBA_INDEXES WHERE OWNER NOT IN ('SYS','SYSMAN','SYSTEM','WMSYS','SERVICE_ORAMAINT','OUTLN','EXFSYS','DBSNMP')
MINUS
SELECT OBJECT_OWNER,OBJECT_NAME FROM dba_hist_sql_plan WHERE OBJECT_TYPE='INDEX' AND OBJECT_OWNER NOT IN ('SYS','SYSMAN','SYSTEM','WMSYS','SERVICE_ORAMAINT','OUTLN','EXFSYS','DBSNMP')
/

unused tables
SELECT OWNER,TABLE_NAME FROM DBA_TABLES WHERE OWNER NOT IN ('SYS','SYSMAN','SYSTEM','WMSYS','SERVICE_ORAMAINT','OUTLN','EXFSYS','DBSNMP')
MINUS
SELECT DISTINCT OBJECT_OWNER,OBJECT_NAME FROM dba_hist_sql_plan WHERE OBJECT_TYPE='TABLE' AND OBJECT_OWNER NOT IN ('SYS','SYSMAN','SYSTEM','WMSYS','SERVICE_ORAMAINT','OUTLN','EXFSYS','DBSNMP')
/

Top Session / Top user over a period of snaps.
SELECT B.USERNAME,A.CNT FROM
(select USER_ID,COUNT(*) AS CNT from DBA_HIST_ACTIVE_SESS_HISTORY where SNAP_ID between 5336 and 5339 GROUP BY USER_ID) A,
DBA_USERS B 
WHERE A.USER_ID=B.USER_ID
ORDER BY CNT;

awr report
-----------
set linesize 500
set pagesize 0
set termout on;
set echo off
spool c:\awrrpt.htm;
SELECT output FROM TABLE(dbms_workload_repository.awr_report_html(991527143,1,9230,9231));
spool off
or
just execute awrrpt.sql avaiable at ORACLE_HOME/rdbms/admin/
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
or run the scritp in a non intractive mode
define inst_num = 1;
define num_days = 3;
define inst_name = 'sabat';
define db_name = 'SABAT';
define dbid = 991527143;
define begin_snap = 9367;
define end_snap = 9368;
define report_type = 'html';
define report_name = c:\awr_report.html
@C:\awrrpt\awrrpti.sql

Multiple Oracle Homes
---------------------
export ORACLE_HOME=/spwuat2/u01/app/oracle/product/9.2.0
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
otherwise you won't be able to connect to instance. "Connected to an idle instance".
if you did not set ORA_NLS33, you may get "error while loading create database character set".

DB Verify
----------
dbv userid=sys/password file=+DATA_1/essfd/datafile/users.264.626461727
here if you are login to server and expecting os authentification, the password can be anything. just put "sys/anything"
if archive logs destination
---------------------------
see $ORACLE_HOME/<SID>/bdump/alertlog
installation time settings like NLS_CHARACTERSET.
------------------------------------------------
select * from sys.database_properties;
select * from sys.props$;
sga details
-----------
select component, current_size from v$sga_dynamic_components
select * from V$SGA_TARGET_ADVICE order by sga_size_factor;

opatch
------
checking the progress
----------------------
/db/db01/home/oracle/product/10.1.0/.patch_storage/5901876/
tail -f Apply_5901876_07-15-2007_03-29-00.log
catcpu hanging.
---------------
set the job_queue_process=0 and bouce the db.
worst case
>alter database close.

rename standby datafile
-----------------------
>startup nomount
>alter database mount standby database
>alter database rename 'oldname' to 'newname'
Cache fusion
------------
SELECT FILE#,BLOCK#,CLASS#,STATUS,XNC FROM GV$CACHE_TRANSFER WHERE NAME ='ORDHXTRA'; 
GV$segment_statistics;
select * from 
(select statistic_name, object_name, owner, value, inst_id from
gv$segment_statistics where object_name = 'DELL_EMP_LISTER_WORK'
order by value desc)
where rownum < 20;
--monitor inter connect (TESTED)
select b1.inst_id, b2.value "RECEIVED",b1.value "RECEIVE TIME",((b1.value / b2.value) * 10) "AVG RECEIVE TIME (ms)"
from gv$sysstat b1, gv$sysstat b2 
where b1.name = 'gc current block receive time' and b2.name = 'gc current blocks received' 
and b1.inst_id = b2.inst_id ORDER BY 1;

--monitor inter node service time
SELECT a.inst_id "Instance", (a.value+b.value+c.value)/d.value "Current Blk Service Time"
FROM GV$SYSSTAT A, GV$SYSSTAT B, GV$SYSSTAT C, GV$SYSSTAT D
WHERE A.name = 'gc current block pin time' AND B.name = 'gc current block flush time' AND
C.name = 'gc current block send time' AND D.name = 'gc current blocks served' AND
B.inst_id=A.inst_id AND C.inst_id=A.inst_id AND D.inst_id=A.inst_id
ORDER BY a.inst_id;
alter system flush buffer_cache;
ALTER SYSTEM FLUSH SHARED_POOL;
--Analysing one particular session give SID
select n.name, s.value
from v$statname n , v$sesstat s
where s.sid = &sid and n.statistic# = s.statistic#
order by n.class, n.name
/

get the mastering informaiton
>select object_id,current_master, previous_master ,remaster_cnt from V$GCSPFMASTER_INFO where object_id = 144615
remaster if required: login to the node where you need to remaster the object.
$>oradebug setmypid
$>oradebug lkdebug -m pkey 144615

Windows instance creation
------------------------
oradim -NEW -SID myclone -STARTMODE auto

Transpotable tablespace
----------------------
findout the destination platforms supported by your source platform and endian format.
>SELECT PLATFORM_NAME,ENDIAN_FORMAT FROM v$transportable_platform
please the tablespace in read only.
SQL> alter tablespace USERS read only;
export the metadata about the trasportable tablespace.
exp sys transport_tablespace=y tablespaces=users file=tts.dmp log=exp_tts.log statistics=none
if endian formats are different then convert datafiles of the tablespace.
RMAN> CONVERT TABLESPACE USERS TO PLATFORM 'Linux 64-bit for AMD' FORMAT ='C:/transport_linux/%U';
ftp the files (both exp dmp and datafiles of tablespaces,if requied the converted ones) to other machine.
import the metadata
imp sys TRANSPORT_TABLESPACE=Y datafiles=<filelocations> file=tts.dmp log=imp_tts.log
set the tablespace in read-write
>alter tablespace users read write;
BLOCK CORRUPTON
---------------
select file#,block# from v$database_block_corruption; #what are the corrupted blocks
SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents
WHERE file_id = 57 and 568454 between block_id AND block_id+blocks-1;
pls dump file # 749, block # 3703936 by:
SQL> alter system dump datafile 749 block 2703936 ;

MANUAL RECOVERY of standby database
---------------
1. by default recovery files will check for archive logs in LOG_ARCHIVE_DEST_n
2. startup nomount
3. ALTER DATABASE MOUNT STANDBY DATABASE;
4. RECOVER STANDBY DATABASE or RECOVER FROM '/logs' STANDBY DATABASE
Creating Directory in Oracle
----------------------------
1. CREATE DIRECTORY <DIRECTORY_NAME> TO 'C:\bkup';
2. GRANT READ,WRITE ON DIRECTORY <DIRECTORY_NAME> TO <USER>
Object Dependancy
-----------------
column c1 heading "object|level" format a16
column c2 heading "object|name" format a40
column c3 heading "referencing|object" format a40
select
lpad (' ', 2 * (level - 1)) || to_char (level, '999') as c1,
owner || '.' || name || ' (' || type || ')' as c2,
referenced_owner || '.' || referenced_name || ' (' || referenced_type || ')' as c3
from
dba_dependencies
start with
owner = 'GLOBAL_HRDB'
and
name = 'DELL_PERS_REMOTE_V'
connect by prior
referenced_owner = owner
and prior
referenced_name = name
and prior
referenced_type = type
/

unregister database from a catelog database
-------------------------------------------
unregister database <database_name>
hang analyse
------------
sqlplus "/ as sysdba"
oradebug setmypid
oradebug unlimit;
oradebug -G all dump systemstate 266
oradebug -G all hanganalyze 2

TEPS TO GENERATE A HANGANALYZE TRACE FILE: Metalink note: 175006.1
======================================== 
Use the following set of commands to generate HANGANALYZE trace files.

1- Using SQL*Plus connect as "INTERNAL" (Oracle8i) or "/ AS SYSDBA" (Oracle9i) 
2- Execute the following commands:
SQL> oradebug hanganalyze 3 
... Wait at least 2 minutes to give time to identify process state changes.
SQL> oradebug hanganalyze 3 
3- Open a separate SQL session and immediately generate a system state dump.
SQL> alter session set events 'immediate trace name SYSTEMSTATE level 10';

Note: Starting with Oracle 9.2 HANGANALYZE can generate HANGANALYZE cluster wide.
Using this feature you can generate session dependencies to all the sessions 
connected to all the instances of the RAC cluster. Use the following set of commands 
to generate a RAC cluster wide HANGANALYZE:
1- Using SQL*Plus connect as "/ AS SYSDBA" 
2- Execute the following commands:
SQL> oradebug setmypid 
SQL> oradebug setinst all
SQL> oradebug -g def hanganalyze 3
... Wait at least 2 minutes to give time to identify process state changes.
SQL> oradebug -g def hanganalyze 3
or find out pid from v$process
SQL> ORADEBUG SETORAPID 77
SQL> oradebug unlimit;
SQL> oradebug dump systemstate 266

Moving datafile from one diskgroup to other diskgroup in DR
-----------------------------------------------------------
--Connect to DR server, and cancell recovery
SQL> recover managed standby database cancel;
--Find datafile_id, file ID,file_name from v$datafiles, which needs to move to other diskgroup
select file#,name from v$datafile where file#=173;

ID FileName
-- --------
173 +DSKGRP1/modsp_dr/datafile/dao_dar_data4m.560.638957467
--Logout from sqlplus connect to RMAN prompt without connecting any catalog
$RMAN target /
RMAN>
--Copy file from DSKGRP1 to DSKGRP2
RMAN>copy datafile '+DSKGRP1/modsp_dr/datafile/dao_dar_data4m.560.638957467' to '+DSKGRP2';

--create subdirectory modsp_dr under +DSKGRP2 folder thru asmcmd if modsp_dr is not exists
--Run Newname command
RMAN>run { 
set newname for datafile '+DSKGRP1/modsp_dr/datafile/dao_dar_data4m.560.638957467' to '+DSKGRP2/modsp_dr/datafile/DAO_DAR_DATA4M.256.639101811';
switch datafile 173; 
}
--Logout for RMAN prompt then connect TO ASMCMD and query for datafile in two diskgroups. you can see files in two location
ASMCMD>
---Drop old file from first diskgroup to gain space in first diskgroup
---Connect to ASM instance
$ export ORACLE_SID=+ASM
$ASM>dba
$sql>ALTER DISKGROUP DSKGRP1 drop file '+DSKGRP1/modsp_dr/datafile/dao_dar_data4m.560.638957467';
--recover in parellel 
recover standby database parellel 12;
recover managed standby database parellel 12;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
--cancel the recovvery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;
--restore archivelog on standby side
CONNECT TAGET /
run{
allocate channel c1 device type disk format '/dbscratch/%U';
backup force archivelog from logseq = 8 until logseq=708 thread=3;
release channel c1;
}
cd /dbscratch
scp * oracle@aushrddrdb01.us.dell.com:/dbscratch
CATALOG BACKUPPIECE '/dbscratch/h3j4r34r_1_1'; OR
catalog start with '/exp/';
RESTORE ARCHIVELOG from logseq = 8 until logseq=708 thread=3;
recover standby database parellel 12;
--legato NMO version
rpm -qa | grep lgto
nsrwatch <backupserver>
push backup to tape
-----------
run {
allocate channel t1 type 'SBT_TAPE'
parms 'ENV=(NSR_SERVER=ausbups360,NSR_DATA_VOLUME_POOL=arclog)';
backup archivelog from sequence 116791 until sequence 116791 thread 3;
}
>select 'Alter system kill session '''||sid||','||serial# ||''';' from v$session where osuser = 'svcgrid';
parellel query
----------------
SELECT qcsid,
sid,
NVL(server_group,0) server_group,
server_set,
degree,
req_degree
FROM SYS.V_$PX_SESSION
ORDER BY qcsid,
NVL(server_group,0),
server_set;
DBMS_FILE_TRANSFER
-------------------
Here we are using pull approch.
That means connecting from destination machine and pull the file over the db link.
Here are the steps:
1. Create a dblink to source database.
create public database link emp_prd
connect to system identified by constellati0n_
using 'emp_prd';
2. Create the directory in database and give privilage to dblink user
create directory sourcedir as '+ARCH_1/emp/ARCHIVELOG/backup';
grant read, write on directory sourcedir to system;
3. Create the Destination directiory.
create directory destdir as '+ARCH_1/EMPTEST/restore';
grant read, write on directory destdir to system;
4.Pull the data over dblike using DBMS_FILE_TRANSFER
BEGIN
DBMS_FILE_TRANSFER.GET_FILE(source_directory_object =>
'sourcedir', source_file_name => 'emp_l7jc9qkb_1_1',
source_database => 'emp_prd', destination_directory_object =>
'destdir', destination_file_name => 'emp_l7jc9qkb_1_1');
END;
/

test by copying files in same machine, 10.2 onwards this way you can move files to ASM.
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => 'DPDUMP_HRDB_DIR1',
source_file_name => 'TRANS_HRDB04.dmp',
destination_directory_object => 'DPDUMP_HRDB_DIR1',
destination_file_name => 'TRANS_HRDB04_1.dmp');
END;
/
or you can push the file from one side to another
BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object => 'DPDUMP_HRDB_DIR1',
source_file_name => 'TRANS_HRDB01.dmp',
destination_directory_object => 'DPDUMP_HRDB_DIR1',
destination_file_name => 'TRANS_HRDB01.dmp',
destination_database => 'HRDBS_LINK');
END;
/

Automated Memory Management
---------------------------
set following 2 parameters
workarea_size_policy = AUTO
pga_aggregate_target = 2G
you can get the details of memory usage like > select * from v$pgastat;
db file scattered read
----------------------
finding the session with high "db file scattered read"
SELECT sid, total_waits, time_waited FROM v$session_event
WHERE event='db file scattered read' and total_waits>0 ORDER BY 3,2;
hidden parameter values:
------------------------
SELECT a.ksppinm "Parameter",b.ksppstvl "Session Value",c.ksppstvl "Instance Value"
FROM sys.x$ksppi a,sys.x$ksppcv b,sys.x$ksppsv c
WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '_allow_level_without_connect_by%';

migration to ASM
-----------------
make sure that the database is in archivelog mode.
alter system set db_create_file_dest='+DATA_1' scope=both;
RMAN> backup device type disk incremental level 0 as copy tag 'ASM_Migration' database format '+DATA_AREA';
sql>select name from v$controlfile; //save the output as x,y,z
sql>alter system set control_files='+DATA_1/TEST/CONTFOLFILES/control01.ctl' scope=spfile;
shutdown immediate;
startup nomount;
rman>restore controlfile from 'x' //directly restore fromt the previous contolfile location
rman>alter database mount;
rman>switch database to copy;
rman>recover database;
alter database open;
alter database add logfile member '+DATA_1' to group 1; //do this for all groups
//remember that after adding oracle will not sync the data with new member so we need to switch the log 3 times to make the new members useful.
select name from v$tempfile;
alter tablespace temp add tempfile size 10m;
alter tablespace temp drop tempfile 'oldtempfile'
sql turning
-----------
@sqltrpt.sql
changing current schema
-----------------------
ALTER SESSION SET CURRENT_SCHEMA=MAT2D;

PARTITION INFORMATION FOR A TABLE | INDEX
---------------------------------
SELECT TABLESPACE_NAME, PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='DELL_PERS_NAMES' AND TABLE_OWNER='GLOBAL_HRDB'
ORDER BY TABLESPACE_NAME;
SELECT TABLESPACE_NAME, PARTITION_NAME FROM DBA_IND_PARTITIONS WHERE INDEX_NAME='DELL_PERS_NAMES_PART_TYPE' AND INDEX_OWNER='GLOBAL_HRDB';
--you can get the size info from dba_segments directly.
select partition_name, bytes/1024/1024 from dba_segments where segment_name = 'PK_CUSTOMER_PRODUCT_COMP';
set pages 999 lines 100
col tablespace_name format a20
col num_rows format 999,999,999
select p.partition_name,p.tablespace_name,p.num_rows,ceil(s.bytes / 1024 / 1204) mb
from dba_tab_partitions p,dba_segments s
where p.table_owner = s.owner and p.partition_name = s.partition_name and p.table_name = s.segment_name
and p.table_owner = 'GBL_SDR_AUD' and p.table_name = 'CUSTOMER_PRODUCT_LOCATION'
order by partition_position
/
--checking local or global index
SELECT locality FROM dba_part_indexes WHERE index_name = 'PK_CUSTOMER_PRODUCT_COMP';

SUBPARTITION INFORMATION FOR TABLE
--------------------------------
SELECT TABLESPACE_NAME, PARTITION_NAME, SUBPARTITION_NAME
FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME='POD_EURO'
ORDER BY TABLESPACE_NAME;
LOGIN ATTEMPTS
--------------
>show parameter audit_trail //this should be "db"
>audit session whenever not successful;
or
>AUDIT SESSION BY PURU WHENEVER NOT SUCCESSFUL;
>select username,OS_USERNAME,userhost,returncode,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') 
from dba_audit_session where timestamp>sysdate-10 and username='COMP';
>noaudit session; //stops the audit
Monitoring column usage
-----------------------
select r.name owner,o.name table ,c.name column, equality_preds,equijoin_preds,
nonequijoin_preds, range_preds, like_preds, null_preds, timestamp
from 
sys.col_usage$ u,sys.obj$ o,sys.col$ c,sys.user$ r
where
o.obj# = u.obj# and c.obj# = u.obj# and c.col# = u.intcol#
and o.owner# = r.user# and (u.equijoin_preds > 0 or u.nonequijoin_preds > 0);
EXECUTION PLAN FROM AWR
-----------------------
1) Locate the query in history views
select p.sql_id, s.sql_text
from dba_hist_sql_plan p,dba_hist_sqltext s
where p.sql_id = s.sql_id and p.OBJECT_NAME='ACTIVE_DIRECTORY_EVENTS'
order by p.cost desc;
2) Get the execution plan from awr
SELECT * FROM TABLE(dbms_xplan.display_awr('3xayqhv2a15wu'));
//or more specificly : sql_id,plan_hash_value,db_id,extend of info
SELECT * FROM TABLE(dbms_xplan.display_awr('brhp13utj5nr4',4254782894,NULL,'ALL'));

or simply quering directly on the table
col ID format 9
col OPERATION for a50
col OPTIONS for a50
col OBJECT_NAME for a30
select id, operation, options, object_name, cost from dba_hist_sql_plan where sql_id = 'brhp13utj5nr4' and plan_hash_value = 4254782894;
ExECUTION PLAN FROM CURSOR CACHE
--------------------------------
1) execute the statement
2) get the execution plan of the last executed statement.
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); 
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('3xayqhv2a15wu'));
utlrp filed with end of communication chanel.
because view recompile is not performend:
cd $ORACLE_HOME/cpu/view_recompile
sqlplus / as sysdba
SQL> STARTUP UPGRADE
SQL> @view_recompile_jan2008cpu.sql
oracm 
/root/start_oracm start
ocrcheck --check for ocr
cd $ORA_CRS_HOME/bin
ocrdump <filename>
ocrconfig -showbackup

Increase CSS Misscount in single instance ASM installations:729878.1
-----------------------------------------------------------
shutdown db and asm
$ORACLE_HOME/bin/localconfig delete //remove the css configuration
open $ORACLE_HOME/bin/localconfig in an editor and look for line "$CLSCFG -local -o $CH -l "AMERICAN_AMERICA.WE8ISO8859P1"
modfy to "$CLSCFG -local -o $CH -l "AMERICAN_AMERICA.WE8ISO8859P1" -misscount 300"
Re-add CSS by running $ORACLE_HOME/bin/localconfig add.
start asm and db
DGMGRL usage
------------
dgmgrl> connect /
dgmgrl>show configuration
ASM DISKGROUP CREATION
----------------------
CREATE DISKGROUP DATA_1 EXTERNAL REDUNDANCY DISK 
'/u02/oradata/asm/data_lun1',
'/u02/oradata/asm/data_lun2',
'/u02/oradata/asm/data_lun3',
'/u02/oradata/asm/data_lun4',
'/u02/oradata/asm/data_lun5',
'/u02/oradata/asm/data_lun6',
'/u02/oradata/asm/data_lun7',
'/u02/oradata/asm/data_lun8';
CREATE DISKGROUP ARCH_1 EXTERNAL REDUNDANCY DISK 
'/u02/oradata/asm/arch_lun1',
'/u02/oradata/asm/arch_lun2',
'/u02/oradata/asm/arch_lun3';
but it won't be mounted in the other nodes, so issue following commands in other nodes
ALTER DISKGROUP DATA_1 MOUNT;
ALTER DISKGROUP ARCH_1 MOUNT;
but diskgroups won't do auto mount until you specify it in asm init file (init+ASM1.ora) like:
asm_diskgroups='DATA_1','ARCH_1'
ASM copy files to remote ASM (11g)
ASMCMD> cp -ifr +DATA_1/PONCP/DATAFILE/data01.264.698338435 sys@ausponcdrdb01.1521.+ASM1:+DATA_1/PONCP/DATAFILE/DATA01

DBCA automation
---------------
cd /misc/software/oracle/tools/dbca 
for DR seed database:
./dell_dbca_auto.sh -rac -noasm -db aussyncdrdb01,aussyncdrdb02 -dbname syncp.dr.amer.dell.com -noarchlog

nls_length_semantics
--------------------
alter system set nls_length_semantics=char;
alter system set nls_length_semantics=byte;
alter session set nls_length_semantics=char;
alter session set nls_length_semantics=byte;
select * from sys.database_properties where PROPERTY_NAME='NLS_LENGTH_SEMANTICS';
default client behavior can be altered by setting enviorment variable.
export NLS_LENGTH_SEMANTICS=char
col DATA_TYPE for a30
select TABLE_NAME,column_name,COLUMN_ID,DATA_TYPE,CHAR_USED from dba_tab_columns a where table_name = 'HRD_PHOTO_TEMP';
11g password and user account
-----------------------------
set head off
set pages 0
set long 9999999
SELECT DBMS_METADATA.GET_DDL('USER', 'TROUX_USER') from dual;
set feedback off
set heading off;
set echo off;
set linesize 1000;
Set pages 2000;
set long 9999999;
set trimspool on
set trim on
set verify off
--col CMD format a300;
spool dblink.txt
select dbms_metadata.get_ddl('INDEX','XIE2REVIEW_DETAIL','PERFMAN_SCHEMA')||';' as CMD from dual;
select dbms_metadata.get_ddl('DB_LINK','DCLP.WORLD','TRANS_HRDB') as CMD from dual;
select dbms_metadata.GET_DDL('TABLE','AFF_CODES_GHRDR','GHRDR') as CMD from dual;
select dbms_metadata.GET_DDL('VIEW','HR_DIRECTORY_VIEW','SERVICE_HR') as CMD from dual;
select dbms_metadata.GET_DEPENDENT_DDL('CONSTRAINT','AFF_CODES_GHRDR','GHRDR') as CMD from dual;
select dbms_metadata.get_ddl('SYNONYM','HR_DIRECTORY','SERVICE_HR') as CMD from dual;
SELECT DBMS_METADATA.GET_DDL('PACKAGE','PCK_QTC','PDS') FROM dual;
spool off
--need to test
set verify off

10.2.0.3 to 10.2.0.4 db upgrade
--------------------------------
for CRS upgrade see the MyRAC doc
shutdown everything running from oracle home (DB,ASM, nodeapps/listner) and invoke the OUI
$cd /misc/software/oracle/10gDB/10.2.0.4_64/Patchset/Disk1
$./runInstaller 
Make sure that you are selecting the oracle home for upgrade from the combo. 
ones OUI session is completed. run root.sh with root privilage
sudo /misc/software/linux/OracleScripts/run_oracle_root /u01/app/oracle/product/10.2.0/db_1/root.sh
bring up the db instance in one node and set the cluster database to false
system set cluster_database=false scope=spfile; 
shudown and startup in upgrade mode
startup upgrade
run the followings, if possible spool the outputs,
can use dbua but i hate it.
@?/rdbms/admin/utlu102i.sql 
@?/rdbms/admin/catupgrd.sql 
shutdown the instance and startup in normal
@?/rdbms/admin/utlrp.sql
finally make sure that you really upgraded the database :)
and don't forget to put the cluster_database=true back.
dba_jobs
---------
select job,log_user,last_date,last_sec,next_date,next_sec,interval,broken from DBA_JOBS;
select job, next_date, broken, interval, failures from dba_jobs order by next_date;
-Jobs currently running
select sid,JOB from dba_jobs_running;
SELECT SID, TYPE, ID1, ID2 FROM V$LOCK WHERE TYPE = 'JQ'; --lock kept by currently running job

marking a job as brocken: job 14144
BEGIN
DBMS_JOB.BROKEN(14144, TRUE);
END;
/
marking job as not brocken:
The following example marks job 14144 as not broken and sets its next execution date to the following Monday:
BEGIN
DBMS_JOB.BROKEN(14144, FALSE, NEXT_DAY(SYSDATE, 'MONDAY'));
END;
/
If you are using 10g dbms_scheduler and not dbms_job package in 10g, then query the below :
select * from dba_scheduler_jobs;
select * from dba_scheduler_jobs_running;

Removing the Job from job queue
-------------------------------
BEGIN
DBMS_JOB.REMOVE(14144);
END;
/
Job not running : Checklist For Job Issues (search with same words in the following page)
http://support.confio.com/blog/post/why-wont-my-oracle-jobs-run/13/
Relink oracle home
-------------------
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk ioracle
11g Sql Plan Managment SPM
---------------------------
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE
OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE
OPTIMIZER_MODE = FIRST_ROWS | ALL_ROWS
SET LINESIZE 150
SET PAGESIZE 2000
SELECT PT.* FROM 
(SELECT DISTINCT sql_handle FROM dba_sql_plan_baselines 
--WHERE sql_text like '%SPM%'
) SPB,
TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(SPB.sql_handle, NULL,'TYPICAL +NOTE')) PT;
redo file size
--------------
select GROUP#,THREAD#,BYTES/1024/1024, 
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 7 SIZE 512M;
Library cache
--------------
select namespace, pins, pinhits, reloads, invalidations, pinhitratio * 100 hitratio from v$librarycache;
in summary:
select sum(pins) pins, sum(pinhits) pinhits, sum(reloads) reloads, sum(invalidations) invalidations, 100-(sum(pinhits)/sum(pins)) *100 reparsing from v$librarycache;
for RDA report please refer:
/misc/software/oracle/tools/RDA/rdacfg/rdawrap.sh bp 
C:\Docs\Dell\OpReady.txt
oswatch / ops readyness:
C:\Docs\Dell\OpReady.txt
oswatcher output.
cd $ORACLE_BASE/tools/osw/archive

DB Tend
--------
/misc/software/oracle/dba_scripts/dbtrends/DbTrends.zip
Set serveroutput on 
set lines 300 
set trimspool on 
set pages 10000 
set echo off 
set head off 
set feed off
spool affdbp.lst
Begin
dbms_output.enable(20000000000);
-- Call the procedure
SERVICE_ORAMAINT.pkg_projected_growth.prc_projected_growth_rate('CORP'); 
end;
/

PCT_FREE : calculate requied pct free for tables.
---------
set heading off;
set pages 9999;
set feedback off;
spool pctused.lst;
define spare_rows = 2;
define blksz = 4096;
select
'alter table '||owner||'.'||table_name||
'pctused '||least(round(100-((&spare_rows*avg_row_len)/(&blksz/10))),95)||' '||
'pctfree '||greatest(round((&spare_rows*avg_row_len)/(&blksz/10)),5)||';'
from dba_tables where table_name IN ('DELL_EMP_LISTER_WORK','DELL_EMP_LISTER','DELL_EMP_HIST_LISTER')
order by owner, table_name;
INI_TRANS
--------
col "Object" format a20
set numwidth 12
set lines 200
set pages 2000
select * from(
select DECODE(GROUPING(a.object_name), 1, 'All Objects', a.object_name) AS "Object",
sum(case when a.statistic_name = 'ITL Waits' then a.value else null end) "ITL Waits",
sum(case when a.statistic_name = 'buffer busy waits' then a.value else null end) "Buffer Busy Waits",
sum(case when a.statistic_name = 'row lock waits' then a.value else null end) "Row Lock Waits",
sum(case when a.statistic_name = 'physical reads' then a.value else null end) "Physical Reads",
sum(case when a.statistic_name = 'logical reads' then a.value else null end) "Logical Reads"
from
v$segment_statistics a
where
a.owner like upper('TRANS_HRDB')
group by rollup(a.object_name)) b
where (b."ITL Waits">0 or b."Buffer Busy Waits">0);
--modified script for seeing ITL waits
col cmd for a50
select * from
(select 'ALTER '||m.object_type||' '||m.owner||'.'||m.object_name||' INITRANS 15'||';' cmd ,m.OBJECT_TYPE,ind.INI_TRANS IND_INI_TRANS,tbs.INI_TRANS TBS_INI_TRANS, m.value, rank() over (order by m.value desc) value_rank
from v$segment_statistics m
left join dba_indexes ind
ON m.object_type = 'INDEX' and m.owner = ind.owner AND m.object_name = ind.INDEX_NAME
left join dba_tables tbs
ON m.object_type = 'TABLE' and m.owner = tbs.owner and m.object_name = tbs.TABLE_NAME
WHERE m.value > 0 AND m.owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP', 'OUTLN', 'WMSYS', 'PERFMON', 'EXFSYS', 'PERFMON') AND m.statistic_name = 'ITL waits')
where value_rank < 11;
SELECT EVENT,TOTAL_WAITS,TIME_WAITED_MICRO FROM v$system_event order by TIME_WAITED_MICRO;
row chaining
------------
select * from v$sysstat where name like 'table fetch cont%';
select a.SID,a.STATISTIC#,a.VALUE,b.name from v$sesstat a,v$statname b where a.statistic# = b.statistic# 
and b.name like 'table fetch cont%' and a.sid=2053;
--find out the tables suffering with row chaining
select owner, table_name, chain_cnt, num_rows, (chain_cnt*100)/num_rows chain_ratio, avg_row_len, pct_free, last_analyzed
from dba_tables where owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP', 'OUTLN', 'WMSYS', 'EXFSYS', 'PERFMON') and chain_cnt > 0
order by chain_cnt desc;
--find out row length
first findout the filed size of each column
SELECT COLUMN_NAME FROM dba_tab_columns WHERE TABLE_NAME = 'DELL_EMP_LISTER_WORK' AND OWNER = 'TRANS_HRDB';
SELECT max(vsize(PREV_LOCAL_JOBID)), max(PREV_LOCAL_JOBID) FROM TRANS_HRDB.DELL_EMP_LISTER_WORK;
select 3 + avg(nvl(dbms_lob.getlength(CASE_DATA),0)+1 +
nvl(vsize(CASE_NUMBER ),0)+1 +
nvl(vsize(CASE_DATA_NAME),0)+1 +
nvl(vsize(LASTMOD_TIME_T),0)+1
) "Total bytes per row"
from arch_case_data where case_number = 301;
sql tuning task
----------------
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 42491,
end_snap => 42492,
sql_id => 'brhp13utj5nr4',
plan_hash_value => 1532844933,
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 3600,
task_name => 'brhp13utj5nr4_AWR_tuning_task',
description => 'Tuning task for statement 1brhp13utj5nr4 in AWR.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
install oracle on ubuntu and other version linux
------------------------------------------------
./runInstaller -ignoresysprereqs
LOBS
---------
--findout lobs associated with tables.
col COLUMN_NAME for a30
col OWNER for a15
col TABLE_NAME for a20
SELECT A.OWNER,A.TABLE_NAME,A.COLUMN_NAME,A.SEGMENT_NAME,A.INDEX_NAME,B.TABLESPACE_NAME, B.BYTES/1024/1024 
FROM DBA_LOBS A JOIN DBA_SEGMENTS B ON A.SEGMENT_NAME = B.SEGMENT_NAME AND A.OWNER = B.OWNER AND A.OWNER = 'LOGISTICS' AND A.TABLE_NAME IN ('GPOSOURCEDATA','QB_MESSAGE_NAMED_STR');
--findout tables
col COLUMN_NAME for a30
col OWNER for a15
col TABLE_NAME for a20
SELECT A.OWNER,A.TABLE_NAME,A.COLUMN_NAME,A.SEGMENT_NAME,A.INDEX_NAME,B.TABLESPACE_NAME, B.BYTES/1024/1024 
FROM DBA_LOBS A JOIN DBA_SEGMENTS B ON A.SEGMENT_NAME = B.SEGMENT_NAME AND A.OWNER = B.OWNER AND A.SEGMENT_NAME IN ('SYS_LOB0000004919C00004$$');

alter table LOGISTICS.QB_MESSAGE_NAMED_STR modify lob (VALUE) (shrink space cascade);
alter table LOGISTICS.GPOSOURCEDATA modify lob (MESSAGE) (shrink space cascade);
--LOBINDEXES ARE attached to LOBSEGMENT. so when we move LOBSEGMENT, LOBINDEXES also will move.
ALTER TABLE GOAL_SCHEMA.GL_PRINTOUT MOVE LOB(REPORT_PRINTOUT) STORE AS (TABLESPACE LGS_LOB_DATA);
ALTER TABLE GOAL_SCHEMA.GL_MANIFEST MOVE LOB(MANIFEST_OBJECT) STORE AS (TABLESPACE LGS_LOB_DATA);
ALTER INDEX
Library cache wait:
select inst_id,sid,username,event from gv$session where seconds_in_wait >= 600
--and username ='DDMPROD'
and event in ('library cache lock')
/
select inst_id,sid,username,event from gv$session where seconds_in_wait >= 6
and username ='TRANS_HRDB'
and event in ('library cache lock')
/
SELECT 'ALTER INDEX '||INDEX_OWNER||'.'||INDEX_NAME||' REBUILD PARTITION '||PARTITION_NAME||';' FROM DBA_IND_PARTITIONS WHERE STATUS IN ('INVALID','UNUSABLE');
SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD PARALLEL '||DEGREE||';' from DBA_INDEXES WHERE STATUS IN ('INVALID','UNUSABLE');
tkprof
------
$tkprof raw_trace_file.trc output_file explain=apps/apps sort=(exeela,fchela) sys=no

Gaurdium
--------
check whether gardium is working.
ps -ef | grep stap
lsmod | grep ktap
c:\docs\dell\Gaurdium.txt
current_scn
-----------
col CURRENT_SCN for 999999999999999
select CURRENT_SCN from v$database;
CURRENT_SCN
----------------
10147692633875

sqlplus preference.
-------------------
keep lines in glogin.sql in $ORACLE_HOME\sqlplus\admin
monitoring utlrp.sql
--------------------
select owner,count(*) from dba_objects where status = 'INVALID' group by owner;
SELECT job_name FROM dba_scheduler_jobs WHERE job_name like 'UTL_RECOMP_SLAVE_%';
SELECT job_name FROM dba_scheduler_running_jobs WHERE job_name like 'UTL_RECOMP_SLAVE_%';
if utlrp is not working you can do it in manual way see section: Recompile all invalid objects
Monitor Rollback / Undo
---------------
select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",
decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated time to complete"
from v$fast_start_transactions

undo monitoring.
select segment_name,status from dba_rollback_segs where tablespace_name='UNDOTBS2' 
--and status = 'NEEDS RECOVERY';
start / stop restart ons
--------------------------
/u01/app/oracle/product/10.2.0/crs_1/bin/crs_stop ora.ausdrmprddb01.ons
/u01/app/oracle/product/10.2.0/crs_1/bin/crs_start ora.ausdrmprddb01.ons
/u01/app/oracle/product/10.2.0/crs_1/bin/crs_stop ora.ausdrmprddb02.ons
/u01/app/oracle/product/10.2.0/crs_1/bin/crs_start ora.ausdrmprddb02.ons
findout current session id:
----------------------------
select sid from v$mystat where rownum = 1;
escape &
---------
set define off
open cursors
------------
select sid,USER_NAME,count(*) cnt from v$open_cursor group by sid,USER_NAME order by cnt;

select 'grant '|| privilege ||' on '|| owner ||'.'|| table_name || ' to ' ||grantee || case when grantable = 'YES' then ' with grant option' else null end ||';'
from dba_tab_privs
WHERE grantee = 'EBI20_ETL_APP_USAGE'
/
long running transactions
-------------------------
col sid for a25
col xid for a30
SELECT t.inst_id, sid||','||serial# sid,xidusn||'.'||xidslot||'.'||xidsqn xid,(SYSDATE - start_date ) * 1440 runlength ,terminal,program
FROM gv$transaction t, gv$session s
WHERE t.addr=s.taddr AND t.inst_id = s.inst_id AND ((SYSDATE - start_date) * 1440) > 20
/
Posted by Unknown at 06:56
Labels:

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)