Show Long OPS

Sho long OPS session

set lines 200
col OPNAME for a25
Select
a.sid,
a.serial#,
b.status,
a.opname,
to_char(a.START_TIME,' dd-Mon-YYYY HH24:mi:ss') START_TIME,
to_char(a.LAST_UPDATE_TIME,' dd-Mon-YYYY HH24:mi:ss') LAST_UPDATE_TIME,
a.time_remaining as "Time Remaining Sec" ,
a.time_remaining/60 as "Time Remaining Min",
a.time_remaining/60/60 as "Time Remaining HR"
From v$session_longops a, v$session b
where a.sid = b.sid
and a.sid =&sid
And time_remaining > 0;

Move tables, indexes, lobs to another tablespace

Move tables to another TS:

SELECT 'ALTER TABLE <USER>.' || OBJECT_NAME ||' MOVE TABLESPACE '||' <TABLESPACE>; '
FROM ALL_OBJECTS
WHERE OWNER = '<USER>'
AND OBJECT_TYPE = 'TABLE';

Move indexes to another TS:

SELECT 'ALTER INDEX <USER>.'||INDEX_NAME||' REBUILD TABLESPACE <TABLESPACE>;'
FROM ALL_INDEXES
WHERE OWNER = '<USER>';

Move LOBs to another TS:

SELECT 'ALTER TABLE <USER>.'||LOWER(TABLE_NAME)||' MOVE LOB('||LOWER(COLUMN_NAME)||') STORE AS (TABLESPACE <TABLESPACE>);'
FROM DBA_TAB_COLS
WHERE OWNER = '<USER>' AND DATA_TYPE like '%LOB%';

Show CPU info in Linux

Show CPU info on Linux system:

more /proc/cpuinfo
cat /proc/cpuinfo
less /proc/cpuinfo
grep processor /proc/cpuinfo
lscpu

DB init parameters script

Db init parameters

SELECT "Parameter", "Value", "Comment", "Type", "Description", "Modified", "Dynamic", "Basic" FROM(
select name "Parameter",
              value "Value",
              update_comment "Comment",
              decode(type,1,'Boolean',2,' String',3,'Integer',4,'Parameter file',5,'Reserved',6,'Big integer') "Type" ,
              description "Description",
              decode(ISDEFAULT,'TRUE','No','FALSE','Yes') "Modified",
              decode(isinstance_modifiable,'TRUE','Yes','No') "Dynamic",
              decode(ISBASIC,'TRUE','Yes','No') "Basic"
              from GV$SYSTEM_PARAMETER Paramter order by 1
)