oracle10g_alter table_测试3

最新推荐文章于 2021-04-05 18:43:13 发布
最新推荐文章于 2021-04-05 18:43:13 发布
阅读量127 收藏
点赞数
文章标签： 数据库

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-614509/

clg10051
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
如何正确的删除表空间数据文件
coco3600的博客
09-06 1796
如何正确的删除表空间数据文件 应该使用如下的命令删除： ALTER TABLESPACE TEST DROP DATAFILE 3; ...
oracle变更表空间,Oracle - 修改表空间 (ALTER TABLESPACE)
weixin_36054159的博客
04-03 1834
Use the ALTER TABLESPACE statement to alter an existing tablespace or one or more of its datafiles or tempfiles.See Also: for information on creating a tablespacePrerequisitesIf you have ALTER TABLESP...
oracle 10g__alter table shrink space compact
07-24 108
压缩或缩小表的空间时,先要 alter table v4 enable row movement; alter table v4 shrink space compact; shrink 语句的相当注意限制: R...
oracle11g新特性 Interval Partition
jolly10的专栏
09-11 2390
  分区(Partition)一直是Oracle数据库引以为傲的一项技术，正是分区的存在让Oracle高效的处理海量数据成为可能，在Oracle 11g中，分区技术在易用性和可扩展性上再次得到了增强。在10g的Oracle版本中，要对分区表做调整，尤其是对RANGE分区添加新的分区都需要DBA手动定期添加，或都使用存储过程进行管理。在11G的版本中的Interval Partition不再需要DB...
ora-01149错误
09-25 693
SQL> shutdown immediate   ORA-01149: cannot shutdown - file 1 has online backup set ORA-01110: data file 1: '/u01/app/oracle/oradata/system01.dbf' 上网查了一下，这个错误是由于联机日志备份，没有备份完成。在我的操作中，主要是没有结束备份。  即...
Oracle10g_李兴华代码收藏.rar
10-10
2. **SQL语法与查询**：Oracle 10g支持标准的SQL语法，包括DML（数据操纵语言）如INSERT、UPDATE、DELETE，以及DDL（数据定义语言）如CREATE TABLE、ALTER TABLE等。此外，可能还有复杂的JOIN操作、子查询、集合操作...
Oracle10g_RMAN备份恢复案例
09-29
根据提供的文件信息，本文将详细解释Oracle 10g RMAN（Recovery Manager）备份与恢复的相关知识点。主要内容包括RMAN的基本概念、Oracle 10g与9i在备份策略上的区别、具体备份操作步骤以及如何进行恢复操作。 ### ...
lab2_SQL.rar_oracle_oracle 10g lab2_sql lab第二关
09-24
在本实验中，我们将深入探讨Oracle 10g数据库管理系统中的SQL（结构化查询语言）应用，这在数据库管理、数据分析以及系统开发中都扮演着核心角色。"lab2_SQL.rar"是一个压缩文件，包含了“oracle_10g_lab2”和“sql_...
Oracle10g_PL_SQL_编程
12-21
掌握Oracle 10g支持的数据类型，如数值型（NUMBER、BINARY_INTEGER、PLS_INTEGER）、字符型（VARCHAR2、CHAR、LONG）、日期型（DATE）、布尔型（BOOLEAN）以及复合数据类型如记录（RECORD）和集合（TABLE）。...
Oracle10G系统简介.ppt
最新发布
11-13
在 Oracle 10G 中，DDL 包括创建、修改和删除表、视图、索引、存储过程等数据库对象的语句，如 `CREATE TABLE`、`ALTER TABLE` 和 `DROP TABLE`。 2. **数据库操纵/查询（DML）**： DML 语句用于插入、更新、删除...
oracle 表空间删除文件,如何正确的删除表空间数据文件
weixin_33921444的博客
04-03 8847
如何正确的删除表空间数据文件应该使用如下的命令删除：ALTER TABLESPACE TEST DROP DATAFILE3;参考mos文章：Unable to Drop a Datafile From the Tablespace Using Alter Tablespace Command (文档ID 1050261.1)违反下列任何一个条件，该datafile均不能被drop：1)必须为空，...
12C ORA-错误汇总3 ORA-01500 to ORA-02098
热门推荐
badman250的专栏
03-02 3万+
ORA-01500 to ORA-02098 4 ORA-01500: failure in getting date/time Cause: During create database or alter tablespace, there was a failure in getting the date and time. Action: Contact your customer
表空间正在热备份时关闭实例重启报错的重现和解决
还不算晕的专栏
11-11 2599
最近一个客户的库在OPEN时报错需要恢复，发现原因为当时一个表空间正在热备份-->ALTER TABLESPACE TEST1 BEGIN BACKUP;  然后实例异常关闭(可能为ABORT或KILL SMON等进程，这里据说为存储直接关闭导致)，然后重启时遇到此错误。 在ORACLE 10.2.0.1及11.2.0.4版本中重现了此错误，在这两个版本中同样的情况但是报错信息不太一样，具体情况
oracle统一命名文件,oracle 各类文件的重命名及移动
weixin_36364044的博客
04-05 199
最近在作一个银行的数据库迁移及升级的case,因为该系统为统计跑批，原系统配置已经没法知足跑批性能要求，故要购买新的主机采用ssd及hd两种混合型的存储，中间过程型的表对应的表空间放入ssd对应目录，历史结果表放入hd对应目录。因此须要在迁移过程当中对数据文件进行移动和重命名。如下是从网上搜索到的一篇文章，比较细致的作了测试！记录如下数据库同时这次，从oracle 11.2.0.4 迁移到12.2...
ORA-02142: missing or invalid ALTER TABLESPACE option
byebyegov5qq
01-30 2332
如果一个tablespace的segment space management的方式为manual 那么在执行alter table tb_name shrink时会出现此提示;
12C ORA 错误汇总4 ORA 02140 to ORA 04099
ugghhj的博客
11-08 3137
12C ORA 错误汇总4 ORA 02140 to ORA 04099
Oracle数据库精讲与疑难解析
07-17 724
【书 名】 Oracle数据库精讲与疑难解析 《Oracle数据库精讲与疑难解析》一书在“2008年度最佳IT技术图书与原创作者评选”活动中，被评为“2008年度优秀IT技术图书”。【作 者】 赵振平...
ORA-01653: 表 xxxx 无法通过 8192 (在表空间 USERS 中) 扩展
波子汽水
07-18 3万+
SQL> SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS FROM DBA_TABLESPACES T,DBA_DATA_FILES D WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME ORDER BY TABLESPACE_NAME,FILE_N
oracle备份失败导致无法关闭数据库
清风弄影
10-23 521
使用nbu备份失败后，无法正常关闭数据库，用alter database end backup解决 SQL> shutdown immediate; ORA-01149: cannot shutdown - file 1 has online backup set ORA-01110: data file 1: 'S:\ORACLE\PRD\SAPDATA1\SYSTEM_1\SYSTEM...
Oracle10G分布式数据库系统概述
在Oracle 10G中，DDL语句用于构建数据库的逻辑结构，如CREATE TABLE、ALTER TABLE和DROP TABLE等。 2. **数据库操纵/查询**： Oracle 10G支持丰富的数据库操纵语言（DML），包括INSERT、UPDATE、DELETE以及查询...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值