CREATE SMALLFILE TABLESPACE "LINUXSPACE" DATAFILE '/u/app/oracle/oradata/testorcl/LINUXDATA' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ----创建永久表空间和数据文件
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES
7 LINUXSPACE YES YES YES
7 rows selected.
SQL> create user oratest identified by oratest default tablespace linuxspace temporary tablespace temp; －－－－－创建用户给予表空间
User created.
SQL> select username,default_tablespace,temporary_tablespace from user_users;
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
-------------------- ------------------------------ ------------------------------
ORATEST TLM52 TEMP
SQL> grant connect,resource,dba to oratest; －－－－－预定义角色
Grant succeeded.
SQL> connect oratest/oratest ----login
Connected.
SQL> show user --------show
USER is "ORATEST"
SQL> create schema s-t authorization oratest; ------给用户创建模式名
SQL> create table student(sno char(5) not null, ---------创建student关系
2 sname char(6),
3 s*** char(2),
4 sage varchar(4),
5 sdept char(4),
6 Constraint StudentPK Primary Key (Sno),
7 Constraint SageCK Check (Sage > 0 AND Sage <150));
Table created.
SQL> create table course ( cno char(4) not null, -----------创建课程关系
2 cname varchar(20),
3 cpno char(1),
4 ccredit int default 0,
5 constraint corusepk primary key (cno));
SQL> create table sc ( sno char(5) not null, ------------选课关系
2 cno char(4) not null,
3 grade int default 0,
4 constraint scpk primary key(sno,cno),
5 constraint studentfk foreign key (sno) references student(sno),
6 constraint coursefk foreign key (cno) references course(cno),
7 Constraint GradeCK Check (Grade >= 0 AND Grade <= 100));
SQL> commit;
SQL> create table xxx as select * from student; ------- 创建xxx
Table created.
SQL> Alter Table student Add age int constraint CheckAge check (age>0 and age<150); --------修改追加age 属性
SQL> drop table xxx;
