ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle10g_alter table_测试3

oracle10g_alter table_测试3

原创 Linux操作系统 作者:wisdomone1 时间:2009-09-12 16:58:43 0 删除 编辑

SQL>
SQL>
SQL> show user
USER is "TBS_02"
SQL> conn /as sysdba
Connected.
SQL> select username from dba_users;

USERNAME
------------------------------
TBS_02
FS1
ZXY
SCOTT
TEST
AUDIT_TEST
TSMSYS
MDDATA
DIP
MDSYS
ORDSYS

USERNAME
------------------------------
EXFSYS
DMSYS
DBSNMP
WMSYS
CTXSYS
ANONYMOUS
SYSMAN
XDB
ORDPLUGINS
SI_INFORMTN_SCHEMA
OLAPSYS

USERNAME
------------------------------
SYS
SYSTEM
OUTLN
MGMT_VIEW

26 rows selected.

SQL> conn test/system
Connected.

SQL> desc user_sys_privs;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                           VARCHAR2(30)
 PRIVILEGE                                 NOT NULL VARCHAR2(40)
 ADMIN_OPTION                                       VARCHAR2(3)

SQL> select username,privilege from user_sys_privs;--查看给这个用户授权的所有权限

USERNAME                       PRIVILEGE
------------------------------ ----------------------------------------
TEST                           DROP TABLESPACE
TEST                           CREATE TABLESPACE
TEST                           UNLIMITED TABLESPACE

SQL> conn /as sysdba
Connected.
SQL> conn test/system
Connected.
SQL> alter tablespace test add datafile '/oracle/db/test02.dbf' size 20m;--test用户没有alter tablespace权限哟
alter tablespace test add datafile '/oracle/db/test02.dbf' size 20m
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> conn /as sysdba
Connected.
SQL> grant alter tablespace to test;--给test用户授权

Grant succeeded.

SQL> conn test/system
Connected.
SQL> select username,privilege from user_sys_privs;

USERNAME                       PRIVILEGE
------------------------------ ----------------------------------------
TEST                           DROP TABLESPACE
TEST                           CREATE TABLESPACE
TEST                           ALTER TABLESPACE
TEST                           UNLIMITED TABLESPACE

SQL> alter tablespace test add datafile '/oracle/db/test02.dbf' size 20m;--添加数据文件(给表空间test)

Tablespace altered.

SQL> conn /as sysdba
Connected.
SQL> revoke alter tablespace from test;--回收权限

Revoke succeeded.

SQL> grant manage tablespace to test;--这个manage tablespace权限只能使表空间上下线,进行表空间备份

Grant succeeded.

SQL> conn test/system
Connected.
SQL> select username,privilege from user_sys_privs;

USERNAME                       PRIVILEGE
------------------------------ ----------------------------------------
TEST                           DROP TABLESPACE
TEST                           MANAGE TABLESPACE
TEST                           CREATE TABLESPACE
TEST                           UNLIMITED TABLESPACE

SQL> alter tablespace test offline;

Tablespace altered.

SQL> alter tablespace test online;

Tablespace altered.

SQL> alter tablespace add datafile '/oracle/db/test03.dbf' size 10m;
alter tablespace add datafile '/oracle/db/test03.dbf' size 10m
                 *
ERROR at line 1:
ORA-02140: invalid tablespace name


SQL> alter tablespace test add datafile '/oracle/db/test03.dbf' size 10m;
alter tablespace test add datafile '/oracle/db/test03.dbf' size 10m
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> alter tablespace test read only;
-- 使表空间只读,这种不能进行事务,可以查看v$transaction;另如果你想从read write切到
---read only(在已有事务情况下,运行这个语句就会hang在哪儿)



Tablespace altered.

SQL> alter tablespace test read write;

Tablespace altered.


SQL> conn test/system
Connected.
SQL> alter tablespace test add datafile '/oracle/db/test04.dbf' size 10m;

Tablespace altered.

SQL> alter tablespace test drop datafile /oracle/db/test04.dbf' size 10m;
alter tablespace test drop datafile /oracle/db/test04.dbf' size 10m
*
ERROR at line 1:
ORA-02236: invalid file name


SQL> alter tablespace test drop datafile '/oracle/db/test04.dbf';            

Tablespace altered.

SQL> conn /as sysdba
Connected.

SQL> alter tablespace tbs_temp_02 add tempfile '/oracle/db/temp03.dbf' size 10m;--添加临时表空间数据文件,记得用tempfile

Tablespace altered.

SQL> alter tablespace tbs_temp_02 drop tempfile '/oracle/db/temp03.dbf';--删除临时表空间的临时文件

Tablespace altered.

SQL> alter tablespace tbs_temp_02 rename to tbs_temp;---重命名临时表空间名字

Tablespace altered.

SQL> select file_name,file_id,tablespace_name from dba_temp_files where tablespace_name='TBS_TEMP';

FILE_NAME                         FILE_ID TABLESPACE
------------------------------ ---------- ----------
/oracle/product/10.2.0/db_1/db          5 TBS_TEMP
s/temp02.dbf



SQL> alter tablespace test offline;

Tablespace altered.


SQL> alter tablespace test online;

Tablespace altered.


SQL> alter tablespace test read only;

Tablespace altered.



SQL> alter tablespace test add datafile '/oracle/newtest.dbf' size 10m;
alter tablespace test add datafile '/oracle/newtest.dbf' size 10m
*
ERROR at line 1:
ORA-01641: tablespace 'TEST' is not online - cannot add data file


SQL> alter tablespace test read write;

Tablespace altered.

SQL> alter tablespace test add datafile '/oracle/newtest.dbf' size 10m;

Tablespace altered.


SQL> alter tablespace test offline;

Tablespace altered.


SQL> host                         
bash-3.00$ mv /oracle/newtest.dbf /oracle/newloc.dbf
bash-3.00$ ls -l /oracle/new*
-rw-r-----   1 ora10g   oinstall 10493952  9月 13 01:01 /oracle/newloc.dbf

/oracle/newdir:
total 0
bash-3.00$ exit
exit

SQL> alter tablespace test rename file '/oracle/newtest.dbf' to '/oracle/newloc.dbf';
alter tablespace test rename file '/oracle/newtest.dbf' to '/oracle/newloc.dbf'
                             *
ERROR at line 1:
ORA-02152: Invalid ALTER TABLESPACE ... RENAME option


SQL> alter tablespace test rename datafile '/oracle/newtest.dbf' to '/oracle/newloc.dbf';

Tablespace altered.

SQL> alter tablespace test datafile offline;

Tablespace altered.



SQL> alter tablespace test online;

Tablespace altered.


SQL> alter tablespace test datafile offline;---datafile offline和online

Tablespace altered.



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_name,tablespace_name,online_status from dba_data_files where tablespace_name='TEST';

FILE_NAME                      TABLESPACE ONLINE_  ---online_status为recover,你要recover tablespace或recover datafile
------------------------------ ---------- -------
/oracle/db/ORA10G/datafile/o1_ TEST       RECOVER
mf_test_5bphyzjl_.dbf

/oracle/db/test02.dbf          TEST       RECOVER
/oracle/newloc.dbf             TEST       RECOVER

SQL> alter tablespace test datafile online;
alter tablespace test datafile online
*
ERROR at line 1:
ORA-01113: file 14 needs media recovery
ORA-01110: data file 14: '/oracle/newloc.dbf'


SQL> recover tablespace test;
Media recovery complete.
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='TEST';

FILE_NAME                      TABLESPACE ONLINE_
------------------------------ ---------- -------
/oracle/db/ORA10G/datafile/o1_ TEST       OFFLINE
mf_test_5bphyzjl_.dbf

/oracle/db/test02.dbf          TEST       OFFLINE
/oracle/newloc.dbf             TEST       OFFLINE


SQL> alter tablespace test datafile online;

Tablespace altered.



SQL> alter tablespace test datafile online;

Tablespace altered.



SQL> alter tablespace test online;

Tablespace altered.

SQL> alter tablespace test force logging;
--令表空间强制记日志,用于flashback query
---和flashback transaction(说白了就会一堆的日志会写在oracle中)

Tablespace altered.


SQL> select tablespace_name,force_logging from dba_tablespaces where tablespace_name='TEST';

TABLESPACE FOR
---------- ---
TEST       YES

SQL> alter tablespace test no force logging;--关闭强制日志

Tablespace altered.

SQL> select tablespace_name,force_logging from dba_tablespaces where tablespace_name='TEST';

TABLESPACE FOR
---------- ---
TEST       NO

SQL> alter tablespace test offline normal;--normal是默认,会自动把sga中的数据flush到数据文件中

Tablespace altered.

SQL> alter tablespace test online;

Tablespace altered.

SQL> alter tablespace test offline temporary;
--会执行一个checkpoint,但不确保把相关数据写入数据文件,online时可能需要介质恢复

Tablespace altered.

SQL> alter tablespace test online;
alte
Tablespace altered.

SQL> alter tablespace  test offline immediate;---这个在online必须要介质恢复

Tablespace altered.

SQL> set long 99999
SQL> select dbms_metadata.get_ddl('TABLESPACE','TEST') from dual;

DBMS_METADATA.GET_DDL('TABLESPACE','TEST')
--------------------------------------------------------------------------------

  CREATE TABLESPACE "TEST" DATAFILE
  '/oracle/db/ORA10G/datafile/o1_mf_test_5bphyzjl_.dbf' SIZE 104857600,
  '/oracle/db/test02.dbf' SIZE 20971520,
  '/oracle/newloc.dbf' SIZE 10485760
  LOGGING OFFLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO


SQL> select file_name,tablespace_name,autoextensible from dba_data_files where tablespace_name='TEST';

FILE_NAME                      TABLESPACE AUT
------------------------------ ---------- ---
/oracle/db/ORA10G/datafile/o1_ TEST
mf_test_5bphyzjl_.dbf

/oracle/db/test02.dbf          TEST
/oracle/newloc.dbf             TEST

SQL> alter tablespace test autoextend on;--autoextend用于大文件表空间和字典管理表空间
alter tablespace test autoextend on
*
ERROR at line 1:
ORA-32773: operation not supported for smallfile tablespace TEST


SQL> alter database datafile '/oracle/newloc.dbf' autoextend on;
alter database datafile '/oracle/newloc.dbf' autoextend on
*
ERROR at line 1:
ORA-00376: file 14 cannot be read at this time
ORA-01110: data file 14: '/oracle/newloc.dbf'


SQL> alter tablespace test online;
alter tablespace test online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/oracle/db/ORA10G/datafile/o1_mf_test_5bphyzjl_.dbf'


SQL> recover tablespace test;
Media recovery complete.
SQL> alter tablespace test online;

Tablespace altered.

SQL> alter database datafile '/oracle/newloc.dbf' autoextend on;

Database altered.

SQL> select file_name,tablespace_name,autoextensible from dba_data_files where tablespace_name='TEST';

FILE_NAME                      TABLESPACE AUT
------------------------------ ---------- ---
/oracle/db/ORA10G/datafile/o1_ TEST       NO
mf_test_5bphyzjl_.dbf

/oracle/db/test02.dbf          TEST       NO
/oracle/newloc.dbf             TEST       YES

SQL> alter database datafile /oracle/newloc.dbf' autoextend  off;
alter database datafile /oracle/newloc.dbf' autoextend  off
                        *
ERROR at line 1:
ORA-02236: invalid file name


SQL> alter database datafile '/oracle/newloc.dbf' autoextend  off;--利用alter database使数据文件扩展

Database altered.

SQL> alter tablespace test maxsize unlimited;
alter tablespace test maxsize unlimited
                      *
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option

SQL> alter database datafile '/oracle/newloc.dbf' autoextend on maxsize unlimited;--最大大小无限,开自动扩展

Database altered.

SQL> alter tablespace test guarantee;--guarantee和noguarantee用于撤消表空间
alter tablespace test guarantee
                      *
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option


SQL> alter tablespace test resize 300m;--resize用于大文件表空间
alter tablespace test resize 300m
*
ERROR at line 1:
ORA-32773: operation not supported for smallfile tablespace TEST


SQL> create bigfile tablespace bigone datafile '/oracle/db/bigone01' size 10m;--对比测试建立大文件表空间

Tablespace created.

SQL> alter tablespace bigone resize 20m;

Tablespace altered.


Tablespace altered.

SQL> alter tablespace test read only;

Tablespace altered.

SQL> alter tablespace test begin backup;
alter tablespace test begin backup
*
ERROR at line 1:
ORA-01642: begin backup not needed for read only tablespace 'TEST'


SQL> alter tablespace test read write;

SQL> alter tablespace test begin backup;

Tablespace altered.

SQL> alter tablespace test end backup;

Tablespace altered.

SQL> alter tablespace test begin backup;

Tablespace altered.

SQL> shutdown immediate;
ORA-01149: cannot shutdown - file 5 has online backup set
ORA-01110: data file 5: '/oracle/db/ORA10G/datafile/o1_mf_test_5bphyzjl_.dbf'
SQL> alter tablespace test offline;
alter tablespace test offline
*
ERROR at line 1:
ORA-01150: cannot prevent writes - file 5 has online backup set
ORA-01110: data file 5: '/oracle/db/ORA10G/datafile/o1_mf_test_5bphyzjl_.dbf'


SQL> alter tablespace test end backup;

Tablespace altered.

SQL> alter tablespace test offline;

Tablespace altered.

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

SQL> alter tablespace trans read write;

Tablespace altered.

SQL> alter tablespace trans read only;

Tablespace altered.

SQL> alter tablespace trans drop datafile '/oracle/db/trans.dbf';
alter tablespace trans drop datafile '/oracle/db/trans.dbf'
*
ERROR at line 1:
ORA-03261: the tablespace TRANS has only one file


SQL> select count(*) from v$transaction;

  COUNT(*)
----------
         0

SQL> /        

  COUNT(*)
----------
         1

SQL> alter tablespace fs1 read only;

Tablespace altered.

SQL> alter tablespace fs1 read write;

Tablespace altered.

SQL> select count(*) from v$transaction;

  COUNT(*)
----------
         1

SQL> alter tablespace fs1 read only;
^Calter tablespace fs1 read only
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation



SQL> select count(*) from v$transaction;

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         0

SQL> alter tablespace fs1 read write;
alter tablespace fs1 read write
*
ERROR at line 1:
ORA-01646: tablespace 'FS1' is not read only - cannot make read write


---测试迁移表空间的数据文件,

SQL> create tablespace newtbs datafile '/oracle/db/newtbs01.dbf' size 10m;--建表空间

Tablespace created.

SQL> alter tablespace newtbs offline normal;--迁移前,离线

Tablespace altered.

SQL> host mv /oracle/db/newtbs01.dbf /oracle/newtbs01.dbf--os级mv

SQL> alter tablespace newtbs rename datafile '/oracle/db/newtbs01.dbf' to '/oracle/newtbs01.dbf';--数据库迁移,要更改控制文件和数据文件的头文件信息

Tablespace altered.

SQL> alter tablespace newtbs online;

Tablespace altered.

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

请登录后发表评论 登录
全部评论
提供针对oracle初学者及进阶的数据库培训,欢迎大家咨询: 微信: wisdomone 微信公众号: lovedb qq: 305076427 微博: wisdomone9

注册时间:2008-04-04

  • 博文量
    2164
  • 访问量
    11761867