How to shift a table from one tablespace to another.

Check Free/Used space per tablespace :

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       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
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

Sample output:

Tablespace                      Size (MB)  Free (MB)     % Free     % Used
------------------------------ ---------- ---------- ---------- -----------------
    UNDOTBS1                               65    17.8125         27         73
     EXAMPLE                               100     22.625         23         75
    USERS                                         5     1.0625         21         79
    TEMP                                         20          2             10         90
   SYSAUX                               625.125     304.5        48      52
   SYSTEM                                  700     9.0625          1         99

---------------------------------------------------------------------------

NLS_LANG parameter :

select DECODE(parameter, 'NLS_CHARACTERSET', 'CHARACTER SET',
'NLS_LANGUAGE', 'LANGUAGE',
'NLS_TERRITORY', 'TERRITORY') name,
value from v$nls_parameters
WHERE parameter IN ( 'NLS_CHARACTERSET', 'NLS_LANGUAGE', 'NLS_TERRITORY');


NAME VALUE
————- —————–
LANGUAGE AMERICAN
TERRITORY AMERICA
CHARACTER SET WE8ISO8859P1
export NLS_LANG=_.

-----------------------------------------------------------------------------

Object Count , Object Type for one particular User :


select owner,object_type,count(*) from dba_objects where owner='XXXXXXX' group by owner,object_type;


-----------------------------------------------------------------------------

Find running jobs in oracle database :



select sid, job,instance from dba_jobs_running;

‎select sid, serial#,machine, status, osuser,username from v$session where username!='NULL'; --all active users;

-----------------------------------------------------------------------------

Find long running jobs in oracle database :

select username,to_char(start_time, 'hh24:mi:ss dd/mm/yy') started, time_remaining remaining, message from v$session_longops where time_remaining = 0 order by time_remaining desc;
-----------------------------------------------------------------------------

Find the Size of Schema:

SELECT SUM (bytes / 1024 / 1024) "size"
FROM dba_segments WHERE owner = '&owner';

-----------------------------------------------------------------------------

How to shift a table from one tablespace to another.

ALTER TABLE <TABLE NAME to be moved> MOVE TABLESPACE <destination TABLESPACE NAME>;


------------------------------------------------------


Query to catch and generate script to kill the blocking sessions

select 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid in (select * from dba_blockers); 


------------------------------------------------------------------------------------

Find the last DDL on Particular Object 

SELECT object_name, object_type, last_ddl_time
FROM dba_objects 
WHERE 
object_name = 'xxxxxxxxxxx';



----------------------------------------------------------------------

Sessions :


To find total session on particular server ( machine )

select * from gv$session where MACHINE like '%XXXXX%' ;

To find session which are Active

select * from gv$session where MACHINE like '%XXXXX%' AND status='ACTIVE';

ACTIVE - Session currently executing SQL

To find users machines by order 

select machine, count(*) from gv$session group by machine order by 2;

----------------------------------------------------------------------

To find uptime of instnace

SELECT host_name, instance_name,
       TO_CHAR(startup_time, 'DD-MM-YYYY HH24:MI:SS') startup_time,FLOOR(sysdate-startup_time) days

FROM   v_$instance;


----------------------------------------------------------------------


SYS.USER$ is an Internal table in Oracle Database

PTIME provides the date the password was last changed.
LCOUNT provides the number of failed logins.
CTIME provides the date the user was created.
LTIME provides the date the user was last locked.

SELECT name " USER NAME ",
ctime " Date user was created ",
ptime " Password was last changed " 
FROM sys.user$
WHERE name = 'XXXX';

** List of all database users and the date when the last password change occurred

select du.username, du.profile, du.account_status, u.ptime last_pwd_change
from dba_users du, sys.user$ u
where du.username = u.name

order by 2, 4;

----------------------------------------------------------------------

Find unusable indexes:-

SELECT owner, index_name, tablespace_name

FROM   dba_indexes

WHERE  status = 'UNUSABLE';

Index partitions:

SELECT index_owner, index_name, partition_name, tablespace_name

FROM   dba_ind_PARTITIONS

WHERE  status = 'UNUSABLE';

Rebuild unusable indexes 

Indexes:

SELECT 'alter index '||owner||'.'||index_name||' rebuild tablespace '||tablespace_name ||';'

 FROM   dba_indexes WHERE  status = 'UNUSABLE';

Index partitions:

SELECT 'alter index '||owner||'.'||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||';'

FROM   dba_ind_partitions

WHERE  status = 'UNUSABLE';

Now verify the status of the index, it will be VALID

---------------------------------------------------------------------------------

How to Check the installed Oracle Software is 32/64 bit ?

select length(addr)*4 || '-bits' word_length from v$process where rownum=1;

---------------------------------------------------------------------------------

IDLE SESSIONS FOR MORE THAN 1 HOUR

select sid,serial#,username,trunc
(last_call_et/3600,2)||' hr'
last_call_et
from V$session where
last_call_et > 3600 and username is not null;