– create tablespace

CREATE SMALLFILE TABLESPACE "MAXDATA" DATAFILE 'D:\app\Administrator\oradata\orcl\MAXDATA' SIZE 1000M AUTOEXTEND ON NEXT 1000K;
CREATE TABLESPACE MAXINDEX DATAFILE 'D:\app\Administrator\oradata\orcl\MAXINDEX' SIZE  2048M AUTOEXTEND ON NEXT 2048K;
CREATE TEMPORARY TABLESPACE MAXTEMP TEMPFILE 'D:\app\Administrator\oradata\orcl\MAXTEMP' SIZE  2048M AUTOEXTEND ON MAXSIZE unlimited;

create user maximo identified by maximo;
--Modify users
alter user maximo default tablespace MAXDATA quota unlimited on MAXDATA;
alter user maximo quota unlimited on MAXINDEX;
alter user maximo temporary tablespace MAXTEMP;
grant create trigger to maximo ;
grant create session to maximo;
grant create sequence to maximo;
grant create synonym to maximo;
grant create table to maximo;
grant create view to maximo;
grant create procedure to maximo;
grant alter session to maximo;
grant execute on ctxsys.ctx_ddl to maximo;

--To allow Maximo to give Database Access to Maximo Users in Sig. Security, run these:
grant create user to maximo;
grant drop user to maximo;
grant create session to maximo with ADMIN OPTION;
grant alter user to  maximo ;
grant dba to maximo ;
grant create job to maximo;

-- em Import required permissions
grant OEM_MONITOR to maximo;
GRANT EXP_FULL_DATABASE TO maximo ;
GRANT IMP_FULL_DATABASE TO maximo;

– delete user name

drop user maxszhlg cascade

– delete the existing file in the tablespace

 drop tablespace MAXINDEX including contents and datafiles cascade constraints;

– query the user's location in memory

select username,sid,serial# from v$session;

– clear users through kill

alter system kill session'141,3526'
select 'alter system kill SESSION '''||sid||','||serial#||''';' from v$session WHERE username='MAXDB';

– query DIRECTORY location path

select * from dba_directories

– import dmp file, DIRECTORY file path, dumpfile: dmp file,
– remap ﹣ schema: corresponding database (if the database name is the same, do not write, otherwise parameter 1 is the name of the exported database and parameter 2 is the name of the imported database)
– remap table space: corresponding table space (in the same format as remap table space)
– EXCLUDE to filter a table. You need to use \ escape under windows“

impdp maxszhlg/maxszhlg@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=EAMPRDDB122_2017Y03M01D02H30M00S.DMP remap_schema=maximo:maxszhlg remap_tablespace=MAXINDEX:MAXDATA_SZHLG  EXCLUDE = TABLE:"IN ('EMP', 'DEPT')" 

impdp maximogz/maximogz@orcl remap_schema=yymaximo:maximogz directory=DATA_PUMP_DIR dumpfile=EXPDP_MAXIMOLZ_2017-01-05.DMP logfile=expdp_maximo_%date:~0,4%-%date:~5,2%-%date:~8,2%.log
--Specify multiple tablespaces
remap_tablespace=users:tablespace_test,user01:tablespace_test,user02:tablespace_test So many to one

– query table space usage

SELECT T.TABLESPACE_NAME,
       D.FILE_NAME,
       D.AUTOEXTENSIBLE,
       D.BYTES,
       D.MAXBYTES,
       D.STATUS
  FROM DBA_TABLESPACES T, DBA_DATA_FILES D
 WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
 ORDER BY TABLESPACE_NAME, FILE_NAME;

– query tablespace path

select file_name from dba_data_files where tablespace_name = 'MAXINDEX'