Oracle12C使用记录

  1.

安装时oracle12C去除勾选为创建为容器数据库

  2.cmd命令

<1>.sqlplus/nolog     // --运行sqlplus命令，进入sqlplus环境，nolog参数表示不登录；

<2>.conn/as sysdba   //   --以系统管理员(sysdba)的身份连接数据库；　　　　　　　　  

3.创建表空间

CREATE   TABLESPACE tbs_rap_data

DATAFILE 'E:\Oracle12Cguanwang\table\rap97new2_space.dbf' size 500m

AUTOEXTEND ON

NEXT 200M MAXSIZE 20480M

EXTENT MANAGEMENT LOCAL;

4.执行公司oracle语句

create user rap97new2 identified by rap97new2 default tablespace tbs_rap_data temporary tablespace temp profile default;

-- Grant/Revoke role privileges

grant connect to rap97new2;

grant resource to rap97new2;

-- Grant/Revoke system privileges

grant alter session to rap97new2;

grant create any context to rap97new2;

grant create database link to rap97new2;

grant create job to rap97new2;

grant create materialized view to rap97new2;

grant create sequence to rap97new2;

grant create session to rap97new2;

grant create synonym to rap97new2;

grant create table to rap97new2;

grant create trigger to rap97new2;

grant create view to rap97new2;

grant debug connect session to rap97new2;

grant manage scheduler to rap97new2;

    grant unlimited tablespace to rap97new2;

1. 在PLsql中配置

<1> E:\app\instantclient_12_2

<2> E:\app\instantclient_12_2\oci.dll

oracle环境变量配置：

环境变量： oracle根目录   E:\app\root\product\12.2.0\dbhome_1\network\admin

NLS_LANG：SIMPLIFIED CHINESE_CHINA.ZHS16GBK

配置 plsql developer 首选项；

1. 导入公司的Oracle数据库文件

工具——导入表——下侧导入文件

1. 删除表空间DROP TABLESPACE tbs_rap_data INCLUDING CONTENTS AND DATAFILES;
2. 删除用户DROP USER rap97new2 CASCADE；

一、sys用户和system用户
Oracle安装会自动的生成sys用户和system用户
(1)、sys用户是超级用户，具有最高权限，具有sysdba角色，有create database的权限，该用户默认的密码是change_on_install
(2)、system用户是管理操作员，权限也很大。具有sysoper角色，没有create database的权限，默认的密码是manager
(3)、一般讲，对数据库维护，使用system用户登录就可以拉
注意：也就是说sys和system这两个用户最大的区别是在于有没有create database的权限。

（4）scott的默认密码是tiger

oracle卸载11g:http://jingyan.baidu.com/article/922554468d4e6b851648f4e3.html

                     Oracle 表及表空间

一个数据库可以有多个表空间，一个表空间里可以有多个表。表空间就是存多个表的物理空间；

可以指定表空间的大小位置等。

创建表空间：create tablespace ts1 datafile 'E:\tablespace\ts1.dbf' size 50M;

自动扩展大小：create tablespace ts2 datafile 'E:\tablespace\ts2.dbf' size 50M autoextend on next 10M;

设置最大空间：create tablespace ts3 datafile 'E;:\tablespace\ts3.dbf' size 50M autoextend on next 10M maxsize 1024M;

更改用户默认表空间：alter database default tablespace ts1;

表空间改名：alter tablespace ts1 rename to tss1;

删除表空间：drop tablespace ts2 including contents and datafiles;

创建表

Oracle12c添加scott用户

1.前言

在Oracle12c中，已经没有了scott这一实例用户。Oracle学习者们可以通过新建hr用户或是新建scott用户，来完成对Oracle的学习。

2.具体流程

• 登录系统用户 
  conn / as sysdba;
• 删除已有的用户(可选) 
  DROP USER scott cascade;
• 新建用户，授予权限，连接

CREATE USER scott identified by TIGER ;            

GRANT CREATE SEQUENCE to scott ;

GRANT UNLIMITED TABLESPACE to scott ;

GRANT CREATE SESSION to scott ;

GRANT CREATE SYNONYM to scott ;

GRANT ALTER SESSION to scott;

GRANT CREATE DATABASE LINK to scott;

GRANT CREATE VIEW to scott;

GRANT CREATE TABLE to scott;

ALTER USER scott DEFAULT TABLESPACE USERS;

ALTER USER scott TEMPORARY TABLESPACE TEMP;

CONNECT scott/TIGER

• 新建表格并添加数据

-- DEPT表格

CREATE TABLE DEPT

       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,

    DNAME VARCHAR2(14) ,

    LOC VARCHAR2(13) ) ;

-- EMP表格

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 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');

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-JUL-87')-85,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-JUL-87')-51,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,20);

INSERT INTO EMP VALUES

(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

-- BONUS表格

CREATE TABLE BONUS

    (

    ENAME VARCHAR2(10)  ,

    JOB VARCHAR2(9)  ,

    SAL NUMBER,

    COMM NUMBER

    ) ;

-- SALGRADE表格

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);

commit;

至此，新建scott用户完成，可以开始学习Oracle了。