Table Reorg - How to do - commands

1. archive destination (sufficient space) - 20GB - 22GB (screen shot)
bdf | grep saparch (SID)


2. Tablespace PSAPEVO - object - SPACE should be same as table size - (20%). (screen shot)

select tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name
, sum(bytes_used + bytes_free)/1024/1024 used_mb
from v$temp_space_header group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name union all
select tablespace_name,sum(bytes_free)/1024/1024 free_mb from v$temp_space_header group by tablespace_name ) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4;

3. Collect table statistics

brconnect -c -u / -f stats -t /BIC/B0000032000 -f collect -p 2

4. Checking TOP 20 Fregmented tables.

SELECT * FROM
(SELECT
SUBSTR(TABLE_NAME, 1, 21) TABLE_NAME,
NUM_ROWS,
AVG_ROW_LEN ROWLEN,
BLOCKS,
ROUND((AVG_ROW_LEN + 1) * NUM_ROWS / 1000000, 0) NET_MB,
ROUND(BLOCKS * (8000 - 23 * INI_TRANS) *
(1 - PCT_FREE / 100) / 1000000, 0) GROSS_MB,
ROUND((BLOCKS * (8000 - 23 * INI_TRANS) * (1 - PCT_FREE /
100) -
(AVG_ROW_LEN + 1) * NUM_ROWS) / 1000000) "WASTED_MB"
FROM DBA_TABLES
WHERE
NUM_ROWS IS NOT NULL AND
OWNER LIKE 'SAP%' AND
PARTITIONED = 'NO' AND Table_name in ('/BIC/B0000034000
','/BIC/B0000582000',
',
(IOT_TYPE != 'IOT' OR IOT_TYPE IS NULL)
ORDER BY 7 DESC)
WHERE ROWNUM <=20;

5. Getting Table & DB size

select sum(bytes)/1024/1024/1024 from dba_segments;

select sum(bytes)/1024/1024/1024 from dba_segments where segment_name=();

Transaction to check ZR2 (Recruitment) System connectivity to TREX (Search Engine) // SRMO

SRMO - It will show the RFC connection from ZR2 to TREX is working or not.

E071 // Details about the transports done in the system

To check the status of transports, we can there is one table E071 where we can check the details of transports imported in the system.

Tuning Oracle DB Parameters - Steps to follow

First we can check the existing parmaters with following commands:

$show sga

$show parameter shared_pool;

$show parameter sga_max_size;

$show parameter db_cache_size;

Then take backup for pfile and spfile (initSID.ora and spfileSID.ora)

/oracle/SID/102_64/dbs

$create pfile from spfile

then edit pfile with the parameters that are recommended

start database with changed pfile

$startup pfile='/oracle/SID/102_64/dbs/initSID.ora'

check the parameters

if they are correct then

$create spfile from pfile

then shutdown and restart the database

$shutdown immediate
$startup

//Issue faced
while restarting database listener not found

$tnsping SID

gave error for resolving name

resolution

setenv TNS_ADMIN /oracle/SID/102_64/network/admin

later on added the same in .dbenv.csh and .dbenv_avggstqh.csh to make the same permanent.


//we can dynamically set the parameters in spfile online but this is not recommended as Oracle needs to adjust the parameters as well.

$alter system set shared_pool_size=400M scope=spfile;

How to find out UID and GID for HP-UX users

HP-UX command

$id oraeb3

$id dn1adm

HP-UX command for looking at memory with Graphics

glance | gpm

Excellent Unix Commands for finding and replacing in vi and file system

If you want to find something

$ find . | xargs grep "string"

It will find all the files recursively and then display the strings


vi commands (advanced)

Go to / Replace functionality can be used in vi... like this

:%s/stringtobereplaced/stringwithwhichtoreplace/g

(Esc + colen (:) + percentage (%) s /string1/string2/g

for eg:

:%s/avggstdh/avggstrh/g

will replace all the occurences of avggstdh with avggstrh.