Oracle数据库管理DBA必会知识点
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle数据库管理DBA必会知识点,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4013字,纯文字阅读大概需要6分钟。
内容图文
![Oracle数据库管理DBA必会知识点](/upload/InfoBanner/zyjiaocheng/547/d92b0f85fb0249ddb137679bf256f628.jpg)
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.
内容总结
以上是互联网集市为您收集整理的Oracle数据库管理DBA必会知识点全部内容,希望文章能够帮你解决Oracle数据库管理DBA必会知识点所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。