数据库用户创建
---删除用户以及跟用户关联的对象
DROP USER PSBCLCZG CASCADE;
----删除非空表空间,包含物理文件
DROP TABLESPACE PSBCLCZG_TEMP INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE PSBCLCZG_DATA INCLUDING CONTENTS AND DATAFILES;
--查询用户
Select * From V$tablespace;
select * from GV_$TEMP_SPACE_HEADER;
--查询表空间
SELECT * FROM V$TEMPFILE;
--若表空间删除后DBF文件还存在解决方法:关闭临时表空间使用
ALTER DATABASE TEMPFILE 'D:\USERS\ADMINISTRATOR\DOCUMENTS\PRODUCT\11.2.0\DBHOME_1\DATABASE\PSBCLCZG_DATA.DBF' OFFLINE;
--再删除
DROP TABLESPACE PSBCLCZG_DATA INCLUDING CONTENTS AND DATAFILES;
/*分为四步 */
/*第1步:创建临时表空间 */
CREATE TEMPORARY TABLESPACE PSBCLCZG_TEMP
TEMPFILE 'PSBCLCZG_TEMP.DBF'
SIZE 50M
AUTOEXTEND ON
NEXT 50M MAXSIZE 20480M
EXTENT MANAGEMENT LOCAL;
/*第2步:创建数据表空间 */
CREATE TABLESPACE PSBCLCZG_DATA
LOGGING DATAFILE 'PSBCLCZG_DATA.DBF'
SIZE 30G
AUTOEXTEND ON NEXT 10G MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
/*第3步:创建用户并指定表空间 */
CREATE USER PSBCLCZG IDENTIFIED BY PSBCLCZG
Default Tablespace PSBCLCZG_DATA
TEMPORARY TABLESPACE PSBCLCZG_TEMP;
/*第4步:给用户授予权限 */
GRANT CONNECT,RESOURCE,DBA TO PSBCLCZG;
IMP PSBCLCZG/PSBCLCZG FILE=F:\psbclczg20171101pm.dmp FULL=Y LOG=F:
\psbclczg20171101pm.log
imp PSBCLCZG/PSBCLCZG File=F:\psbclczg20180312.dmp Full=Y Log=F:\psbclczg20180312.Log