How to check character set in Oracle

SELECT value$ FROM sys.props$ WHERE name = ‘NLS_CHARACTERSET’ ; SELECT * FROM NLS_DATABASE_PARAMETERS select owner, table_name, column_name      from dba_tab_columns     where (data_type = ‘NCHAR’ or             data_type = ‘NVARCHAR2’ or             data_type = ‘NCLOB’) and             owner != ‘SYS’ and             owner != ‘SYSTEM’;

List all tablespaces size

SELECT /* + RULE */  df.tablespace_name “Tablespace”,  df.bytes / (1024 * 1024) “Size (MB)”,          from dba_data_files ) a, ( select nvl(sum(bytes/1024/1024/1024),0) temp_size        SUM(fs.bytes) / (1024 * 1024) “Free (MB)”, Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) “% Free”, Round((df.bytes – SUM(fs.bytes)) * 100 / df.bytes) “% Used”   FROM dba_free_space fs, (SELECT tablespace_name,SUM(bytes) bytes          …

GET PID for SID

SELECT P.SPID, S.SID, S.SERIAL# FROM V$PROCESS P, V$SESSION S WHERE P.ADDR = S.PADDR AND S.SID = 405; col “SID/SERIAL” format a10 col username format a15 col osuser format a15 col program format a40 select     s.sid || ‘,’ || s.serial# “SID/SERIAL” ,     s.username ,     s.osuser ,     p.spid “OS PID” ,     s.program from     v$session s ,     v$process…

Session locks

set serveroutput on BEGIN    dbms_output.enable(1000000);   for do_loop in (select session_id, a.object_id, xidsqn, oracle_username, b.owner owner,    b.object_name object_name, b.object_type object_type   FROM v$locked_object a, dba_objects b     WHERE xidsqn != 0     and b.object_id = a.object_id)    loop    dbms_output.put_line(‘.’);   dbms_output.put_line(‘Blocking Session : ‘||do_loop.session_id);   dbms_output.put_line(‘Object (Owner/Name): ‘||do_loop.owner||’.’||do_loop.object_name);   dbms_output.put_line(‘Object Type :…

INDEX CREATE PROGRESS

set lin 600 col sid format 9999 col start_time format a5 heading “Start|time” col elapsed format 9999 heading “Mins|past” col min_remaining format 9999999 heading “Mins|left” col message format a81 select sid      , to_char(start_time,’hh24:mi’) start_time      , elapsed_seconds/60 elapsed      , round(time_remaining/60,2) “min_remaining”      , message from v$session_longops where time_remaining > 0 /

MOVE LOBS

–moving the table to the new tablespace alter table table_name move tablespace new_tablespace;   –rebuilding the index(es) and moving them to the new tablespace alter index index_name rebuild tablespace new_tablespace;   –moving the lobsegment(s) to the new tablespace alter table table_name move lob (lob_column) store as (tablespace new_tablespace);   –shrinking the table and lobsegment alter…

HOW to rebuild indexes

Partitions: set head off pagesize 0 linesize 100 select ‘ALTER INDEX ‘|| a.index_name ||’ rebuild partition ‘ || a.PARTITION_NAME ||’ ONLINE;’ from dba_IND_PARTITIONS a,dba_indexes b where a.index_name=b.index_name and b.table_name=upper(‘&table_name’) and a.status <>’USABLE’; Subpartitions: set head off pagesize 0 linesize 100 select ‘ALTER INDEX ‘ || a.index_owner || ‘.’ || a.index_name || ‘ REBUILD SUBPARTITION ‘…