创建表空间:
表空间名称:TBS_YJTX_DATA;
文件路径:D:\app\ldz\oradata\orcl\TBS_YJTX_DATA_01.dbf
单个文件大小:500M
create tablespace TBS_YJTX_DATA datafile ‘D:\app\ldz\oradata\orcl\TBS_YJTX_DATA_01.dbf’ size 500M ;
扩展表空间文件:
ALTER TABLESPACE TBS_YJTX_DATA
ADD DATAFILE 'D:\APP\LDZ\ORADATA\ORCL\TBS_YJTX_DATA_02.DBF' SIZE 1000M;
创建临时表空间:
create temporary tablespace TBS_YJTX_TMP tempfile D:\app\ldz\oradata\orcl\TBS_YJTX_TMP_01.dbf’ size 100M ;
表空间自动扩容
create tablespace TBS_DM_DATA datafile 'D:\app\Oracle\oradata\orcl\DATAFILE\TBS_DM_DATA_01.DBF' size 1024m reuse autoextend on next 100m maxsize UNLIMITED extent management local;
CREATE TEMPORARY TABLESPACE TBS_DM_TEMP TEMPFILE 'D:\app\Oracle\oradata\orcl\DATAFILE\TBS_DM_TEMP_01.DBF' SIZE 100M autoextend on next 32m maxsize 2048m extent management local;
删除表空间
Drop tablespace TBS_DM_DATA ;
Drop tablespace TBS_DM_TEMP;
创建用户
CREATE USER test IDENTIFIED BY password DEFAULT TABLESPACE TBS_DM_DATA TEMPORARY TABLESPACE TBS_DM_TEMP ACCOUNT UNLOCK;
GRANT CREATE TABLE TO test ;
GRANT UNLIMITED TABLESPACE TO test ;
GRANT CONNECT TO test WITH ADMIN OPTION;
GRANT RESOURCE TO test WITH ADMIN OPTION;
GRANT DBA TO test ;
给”tpm”用户授予”temp”表的select/delete/update/insert操作的权限
grant select,delete,update,insert on temp to tpm;
删除用户”tpm”对表”temp”的操作及查询权限
revoke select on temp from tpm;
数据闪回到某个时间点
select * from subject
as of timestamp to_timestamp('2017-08-15 09:20:30','YYYY-MM-DD HH24:MI:SS')