Oracle: How to move tablespace data file to a different location

I added a new data file to my tablespace, and then realized that I added it to the wrong drive.

Once you add the data file, there is no way to drop it. Only option is to rename or relocated the file.

Here are the steps to relocate a data file or multiple files to a different drive.

Login as SYS

Find the current location of your tablespace

SELECT tablespace_name, FILE_NAME, BYTES,STATUS, online_status 
FROM DBA_DATA_FILES
where tablespace_name = 'MY_DATA';

Take the tablespace offline

ALTER TABLESPACE MY_DATA OFFLINE NORMAL;

You can check the status again

SELECT tablespace_name, FILE_NAME, BYTES,STATUS, online_status 
FROM DBA_DATA_FILES
where tablespace_name = 'MY_DATA'; 

Now, Move the file from your current location to new location. I am running my database on Windows, So I opened Windows explorer and cut and paste the file to the new location. You can use any OS Move command to transfer the file.

Now that the file is moved, You rename the data file location

ALTER TABLESPACE MY_DATA
 RENAME DATAFILE 'E:\APP\ORACLE\ORADATA\ORCLDB\MY_DATA2.DBF'    TO 'L:\APP\ORACLE\ORADATA\ORCLDB\MY_DATA2.DBF';

Bring the tablepace online

ALTER TABLESPACE MY_DATA ONLINE;

Check the status again

SELECT tablespace_name, FILE_NAME, BYTES,STATUS, online_status 
FROM DBA_DATA_FILES
where tablespace_name = 'MY_DATA'; 

DRG-10700: preference does not exist

Recently I got this error on my db, while setting up Oracle Text


Here is how it was resolved.

Login as CTXSYS ( Reset the password, if you dont know the password)

Run the following block

Exec CTX_DDL.create_preference('CTXSYS.DEFAULT_WORDLIST', 'BASIC_WORDLIST');

update dr$parameter
set par_value = 'CTXSYS.EMPTY_STOPLIST'
where par_value = 'CTXSYS.DEFAULT_STOPLIST';


Exec CTX_DDL.create_preference('CTXSYS.DEFAULT_LEXER', 'BASIC_LEXER');

COMMIT;

How to kill a job

DBA_JOBS_Running view provides the list of all jobs that are currently running.

If you run the below SQL as SYS or SYSTEM, You will get the list of all jobs, as well as the remove job and kill session statements.

select j.job,j.log_user,j.schema_user,j.broken,nvl2(r.job,'Running now','Not Running') job_current_status,j.what,
'exec dbms_ijob.remove('||j.job||');' Remove_Job,
'Alter System Kill Session '''||s.sid||','||s.serial#||''';' Kill_Session
from dba_jobs j, dba_jobs_running r, v$session s
where j.job = r.job
and r.sid = s.sid
 


If you kill the session, without removing the job from the job queue, it will reappear after some time. So, remove the job from the queue , and then kill the session

enq: TX - row lock contention

Find your blocking session with the following query

Select v.sid,s.blocking_session, s.event, v.type,v.lmode,v.request,o.object_name, o.object_type
from v$lock v ,dba_objects o, v$session s
where v.id1 = o.object_id
and v.sid = s.sid
and event = 'enq: TX - row lock contention'

You can get the blocking session details from

select * from v$session where sid =<blocking_session from above query>;

Then kill the session

Alter system Kill session 'SID,SERIAL#'

Oracle Schema size

You can run the following SQL as SYS or SYSTEM account, to get all the schemas in the database and its size.

select owner, size_byte, round(size_byte/1024/1024,3) size_mb, round(size_byte/1024/1024/1024,3) size_gb,
u.account_status, u.created
from (  select owner, sum(bytes)  size_byte
        from dba_segments
        where owner not in ('SYS','APEX_030200','CTXSYS','DBSNMP','EXFSYS','MDSYS','OLAPSYS','ORDDATA','ORDSYS','OUTLN','SYSMAN','SYSTEM','WMSYS','XDB')
        group by owner
     ) z, dba_users u
where z.owner = u.username
order by 1