创建表空间以及正式库导入本地的基本语句

–创建表空间

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;
--对用户进行修改
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导入需要的权限
grant OEM_MONITOR to maximo;
GRANT EXP_FULL_DATABASE TO maximo ;
GRANT IMP_FULL_DATABASE TO maximo;

–删除用户名

drop user maxszhlg cascade

–删除表空间已经文件

 drop tablespace MAXINDEX including contents and datafiles cascade constraints;

–查询用户在内存中的位置

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

–通过kill清除用户

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

–查询DIRECTORY位置路径

select * from dba_directories

–导入dmp文件， DIRECTORY文件路径，dumpfile：dmp文件 ，
–remap_schema：对应数据库（如数据库名相同则不用写，否则参数1为导出的数据库名，参数2为导入数据库名）
–remap_tablespace：对应表空间（与remap_tablespace格式一样）
–EXCLUDE过滤某个表，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
--指定多个表空间
remap_tablespace=users:tablespace_test,user01:tablespace_test,user02:tablespace_test可以这样多对一

–查询表空间使用情况

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;

–查询表空间路径

select file_name from dba_data_files where tablespace_name = 'MAXINDEX'