Oracle新建用户和表

登陆 > sqlplus / as sysdba

建立表空间 create tablespace test datafile ‘C:\oracle\product\10.1.0\oradata\daxi\test.dbf’ size 100M;

创建用户 > create user test identified by test password expire default tablespace test quota unlimited on test temporary tablespace temp quota unlimited on temp;

授权 > grant connect, resource to test;

执行过程

SQL> create tablespace test 2 datafile 'C:\oracle\product\10.1.0\oradata\daxi\test.dbf' size 100M; Tablespace created.

SQL> create user test identified by test 2 password expire 3 default tablespace test quota unlimited on test 4 temporary tablespace temp quota unlimited on temp; User created.

SQL> grant connect, resource to test; Grant succeeded.

SQL> conn test Enter password: ERROR: ORA-28001: the password has expired Changing password for test New password: Retype new password: Password changed Connected.

SQL> show user USER is "TEST"

SQL> create table stu(id number, name varchar2(15)); Table created.

SQL> desc stu Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER NAME VARCHAR2(15)

SQL> alter table stu add college varchar2(20); Table altered.

SQL> desc stu Name Null? Type ----------------------------------------- -------- ------------------------- ID NUMBER NAME VARCHAR2(15) COLLEGE VARCHAR2(20) SQL> alter table stu modify id number not null primary key; Table altered. SQL> desc stu Name Null? Type ----------------------------------------- -------- ------------------------- ID NOT NULL NUMBER NAME VARCHAR2(15) COLLEGE VARCHAR2(20)

SQL> insert into stu values(02, 'Daxi', 'HUST'); 1 row created.

SQL> insert into stu values(01, 'Jim', 'MIT'); 1 row created. SQL> insert into stu values(03, 'Hary', 'Cambridge'); 1 row created.

SQL> select * from stu order by id; ID NAME COLLEGE ---------- --------------- -------------------- 1 Jim MIT 2 Daxi HUST 3 Hary Cambridge SQL> delete from stu where id=1; 1 row deleted.

SQL> select * from stu order by id; ID NAME COLLEGE ---------- --------------- -------------------- 2 Daxi HUST 3 Hary Cambridge

删除表空间 > drop tablespace test including contents and datafiles;

删除用户 > drop user test;