创建表空间、用户、扩容、移动数据文件
创建表空间、用户、扩容、移动数据文件
1、创建新的表空间;
2、创建用户;
3、通过修改文件大小与增加文件的方式扩容表空间;
4、人为移动数据文件,检验创建表的效果;
1、创建新的表空间; SQL> create tablespace test_data 2 logging 3 datafile '/opt/oracle/oradata/bisal/test_data_01.dbf' 4 size 10M 5 autoextend on 6 next 10m maxsize 2000m 7 extent management local; Tablespace created. [oracle@liu bisal]$ ls -rlht total 1.5G -rw-r----- 1 oracle oinstall 11M Sep 27 22:56 test_data_01.dbf 2、创建用户; SQL> create user test_data identified by test_data 2 default tablespace test_data 3 temporary tablespace tempts1; User created. SQL> select username from dba_users; USERNAME ------------------------------ TEST_DATA 3、通过修改文件大小与增加文件的方式扩容表空间; SQL> alter database datafile '/opt/oracle/oradata/bisal/test_data_01.dbf' resize 25m; Database altered. [oracle@liu bisal]$ ls -rlht total 1.5G -rw-r----- 1 oracle oinstall 26M Sep 27 23:01 test_data_01.dbf SQL> alter tablespace test_data add datafile '/opt/oracle/oradata/bisal/test_data_02.dbf' size 3m; Tablespace altered. [oracle@liu bisal]$ ls -rlht total 1.5G -rw-r----- 1 oracle oinstall 26M Sep 27 23:01 test_data_01.dbf -rw-r----- 1 oracle oinstall 3.1M Sep 27 23:03 test_data_02.dbf 4、人为移动数据文件,检验创建表的效果; mv test_data_01.dbf ../. mv test_data_02.dbf ../. 用该用户登录之前需要赋予相关权限: CREATE SESSION权限: [oracle@liu Desktop]$ sqlplus test_data/test_data SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 27 23:40:23 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. ERROR: ORA-01045: user TEST_DATA lacks CREATE SESSION privilege; logon denied Enter user-name: ERROR: ORA-01017: invalid username/password; logon denied Enter user-name: ERROR: ORA-01017: invalid username/password; logon denied SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus CREATE TABLE权限: SQL> create table test (x int); create table test (x int) * ERROR at line 1: ORA-01031: insufficient privileges SQL> grant create table to test_data; Grant succeeded. 增加用户在表空间的使用量限制: SQL> create table test (x int); create table test (x int) * ERROR at line 1: ORA-01950: no privileges on tablespace 'TEST_DATA' 创建时错误: SQL> create table test(x int); create table test(x int) * ERROR at line 1: ORA-01116: error in opening database file 7 ORA-01110: data file 7: '/opt/oracle/oradata/bisal/test_data_02.dbf' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3 SQL> create table test(x int); Table created.