oracle表空间( 查看路径,修改,创建)
1 查看表空间路径
1
|
select
*
from
dba_data_files;
|
2 修改表空间大小
1
|
ALTER
TABLESPACE MAXDATA
ADD
DATAFILE
'/oradata/XX/MAXDATA02.DBF'
SIZE
1000M;
|
1
2
3
4
5
6
|
[sql]
可以通过PL/SQL查看。
1. 查看所有表空间大小
select
tablespace_name,
sum
(bytes)/1024/1024
from
dba_data_files
group
by
tablespace_name;
2. 未使用的表空间大小
select
tablespace_name,
sum
(bytes)/1024/1024
from
dba_free_space
group
by
tablespace_name;
|
补充回答:
查看当前用户每个表占用空间的大小:
1
|
Select
Segment_Name,
Sum
(bytes)/1024/1024
From
User_Extents
Group
By
Segment_Name
|
3.创建表空间
1
2
3
4
5
6
7
8
9
10
11
12
|
[sql]
-- 创建 DZJC 表空间
CREATE
SMALLFILE TABLESPACE
"LHCZW"
DATAFILE
'F:\oracle\product\10.2.0\oradata\LH\LHCZW'
SIZE
100M AUTOEXTEND
ON
NEXT
100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT
LOCAL
SEGMENT
SPACE
MANAGEMENT AUTO ;
-- 创建 DZJC_INDEX 表空间
CREATE
SMALLFILE TABLESPACE
"LHCZW_INDEX"
DATAFILE
'F:\oracle\product\10.2.0\oradata\LH\LHCZW_index'
SIZE
100M AUTOEXTEND
ON
NEXT
50M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT
LOCAL
SEGMENT
SPACE
MANAGEMENT AUTO ;
-- 创建 DZJC_TEMP 表空间
CREATE
SMALLFILE
TEMPORARY
TABLESPACE
"LHCZW_TEMP"
TEMPFILE
'F:\oracle\product\10.2.0\oradata\LH\LHCZW_temp'
SIZE
100M AUTOEXTEND
ON
NEXT
25M MAXSIZE UNLIMITED EXTENT MANAGEMENT
LOCAL
UNIFORM
SIZE
1M;
-- 创建 DZJC 用户
CREATE
USER
"LHCZW"
PROFILE
"DEFAULT"
IDENTIFIED
BY
"123456"
DEFAULT
TABLESPACE
"LHCZW"
TEMPORARY
TABLESPACE
"LHCZW_TEMP"
ACCOUNT UNLOCK;
GRANT
"CONNECT"
TO
"LHCZW"
WITH
ADMIN
OPTION
;
GRANT
"DBA"
TO
"LHCZW"
WITH
ADMIN
OPTION
;
|