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;