Oracle的简单应用

创建表空间：

CREATE BIGFILE TABLESPACE TS_TEST
DATAFILE 'D:\Test\TS_TEST_01.DBF'
SIZE 500M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED 
LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

 

创建逻辑目录：

create or replace directory LGDIR
  as 'D:\OracleTrain\TestData';

 

创建用户：

CREATE USER TEST PROFILE DEFAULT IDENTIFIED BY TEST DEFAULT TABLESPACE TS_TEST ACCOUNT UNLOCK;

赋予用户系统权限或角色：

GRANT ALTER ANY TABLE TO TEST;
GRANT CREATE ANY DIRECTORY TO TEST;
GRANT CREATE ANY INDEX TO TEST;
GRANT CREATE ANY TABLE TO TEST;
GRANT CREATE ANY VIEW TO TEST;
GRANT CREATE ANY PROCEDURE TO TEST;
GRANT CREATE ANY TYPE TO TEST;
GRANT DROP ANY TABLE TO TEST;
GRANT INSERT ANY TABLE TO TEST;
GRANT SELECT ANY TABLE TO TEST;
GRANT UNLIMITED TABLESPACE TO TEST;
GRANT CREATE SESSION TO TEST;
GRANT EXECUTE ANY TYPE TO TEST;
GRANT UPDATE ANY TABLE TO TEST;
GRANT EXECUTE ANY PROCEDURE TO TEST;
GRANT RESOURCE TO TEST;

GRANT DBA TO TEST;

创建新类型和自定义类型：

CREATE OR REPLACE TYPE TEST."T_TEST" AS VARRAY(256) OF VARCHAR2(2);

CREATE OR REPLACE TYPE TEST."O_G3_POS" AS OBJECT (
 POS_X  NUMBER(10,0),  /* X */
 POS_Y  NUMBER(10,0),  /* Y */
 POS_Z  NUMBER(10,0)  /* Z */
);

创建基本表：

create table TEST.EMP
(
  EMPNO    NUMBER(4) not null,
  ENAME    VARCHAR2(10),
  JOB      VARCHAR2(9),
  MGR      NUMBER(4),
  HIREDATE DATE,
  SAL      NUMBER(7,2),
  COMM     NUMBER(7,2),
  DEPTNO   NUMBER(2)
)
tablespace TS_TEST;

创建索引、主键、外键、约束等：

CREATE INDEX IDX_AREA_NAME ON TEST.EMP(EMPNO) TABLESPACE TS_TEST;

===========================================================================================================================

*由部门培训资料整理