grant select any dictionary to scott;create table t1 as select * from emp;insert into t1 select * from t1;--查用户看scot
grant select any dictionary to scott;
create table t1 as select * from emp;
insert into t1 select * from t1;
--查用户看scott用户下的段名为t1的存储分区记录
select segment_name,extent_id,file_id,block_id,blocks
from dba_extents where owner='scott' and segment_name='t1';
--给段t1分配大小为100k的存储区间
alter table t1 allocate
extent(datafile '/u01/app/oracle/oradata/orcl/users01.dbf' size 100k);
--回收高水位线之后的空闲空间
alter table t1 deallocate unused;
--回收高水位线20k之后的空闲空间
alter table a deallocate unused keep 20k;
sql> truncate table t1;
截断表之后，，段的第一个分区依然存在，但是数据都已经清空
oracle重命名数据文件的名字
sql> alter tablespace aaa offline;
tablespace altered.
sql> select ts#,name from v$tablespace;
ts# name
---------- ------------------------------
0 system
1 sysaux
2 undotbs1
4 users
3 temp
6 example
7 yuanlei
8 aaa
sql> select ts#,file#,name,status from v$datafile;
ts# file# name status
---------- ---------- --------------------------------------------- -------
0 1 /u01/app/oracle/oradata/orcl/system01.dbf system
1 2 /u01/app/oracle/oradata/orcl/sysaux01.dbf online
2 3 /u01/app/oracle/oradata/orcl/undotbs01.dbf online
4 4 /u01/app/oracle/oradata/orcl/users01.dbf online
6 5 /u01/app/oracle/oradata/orcl/example01.dbf online
8 6 /u01/app/oracle/oradata/orcl/bbb01.dbf offline
sql> host rename /u01/app/oracle/oradata/orcl/bbb01.dbf aaa01.dbf;
[oracle@oracle11gr2 orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@oracle11gr2 orcl]$ cp bbb01.dbf aaa01.dbf
[oracle@oracle11gr2 orcl]$ ls
aaa01.dbf example01.dbf redo03.log temp01.dbf yuanlei01.dbf
bbb01.dbf redo01.log sysaux01.dbf undotbs01.dbf
control01.ctl redo02.log system01.dbf users01.dbf
sql> alter database rename file '/u01/app/oracle/oradata/orcl/bbb01.dbf' to '/u01/app/oracle/oradata/orcl/aaa01.dbf';
database altered.
sql> alter tablespace aaa online;
tablespace altered.
sql> select ts#,file#,name,status from v$datafile;
ts# file# name status
---------- ---------- --------------------------------------------- -------
0 1 /u01/app/oracle/oradata/orcl/system01.dbf system
1 2 /u01/app/oracle/oradata/orcl/sysaux01.dbf online
2 3 /u01/app/oracle/oradata/orcl/undotbs01.dbf online
4 4 /u01/app/oracle/oradata/orcl/users01.dbf online
6 5 /u01/app/oracle/oradata/orcl/example01.dbf online
8 6 /u01/app/oracle/oradata/orcl/aaa01.dbf online
6 rows selected.
重命名成功
-----创建临时表空间
sql> create temporary tablespace test_temp
tempfile '/u01/app/oracle/oradata/orcl/test_temp.dbf' size 10m
autoextend on next 10m maxsize 100m extent management local;
------创建用户表空间并制定用户表空间
sql> create temporary tablespace test_temp tempfile '/u01/app/oracle/oradata/orcl/test_temp.dbf' size 10m autoextend on next 10m maxsize 100m extent management local;
tablespace created.
sql> create tablespace test_data logging datafile '/u01/app/oracle/oradata/orcl/test_data.dbf'
2 size 10m autoextend on next 20m maxsize 100m extent management local;
tablespace created.
sql> create user yuanlei identified by leiyuan default tablespace test_data temporary tablespace test_temp;
user created.
------查看所有用户
select * from dba_users;
-----查看用户所在的默认和临时表空间，后面可跟where 条件
sql> select username,default_tablespace,temporary_tablespace from dba_users;
-----修改用户的默认和临时表空间
sql> alter user yuanlei default tablespace users;
user altered.
sql> alter user yuanlei temporary tablespace temp;
user altered.