Oracle創建表空間

1.創建表空間

導出Oracle數據的指令：exp jds/jds@192.168.99.199/orcl file=C:\jds.dmp owner=jds
導入Oracle數據的指令：imp zcl/zcl@127.0.0.1:1521/orcl file=C:\jds.dmp full=y

--第1步：創建臨時表空間
CREATE TEMPORARY TABLESPACE User_Temp TEMPFILE 'E:\OrclData\DataTemp\User_Temp.dbf' SIZE 50m AUTOEXTEND ON NEXT 50m MAXSIZE 20480m EXTENT MANAGEMENT LOCAL;

--第2步：創建表空間語句(創建表空間名稱：User_Data;創建位置：E:\OrclData\DataSpace\User_Data.dbf 初始大小：50m;按50M大小自動增長，最大20480M)
CREATE TABLESPACE User_Data DATAFILE 'E:\OrclData\DataSpace\User_Data.dbf'SIZE 50m AUTOEXTEND ON NEXT 50m MAXSIZE 20480m EXTENT MANAGEMENT LOCAL;

--第3步：創建表空間內的用戶(創建用戶名和密碼 用戶名：zcl 密碼：zcl;預設的表空間位置：User_Data)
CREATE USER zcl IDENTIFIED BY zcl DEFAULT TABLESPACE User_Data TEMPORARY TABLESPACE User_Temp;

--第4步：給用戶授予許可權(賦予用戶zcl許可權：連接許可權，修改許可權，最高許可權)
GRANT CONNECT,RESOURCE,DBA TO zcl;

 2.創建用戶、表空間並設置許可權

//dba賬戶登錄
sqlplus

請輸入用戶名：dpp_data as sysdba
請輸入口令：dpp_data

//創建賬號
create user techrpt_data identified  by techrpt_data;

//創建臨時表空間
create temporary tablespace TECHRPT_DATA_TEMP
tempfile 'D:\ORACLE\ORADATA\ORCL\TECHRPT_DATA_TEMP.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;

//將臨時表空間分配給用戶
alter user techrpt_data temporary tablespace TECHRPT_DATA_TEMP;

//創建表空間
create tablespace TECHRPT_DATA 
logging  
datafile 'D:\ORACLE\ORADATA\ORCL\TECHRPT_DATA.ora' 
size 50m  
autoextend on  
next 50m maxsize 2048m  
extent management local; 

//將表空間分配給用戶
alter user techrpt_data default tablespace TECHRPT_DATA;

//授權
grant dba to techrpt_data;
grant connect,resource to techrpt_data;
grant create session to techrpt_data;
grant create any sequence to techrpt_data;
grant create any table to techrpt_data;
grant delete any table to techrpt_data;
grant insert any table to techrpt_data;
grant select any table to techrpt_data;
grant update any table to techrpt_data;
grant unlimited tablespace to techrpt_data;
grant execute any procedure to techrpt_data;
grant create any view to techrpt_data;

grant select on v_$statname to techrpt_data; 
grant select on v_$sesstat to techrpt_data; 
grant select on v_$session to techrpt_data; 
grant select on v_$mystat to techrpt_data;

//提交
commit;

//退出
quit;

//刪除用戶
drop user techrpt_data cascade;

//刪除表空間
drop tablespace techrpt_data including contents and datafiles;

//修改密碼
alter user system identified by system;