oracle基础练习6章表空间和数据文件-优快云博客

本文介绍了Oracle数据库中表空间和数据文件的管理方法，包括查看表空间详细信息、创建表空间的过程及遇到的问题与解决办法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/* 2008/06/1   星期五
＊蒙昭良
*环境：linux5 + Oracle10gR2
*oracle基础练习6章
＊表空间和数据文件
*/

SQL> set line 130
SQL> col tablespace_name for a15
SQL> select tablespace_name,block_size,extent_management,segment_space_management
2 from dba_tablespaces;

TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN SEGMEN
--------------- ---------- ---------- ------
SYSTEM                8192 LOCAL      MANUAL
UNDOTBS1              8192 LOCAL      MANUAL
SYSAUX                8192 LOCAL      AUTO
TEMP                  8192 LOCAL      MANUAL
USERS                 8192 LOCAL      AUTO
UNDOTBS2              8192 LOCAL      MANUAL
EXAMPLE               8192 LOCAL      AUTO
PERFSTAT              8192 LOCAL      AUTO
RISENET               8192 LOCAL      AUTO

9 rows selected.

SQL> select tablespace_name,initial_extent,next_extent,max_extents,pct_increase,min_extlen from dba_tablespaces;

TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS PCT_INCREASE MIN_EXTLEN
--------------- -------------- ----------- ----------- ------------ ----------
SYSTEM                   65536              2147483645                   65536
UNDOTBS1                 65536              2147483645                   65536
SYSAUX                   65536              2147483645                   65536
TEMP                   1048576     1048576                        0    1048576
USERS                    65536              2147483645                   65536
UNDOTBS2                 65536              2147483645                   65536
EXAMPLE                  65536              2147483645                   65536
PERFSTAT                 65536              2147483645                   65536
RISENET                  65536              2147483645                   65536

9 rows selected.

SQL> create tablespace jinlian
2 datafile '/u01/disk2/jinlian01.dbf' size 10M,
3           '/u01/disk4/jinlian02.dbf' size 20M
4 minimum extent 50k extent management dictionary
5 default storage (initial 50K next 50K maxextents 100 pctincrease 0);
create tablespace jinlian
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace

SQL> l
1 create tablespace jinlian
2 datafile '/u01/disk2/jinlian01.dbf' size 10M,
3           '/u01/disk4/jinlian02.dbf' size 20M
4 minimum extent 50k extent management dictionary
5* default storage (initial 50K next 50K maxextents 100 pctincrease 0)
SQL> del 5
SQL> l
1 create tablespace jinlian
2 datafile '/u01/disk2/jinlian01.dbf' size 10M,
3           '/u01/disk4/jinlian02.dbf' size 20M
4* minimum extent 50k extent management dictionary
SQL> /
create tablespace jinlian
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace

SQL> del 4
SQL> l
1 create tablespace jinlian
2 datafile '/u01/disk2/jinlian01.dbf' size 10M,
3*          '/u01/disk4/jinlian02.dbf' size 20M
SQL> a   management dictionary;
3*          '/u01/disk4/jinlian02.dbf' size 20M management dictionary
SQL> l
1 create tablespace jinlian
2 datafile '/u01/disk2/jinlian01.dbf' size 10M,
3*          '/u01/disk4/jinlian02.dbf' size 20M management dictionary
SQL> /
         '/u01/disk4/jinlian02.dbf' size 20M management dictionary
                                              *
ERROR at line 3:
ORA-02180: invalid option for CREATE TABLESPACE

SQL> select file_id,file_name,tablespace_name from db_data_files order by file_id;
select file_id,file_name,tablespace_name from db_data_files order by file_id
                                              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> desc db_data_files;
ERROR:
ORA-04043: object db_data_files does not exist

SQL> desc dba_data_files;
Name                                                                    Null?    Type
----------------------------------------------------------------------- -------- ------------------------------------------------
FILE_NAME                                                                        VARCHAR2(513)
FILE_ID                                                                          NUMBER
TABLESPACE_NAME                                                                  VARCHAR2(30)
BYTES                                                                            NUMBER
BLOCKS                                                                           NUMBER
STATUS                                                                           VARCHAR2(9)
RELATIVE_FNO                                                                     NUMBER
AUTOEXTENSIBLE                                                                   VARCHAR2(3)
MAXBYTES                                                                         NUMBER
MAXBLOCKS                                                                        NUMBER
INCREMENT_BY                                                                     NUMBER
USER_BYTES                                                                       NUMBER
USER_BLOCKS                                                                      NUMBER
ONLINE_STATUS                                                                    VARCHAR2(7)

SQL> select file_id,file_name,tablespace_name from dba_datafile_files order by file_id;
select file_id,file_name,tablespace_name from dba_datafile_files order by file_id
                                              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> l
1* select file_id,file_name,tablespace_name from dba_datafile_files order by file_id
SQL> c /datafile/data
1* select file_id,file_name,tablespace_name from dba_data_files order by file_id
SQL> l
1* select file_id,file_name,tablespace_name from dba_data_files order by file_id
SQL> /

   FILE_ID
----------
FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
---------------
         1
/u01/app/oracle/oradata/orcl/system01.dbf
SYSTEM

         2
/u01/app/oracle/oradata/orcl/undotbs01.dbf
UNDOTBS1

   FILE_ID
----------
FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
---------------

         3
/u01/app/oracle/oradata/orcl/sysaux01.dbf
SYSAUX

         4
/u01/app/oracle/oradata/orcl/users01.dbf

   FILE_ID
----------
FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
---------------
USERS

         5
/u01/app/oracle/oradata/orcl/example01.dbf
EXAMPLE

         6

   FILE_ID
----------
FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
---------------
/u01/app/oracle/oradata/orcl/perfstat.dbf
PERFSTAT

         7
/u01/app/oracle/oradata/orcl/risenet.dbf
RISENET


   FILE_ID
----------
FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
---------------
         8
/u01/app/oracle/oradata/orcl/undotbs02.dbf
UNDOTBS2

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12778571/viewspace-344628/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12778571/viewspace-344628/