Some useful queries

è To get the max value occupied by column’s

SELECT peid,emp_id, LENGTH(TRIM(e1.emp_id )) FROM app_ehr_location.em_employee_employment e1 where  LENGTH(TRIM(e1.emp_id ))>15

à Moving Index and table to different tablespace

      alter index sys.SOURCE$ rebuild tablespace users;

   ALTER TABLE sys.TRIGGER$ MOVE TABLESPACE users

è Killing the pumping jobs

      drop table system.SYS_EXPORT_SCHEMA_01

select job_name, state from dba_datapump_jobs;

à Select space each Table OR Index

select *

  from (select owner, segment_name, bytes / 1024 / 1024 MG

          from dba_segments

         where segment_type = 'TABLE' and owner like'SYS%' and tablespace_name='SYSTEM'

         order by bytes / 1024 / 1024 desc)

 where rownum <= 50;

Re-creating New TEMP tablespace

Here are the steps: First create temp tablespace --Create Temporary Tablespace Temp2 CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE   '/oradata/testdb/temp02.dbf' SIZE 500M; Make the newly create tablespace as default one --Move Default Database temp tablespace ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2; Now drop the old tablespace --Drop temp tablespace DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;