【oracle 学习笔记 12】 管理存储结构

SQL> conn system as sysdba
Connected.
SQL> ed
Wrote file afiedt.buf
  1  CREATE TABLESPACE exer_tabspace
  2  datafile 'D:/exer_tabspace.dbf' size 100m
  3  autoextend on next 10m maxsize 1000m
  4* extent management local
SQL> /
Tablespace created.
SQL> ed
Wrote file afiedt.buf
  1  SELECT tablespace_name, initial_extent, next_extent,
  2  	extent_management, allocation_type
  3* FROM dba_tablespaces
SQL> /
TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN ALLOCATIO  
------------------------------ -------------- ----------- ---------- ---------  
SYSTEM                                  65536             LOCAL      SYSTEM     
UNDOTBS1                                65536             LOCAL      SYSTEM     
SYSAUX                                  65536             LOCAL      SYSTEM     
TEMP                                  1048576     1048576 LOCAL      UNIFORM    
USERS                                   65536             LOCAL      SYSTEM     
EXAMPLE                                 65536             LOCAL      SYSTEM     
EXER_TABSPACE                           65536             LOCAL      SYSTEM     
7 rows selected.
SQL> ed
Wrote file afiedt.buf
  1  CREATE temporary TABLESPACE school_temp
  2  tempfile 'd:school_temp.dbf'
  3  size 20m reuse
  4  autoextend on
  5  maxsize 100m
  6* extent management local
SQL> /
Tablespace created.
SQL> ed
Wrote file afiedt.buf
  1  SELECT * 
  2* FROM v$tempfile
SQL> /
     FILE# CREATION_CHANGE# CREATION_TIME         TS#     RFILE# STATUS         
---------- ---------------- -------------- ---------- ---------- -------        
ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE                        
---------- ---------- ---------- ------------ ----------                        
NAME                                                                            
--------------------------------------------------------------------------------
         1                0                         3          1 ONLINE         
READ WRITE   20971520       2560     20971520       8192                        
C:/ORACLE/PRODUCT/10.1.0/ORADATA/ORCL/TEMP01.DBF                                
                                                                                
         2                0                         8          1 ONLINE         
READ WRITE   20971520       2560     20971520       8192                        
D:/SCHOOL_TEMP.DBF                                                              
     FILE# CREATION_CHANGE# CREATION_TIME         TS#     RFILE# STATUS         
---------- ---------------- -------------- ---------- ---------- -------        
ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE                        
---------- ---------- ---------- ------------ ----------                        
NAME                                                                            
--------------------------------------------------------------------------------
                                                                                
SQL> ed
Wrote file afiedt.buf
  1  CREATE bigFile TABLESPACE bf_tbs
  2* datafile 'd:/test_bf.dbf' size 5m
SQL> /
Tablespace created.
SQL> ed
Wrote file afiedt.buf
  1  CREATE undo TABLESPACE undo2
  2  datafile 'd:/undo_tbs01.dbf' size 50m
  3  autoextend on
  4* extent management local
SQL> /
Tablespace created.

SQL> ed
Wrote file afiedt.buf
  1* alter tablespace exer_tabspace offline immediate
SQL> /
alter tablespace exer_tabspace offline immediate
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled 

SQL> ed
Wrote file afiedt.buf
  1* alter tablespace exer_tabspace ADD datafile 'd:exer_tablespace02.dbf' size 20m 
SQL> /
Tablespace altered.
SQL> ed
Wrote file afiedt.buf
  1* alter tablespace datafile 'd:/exer_tabspace02.dbf' offline
SQL> /
alter tablespace datafile 'd:/exer_tabspace02.dbf' offline
                          *
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option 

SQL> ed
Wrote file afiedt.buf
  1* alter database datafile 'd:/exer_tabspace02.dbf' offline
SQL> /
alter database datafile 'd:/exer_tabspace02.dbf' offline
*
ERROR at line 1:
ORA-01516: nonexistent log file, datafile, or tempfile "d:/exer_tabspace02.dbf" 

SQL> spool off