表空间 用户 表

一、创建表空间：

 create tablespace MW_APP DATAFILE  'D:/oracle/product/10.2.0/oradata/orcl/MW_APP.ORA' SIZE 2000M

    LOGGING
    DATAFILE 'D:/oracle/product/10.2.0/oradata/orcl/MW_APP.ORA' SIZE 6000M
    AUTOEXTEND
    ON NEXT  100M MAXSIZE UNLIMITED

    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT  AUTO ;
    二、创建用户
create user mw_app identified by mw_app;

--修改用户的默认表空间

ALTER USER MW_APP DEFAULT TABLESPACE MW_APP;

--授权
GRANT DBA TO MW_APP WITH ADMIN OPTION;
GRANT CREATE TABLE TO MW_APP WITH ADMIN OPTION ;

三、查询表空间和用户及用户的权限

select * from dba_tablespaces
SELECT * FROM DBA_USERS WHERE USERNAME='MW_APP' OR USERNAME LIKE 'SCO%';
SELECT * FROM ALL_USERS WHERE USERNAME='MWS_SYS';
SELECT * FROM USER_USERS WHERE USERNAME='MWS_SYS'
select * from dba_sys_privs where grantee LIke 'MW%';

四、创建表
CREATE TABLE MW_APP.A(NUM INT ,COMPANY VARCHAR2(20),DAT DATE ) TABLESPACE "MW_APP";
SELECT * FROM DBA_TABLES WHERE TABLE_NAME = 'A' AND OWNER = 'MW_APP';
SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'TABLE'AND OBJECT_NAME='A' AND OWNER='MW_APP'

CREATE TABLE MW_APP.B(STU_ID INT ,PLACE VARCHAR2(50),BIRTH DATE)TABLESPACE "MW_APP";
INSERT INTO MW_APP.B(STU_ID,PLACE,BIRTH) VALUES(1,'河北石家庄',to_date('19800918','yyyy-mm-dd'))
commit;

select * from mw_app.a;

drop table mw_app.a;
delete mw_app.b