How will you swap objects into a different table space for an existing database?

- Export the user

- Perform import using the command imp system/manager file=export.dmp indexfile=newrite.sql. This will create all definitions into newfile.sql.

- Drop necessary objects.

- Run the script newfile.sql after altering the tablespaces.

- Import from the backup for the necessary objects.

Get DDL command in oracle

Get DDL command from SQL query
select dbms_metadata.get_ddl('OBJECT_TYPE','OBJECT_NAME') FROM DUAL;

e.g:

select dbms_metadata.get_ddl('TABLE','WORK') FROM DUAL;

What is data guard

Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data. Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as transactionally consistent copies of the production database. Then, if the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage. Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.
With Data Guard, administrators can optionally improve production database performance by offloading resource-intensive backup and reporting operations to standby systems.

How to check tablespace usage

SELECT
  tablespace_name "Tablespace",
  TO_CHAR((a.bytes / 1048576),'99,999,990.900') "Total MB",
  TO_CHAR(((a.bytes - DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576),'99,999,990.900') "Used MB",
  TO_CHAR((((a.bytes - DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576)*100) / (a.bytes / 1048576),'999,999.9') "% Used"
FROM
  (sys.dba_tablespaces d JOIN sys.sm$ts_avail a USING (tablespace_name))
  LEFT OUTER JOIN sys.sm$ts_free f USING (tablespace_name)
  where tablespace_name in ('xxx')
ORDER BY 4;

How to check segments approaching max extents

col segment_name format a40
select owner
, segment_type
, segment_name
, max_extents - extents as "spare"
, max_extents
from dba_segments
where owner not in ('SYS','SYSTEM')
and (max_extents - extents) < 10
order by 4
/

How to remove table locks

Removing Table Lock
Login to database as SYS or SYSTEM user. Find out the SESSION_ID by running the following SELECT statement
SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = Table Name;

Use this session id to find SERIAL# by using following SELECT statment
SELECT SID,SERIAL#
FROM V$SESSION
WHERE SID IN (SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = Table Name)

Use ALTER SYSTEM command to KILL SESSION and this will release the lock.
ALTER SYSTEM KILL SESSION 'SID,SERIALl#';