Unix Addiction

In this example, we will move UNDO tablespace datafiles in another location. 1- Check what daatfiles does contains UNDO tablespace. SQL> select file_name from dba_data_files where tablespace_name='UNDOTBS'; file_name -------------------------------- /home/ora11/oradata/db1/undo.dbf 2- SQL> shutdown immediate 3- Copy physically the datafile to the new location with same name. mv /home/ora11/oradata/db1/undo.dbf  /home/ora11/oradata/db2/undo.dbf 4- SQL> startup mount 5- SQL> alter database rename file '/home/ora11/oradata/db1/undo.dbf ' to '/home/ora11/oradata/db2/undo.dbf'; 6-  SQL> shutdown immediate     SQL> startup     SQL> select file_name     from dba_data_files     where tablespace_name='UNDOTBS';     file_name     --------------------------------     /home/ora11/oradata/db2/undo.dbf

This report generates Generacion_archives_db.html in html format. set echo off set termout on set heading on set feedback off set trimspool on set pagesize 500 set linesize 180 set markup html on spool on spool Generacion_archives_db.html SELECT LOG_HISTORY.*,          SUM_ARCH.GENERATED_MB,          SUM_ARCH_DEL.DELETED_MB,          SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"     FROM (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,                    SUM (                       DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '00', 1, NULL))                       "00-01",                    SUM (                       DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '01', 1, NULL))                       "01-02",                    SUM (                       DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '02', 1, NULL))                       "02-03",

SELECT owner, count (distinct segment_name) as obj_number, sum(bytes) / 1024/1024/1024 as "Gigas" FROM dba_segments WHERE owner in  ('SAPSR3') GROUP BY owner;

Hot to check table size including their indexes. SELECT    owner, table_name, TRUNC(sum(bytes)/1024/1024) gigas FROM (SELECT segment_name table_name, owner, bytes  FROM dba_segments  WHERE segment_type = 'TABLE'  AND SEGMENT_NAME NOT LIKE  ('BIN%')  UNION ALL  SELECT i.table_name, i.owner, s.bytes  FROM dba_indexes i, dba_segments s  WHERE s.segment_name = i.index_name  AND   s.owner = i.owner  AND   s.segment_type = 'INDEX'  AND s.SEGMENT_NAME NOT LIKE  ('BIN%')  UNION ALL  SELECT l.table_name, l.owner, s.bytes  FROM dba_lobs l, dba_segments s  WHERE s.segment_name = l.segment_name  AND s.SEGMENT_NAME NOT LIKE  ('BIN%')  AND   s.owner = l.owner  AND   s.segment_type = 'LOBSEGMENT'  UNION ALL  SELECT l.table_name, l.owner, s.bytes  FROM dba_lobs l, dba_segments s  WHERE s.segment_name = l.index_name  AND s.SEGMENT_NAME NOT LIKE  ('BIN%')  AND   s.owner = l.owner  AND   s.segment_type = 'LOBIND

Script to generate dbverify for all datafiles. set head off set lines 200 set feedback off set define off spool dbv.sh select 'dbv file='||name||' blocksize= '||block_size|| ' LOGFILE=FILE-'||FILE#||'.LOG' from v$datafile; spool off  

How to check repeated datafiles  set linesize 300 select tablespace_name,file_name from dba_data_files a where 1< (select count(*) from dba_data_files b where substr(a.file_name,24,60)=substr (b.file_name,24,60));

How to check i/o load in database datafiles. set linesize   145 set pagesize  1000 set trimout     on set trimspool   on Set Feedback   off set timing     off set verify     off Column Disque         Heading 'Disque'         Format A25 Column Total_ES       Heading 'Total_ES'       Format 99999999999 Column Charge         Heading 'Charge'         Format 9999.00 Column Nom_Fichier    Heading 'Nom Fichier'    Format A25 Column Nom_Tablespace Heading 'Nom Tablespace' Format A18 column Alloc          Heading 'Alloc|(Mb)'     Format 9999,999          column avgwait        Heading 'Avg I/O|Time'   Format 9999,999          Break on Disque Skip 2 Compute Sum of Charge on Disque Compute Sum of Alloc on Disque Select         Substr(DF.Name, 1, Instr(DF.Name,'/',-1,1) - 1)  Disque      , T.Tablespace_Name                                Nom_Tablespace      , T.bytes/1048576                                

How to check invalid objects and compile them. 1- Check invalid objects set linesize 120 set pagesize 100 col owner format a15 col object_type format a30 col object_name format a30 SELECT owner, object_type, object_name, status from dba_objects WHERE status <> 'VALID' order by owner, object_type, object_name; 2- Check Oracle components valid status objects @$ORACLE_HOME/rdbms/admin/utlrp.sql set linesize 300 col COMP_NAME format a40 col VERSION format a14 col STATUS format a10 select COMP_NAME,VERSION,STATUS from dba_registry; 3- Compile all database objects. 3.1 sqlplus "/ as sysdba" 3.2 @$ORACLE_HOME/rdbms/admin/utlrp.sql

How to Drop all objects for a user. select * from (select 'ALTER TABLE '||owner||'.'||table_name||' drop CONSTRAINT '||constraint_name||';' from dba_constraints where owner IN ('SAPSR3') and constraint_type in ('R','P') and constraint_name not like 'BIN$%' order by constraint_type desc) union all select 'drop '||object_type||' '||owner||'.'||object_name||';' from dba_objects where owner in ('SAPSR3') and object_type in ('VIEW','TABLE','SEQUENCE','PROCEDURE','FUNCTION','PACKAGE', 'SYNONYM', 'MATERIALIZED VIEW') and object_name not like 'BIN$%';  

Undo advisors. 1- Advisor for Undo retention. sELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",        SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",        ROUND((d.undo_size / (to_number(f.value) *        g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"   FROM (        SELECT SUM(a.bytes) undo_size           FROM v$datafile a,                v$tablespace b,                dba_tablespaces c          WHERE c.contents = 'UNDO'            AND c.status = 'ONLINE'            AND b.name = c.tablespace_name            AND a.ts# = b.ts#        ) d,        v$parameter e,        v$parameter f,        (        SELECT MAX(undoblks/((end_time-begin_time)*3600*24))               undo_block_per_sec          FROM v$undostat        ) g WHERE e.name = 'undo_retention'   AND f.name = 'db_block_size'; 2- Advisor for Undo size. SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",

spool dbinfo.1st set linesize 300; set pagesize 1000; set wrap off; set numwidth 20 set echo off; break on today break on report column today noprint new_value xdate select TRUNC(sysdate) today from dual; column instance_name noprint new_value instancename select instance_name from v$instance; column startup_time heading 'Ran|Since' column instance heading 'Instance|Name' column host_name heading 'Host|Name' column platform_name heading 'Platform' column created heading 'Created|Since' column log_mode heading 'Log|Mode' column 'index partion' heading 'Index|Partion' column 'table partition' heading 'Table|Partition' column 'materialized view' heading 'Materialize|View' column platform_name format a20 column version format a10 column username format a20 column object_type format a20 column num_of_objects format 999,999 column owner format a20 column host_name format a20 column tablespace_nam

Check roles and privilegies for an user. select 'Column' lvl,c.privilege,c.grantable,c.owner,c.table_name,c.column_name from dba_col_privs c where grantee in ('USER') union select 'Role' GrType,r.granted_role obj,r.admin_option a, null,null,null from dba_role_privs r where r.grantee in ('USER') union select 'Sys Priv',s.privilege,s.admin_option,null,null,null from dba_sys_privs s where s.grantee in ('USER') union select 'table',t.privilege,t.grantable,t.owner,t.table_name,null from dba_tab_privs t where t.grantee in ('USER') and t.privilege !='EXECUTE' union select 'Program', e.privilege,e.grantable,e.owner,e.table_name,null from dba_tab_privs e where e.grantee in ('USER') and e.privilege ='EXECUTE' order by 1,2,4,5,6;

How to check size for each user. select DISTINCT(OWNER),SUM(BYTES)/1024/1024/1024 as gigas from  dba_segments where owner not in ('SYS','SYSTEM') group by OWNER;

How to commit an update each 10000 rows. SET SERVEROUTPUT ON SIZE 1000000 DECLARE CURSOR c1 IS SELECT ROWID RID from inter.revshar_daily_summary where txn_dt between 'XX-mar-2005' and 'XX-mar-2005' v_count NUMBER:=0; BEGIN          FOR r1 in c1 LOOP                   v_count:=v_count+1;                   UPDATE inter.revshar_daily_summary set "....XXXXX...." WHERE rowid=r1.rid;                   IF mod(v_count, 10000) = 0 THEN                            commit;                            dbms_output.put_line(v_count|| ' revshar_daily_summary records updated.');                   END IF;          END LOOP;          dbms_output.put_line(v_count|| ' revshar_daily_summary records updated.');          commit; END; / commit;

How to do import tables and objects 1- Import a table and migrate it from one schema to another.    impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp REMAP_SCHEMA=hr:scott    If SCOTT account exist in the database then hr objects will be loaded into scott schema.    If scott account does not exist, then Import Utility will create the SCOTT account with an unusable password because, the dump file was exported by the user SYSTEM     and imported by the user SYSTEM who has DBA privileges. 2- Import all object from one tablespace and migrate to another and also remaping schemas. impdp user/***** DIRECTORY=DATA_PUMP_DIR DUMPFILE=export_user.dmp REMAP_TABLESPACE=TS_1:TS_2 REMAP_SCHEMA=USER1:USER2

How to do tables export. 1- Export more that one table : expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tables.dmp TABLES=employees,jobs,departments 2- Export tables that belong to only tablespaces tbs_4, tbs_5 and tbs_     expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tbs.dmp TABLESPACES=tbs_4, tbs_5, tbs_6 3- Exportar tables for different schemas. expdp user/****** directory=DATA_PUMP_DIR dumpfile=expdp_test_1.dmp logfile=expdp_test_1.log schemas='scott','hr' include=TABLE:\"IN (\'EMP\', \'JOBS\')\"

How to do tables export. 1- Export more that one table : expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tables.dmp TABLES=employees,jobs,departments 2- Export tables that belong to only tablespaces tbs_4, tbs_5 and tbs_     expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tbs.dmp TABLESPACES=tbs_4, tbs_5, tbs_6 3- Exportar tables for different schemas. expdp user/****** directory=DATA_PUMP_DIR dumpfile=expdp_test_1.dmp logfile=expdp_test_1.log schemas='scott','hr' include=TABLE:\"IN (\'EMP\', \'JOBS\')\"  

How to do export excluding objects. 1- On this example we exclude tables that begin with "A"    expdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=scott_schema.dmp SCHEMAS=SCOTT EXCLUDE=TABLE:"like 'A%'" 2- On this example we include tables that begin with "A"    expdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=scott_schema.dmp SCHEMAS=SCOTT INCLUDE=TABLE:"like 'A%'"    También se puede usar el include y exclude para los INDEXES, CONSTRAINTS, GRANTS, USER, SCHEMA 3- Here we can filter rows in the export file    expdp hr/hr QUERY=emp:'"WHERE dept_id > 10 AND sal > 10000"' NOLOGFILE=y DIRECTORY=dpump_dir1 DUMPFILE=exp1.dmp NOLOGFILE=y DIRECTORY=dpump_dir1 DUMPFILE=exp1.dmp  

Last query executed and the user that who was the owner of them. select distinct vs.sql_text, vs.sharable_mem,vs.persistent_mem, vs.runtime_mem, vs.sorts, vs.executions, vs.parse_calls, vs.module,vs.buffer_gets, vs.disk_reads, vs.version_count,vs.users_opening, vs.loads,to_char(to_date(vs.first_load_time,      'YYYY-MM-DD/HH24:MI:SS'),'MM/DD HH24:MI:SS') first_load_time, rawtohex(vs.address) address, vs.hash_value hash_value , rows_processed , vs.command_type, vs.parsing_user_id      ,OPTIMIZER_MODE , au.USERNAME parseuser from v$sqlarea vs , all_users au where (parsing_user_id != 0) AND (au.user_id(+)=vs.parsing_user_id) and (executions >= 1) order by buffer_gets/executions desc;

1- Check if one session is locking to another session.  select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status, 'ALTER SYSTEM KILL SESSION ' || chr(39) || s1.sid || ',' || s1.serial# || chr(39) || ' IMMEDIATE;' AS KILL from v$lock l1, v$session s1, v$lock l2, v$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 ; 2- See locked object and lock type. select    oracle_username || ' (' || s.osuser || ')' username ,    s.sid || ',' || s.serial# sess_id ,    owner || '.' ||    object_name object ,    object_type ,    decode(    l.block     ,    0, 'Not Blocking'     ,    1, 'Blocking'     ,    2, 'Global') status ,    decode(v.locked_mode     ,    0, 'None'     ,    1, &

Percentage fragmentation for tables higher than 1 giga and more than 50% of fragmentation. set linesize 500 set pagesize 100 col owner format   a10 col table_name format   a10 col TOTAL_SIZE format   a14 col ACTUAL_SIZE format   a14 col FRAGMENTED_SPACE format   a14 col Porcentaje_Fragmentado format   99999 select owner,table_name,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE", round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE", round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE", round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) *100 /round(((blocks*8/1024)),2)  Porcentaje_Fragmentado from dba_tables where owner not in ('SYSTEM','SYS','XDB','EXFSYS','ORSYS','MDSYS','OLAPSYS','SYSMAN','OUTLN','DBSNMP','TSMSYS','WMSYS','CTXSYS','DMSYS','ORDSYS') and round(((block

How to obtain all dictionary tables in Oracle. SELECT table_name FROM dictionary WHERE table_name like 'DBA/_HIST/_%' ESCAPE '/' ORDER BY table_name;

How to obtain trace name  for current Oracle session. select rtrim(c.value,'/') || '/' || d.instance_name || '_ora_' || ltrim(to_char(a.spid)) || '.trc' from v$process a, v$session b, v$parameter c, v$instance d where a.addr = b.paddr and b.audsid = sys_context( 'userenv', 'sessionid') and c.name = 'user_dump_dest' and rownum < 50;

Check active session in Oracle  SELECT NVL(s.username, '(oracle)') "USERNAME",        s.osuser "OSUSER",        s.sid "SID",        s.serial# "SERIAL#",        p.spid "SPID",        s.lockwait "LOCKWAIT",        s.status "STATUS",        s.module "MODULE",        s.machine "MACHINE",        s.program "PROGRAM",        s.sql_hash_value "HASH",        TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') "LOGONTIME" FROM   v$session s,        v$process p WHERE  s.paddr  = p.addr AND    s.status = 'ACTIVE' ORDER BY s.username, s.osuser;

How to kill user session through database and Unix command select /*    substr(a.spid,1,9) pid,    substr(b.sid,1,5) sid,    substr(b.serial#,1,5) ser# */ 'alter system kill session ' || '''' || b.SID || ',' || b.serial# || ''' immediate;', '!kill -9 ' || a.spid from    v$session b,    v$process a where b.paddr = a.addr --and type='USER' --and b.username='BGGT' --and b.LOGON_TIME < '12-FEB-08' --and sid=47 order by spid; This is and outuput example. alter system kill session '82,15519' immediate;    !kill -9 1076 alter system kill session '170,1' immediate;    !kill -9 11990 alter system kill session '169,1' immediate;    !kill -9 11994 alter system kill session '168,1' immediate;    !kill -9 12000 alter system kill session '167,1' immediate;    !kill -9 12004 alter system kill session '166,1' immediate;    !kill -9 12009 alt

Open cursors for users select b.sid, a.username, b.value Cursores_Abiertos from v$session a, v$sesstat b, v$statname c where c.name in ('opened cursors current') and b.statistic# = c.statistic# and a.sid = b.sid and a.username is not null and b.value >0 order by 3 desc;

The UGA, or User Global Area, is allocated in the PGA for each session connected to Oracle in a dedicated server environment. The PGA is memory allocated at the client to hold a stack which contains all of the session's variables, etc SET LINESIZE 200 SET PAGESIZE 9999 COLUMN sid                     FORMAT 999            HEADING 'SID' COLUMN oracle_username         FORMAT a12            HEADING 'Oracle User'     JUSTIFY right COLUMN os_username             FORMAT a9             HEADING 'O/S User'        JUSTIFY right COLUMN session_program         FORMAT a11          HEADING 'Session Program' TRUNC COLUMN session_machine         FORMAT a8             HEADING 'Machine'         JUSTIFY right TRUNC COLUMN session_pga_memory      FORMAT 9,999,999,999  HEADING 'PGA Memory' COLUMN logon_time              FORMAT 9,999,999,999  HEADING 'logon_time' COLUMN session_pga_memory_max  FORMAT 9,999,999,999  HEADING 'PGA

Brspace use internally Oracle DBMS_REDEFINITION. If you have SAP with Oracle, this is a very fast way to reorganize object in Oracle Database. In this example we will organize simultaneously S562,MLPPF and MLCRP tables. Important : If you want to reorganize various tables and indexes, these must reside in same tablespace. 1- Tables reorganization. brspace -p /oracle/PRD/102_64/dbs/initPRD.sap -c force -s 20 -l E -f tbreorg -a reorg -s PSAPSR3 -o SAPSR3 -t "S562,MLPPF,MLCRP" -n PSAPSR3 -e 16 -p 16 -m online *  /oracle/PRD/102_64/dbs/initPRD.sap : SAP Parameter file * PSAPSR3 : Source tablespace * SAPSR3  : Table owner * PSAPSR3 :  Destiny tablespace. * -e 16 -p 16 -m :  It indicates how many parallel processes that will perform the operation,in this case are 16. * online : It indicates that the reorganization of the tables will be made ONLINE 2- After tables reorganization you will need to rebuild the S562,MLPPF and MLCRP indexes tables . brspac

How to resize a redolog file. 1) Check the redo log groups, their members and size       set linesize 300      col group# format 99      col member format a70      col bytes format 999999,9999      SELECT a.group#, a.member, b.bytes/1024/1024 as megas      FROM v$logfile a, v$log b      WHERE a.group# = b.group#; GROUP# MEMBER                                                                      MEGAS ------ ---------------------------------------------------------------------- ----------      3 /data/oracle11/test/redo03.log                                                   50      2 /data/oracle11/test/redo02.log                                                   50      1 /data/oracle11/test/redo01.log                                                   50                2) Check which redo logs groups are status active current and inactive.         select group#, status from v$log;           GROUP# STATUS          ---------- ----------------       

1.-Create   Metaset Called "oracle_set" to the host lab01 , This Step Can Be Not Executed If The Metaset Is Created #metaset -s oracle_set -a -h lab01 -a Adds drives or hosts to the named set. -h   Specifies one or more host names to be added to or deleted from a diskset. 2.-If you have the LUN’s allocated in the server you can add each one to the configuration, in this case we have 5 disk to aggregate to the diskset   2.1Just in case in order to discover “devfsadm” or cfgadm –c configure cX CX=Controller Number #metaset -s oracle_set -a c2txxxxxxxxxxxxxxxxd0 #metaset -s oracle_set -a c2txxxxxxxxxxxxxxxxd0 #metaset -s oracle_set -a c2xxxxxxxxxxxxxxxxxd0 #metaset -s oracle_set -a c2xxxxxxxxxxxxxxxxxd0 #metaset -s oracle_set -a c2xxxxxxxxxxxxxxxxxd0 3-After adding the LUN’s to the diskset created, you can check them with the command below #metaset 3-You can check with prtvtoc the partition for each disk and change it if you