oracle表空间分配,oracle 数据库、用户默认数据表空间分配

SQL> select property_name,property_value from database_properties;

PROPERTY_NAME PROPERTY_VALUE

------------------------------ --------------------

DICT.BASE 2

DEFAULT_TEMP_TABLESPACE TEMPTS1

DEFAULT_PERMANENT_TABLESPACE SYSTEM

DBTIMEZONE +08:00

DEFAULT_TBS_TYPE SMALLFILE

NLS_LANGUAGE AMERICAN

NLS_TERRITORY AMERICA

NLS_CURRENCY $

NLS_ISO_CURRENCY AMERICA

NLS_NUMERIC_CHARACTERS .,

NLS_CHARACTERSET ZHS16GBK

NLS_CALENDAR GREGORIAN

NLS_DATE_FORMAT DD-MON-RR

NLS_DATE_LANGUAGE AMERICAN

NLS_SORT BINARY

NLS_TIME_FORMAT HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXF

F AM

NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXF

F AM TZR

NLS_DUAL_CURRENCY $

NLS_COMP BINARY

NLS_LENGTH_SEMANTICS BYTE

NLS_NCHAR_CONV_EXCP FALSE

NLS_NCHAR_CHARACTERSET AL16UTF16

NLS_RDBMS_VERSION 10.2.0.4.0

GLOBAL_DB_NAME ORCL

EXPORT_VIEWS_VERSION 8

WORKLOAD_CAPTURE_MODE

28 rows selected.

SQL> select username,default_tablespace from dba_users where username='ZWZ';

USERNAME DEFAULT_TABLESPACE

------------------------------ ------------------------------

ZWZ SYSTEM

SQL> select name from v$tablespace;

NAME

------------------------------

SYSTEM

UNDOTBS1

SYSAUX

TEMPTS1

CMS

ICME

IPTV

NOVARTIS

SANJI

SIEBELDB

TOPBOX

XMSB

XNH

ZYY

USERS

TEMP2

16 rows selected.

SQL> alter user zwz default tablespace icme;

User altered.

SQL> select username,default_tablespace from dba_users where username='ZWZ';

USERNAME DEFAULT_TABLESPACE

------------------------------ ------------------------------

ZWZ ICME

SQL> alter database default tablespace users;

Database altered.

SQL> select property_name,property_value from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE

------------------------------ --------------------

DEFAULT_PERMANENT_TABLESPACE USERS

SQL> select username,default_tablespace from dba_users where username='ZWZ';

USERNAME DEFAULT_TABLESPACE

------------------------------ ------------------------------

ZWZ ICME

SQL> create user anbob identified by anbob;

User created.

SQL> select username,default_tablespace from dba_users where username='ANBOB';

USERNAME DEFAULT_TABLESPACE

------------------------------ ------------------------------

ANBOB USERS

SQL> drop user anbob ;

User dropped.

SQL> create tablespace test datafile '/oradata/orcl/test.dbf' size 10m;

Tablespace created.

SQL> alter database default tablespace test;

Database altered.

SQL> select property_name,property_value from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE

------------------------------ --------------------

DEFAULT_PERMANENT_TABLESPACE   TEST

SQL> drop tablespace test;

drop tablespace test

*

ERROR at line 1:

ORA-12919: Can not drop the default permanent tablespace

SQL> alter database default tablespace users;

Database altered.

SQL> drop tablespace test including contents and  datafiles;

Tablespace dropped.

DEFAULT_PERMANENT_TABLESPACE 是数据库级默认表空间，如果建用户不指定明空间默认就会用这个参数批定的，当然创建用户时也可以让他自立门户分配一个独的表间default tablespace ，比如数据与索引分别再两个不同盘区的两个表空间来做IO负载均衡,比如我今天发现默认表空间是SYSTEM这种习惯就不太好, 数据库默认表空间 是不允许删除的，现在考考你，用户的默认表空间能不能删呢？删了以后用户默认表空间又是多少呢？

打赏

微信扫一扫，打赏作者吧～