Export database from one tablespace and import into another

Export database.

Export the database into the file.

expdp <username>/<password>@<database-name> schemas=<username> DIRECTORY=DATA_PUMP_DIR DUMPFILE=<filename>.DMP flashback_time=systimestamp

Example : expdp test/test@123@xe schemas=test_schema DIRECTORY=DATA_PUMP_DIR DUMPFILE=TEST-BACKUP-29-May-2019.DMP flashback_time=systimestamp

User creation and permissions.

Creates a user in the default SYSTEM tablespace.

Creates DB user

CREATE USER <username> IDENTIFIED BY <password>;

Creates a user in the custom tablespace.

CREATE USER <username> IDENTIFIED BY <password> DEFAULT TABLESPACE <tablespace>;

Granting permissions to the newly created user.

GRANT IMP_FULL_DATABASE TO <username>;
GRANT CONNECT, RESOURCE, CREATE ANY TABLE, DROP ANY TABLE, CREATE ANY TRIGGER, DROP ANY TRIGGER, CREATE ANY VIEW, DROP ANY VIEW, CREATE ANY PROCEDURE, DROP ANY PROCEDURE TO <username>;
GRANT CREATE SESSION to <username>;    	
GRANT BECOME USER to <username>;
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO <username>;

Check the granted privileges/roles:

SELECT * FROM dba_sys_privs WHERE grantee = <username>;

SELECT * FROM dba_role_privs WHERE grantee = <username>;

Import database.

Import the database into another tablespace.

impdp <username>/<password> REMAP_SCHEMA=<old_schema>:<new_schema> REMAP_TABLESPACE=<old_tablespace>:<new_tablespace> DUMPFILE=TEST-BACKUP-29-May-2019.DMP DIRECTORY=DATA_PUMP_DIR TRANSFORM=oid:n;

Example : impdp test1/test@123 REMAP_SCHEMA=TEST:NEWTEST REMAP_TABLESPACE=SYSTEM:OSTEST DUMPFILE=TEST-BACKUP-29-May-2019.DMP DIRECTORY=DATA_PUMP_DIR TRANSFORM=oid:n;

Tablespace statistics.

Run below SQL to check all information related tablespace like allocated/total space, space used, space used (in %), free space.

SELECT  a.tablespace_name,
  ROUND (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES) * 100,2) percentage_used,
  c.BYTES / 1024 / 1024 space_allocated,
  ROUND (c.BYTES / 1024 / 1024 - NVL (b.BYTES, 0) / 1024 / 1024,2) space_used,
  ROUND (NVL (b.BYTES, 0) / 1024 / 1024, 2) space_free, 
  c.DATAFILES
FROM dba_tablespaces a,
  (SELECT tablespace_name, 
  	 SUM (BYTES) BYTES
     FROM  dba_free_space
     GROUP BY tablespace_name
  ) b,
  (SELECT COUNT (1) DATAFILES, 
     SUM (BYTES) BYTES, 
     tablespace_name
   FROM   dba_data_files
     GROUP BY tablespace_name
  ) c
  WHERE b.tablespace_name(+) = a.tablespace_name
    AND c.tablespace_name(+) = a.tablespace_name
ORDER BY NVL (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES), 0) DESC;

Resizing tablespace.

Extend space to the existing datafile.
```
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/XE/system.dbf' RESIZE 1000M;
```
Above SQL adds 1GB of size to the system datafile.

Add datafile to the tablespace.

ALTER TABLESPACE <tablespace_name> ADD DATAFILE '/u01/app/oracle/oradata/XE/ostest_migration1.dbf' SIZE 1000M;

Above SQL adds 1GB of the data file to the existing tablespace.

Some useful queries:

Command to check how many tables are present inside specified tablespace.

SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLESPACE_NAME = '<tablespace>';

Check the default tablespace of all users.

SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS;

Retrieve the value of the 'DATA_PUMP_DIR' variable.

SELECT directory_name, directory_path FROM dba_directories WHERE directory_name='DATA_PUMP_DIR';

Equivalent to show tables(MySQL).

SELECT TABLE_NAME, OWNER FROM ALL_TABLES WHERE OWNER='<username>' ORDER BY OWNER, TABLE_NAME;

List all Datafiles and Tablespaces

SELECT
substr(file_name,1,40) as File_Name,
substr(tablespace_name,1,10) as Tablespace_Name,
bytes/1024/1024 as Size_Mb
FROM dba_data_files
ORDER BY tablespace_name, file_name;

Top 10 tables from all databases;

SELECT * 
FROM (SELECT 
    	owner, 
    	segment_name, 
    	bytes/1024/1024 meg
  	  FROM 
    	dba_segments 
  	  WHERE
    	segment_type = 'TABLE'
  	  ORDER BY 
    	bytes/1024/1024 desc
	) 
WHERE 
   rownum <= 10;

Note: Change the row count number according to need.

Shows list of all running processes:

SET LINESIZE 200
SET PAGESIZE 200
SELECT PROCESS pid, sess.process, sess.status, sess.username, sess.schemaname, sql.sql_text FROM v$session sess, v$sql sql WHERE sql.sql_id(+) = sess.sql_id AND sess.type = 'USER';

Check on which columns constraints are present for the given table name.

SELECT *
  FROM user_cons_columns
 WHERE table_name = '<table name>';

Get the primary key constraint name:

select constraint_name 
from dba_constraints 
where owner = '<SCHEMA_NAME>' and table_name = '<TABLE_NAME>' and constraint_type = 'P';

To check where the above primary key is used as a foreign key.

select owner, table_name, constraint_name
from dba_constraints 
where r_constraint_name = '<result from previous query>' and constraint_type = 'R';

Run below query to give output as equivalent to `show create table <table name>` in MySQL.

set long 10000;
select dbms_metadata.get_ddl( 'TABLE', '<table name>', '<schema name>' )
from dual
/

Note: Before running query run set long 10000 commands.

SQL to retrieve the blob value in text format in Oracle.

Example : Extract the form XML stored in the dyextn_containers table.

select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(XML, 2000,1)) form_xml from dyextn_containers where dbms_lob.instr (xml, utl_raw.cast_to_raw ('<form_name>'), 2000, 1) > 0;

Note: At a time we can only retrieve 2000 bytes only, 2000 is no of bites to print, and 1 is offset. If someone wants to print data after 2000th byte replaces 1 with 2001 next 2000 bytes will get printed.