表空间和数据文件：
查询数据文件信息：
SQL>select*from dba_data_files
dba_data_files;;
查询数据块大小：
db_block_size;;
SQL>show parameter db_block_size
查询表空间类型：SQL>SELECT
tablespace_name,extent_management,allocation_type from dba_tablespaces;;
dba_tablespaces
显示：
TABLESPACE_NAME EXTENT_MAN ALLOCATIO
-------------------------------------------------
SYSTEM LOCAL SYSTEM
UNDOTBS1LOCAL SYSTEM
TEMP LOCAL UNIFORM CWMLITE LOCAL SYSTEM
DRSYS LOCAL SYSTEM
EXAMPLE LOCAL SYSTEM
INDX LOCAL SYSTEM
ODM LOCAL SYSTEM
TOOLS LOCAL SYSTEM
USERS LOCAL SYSTEM
XDB LOCAL SYSTEM
已选择11行。
SQL>DESC dba_data_files
dba_data_files;;
v$datafile;;(动态数据字典)
SQL>DESC v$datafile
SQL>select tablespace_name,bytes,blocks,
dba_data_files;; bytes/blocks,autoextensible,file_name from dba_data_files TABLESPACE_NAME BYTES BLOCKS AUT FILE_NAME
-----------------------------------------------------------------------------------------
SYSTEM34078720083200YES C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF UNDOTBS20971520051200YES C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF CWMLITE2097
15205120YES C:\ORACLE\ORADATA\ORCL\CWMLITE01.DBF DRSYS209715205120YES C:\ORACLE\ORADATA\ORCL\DRSYS01.DBF
EXAMPLE15990784039040YES C:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF
INDX262144006400YES C:\ORACLE\ORADATA\ORCL\INDX01.DBF
TOOLS104857602560YES C:\ORACLE\ORADATA\ORCL\TOOLS01.DBF
USERS262144006400YES C:\ORACLE\ORADATA\ORCL\USERS01.DBF
已选择8行。
查询块的大小：
SQL>select tablespace_name,bytes,blocks,
bytes/blocks,autoextensible,file_name from dba_data_files
dba_data_files;;
db_block_size;;
SQL>show parameter db_block_size
NAME TYPE VALUE
----------------------------------------------------
db_block_size integer4096
修改数据文件的大小：
查询数据文件空闲空间：
SQL>SELECT*from dba_free_space
dba_free_space;;
SQL>SELECT tablespace_name,sum(bytes)from
tablespace_name;;
dba_free_space group by tablespace_name
TABLESPACE_NAME SUM(BYTES)
----------------------------------------
UNDOTBS11245184
SYSAUX2293760
USERS1900544
SYSTEM6160384
EXAMPLE23396352
查询数据文件大小：
dba_data_files;; SQL>SELECT file_id,bytes,file_name from dba_data_files
FILE_ID BYTES FILE_NAME
-------------------------------------------------------
1340787200C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
2209715200C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
320971520C:\ORACLE\ORADATA\ORCL\CWMLITE01.DBF
420971520C:\ORACLE\ORADATA\ORCL\DRSYS01.DBF
5159907840C:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF
水表怎么看626214400C:\ORACLE\ORADATA\ORCL\INDX01.DBF
710485760C:\ORACLE\ORADATA\ORCL\TOOLS01.DBF
826214400C:\ORACLE\ORADATA\ORCL\USERS01.DBF
查询数据文件自动扩展属性：
SQL>select bytes,autoextensible,maxbytes,file_name from dba_data_files;;
dba_data_files
BYTES AUT MAXBYTES FILE_NAME
-----------------------------------------------------------
419430400YES3.4360E+10C:\ORACLE\ORADATA\JDS\SYSTEM01.DBF 209715200YES3.4360E+10C:\ORACLE\ORADATA\JDS\UNDOTBS01.DBF 20971520YES3.4360E+10C:\ORACLE\ORADATA\JDS\CWMLITE01.DBF 20971520YES3.4360E+10C:\ORACLE\ORADATA\JDS\DRSYS01.DBF 156631040YES3.4360E+10C:\ORACLE\ORADATA\JDS\EXAMPLE01.DBF 26214400YES3.4360E+10C:\ORACLE\ORADATA\JDS\INDX01.DBF
20971520YES3.4360E+10C:\ORACLE\ORADATA\JDS\ODM01.DBF
10485760YES3.4360E+10C:\ORACLE\ORADATA\JDS\TOOLS01.DBF
26214400YES3.4360E+10C:\ORACLE\ORADATA\JDS\USERS01.DBF 39976960YES3.4360E+10C:\ORACLE\ORADATA\JDS\XDB01.DBF
已选择10行。
修改数据文件的大小：
XXM;; SQL>Alter database datafile
datafile''数据文件名称及路径'resize XXM
扩展数据文件
SQL>alter database datafile'
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DB 200m;;
F'resize200m
压缩数据文件
SQL>alter database
datafile'C:\ORACLE\ORADATA\ORCL\USERS01.DBF'resize 300m;;
300m
数据文件自动扩展属性：
datafile''
SQL>Alter database datafile
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\BCM_DATA0 3.DBF
3.DBF''autoextend on/off
on/off;;
datafile''
SQL>Alter database datafile
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\BCM_DATA0 150M;;
3.DBF''resize150M
3.DBF
查询表空间与数据文件的对应关系：
养老保险能退钱吗
SQL>select tablespace_name,bytes,file_name from
dba_data_files;;
dba_data_files
TABLESPACE_NAME BYTES FILE_NAME
---------------------------------------------------------------------------
SYSTEM419430400C:\ORACLE\ORADATA\JDS\SYSTEM01.DBF
UNDOTBS1209715200C:\ORACLE\ORADATA\JDS\UNDOTBS01.DBF
CWMLITE20971520C:\ORACLE\ORADATA\JDS\CWMLITE01.DBF
DRSYS20971520C:\ORACLE\ORADATA\JDS\DRSYS01.DBF
EXAMPLE156631040C:\ORACLE\ORADATA\JDS\EXAMPLE01.DBF
INDX26214400C:\ORACLE\ORADATA\JDS\INDX01.DBF
ODM20971520C:\ORACLE\ORADATA\JDS\ODM01.DBF
TOOLS10485760C:\ORACLE\ORADATA\JDS\TOOLS01.DBF
USERS26214400C:\ORACLE\ORADATA\JDS\USERS01.DBF
XDB39976960C:\ORACLE\ORADATA\JDS\XDB01.DBF
已选择10行。
查询表空间运行状态：
dba_tablespaces;; SQL>select tablespace_name,status from dba_tablespaces TABLESPACE_NAME STATUS
-------------------------------------
SYSTEM ONLINE
UNDOTBS1ONLINE
TEMP ONLINE
CWMLITE ONLINE
DRSYS ONLINE
EXAMPLE ONLINE
INDX ONLINE
ODM ONLINE
TOOLS ONLINE
USERS ONLINE
XDB ONLINE
已选择11行
查询表空间类型：
SQL>select
tablespace_name,extent_management,allocation_type dba_tablespaces;;
from dba_tablespaces
TABLESPACE_NAME EXTENT_MAN ALLOCATIO
-------------------------------------------------
SYSTEM LOCAL SYSTEM
UNDOTBS1LOCAL SYSTEM
TEMP LOCAL UNIFORM
CWMLITE LOCAL SYSTEM
DRSYS LOCAL SYSTEM
EXAMPLE LOCAL SYSTEM
INDX LOCAL SYSTEM
ODM LOCAL SYSTEM
TOOLS LOCAL SYSTEM
USERS LOCAL SYSTEM
七夕12字祝福语XDB LOCAL SYSTEM
TABLESPACE_NAME EXTENT_MAN ALLOCATIO
-------------------------------------------------
BCM_DATA LOCAL UNIFORM
已选择12行。
查询用户缺省表空间：
dba_users;; SQL>select username,default_tablespace from dba_users USERNAME DEFAULT_TABLESPACE
-------------------------------------------------------
大专生出国留学条件SYS SYSTEM
SYSTEM SYSTEM
DBSNMP SYSTEM
USER02USERS
SCOTT SYSTEM
USER01USERS
USER03USERS
USER05USERS
USER07USERS
USER16USERS
USER15USERS
USERNAME DEFAULT_TABLESPACE
-------------------------------------------------------
USER14USERS
USER13USERS
USER12USERS
USER11USERS
USER10USERS USER09
USERS
修改表空间名称：
SQL>alter tablespace big_tbs rename to big_file_tbs；表空间已更改。
设置数据库默认表空间：
女用香水SQL>alter database default tablespace users；数据库已更改。
创建用户表空间：
SQL>create tablespace sale sale01
01datafile 'C:\app\Administrator\oradata\orcl\'C:\app\Administrator\oradata\orcl\sale sale sale01.dbf'size 01.dbf'size 10m autoextend on next 10m maxsize 10100
0m,'C:\app\Administrator\oradata\orcl\sale sale02.dbf'size 02.dbf'size 10m autoextend on next 5m maxsize unlimited,
'C:\app\Administrator\oradata\orcl\sale sale03.dbf'size 03.dbf'size 10m EXTENT MANAGEMENT LOCAL UNIFORM SIZE 500k 500k;
;SQL>create tablespace sale sale02
02datafile 'C:\app\Administrator\oradata\orcl\'C:\app\Administrator\oradata\orcl\sale sale sale004.dbf'size 10m autoextend on next 100m maxsize 10m,
'C:\app\Administrator\oradata\orcl\sale sale005.dbf'size 10m autoextend on next 5m maxsize unlimited,
'C:\app\Administrator\oradata\orcl\sale sale006.dbf'size 10m EXTENT MANAGEMENT LOCAL UNIFORM SIZE 500k 500k;
;SQL>CREATE SMALLFILE TABLESPACE "SALE"DATAFILE
'C:\app\Administrator\oradata\orcl\sale01'SIZE 10M AUTOEXTEND ON NEXT 50K MAXSIZE 50M ,'C:\app\Administrator\oradata\orcl\sale02'SIZE 10M AUTOEXTEND ON NEXT 20K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO AUTO;
；修改数据文件大小：
深渊派对邀请函SQL>alter database datafile 'C:\oracle\product\10.2.0\oradata\orcl \system01.dbf'Resize 300M;
为表空间增加数据文件时，也可以指定新增加的数据文件的自动扩展属性：SQL>alter database bcm_data
Add datafile 'C:\oracle\product\10.2.0\oradata\orcl
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论