Oracle基础(一）:表空间和数据文件

 

1.--创建tablespace
create (undo|temporary) tablespace byy
logging
datafile '/export/home/oracle/biyy/biyy.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;

2.--创建用户并指定表空间
create user hr identified by hr
default tablespace byy
temporary tablespace temp;

create user hr identified by hr1
default tablespace byy
temporary tablespace temp
quota 10M on byy
password expire;  --用户会在初次登录的时候被提示修改密码

3.--删除用户
drop user 用户名 cascade --级联删除掉该模式中的所有对

4.--限制具有select any table的用户不能访问系统字典表
o7_dictionary_accessibility = false

5.--将dictionary-managed tablespace 改成local managed tablespace执行下列pl/sql包
DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')

##Make a complete backup of your database;
##Ensure that the database has a default temporary tablespace that is not SYSTEM.
  The temporary tablespace is created using the create temporary tablesapce command
##Elimanate any undo(rollback) segments in dictionary-managed tablespaces;
##There should be at least one online undo segment in a locally managed tablespace.or
   an undo tablespace should be online;
##All tablespaces other than the tablespace containing the undo space the default
  temporary tablespace should be placed in READ ONLY mode;  
##Start up the instance in restricted mode;
##Migrate the SYSTEM tablespace by using
  DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');
 
6.--修改默认临时表空间
alter database default temporary tablespace default_temp2;
##临时表空间不能被离线
##不能被drop直到有新的临时表空间被创建(至少有一个临时表空间存在)
##不能被alter成永久表空间(permanent tablespace)

7.--修改表空间为只读(读写)
alter dataspace userdata READ ONLY(read write)
##Causes a checkpoint
##Data available only for read operations
##Object can be dropped from tablespace(因为修改的dictionary)

8.--显示当前用户
show user;

9.--修改表空间离线/在线
alter tablespace tablespace1 offline(online);

10.--调整表空间大小
##Automatically using autoextend
alter database datafile 'data.ora'
autoextentend on next 20M maxsize 1000M

##Manually using Alter Database
alter tablespace tablespace_name
add datafile 'Data2.ora' size 200M

##Adding a data file using Alter tablespace
alter tablespace user_data
datafile
'/u01/oradata/userdata02.dbf' size 200M
autoextentend on next 10M maxsize 500M

alter database
datafile '已有的数据文件的文件名'
autoextentend on next 10M maxsize 500M

##Manually Resizing Data File(用于缩小数据文件，不能缩小到比实际数据小)
Alter database
Datafile 'u02/oradata/userData02.dbf'
Resize 200M

11.--移动数据文件(与redo logfile rename 对比)
##两种方式：一种是通过修改tablespace，另外一种是通过database
##Tablespace must be offline;
##Target data files must exist.copy the old file to the new target
alter tablespace userData RENAME
Datafile '/u01/oradata/userdata01.dbf'
To '/u02/oradata/userdata02.dbf';

##Database must be mounted
##Target data file must exist
Alter database rename
file '/u01/oradata/system01.dbf'
to '/u03/oradata/system01.dbf'

12.--删除表空间
drop tablesapce XXX      仅去除关联
including contents       并且删除内容
including contents and datafiles 并且删除文件
cascade constraints       级联删除约束