查看用户相关信息

select * from USER_USERS

查看表空间相关信息-主要用来查看表空间绝对路径

SELECT * FROM Dba_Data_Files ddf
--WHERE ddf.tablespace_name = 'TablespaceName';

查看ORACLE版本

select * from v$version;

结果可能为

BANNER REMARK
1 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production 版本号
2 PL/SQL Release 11.2.0.1.0 - Production
3 CORE 11.2.0.1.0 Production
4 TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
5 NLSRTL Version 11.2.0.1.0 - Production

创建表空间

create tablespace TABLESPACE_NAME 
datafile 'G:\TABLESPACE\TABLESPACE_NAME.DBF' 
size 1500M 
autoextend on next 5M maxsize 3000M;

创建用户、指定表空间、赋予权限

  1. 创建用户+指定表空间
--创建用户
create user USER_NAME identified by USER_PASSWORD;
--指定 表空间 和 临时表空间
default tablespace TABLESPACE_NAME 
temporary tablespace TEMP_TABLESPACE_NAME;
  1. 赋予权限
GRANT
CONNECT,                
RESOURCE,               
DBA,                  
unlimited tablespace,
CREATE  SESSION,         
CREATE ANY SEQUENCE,     
CREATE ANY TABLE,        
CREATE ANY VIEW ,        
CREATE ANY INDEX,        
CREATE ANY PROCEDURE,    
CREATE ANY DIRECTORY,    
ALTER  SESSION, 
ALTER ANY SEQUENCE,     
ALTER ANY TABLE,        
--ALTER ANY VIEW ,        --不能修改视图
ALTER ANY INDEX,        
ALTER ANY PROCEDURE,    
--ALTER ANY DIRECTORY,    --不能修改目录
--DROP  SESSION,       --不能删除Session
DROP ANY SEQUENCE,     
DROP ANY TABLE,        
DROP ANY VIEW ,        
DROP ANY INDEX,        
DROP ANY PROCEDURE,    
DROP ANY DIRECTORY,    
SELECT ANY TABLE, 
SELECT ANY DICTIONARY,
INSERT ANY TABLE, 
UPDATE ANY TABLE, 
DELETE ANY TABLE,
DEBUG ANY PROCEDURE,
DEBUG CONNECT SESSION,
exp_full_database,  
imp_full_database     
TO USER_NAME;

通过exp方式导出dmp

exp USER_NAME/USER_PASSWPRD@127.0.0.1:1521/ORCL file=D:\dmp\file.dmp

通过imp方式导入dmp

imp USER_NAME/USER_PASSWPRD@127.0.0.1/ORCL file=D:\dmp\file.dmp log=D:\dmp\file.log full=y