How to change Database Default Tablespace for users

1. Create new Tablespace <KAFA_ITGFIN_DBF> for schema KAFITGFIN
create tablespace KAFA_ITGFIN_DBF datafile
'D:\ORACLE\ORA92\MUJAZORC\KAFA_ITGFIN_DBF.DBF' size 5120m autoextend on;
2. Take Export of schema KAFAFIN on Seperate drive.
EXP SYSTEM/SYSMAN@mujazorc OWNER=KAFAFIN FILE=F:\Dump\kafafin_16022015.DMP GRANTS=Y BUFFER=10000 COMPRESS=Y ROWS=Y  LOG= F:\Dump\kafafin_16022015.log
3. Now create new schema or if you have already existing schema then drop and recreate it.
drop KAFITGFIN CASCADE;

CREATE USER KAFAITGFIN
  IDENTIFIED BY VALUES KAFAITGFIN
  DEFAULT TABLESPACE KAFA_ITGFIN_DBF
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 1 Role for KAFITGFIN
  GRANT DBA TO KAFAITGFIN WITH ADMIN OPTION;
  ALTER USER KAFAITGFIN DEFAULT ROLE ALL;
  -- 1 System Privilege for KAFAITGFIN
  GRANT UNLIMITED TABLESPACE TO KAFAITGFIN WITH ADMIN OPTION;
  -- 1 Tablespace Quota for KAFITGFIN
  ALTER USER KAFAITGFIN QUOTA UNLIMITED ON KAFA_ITGFIN_DBF;

alter user KAFAITGFIN quota 0 on KAFA_FIN_DBF quota unlimited on KAFA_ITGFIN_DBF;
alter user KAFAITGFIN quota 0 on MUJ_FIN_DBF quota unlimited on KAFA_ITGFIN_DBF;
Note: Here in above example two different tablespace having the tables of same users.

In the case while changing the default tablespace for the user's in the same database, don't forget to assign
quota unlimited on new tablespace and revoke unlimited quota on old tablespace. This might not required while changing the tablespace of user's of different database.

revoke unlimited tablespace from KAFA_ITGFIN_DBF;

4. Now try to import the dump you have taken. Make sure dump is properly copied on the drive.

IMP SYSTEM/sysman@MUJAZORC FILE=F:\Dump\kafafin_16022015.DMP FROMUSER=KAFAFIN TOUSER=AWAED LOG=F:\Dump\kafafin_16022015.DMP.log
Don't forget to assign back quota unlimited on 'awaed' tablespace.
alter user awaed DEFAULT tablespace awaed QUOTA UNLIMITED ON awaed;

How to Move Tables into new tablespace of another database:
The case when you need to change tablespaces which is wrongly assigned and you want to move those users tables to different tablespace. First identify the tables from database which you want to move:
Select owner,table_name,tablespace_name from dba_tables where owner = 'ORAFIN';
OWNER TABLE_NAME TABLESPACE_NAME
ORAFIN ACCFILE MUJ_FIN_DBF
ORAFIN ACCOUNT_TRN           MUJ_FIN_DBF
ORAFIN ACCOUNT_TRN1         MUJ_FIN_DBF
ORAFIN APPLICATION MUJ_FIN_DBF
ORAFIN ASSETS1 MUJ_FIN_DBF
ORAFIN ASSETS1_ALL MUJ_FIN_DBF
ORAFIN ASSETS_ALL         MUJ_FIN_DBF
ORAFIN ASSETS_ALL_HI         MUJ_FIN_DBF
ORAFIN AUT_DISC         MUJ_FIN_DBF
ORAFIN AUT_LABEL         MUJ_FIN_DBF

Now you can use alter table command to move that tables to new tableapace. In the case while
moving the tables having index then you need to rebuild the index otherwise no need to do any thing.

Alter table table_name move tablespace KAF_ITGFIN_DBF;
Alter index <index_name> rebuild tablespace new_tablespace_name;

Generally Normal index moved explicitly where as LOB indexes are moved implicityly where as
related constraint will automatically moved to the new tablespace.

Now make sure the new schema is having the new default tablespace or not. Then the next time object is created on new tablespace.

Tablespace management related commands

Tablespace is a logical structure in database.Tablespace is the grouping of one or more datafiles which are physical structures that store database data.
Create tablespace:
SQL>create tablespace tsname
datafile ‘/pathofdatafile’ size datafile-size
extent management local — default.other value is dictionary
autoallocate size — other value is uniform
segment space management auto; — default is auto.Other value is manual
To check if the datafile has been created successfully use :
SQL>select extent_management,allocation_type,segment_space_management from dba_tablespaces where tablespace_name=’tsname’;
Remove tablespace :
SQL>drop tablespace tsname;
If the tablespace has objects like table,index when we issue the above command we get error.So use:
sql>drop tablespace tsname including contents;
To physically remove the datafile as well use:
sql>drop tablespace tsname including contents and datafiles;
To drop referential integrity constraints use:
sql>drop tablespace tsname cascade constraints;
Alter the tablespace size – increase and decrease.If tablespace is already occupied it is not possible to decrease the size of the tablespace.
sql>alter tablespace tsname add datafile ‘/datfile-path’ size datafile-size;
To change the size of a datafile:
sql>alter database datafile ‘/datafilepath’ resize new-datafilesize

Important Data Dictionary Views And Their Uses is given below :

dba_catalog – shows names and owners of all tables, indexes, views, synonyms, sequences and clusters in a database
dba_objects – shows all objects in the database, including their creation time and when they were last altered
dba_tablespaces – shows all tablespaces and provides information on tablesapce attributes like type of tablespace management, space allocation, segment space management
dba_tables – shows all tables, their owners, tablespace they belong to. We can find information like last time table was analyzed, average row length, number of rows in a table
dba_indexes – shows all indexes and tables on which they are defined
dba_part_tables – shows information about all partitioned tables, including tablenames, partitioning, subpartitioning types
dba_synonyms – shows all synonyms,table names, owners of the tables on which synonym was degined
dba_triggers – shows all triggers and tells the triggering events that set off the trigger.TRIGGEr_BODY column contains the actual trigger definition
dba_sequences – shows all sequences and includes the maximum , minimum and last value of a sequence
dba_constraints – shows all constraints and constraint types
dba_cons_columns – shows all the constraints in the database, the column in which they are defined at
dba_tab_columns – provides detailed information on every column in the table. Gives information on average column length, density of column, time it was last analyzed.