(1)我们知道当使用sqlplus / as
sysdba登录到Oracle时,启动的实例为Orcl,这个时候如何使用oms登录呢?
如果实在Windows下:SET ORACLE_NAME =oms
如果在LINUX下:export ORACLE_NAME
=oms
之后shutdown实例再startup ,重启实例。使用sqlplus / as
sysdba登录查看
select instance_name ,status from v$instance;
可以看到 instance_name 为oms,状态为open了。
(2)使用PLSQL
developer登录,当然实现需要在NET配置中配置好oms相关属性,或者直接在tnsname.ora中复制更改。之后打开PLSQL
developer才能看到我设置的Linuxoms.但是登录时发现无监听程序。这个时候我们知道明明是使用netca设置过监听程序的,为什么会没有呢?思前想后可能是因为服务未启动,我们使用lsnrctl
start启动。果然可以登录了。我就是天才。
数据库dba_data_files>表空间tablespace_name->文件存储位置file_name
(3)新项目需要做,这个时候要在orcl数据库中建立一个新的表空间存储数据。如何创建呢?create
tablespace bank_tbs datafile
'D:\APP\KIMHOME\ORADATA\ORCL\bank_data02.dbf' size 5M autoextend on
maxsize 10G
为表空间创建需要的数据库用户
create user bankuser
identified by Koko3562 default tablespace
bank_tbs
用户创建之后很多权限是没有的,几乎没什么用只是个空壳,这是要给他放权。使用Grant。
grant connect,resource to bankuser
现在就可以使用plsql developer登录了。
登录后创建表:
1、部门表。
create table dept
(
deptno number(2) constraint pk_dept primary key,
dname varchar2(14),
loc varchar2(13)
);
插入数据:
insert into dept values(10,'accounting','new york')
insert into dept values(20,'research','dallas')
insert into dept values(30,'sales','chicago')
insert into dept values(40,'operations','boston')
commit;
2、员工表
create table emp
(
empno number(4) constraint pk_emp primary key,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2) constraint fk_deptno references dept
);
插入数据:
insert into emp values
(7369,'smith','clerk',7902,to_date('17-12-1980','dd-mm-yyyy'),800,null,20)
insert into emp values
(7499,'allen','salesman',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30)
insert into emp values
(7521,'ward','salesman',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30)
insert into emp values
(7566,'jones','manager',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,null,20)
insert into emp values
(7654,'martin','salesman',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30)
insert into emp values
(7698,'blake','manager',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,null,30)
insert into emp values
(7782,'clark','manager',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,null,10)
insert into emp values
(7788,'scott','analyst',7566,to_date('13-7-87','dd-mm-yyyy'),3000,null,20)
insert into emp values
(7839,'king','president',null,to_date('17-11-1981','dd-mm-yyyy'),5000,null,10)
insert into emp values
(7844,'turner','salesman',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30)
insert into emp values
(7876,'adams','clerk',7788,to_date('13-7-1987','dd-mm-yyyy'),1100,null,20)
insert into emp values
(7900,'james','clerk',7698,to_date('3-12-1981','dd-mm-yyyy'),950,null,30)
insert into emp values
(7902,'ford','analyst',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,null,10)
insert into emp values
(7934,'miller','clerk',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,null,10)
3、奖金表
create table bonus
(
ename varchar2(10),
job varchar2(9),
sal number,
comm number
)
4、薪水等级表
create table salgrade
(
grade number,
losal number,
hisal number
)
插入数据:
insert into salgrade values(1,700,1200)
insert into salgrade values(2,1201,1400)
insert into salgrade values(3,1401,2000)
insert into salgrade values(4,2001,3000)
insert into salgrade values(5,3001,9999)
我们可以通过select table_name,tablespace_name from
user_tables查看用户表的相关信息。