This is my Notes as an Oracle DBA over several years Last updated on 3rd June 2010 current release number : SELECT * FROM PRODUCT_COMPONENT_VERSION; 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 999.9999 col free_sp format 999.9999 col used_sp format 999.9999 col max_sp format 999.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 / 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 compute sum label Total of cursize maxsize on tablespace_name 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')) col file_name format a60 SELECT FILE_NAME,FILE_ID,BYTES/1024/1024 MB,AUTOEXTENSIBLE AS AUTO FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'TRANS_DATA01'; --segments consuming space in a tablespace select segment_name,segment_type,sum(bytes)/1024/1024/1024 from dba_segments where TABLESPACE_NAME = 'PRODDTAT' group by segment_name,segment_type order by 3 desc TEMP USAGE ------------ 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 v$session s, v$sort_usage u, v$access a, dba_temp_files k, v$sql q WHERE s.saddr=u.session_addr and s.sql_address=q.address and s.sid=a.sid and u.tablespace=k.tablespace_name; select tablespace_name,extent_size,total_extents,total_blocks,max_size,max_blocks from v$sort_segment; SELECT tablespace_name, file_id, block_id, BYTES / 1024 / 1024, owner FROM v$temp_extent_map; or SELECT tablespace_name, file_id,count(*) from v$temp_extent_map group by tablespace_name,file_id; 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 --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 120 col owner for a15 col table_name for a24 col tablespace_name for a20 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 1,3,5 desc SQL> set serveroutput on SQL> 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 ('<SCHEMA_NAME>', '<SEGMENT_NAME>', '<SEGMENT TYPE like TABLE|INDEX|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); 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> 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; 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 a30 col MACHINE for a30 col PROGRAM for a20 col OSUSER for a20 select a.object_name,a.object_id,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 ; --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%' --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 = 2024; 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 a120 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'; --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';S select SID,username from gv$session where sql_id= 'crkb4bf9mv3f9'; 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 SELECT SID,EVENT,TOTAL_WAITS,TIME_WAITED_MICRO FROM V$SESSION_EVENT WHERE SID in (SELECT SID FROM v$session WHERE SQL_ID='1b0p26k0dbfz7') 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='crkb4bf9mv3f9') 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; SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('a9m4d5v75a75u',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; 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 --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 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 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; >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; 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')); Moving table to another TABLESPACE ---------------------------------- SQL>alter table ISIS_USR.YP_REMAPCTRLOG move tablespace ts_ISIS_DATA; 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; 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 --------- SELECT NAME,STATE,TOTAL_MB,FREE_MB,FREE_MB/TOTAL_MB*100 AS PCT_FREE FROM v$asm_diskgroup; col name format a10 col path format a50 set lines 200 select a.name,a.group_number,b.path,b.TOTAL_MB from v$asm_diskgroup a, v$asm_disk b where a.group_number=b.group_number; col name format a15 select group_number, name, TOTAL_MB, FREE_MB from V$asm_disk_stat; 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 / --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 / ========================================================== 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; 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 cluster name:- ./cemutlo -n ($ORA_CRS_HOME/bin) findout the voting disk >crsctl query css votedisk 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 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 --export configuraiton srvconfig -exp out.txt 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 linux1 -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 "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 IN ('TRANS_HRDB') MINUS SELECT OBJECT_OWNER,OBJECT_NAME FROM dba_hist_sql_plan WHERE OBJECT_TYPE='INDEX'; 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; 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'; 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('821qu0bt4gy7k')); //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('dwtt8fpn8ydb5')); 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 add disk to asm diskgroup ------------------------- ALTER DISKGROUP DATA_1 ADD DISK '/oradata/axisp/db_lun23' NAME DATA_1_0019, '/oradata/axisp/db_lun20' NAME DATA_1_0020, '/oradata/axisp/db_lun21' NAME DATA_1_0021, '/oradata/axisp/db_lun22' NAME DATA_1_0022; ALTER DISKGROUP DATA_1 REBALANCE POWER 7; 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 ./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 heading off; set echo off; set lines 200; Set pages 2000; set long 90000; set trimspool on set trim on 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; 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 --job locks SELECT SID, TYPE, ID1, ID2 FROM V$LOCK WHERE TYPE = 'JQ'; 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: C:\Docs\Oracle\RDAWRAP_Playbook.docx oswatch / ops readyness: C:\Docs\Dell\OpReady.txt DB Tend -------- /misc/software/oracle/dba_scripts/dbtrends/DbTrends.zip 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); --ganesh kumar script for seeing ITL waits select * from (select 'ALTER '||m.object_type||' '||m.owner||'.'||m.object_name||' INITRANS 15'||';' , m.statistic_name, m.value, rank() over (order by m.value desc) value_rank from v$segment_statistics m where owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP', 'OUTLN', 'WMSYS', 'PERFMON', 'EXFSYS', 'PERFMON') and statistic_name = 'ITL waits' and value > 0) 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 --------- 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.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'); alter table LOGISTICS.QB_MESSAGE_NAMED_STR modify lob (VALUE) (shrink space cascade); alter table LOGISTICS.GPOSOURCEDATA modify lob (MESSAGE) (shrink space cascade); |
Oracle Notes |