Oracle 用户管理

查询用户权限

1
2
3
select * from USER_ROLE_PRIVS where USERNAME = USER;
select * from USER_TAB_PRIVS where Grantee = USER;
select * from USER_SYS_PRIVS where USERNAME = USER;

创建用户

1
2
3
4
5
create user {username} identified by {password}
default tablespace {tablespace_name}
temporary tablespace {temp_tablespace_name};

grant connect,resource to {username};

删除用户

1
drop user {user} cascade

表空间管理

查询表空间

1
2
3
4
5
6
7
8
-- 表空间和临时表空间
select * from dba_tablespaces;

-- 表空间文件
select * from dba_data_files;

-- 临时表空间文件
select * from dba_temp_files;

创建表空间

1
2
3
4
5
6
7
CREATE TABLESPACE aneat
logging
datafile '/opt/oracle/oradata/ORCL/aneat.dbf'
size 50m
autoextend on
next 50m maxsize 2048m
extent management local;

创建临时表空间

1
2
3
4
5
6
CREATE TEMPORARY TABLESPACE aneat_tmp
tempfile '/opt/oracle/oradata/ORCL/aneat_tmp.dbf'
size 50m
autoextend on
next 50m maxsize 512m
extent management local;

调整表空间容量大小

1
2
3
4
5
6
7
8
9
方法一，允许已存在的数据文件自动增长
alter database datafile '/home/oracle/app/oradata/orcl/system02.dbf'
autoextend on next 100M maxsize 4096M;

方法二，给表空间增加数据文件
alter tablespace SYSTEM add datafile
'/home/oracle/app/oradata/orcl/system02.dbf' size 1024M
-- 并且允许数据文件自动增长
autoextend on next 100M maxsize 4096M;

删除表空间

1
drop tablespace {tbs} including contents and datafiles cascade constraints;

释放磁盘空间

计算表空间占用磁盘情况

1
2
3
4
5
6
7
8
9
10
11
select
  upper(f.tablespace_name) "表空间名", d.tot_grootte_mb "表空间大小(M)", d.tot_grootte_mb - f.total_bytes "已使用空间(M)",
  to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100, 2), '990.99') "使用比",
  f.total_bytes "空闲空间(M)", f.max_bytes "最大块(M)"
from
( select tablespace_name, round(sum(bytes) / (1024 * 1024), 2) total_bytes, round(max(bytes) / (1024 * 1024), 2) max_bytes
  from sys.dba_free_space group by tablespace_name ) f,
( select dd.tablespace_name, round(sum(dd.bytes) / (1024 * 1024), 2) tot_grootte_mb
  from sys.dba_data_files dd group by dd.tablespace_name ) d
where d.tablespace_name = f.tablespace_name
order by 4 desc;

计算datafile可以resize收缩的空间

1
2
3
4
5
6
7
8
9
10
11
12
13
select
  a.file#, a.name, a.bytes/1024/1024 currentmb,
	ceil(HWM * a.block_size)/1024/1024 resizeto,
	(a.bytes - HWM * a.block_size)/1024/1024 releasemb,
	'alter database datafile ''' || a.name || ''' resize ' || ceil(HWM * a.block_size/1024/1024) || 'M;' resizecmd
from v$datafile a,
( select file_id,max(block_id+blocks-1) HWM from dba_extents group by file_id ) b
where a.file# = b.file_id(+)
and (a.bytes - HWM * block_size) > 0
order by 5

-- 调整表空间物理大小
alter database datafile '/home/oracle/app/oradata/orcl/system02' resize 100M;

更改所属表空间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 更改 TABLE 所属表空间
select * from user_tables;
alter table t_user move tablespace tbs_name;

-- 更改 INDEX 所属表空间
select * from user_indexes;
alter index index_name rebuild tablespace tbs_name;
alter index index_name rebuild;

-- 更改 LOBSEGMENT 所属表空间
select * from dba_lobs;
alter table table_name move lob(lob-col) store as (tablespace new_tablespace_name);

-- 更改 TABLE PARTITION 所属表空间
select * from dba_part_tables;
select * from dba_tab_partitions;
select 'alter table ' || table_owner || '.' || table_name || ' move partition ' || partition_name || ' tablespace tbs_name;'
from dba_tab_partitions
where table_owner = tbs_name and table_name in ('t_gaokao_msg_his')