To Calculate total Useable Disk Space with different RAID Configurations

- To Calculate total Useable Disk Space
www.ibeast.com/content/tools/RaidCalc/RaidCalc.asp

Drop/Create Oracle Enterprise Manager

Drop/Create Oracle Enterprise Manager


1. make sure the DB Console service is stopped. If not, try to stop it.

2. Delete the service:
sc delete <service_name>

- where <service_name> is the DB Control service name (typically:
OracleDBConsole<sid>)

3. Check if the service is deleted now. If not, reboot the whole server

4. Remove the following directories from your filesystem:
<ORACLE_HOME>/<hostname_sid>
<ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>

5. Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects:
SQL> drop user sysman cascade;
SQL> drop role MGMT_USER;
SQL> drop user MGMT_VIEW cascade;
SQL> drop public synonym MGMT_TARGET_BLACKOUTS;
SQL> drop public synonym SETEMVIEWUSERCONTEXT;

6. Rebuild the EM Console: emca -config dbcontrol db -repos create

Script to count specific string in whole statement

1- Create function that will count specific string in whole statement

CREATE OR REPLACE FUNCTION scott.CountString

( pInput VARCHAR2, pSearchString VARCHAR2 )

RETURN number

as

BEGIN

   RETURN (LENGTH (pInput) - LENGTH (REPLACE (pInput, pSearchString, '')))

          / LENGTH (pSearchString);

END;

/

2- Test it

select scott.CountString ('oracle world','o') from dual;

Scipt to list files names at server directory

1- 

create TYPE fname_tab IS TABLE OF varchar2(1000);

2- 

create or replace function get_fname (p_directory varchar2) return fname_tab

is

fname_tab_v  fname_tab;

l_null      varchar2(100);

l_directory varchar2(100);

begin

       l_directory := p_directory;

       sys.dbms_backup_restore.searchfiles (l_directory, l_null);

      

select fname_krbmsft fname  BULK COLLECT INTO  fname_tab_v  from  x$krbmsft;

return fname_tab_v;

Exception

when no_data_found then    fname_tab_v:= null;

when others then  fname_tab_v:= null;

End;

/

3-

grant execute on get_fname to <User_name>;

4-

select * from table (sys.get_fname('d:\dba_temp'))

script to get sessions that consume undo

SELECT   r.name rbs,
           NVL (s.username, 'None') oracle_user,
           s.osuser client_user,
           p.username unix_user,
           TO_CHAR (s.sid) || ',' || TO_CHAR (s.serial#) AS sid_serial,
           p.spid unix_pid,
           TO_CHAR (s.logon_time, 'mm/dd/yy hh24:mi:ss') AS login_time,
           TO_CHAR (SYSDATE - (s.last_call_et) / 86400, 'mm/dd/yy hh24:mi:ss')
              AS last_txn,
           t.used_ublk * TO_NUMBER (x.VALUE) / 1024 AS undo_kb
    FROM   v$process p,
           v$rollname r,
           v$session s,
           v$transaction t,
           v$parameter x
   WHERE       s.taddr = t.addr
           AND s.paddr = p.addr(+)
           AND r.usn = t.xidusn(+)
           AND x.name = 'db_block_size'
ORDER BY   r.name;

Move indexes , tables and LOB segments from Tablespace to other

1) Identify and Move Indexes from Tablespace to another:-

DECLARE

   x   VARCHAR2 (512);

BEGIN

   FOR i IN (SELECT   s.tablespace_name, s.owner, s.segment_name,

                      s.segment_type, SUM (s.BYTES) size_in_bytes,

                      ROUND (SUM (s.BYTES) / 1024 / 1024, 2) size_in_m,

                      f.file_name

                 FROM SYS.dba_segments s, SYS.dba_data_files f

                WHERE f.tablespace_name = s.tablespace_name

                  AND f.file_id = s.header_file

                  AND s.tablespace_name IN ('XLPDATA')

                  AND segment_type = 'INDEX'

                  AND Owner = 'OMFX'

             GROUP BY s.tablespace_name,

                      s.owner,

                      s.segment_name,

                      s.segment_type,

                      f.file_name)

   LOOP

      x :=

         'ALTER INDEX OMFX.' || i.segment_name

         || ' REBUILD TABLESPACE OMFX_TBS';

      DBMS_OUTPUT.put_line (x);

      EXECUTE IMMEDIATE (x);

   END LOOP;

END;

/

2) Identify and Move tables from Tablespace to another:-

DECLARE

   y   VARCHAR2 (512);

BEGIN

   FOR i IN (SELECT   s.tablespace_name, s.owner, s.segment_name,

                      s.segment_type, SUM (s.BYTES) size_in_bytes,

                      ROUND (SUM (s.BYTES) / 1024 / 1024, 2) size_in_m,

                      f.file_name

                 FROM SYS.dba_segments s, SYS.dba_data_files f

                WHERE f.tablespace_name = s.tablespace_name

                  AND f.file_id = s.header_file

                  AND s.tablespace_name IN ('XLPIDX')

                  AND segment_type = 'TABLE'

                  AND Owner = 'OMFX'

             GROUP BY s.tablespace_name,

                      s.owner,

                      s.segment_name,

                      s.segment_type,

                      f.file_name)

   LOOP

      y := 'ALTER TABLE OMFX.' || i.segment_name || ' MOVE TABLESPACE OMFX_TBS';

      DBMS_OUTPUT.put_line (Y);

      EXECUTE IMMEDIATE (y);

   END LOOP;

END;

/

3) Identify and LOB segments from Tablespace to another:-

SELECT    'alter table '

       || owner

       || '.'

       || table_name

       || ' move lob ('

       || column_name

       || ')'

       || 'store as (tablespace AWFE_TBS);'

  FROM dba_lobs

 WHERE segment_name IN (

          SELECT segment_name

            FROM dba_segments

           WHERE tablespace_name = 'AWFE_TBS'

             AND owner = 'PORTAL'

             AND segment_type = 'LOBSEGMENT');