Oracle

数据库体系结构

数据库逻辑存储结构
重做日志文件
　　一个数据库至少包含两个重做日志文件组。
　　每一个重做日志文件成员对应一个物理文件。
　　.LOG结尾
数据块
　　数据块是数据库储存空间的最小数据储存空间。
表空间
　　SYSTEM .DBF结尾
　　TEM 临时表空间

建立表

   create table article (
   article_id number(16) PRIMARY KEY,
   title varchar2(100),
   user_id number(16),
   detail varchar2(100),
   digest varchar2(100),
   create_time number(16),
   status number(2)
   );

修改表

添加列：ALTER TABLE article ADD (NUM INTEGER,CLICKTIME DATE)
插入数据：INSERT INTO  article VALUES(1,1,'XX',1,'XX','XX',123123123,1)
修改列的类型及长度：ALTER TABLE article MODIFY title varchar2(66);
修改列名：ALTER TABLE article RENAME COLUMN title TO yy;
删除列：ALTER TABLE article DROP COLUMN title;

删除表与查看表

DROP TABLE article;
DESCRIBE article;

查询表

内连接（一个以上表连接：select 字段 from 表1，表2 where 连接条件：

创建序列(可以用于主键自增)

    -- 创建序列
    create sequence 
    minvalue 1 --最小值
    nomaxvalue --不设置最大值
    start with 1 --从1开始计数
    increment by 1 --每次加1个
    nocycle --一直累加，不循环
    nocache;
    用作自增：INSERT INTO article VALUES (AUTO_ARTICLEID.NEXTVAL, 'xx', 1, 1, 'xx',1,1);

Oracle 通过触发器实现ID自增

    1 创建测试表
    CREATE TABLE test (
    id number(4) NOT NULL PRIMARY KEY,
    age varchar2(10) DEFAULT NULL
    );
    2 创建序列 
    create sequence AUTO_test
    minvalue 1 --最小值
    nomaxvalue --不设置最大值
    start with 1 --从1开始计数
    increment by 1 --每次加1个
    nocycle --一直累加，不循环
    nocache;
    3 创建触发器
    create or replace trigger auto_insert_test_id
    before insert on test for each row
    begin
    select AUTO_test.nextval
    into :new.id
    from dual;
    end; 
    4 插入测试数据
    INSERT INTO test(age) VALUES (103);  
    5 查询测试结果
    select * from test t;

触发器与函数

    --建立存储过程-函数
    create or replace function get_function(test_ID number)
     return number is
      FunctionResult number;
    begin
      SELECT AGE INTO FunctionResult FROM TESTJIAWEN WHERE id=test_ID;
      return(FunctionResult);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.put_line(0);
        when others then
          DBMS_OUTPUT.put_line(sqlcode||'---'||sqlerrm);

    end get_function;

    --调用函数
    declare
        v_age number;    
    begin
        v_age:=get_function(2);
        DBMS_OUTPUT.put_line(v_age);
    end;

建立存储过程

--建立存储过程-函数
create or replace function get_function(test_ID number)
 return number is
  FunctionResult number;
begin
  SELECT AGE INTO FunctionResult FROM TESTJIAWEN WHERE id=test_ID;
  return(FunctionResult);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.put_line(0);
    when others then
      DBMS_OUTPUT.put_line(sqlcode||'---'||sqlerrm);

end get_function;

使用存储过程

--调用函数
declare
    v_age number;    
begin
    v_age:=get_function(2);
    DBMS_OUTPUT.put_line(v_age);
end;

对象：procedure：过程，function：函数
执行过程：execute｜【call】过程名【参数值】

游标

1.声明游标 cursor
declare cursor 游标名【参数列表 id number】 is select *from cur where name= id；

2.打开游标 open
open 游标名【值列表】

3.读取数据/检索游标 fetch
fetch 游标名 into 变量列表；
4.关闭游标 close
close 【游标名】

游标属性

    1.%sopen 判断游标是否被打开 【true/flase】
    2.%found与%nofount 判断当前游标是否有效 【true/flase】
    3.%rowcount 返回当前游标的下标

游标 for 循坏

    ````
    for 记录【变量】名 in 游标名 
    loop
        循坏体
    endloop；

    for 记录【变量】名 in select 【查询】
    loop 
        循坏体
    endloop；
    —for 不需要声明，不需要关闭&fetch  cxv游标
    ````

Linux

修改ip 远程连接使用
vi /etc/hosts
2.切换用户同时切换环境
su - oracle
3.启动监听
lsnrctl start

sql plus

显示当前登入用户

SHOW USER
登入角色（sys/system）

用户名： sys as sysdba 口令：自己安装时设置的
系统权限分类
- DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
- RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
- CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。
- 对于普通用户：授予connect, resource权限。
- 对于DBA管理用户：授予connect，resource, dba权限。
create user 用户名 identified by 密码 default tablespace 表空间；
切换用户连接

connect 账号
查看当前的数据库文件位置

select name from v$datafile;
查看表空间

SELECT tablespace_name 表空间, sum(blocks * 8192 / 1000000) 剩余空间M FROM dba_free_space GROUP BY tablespace_name;

练习

向 ORCL数据库的 USERS表空间添加一个大小为10MB的数据文件users02.dbf，数据文件路径与其他数据文件一致；

alter tablespace users add datafile 'C:\APP\ORAC\ORADATA\ORCL\user02.dbf' size 10m;
向 ORCL数据库的临时表空间TEMP添加一个大小为10MB的临时数据文件Temp02.dbf，路径同其他数据文件一致；

alter tablespace TEMP add tempfile 'C:\APP\ORAC\ORADATA\ORCL\user02.dbf' size 10m;
向 ORCL数据库的 USERS表空间中添加一个可以自动扩展的数据文件user03.dbf，大小为5MB，每次扩展1MB，最大容量为100MB；

alter tablespace USERS add DATAFILE 'C:\APP\ORAC\ORADATA\ORCL\USER03.dbf' size 5m REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 100M;
取消 ORCL数据库数据文件user03.dbf的自动扩展；

alter database datafile 'C:\APP\ORAC\ORADATA\ORCL\USER03.DBF' autoextend off;
查询 ORCL数据库当前所有的数据文件的详细信息；

select * from dba_data_files;
备份 ORCL数据库的控制文件，备份文件名和路径为： d:\control.bkp；(选做)

ALTER DATABASE BACKUP CONTROLFILE TO 'D:\control.bkp';
查询 ORCL数据库当前所有控制文件信息；

SELECT*FROM V$controlfile
向 ORCL数据库添加一个重做日志文件组（组号为5），包含一个成员文件d:\redo05a.log,大小为4MB ；

alter database orcl add logfile 'd:\redo05a.log' size 4m reuse;
向 ORCL数据库的重做日志文件组5中添加一个成员文件，名称为redo05b.log(D盘下)；

alter database orcl add logfile member 'd:\redo05a.log' to group 5;
查询 ORCL数据库中所有重做日志文件组的状态；

select group#,status from v$log;
查询 ORCL数据库中所有重做日志文件成员的状态；

select group#,status from v$logfile;
删除 ORCL数据库的重做日志文件组5中的成员文件redo05b.log(D盘下)；

alter database drop logfile member 'd:\redo05b.log';
删除 ORCL数据库的重做日志文件组5；

alter database drop logfile group 5;
查看 ORCL数据库是否处于归档模式；

select log_mode from v$database;
将 ORCL数据库设置为归档模式；

关闭数据库：shutdown immediate;启动到：mount；改归档：alter database archivelog;用户可用：alter database open;
对 ORCL数据库进行5次日志切换，查看归档日志信息。（此步选做）

alter system switch log file

select name,sequence#,fist_change#,next_change#,completion_time from v$archived_log;

练习3

为 ORCL 数据库创建一个名为 BOOKTBS1 的永久性表空间，数据文件为'd:\bt01.dbf' ，大小为100M，区采用自动扩展方式（即自动分配），段采用自动管理方式；

create tablespace BOOKTBS1 datafile 'd:\bt01.dbf' size 100m extent management local autoallocate segment space management auto;
为ORCL数据库创建一个名为BOOKTBS2的永久性表空间，数据文件为'd:\bt02.dbf'，大小为100M，区采用定制分配，每次分配大小（即每个区间）为1MB，段采用手动管理方式。

create tablespace BOOKTBS2 datafile 'd:\bt02.dbf'size 100m extent management local uniform size 1m segment space management manual;
为 ORCL 数据库创建一个临时表空间 TEMP02，数据文件为'd:\tp02.dbf'，大小为15M；

create temporary tablespace TEMP02 TEMPFILE 'd:\TP02.dbf' SIZE 15M;
为 ORCL 数据库创建一个名为 UNDO02 的撤销表空间，数据文件为'd:\un02.dbf' ，大小10M；

create UNDO tablespace UNDO02 DATAFILE 'd:\un02.dbf' SIZE 10M;
为 ORCL 数据库的表空间 BOOKTBS1 添加一个大小为 20M 的数据文件'd:\bt03.dbf'，以改变该表空间的大小；

alter tablespace BOOKTBS1 add datafile 'd:\bt03.dbf' size 20m;

创建一个名为 test 的表，存储于 BOOKTBS1 表空间中，向表中插入一条记录；

    id number(6),
    name varchar2(10),
    age varchar2(60),
    sex varchar2(20)
    ) tablespace BOOKTBS1;
    INSERT INTO test vaules(1,'王大锤','67','男');```

将 ORCL 数据库的 BOOKTBS1 表空间设置为脱机状态，测试该表空间是否可以使用；
```
     SELECT*FROM test;```
```
将 ORCL 数据库的 BOOKTBS1 表空间设置为联机状态，测试该表空间是否可以使用；
```
    DESC TEST;```
```
将 ORCL 数据库的 BOOKTBS1 表空间设置为只读状态，测试该表空间是否可以进行数据写入操作；
```
   INSERT INTO TEST VALUES(1,’1’,’1’,’1’);```
```
将 ORCL 数据库的 BOOKTBS1 表空间设置为读写状态，测试该表空间是否可以进行数据读写操作；
```
   INSERT INTO TEST VALUES(2,’1’,’1’,’1’);```
```
将 ORCL 数据库的 BOOKTBS1 设置为数据库默认表空间，将临时表空间temp02 设置为数据库的默认临时表空间；（选做）
```
   ALTER TABLESPACE DEAFULT TEMPORARY TABLESPACE temp02;```
```
分别备份 ORCL 数据库的 USERS 和 BOOKTBS1两个表空间；（选做）

-------------------
查询 ORCL 数据库控制文件中的表空间信息；

Select*from V$tablespace;
查询 ORCL 数据库所有表空间及其数据文件信息；

Select*from dba_tablespaces;
删除 ORCL 数据库 BOOKTBS2 表空间及其所有内容，同时删除操作系统上的数据文件。

drop tablespace BOOKTBS1 including contents and datafiles cascade constraints;

练习7

创建一个名为 Tom 的用户，采用口令认证方式，口令为 Tom，默认表空间为 USERS表空间，临时表空间为 TEMP，在 USERS 表空间上配额为 10M，在 BOOKTBS1 表空间上的配额为 50M。

CREATE USER Tom IDENTIFIED BY Tom DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 10M ON USERS QUOTA 50 ON BOOKTBS1 ;
创建一个名为Joan的用户，采用口令认证方式，口令为Joan，默认表空间为BOOKTBS2表空间（实验3中题目2已创建），默认临时表空间为TEMP，在USERS表空间上配额为10MB，在BOOKTBS2表空间上的配额为20MB。该用户的初始状态为锁定状态。

create user Joan identified by Joan default tablespace booktbs2 temporary tablespace temp quota 10m on users quota 20m on booktbs2 account lock;
为方便数据库中用户的登录，为 ORCL 数据库中所有用户授予 CREATE SESSION系统权限。

GRANT CREATE SESSION TO PUBLIC
分别使用 Tom 用户和 Joan 用户登录 ORCL 数据库，测试是否成功。
```
    CONN Joan/Joan@ORCL```
```
为 Joan 用户帐户解锁，并重新进行登录。

ALTER USER Joan ACCOUNT UNLOCK
Tom 用户和 Joan 用户登录成功后，分别查询 Tom 表、Joan 表中的数据。
```
 SELECT*FROM Tom
 CONN Joan/Joan@ORCL
 SELECT*FROM Joan```
```

为 Tom 用户授予 CREATE TABLE、 CREATE VIEW 系统权限，并可以进行权限传递；将图书销售系统中的各个表的SELECT、UPDATE、DELETE、INSERT 对象权限授予 Tom用户，也具有传递性。

    GRANT SELECT,UPDATE,DELETE,INSERT ON bs.PUBLISHERS TO Tom WITH GRANT OPTION;
    GRANT SELECT,UPDATE,DELETE,INSERT ON bs.BOOKS TO Tom WITH GRANT OPTION;
    GRANT SELECT,UPDATE,DELETE,INSERT ON bs.ORDERS TO Tom WITH GRANT OPTION;
    GRANT SELECT,UPDATE,DELETE,INSERT ON bs.ORDERITEM TO Tom WITH GRANT OPTION;
    GRANT SELECT,UPDATE,DELETE,INSERT ON bs.PROMOTION TO Tom WITH GRANT OPTION;```

Tom 用户将图书销售系统中的 customers 表、 publishers 表、 books 表的查询权限以及CREATE VIEW、 CREATE TABLE 的系统权限授予 Joan 用户。
```
    GRANT SELECT ON bs.PUBLISHERS TO Joan;
    GRANT SELECT ON bs.BOOKS TO Joan;
    GRANT CREATE TABLE,CRETAE VIEW TO Joan;```
```
利用 Joan 用户登录 ORCL 数据库，查询 customers 表、 publishers 表、 books 表中的数据。创建一个包含出版社及其出版的图书信息的视图publisher_book。
```
    SELECT*FROM bs.PUBLISHERS;
    SELECT*FROM bs.BOOKS;
    CREATE VIEW PUBLISHER_VIEW AS SELECT NAME,CONTACT,PHONE,FROM bs.PUBLISHERS;```
```
Tom 用户回收其授予 Joan 用户的 CREATE VIEW 的系统权限。

REVOKE CREATE VIEW FROM Joan;
Tom 用户回收其授予 Joan 用户的在 customers 表上的 SELECT 权限。

REVOKE SELECT ON bs.CUSTOMERS FROM Joan;
利用 system用户登录ORCL数据库，回收Tom用户所有具有的 CREATE TABLE系统权限以及在 customers 表、 publishers 表、 books 表上 SELECT 权限。
```
REVOKE CREATE TABLE FROM Tom;
REVOKE SELECT ON bs.CUSTOMERS FROM Tom;
REVOKE SELECT ON bs.PUBLISHERS FROM Tom;
REVOKE SELECT ON bs.BOOKS FROM Tom;```
```

分别查询 Tom 用户、Joan 用户所具有的对象权限和系统权限详细信息。

    select * from user_sys_privs;
    conn Joan/Joan@orcl
    select * from user_sys_privs;```

创建一个角色 bs_role，将 ORCL 数据库中 books 表的所有对象权限以及对customers 表、 publisher 表、orders 表的 SELECT 权限授予该角色。

    grant select,update,delete,insert on bs.books to bs_role;
    grant select on bs.customers to bs_role;
    grant select on bs.publisher to bs_role;
    grant select on bs.orders to bs_role;```

将 bs_role 角色授予 Joan 用户，将 CREATE SESSION、 RESOURCE、 bs_role 角色授予 Tom 用户。
```
    grant create session,resourece,bs_role to Tom;```
```
创建一个 bs_profile1 的概要文件，限定该用户的最长会话时间为 30 分钟，如果连续10 分钟空闲，则结束会话。同时，限定其口令有效期为 20 天，连续登录 2 次失败后将锁定账户，10 天后自动解锁。

create profile bs_profile1 limit connect_time 30 idle_time 10 password_life_time 20 failed_login_attempts 2 password_lock_time 10;
创建一个概要文件 bs_profile2，要求每个用户的最多会话数为 3 个，最长的连接时间为 60 分钟，最大空闲时间为 20 分钟，每个会话占用 CPU 的最大时间为 10 秒；用户最多尝试登录次数为 3 次，登录失败后账户锁定日期为 7 天。
```
    SESSIONS_PER_USER 3  CONNECT_TIME 60 
    IDLE_TIME 20 CPU_PER_CALL 10000 
    FAILED_LOGIN_ATTEMPTS 3 
    PASSWORD_LOCK_TIME 7;```
```
将概要文件bs_profile1指定给Tom用户，将概要文件bs_profile2指定给Joan用户。
```
    alter user Joan profile bs_profile2;```
```
利用Tom用户登录ORCL数据库，连续两次输入错误口令，查看账户状态；利用Joan用户登录ORCL数据库，测试最多可以启动多少个会话。
```
    alter system set resource_limit=true --开启用户多个会话
    conn Tom/2@orcl;
    conn Tom/2@orcl;
    conn Tom/2@orcl;```
```

其他

SQL plus 修改显示列宽
col name for a50
1.无法访问动态性能表, 禁用此会话的自动统计信息您可以在首选项菜单中禁用统计信

    grant select on v_$sesstat to 你的用户名;
    grant select on v_$statname to 你的用户名; ```

posted @ 2022-10-08 14:52 dayuya 阅读(528) 评论(1) 收藏举报

刷新页面返回顶部

tjwsaereee