1.用SYS用户登录PL/SQL
SYS@XE as SYSDBA
2.创建表空间
create tablespace Mars datafile 'F:\oracle\Mars.dbf' size 500M
autoextend on next 100M maxsize unlimited logging
extent management local autoallocate
segment space management auto;
3.创建用户
create user Mars identified by welcome1
DEFAULT tablespace Mars;
grant connect to Mars;
grant resource to Mars;
-- Grant/Revoke system privileges
grant create any sequence to Mars;
grant create any table to Mars;
grant create any view to Mars;
grant create synonym to Mars;
grant debug any procedure to Mars;
grant debug connect session to Mars;
grant delete any table to Mars;
4.创建表
-- Create table
create table Mars.ORGANIZATIONS
(
organization_id NUMBER not null,
organization_code VARCHAR2(100),
organization_name VARCHAR2(100),
parent_organization_id NUMBER,
organization_level VARCHAR2(100),
organization_type VARCHAR2(100),
organization_status VARCHAR2(100),
order_num NUMBER,
organization_desc VARCHAR2(4000),
organization_seq VARCHAR2(4000),
organization_address VARCHAR2(500),
object_version_number NUMBER default 1 not null,
creation_date DATE default sysdate not null,
created_by VARCHAR2(100) not null,
last_updated_by VARCHAR2(100) not null,
last_update_date DATE default sysdate not NULL,
attribute1 VARCHAR2(240),
attribute2 VARCHAR2(240),
attribute3 VARCHAR2(240),
attribute4 VARCHAR2(240),
attribute5 VARCHAR2(240),
attribute6 VARCHAR2(240),
attribute7 VARCHAR2(240),
attribute8 VARCHAR2(240),
attribute9 VARCHAR2(240)
)
tablespace Mars
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
5.创建sequence
-- Create sequence
create sequence Mars.Organizations_S
minvalue 1
maxvalue 9999999999999999999999999999
start with 10001
increment by 1
cache 20;
6.创建数据
INSERT INTO MARS.ORGANIZATIONS
(ORGANIZATION_ID,
ORGANIZATION_CODE,
ORGANIZATION_NAME,
-- PARENT_ORGANIZATION_ID,
ORGANIZATION_TYPE,
ORGANIZATION_STATUS,
ORDER_NUM,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES
(MARS.ORGANIZATIONS_S.NEXTVAL,
MARS.ORGANIZATIONS_S.NEXTVAL,
'XXX公司',
'Company',
'Enable',
1,
SYSDATE,
'MARS',
'MARS',
SYSDATE);
INSERT INTO MARS.ORGANIZATIONS
(ORGANIZATION_ID,
ORGANIZATION_CODE,
ORGANIZATION_NAME,
PARENT_ORGANIZATION_ID,
ORGANIZATION_TYPE,
ORGANIZATION_STATUS,
ORDER_NUM,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES
(MARS.ORGANIZATIONS_S.NEXTVAL,
MARS.ORGANIZATIONS_S.NEXTVAL,
'IT部',
10001,
'Department',
'Enable',
2,
SYSDATE,
'MARS',
'MARS',
SYSDATE);
INSERT INTO MARS.ORGANIZATIONS
(ORGANIZATION_ID,
ORGANIZATION_CODE,
ORGANIZATION_NAME,
PARENT_ORGANIZATION_ID,
ORGANIZATION_TYPE,
ORGANIZATION_STATUS,
ORDER_NUM,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES
(MARS.ORGANIZATIONS_S.NEXTVAL,
MARS.ORGANIZATIONS_S.NEXTVAL,
'财务部',
10001,
'Department',
'Enable',
2,
SYSDATE,
'MARS',
'MARS',
SYSDATE);
INSERT INTO MARS.ORGANIZATIONS
(ORGANIZATION_ID,
ORGANIZATION_CODE,
ORGANIZATION_NAME,
PARENT_ORGANIZATION_ID,
ORGANIZATION_TYPE,
ORGANIZATION_STATUS,
ORDER_NUM,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES
(MARS.ORGANIZATIONS_S.NEXTVAL,
MARS.ORGANIZATIONS_S.NEXTVAL,
'SOA部门',
10002,
'Department',
'Enable',
3,
SYSDATE,
'MARS',
'MARS',
SYSDATE);