Oracle建立表空間、建立使用者、授予權限、鎖定、解鎖以及删除使用者等

Oracle建立表空間、建立使用者、授予權限、鎖定、解鎖以及删除使用者等

--建立表空間
CREATE   TABLESPACE caiyl
DATAFILE 'D:\Oracle\app\caiyl\oradata\orcl\caiyl_space.dbf' size 500m
AUTOEXTEND ON 
NEXT 200M MAXSIZE 20480M 
EXTENT MANAGEMENT LOCAL;
           

--建立臨時表空間
CREATE TEMPORARY TABLESPACE caiyl_temp
TEMPFILE 'D:\Oracle\app\caiyl\oradata\orcl\caiyl_temp.dbf'
SIZE 200M
AUTOEXTEND ON
NEXT 50M MAXSIZE 20480M
EXTENT MANAGEMENT LOCAL;
           

--建立使用者并指定表空間
CREATE USER caiyl IDENTIFIED BY 123456 DEFAULT TABLESPACE caiyl_space;
           

--建立使用者并指定表空間和臨時表空間
CREATE USER caiyl IDENTIFIED BY 123456
DEFAULT TABLESPACE caiyl_space
TEMPORARY TABLESPACE caiyl_temp;
           

--授權使用者
GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,
     DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,
     DBA,CONNECT,RESOURCE,CREATE SESSION TO  caiyl;
           

--删除表空間
DROP TABLESPACE caiyl_space INCLUDING CONTENTS AND DATAFILES;
           

--檢視表空間
SELECT tv.TABLESPACE_NAME "TABLESPACE_NAME",TOTALSPACE "TOTALSPACE/M",FREESPACE "FREESPACE/M",ROUND((1-FREESPACE/TOTALSPACE)*100,2) "USED%"
FROM 
(SELECT TABLESPACE_NAME,ROUND(SUM(bytes)/1024/1024) TOTALSPACE FROM    DBA_DATA_FILES GROUP BY TABLESPACE_NAME) tv,
         (SELECT TABLESPACE_NAME,ROUND(SUM(bytes)/1024/1024) FREESPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) fs
WHERE tv.TABLESPACE_NAME=fs.TABLESPACE_NAME;
           

--檢視臨時表空間
SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;
           

--增加表空間大小
ALTER TABLESPACE caiyl_space ADD DATAFILE 'D:\Oracle\app\caiyl\oradata\orcl\caiyl_space.dbf' size 4096M;
           

--增加臨時表空間大小
ALTER DATABASE TEMPFILE 'D:\Oracle\app\caiyl\oradata\orcl\caiyl_temp.dbf' RESIZE 8192M;
           

--删除使用者
DROP USER caiyl CASCADE
           

--把資料導入不同于原系統的表空間，在導入之後卻往往發現，資料被導入了原表空間(下面解決此方法)
grant connect, resource,dba to caiyl;
--回收使用者unlimited tablespace權限，這樣就可以導入到使用者預設表空間：
revoke unlimited tablespace from caiyl;
alter user asd quota 0 on caiyl_space;
alter user asd quota unlimited on caiyl_space;
           

--導入導出
EXP caiyl/[email protected]:1521/orcl FILE=f:/caiyl.dmp FULL=y;
EXP caiyl/[email protected] FILE=f:/caiyl.dmp FULL=y;
IMP caiyl/[email protected] FILE=f:/caiyl.dmp FULL=y IGNORE=y;
           

--建立一個使用者，指定在該表空間的配額為10mb,初始狀态為鎖定
create user caiyl identified by 123456
default tablespace caiyl_space 10M on caiyl_space account lock;
           

--建立使用者，在表空間的配額為10mb,密碼設為過期狀态(即首次連接配接資料庫時要修改密碼)
create user caiyl identified by 123456
default tablespace caiyl_space 10M on caiyl_space
profile example_profile password expire;
           

--修改使用者密碼,同時将該使用者解鎖
alter user caiyl identified by newpassword account unlock;
           

--修改預設表空間,在該表空間的配額為20MB,在USER的表空間配額為10MB
alter user caiyl
default tablespace caiyl_space quota 20M ON caiyl_space quota 10M on users;