mysql dba知识点_Oracle数据库管理 DBA必会知识点

这篇博客详细介绍了在Oracle数据库中如何授予用户权限,创建和操作表空间,包括分配存储、回收空间以及截断表。还涉及到了数据文件的重命名过程,并演示了如何创建临时表空间和用户表空间,以及修改用户默认和临时表空间的设置。
摘要由CSDN通过智能技术生成

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.

本文原创发布php中文网,转载请注明出处,感谢您的尊重!

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值