Oracle基础知识总结

最新推荐文章于 2024-09-15 21:58:44 发布

bob12358

最新推荐文章于 2024-09-15 21:58:44 发布

阅读量243

点赞数

分类专栏： Oracle 文章标签： oracle

本文链接：https://blog.csdn.net/bob12358/article/details/46940869

版权

Oracle 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

设置输出格式

显示

Set linesize 200;
set pagesize 20;
column deptno format a5 修改deptno列的格式为5个字节

执行计划

explain plan for select * from noidx where id=131;

select * from table(dbms_xplan.display)

触发器

2014年11月12日19:19:12

事务锁

事务提交 
commit disconn ddl操作

事务隔离层：
READ COMMITTED  这是Oracle默认的隔离层

SERIALIZABLE 序列化，事务与事务完全隔开
READ ONLY:其他事务提交后当前事务仍无法查看 且无法执行ddl操作

锁
(表级)共享锁 (S – Share)
LOCK TABLE EMP IN SHARE MODE;
其他用户只能看，不能修改，修改会处于等待状态,当前事务无限制
可能加所有共享锁。

(表级)排他锁 (X – Exclusive)
其他用户只能看，不能修改，不能加其他锁。在scott中操作：lock table salgrade in exclusive mode;update salgrade set losal = 10 where grade=1;在sys中执行操作：update salgrade set losal = 202 where grade=2;这个操作一直在等待，一直等到scott将salgrade中的锁释放：commit,rollback;

创建用户

alter user chao default tablespace users
temporary tablespace temp;
alter user chao quota 50m on users;

授权
grant create session,create table to chao;
grant select dictionary to chao

取消授权
revoke select any table from chao;

数据字典前缀的区别

DBA是只有DBA权限的用户才能查询，内容包含了系统全部；
ALL是普通用户可以查询，内容包含了该用户所能访问的部分；
User是普通用户可以查询，内容包含了该用户是所有者的部分；
V$是数据字典动态视图，显示系统当前的状态。它的值在随时都可能发生变化。

配置文件

create profile mypro limit
connect_time 2          -- 连接时间不超过2min
idle_time 1             -- 使用系统资源不超过1min 超过1min连接断开数据
failed_login_attempts 2     -- 密码策略 登录失败不能超过两次 会被锁住
;
alter user chao profile mypro;
alter system set resource_limit = true; 
drop profile mypro cascade;

select 'GRANT SELECT ON '||table_name||' to user;' from user_tables;

创建一个角色并赋予其一个模式中所有表的select权限

create role select_all_scott_tab;
declare
    CURSOR c_tabname is select table_name from dba_tables where owner='SCOTT';
    v_tabname dba_tables.table_name%TYPE;
        sqlstr varchar2(200);
   begin
        open c_tabname;
        loop
            fetch c_tabname into v_tabname;
            exit when c_tabname%NOTFOUND;
            sqlstr :='grant select on scott.'||v_tabname||' to select_all_scott_tab';
            dbms_output.put_line(v_tabname);
        execute immediate sqlstr;
        end loop;
        close c_tabname;
 end;

 grant select_all_scott_tab to chao;

2014年11月26日19:38:36

表空间管理

创建表空间

create tablespace mytbs
datafile 'd:\data、myfile.dbf' size 100m;

查询表空间
select tablespace_name from dba_tablespace;
select tablespace_name,file_name from  dba_data_files;
查询表空间状态：select tablespace_name,status from dba_tablespaces;     

调整表空间数据文件
{
    增加数据文件
    alter tablespace mytbs add datafile file_spezification
    重新命名（移动）数据文件
    alter tablespace mytbs offline;alter tablespace mytbs rename filename to filename;
    重新设置数据文件的大小
    alter database datafile resize number m;
    删除表空间与文件:
    drop tablespace mytbs including contents and datafiles;
}

修改表空间状态
alter tablespace mytbs online;
alter tablespace mytbs read only;
要成功操作：必须满足下列3个条件:
表空间处于ONLINE,表空间不能包含任何回退段,表空间不能处于联机备份。
alter tablespace mytbs read write;
alter tablespace mytbs offline;

创建临时表空间
create temporary tablespace tmp
tempfile 'd:\data\temp.dbf' size 100m;

创建撤销表空间

create undo tablespace undotbs
datafile 'd:\data\temp.dbf' size 100m;

2014年12月3日19:08:44

备份

完全脱机备份是对数据库的全部数据文件、日志文件和控制文件，
又称为冷备份，是为了保护现场
V$DATAFILE

V $LOGFILE V$ CONTROLFILE

在数据库关闭的情况下进行物理备份。当oracle运行在日志非归档模式下，只能进行脱机备份。

shutdown normal 必须在所有用户断开连接后才能关闭数据库

缺点：必须关闭数据库

例程：数据库进程和服务进程调入内存与数据库没关启动时没有为任意一个数据库提供服务

startup nomount
开启例程读取参数文件不装载数据库

startup mount
开启例程打开控制文件

oracle的启动过程
启动例程
startup nomount

读取参数文件

SQL> show parameter spfile;

NAME TYPE VALUE

spfile string /oracle/app/oracle/product/11.
2/db_1/dbs/spfiledevdb.ora
SQL> show parameter db_name;

NAME TYPE VALUE

db_name string devdb

SQL> show parameter control_files;

NAME TYPE VALUE

control_files string /oracle/oradata/devdb/control0
1.ctl, /oracle/app/oracle/flas
h_recovery_area/devdb/control0
2.ctl

装载数据库
alter database mount;

打开数据库
alter database open;

只有是open状态普通用户才能访问数据库

备份日志文件

查询日志成员
select group#,member from v$logfile;

增加日志成员
SQL> alter database add logfile member ‘/oracle/devdb/redo1.log’ to group 1;

备份数据文件

查看归档模式
archive log list
select log_mode from v$database;

alter tablespace users begin backup

归档模式
写满的文件拷贝到新的归档文件

不归档
默认为不归档日志写满后会被新的内容覆盖掉

在更改过程中有可能被覆盖掉

SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instance

shutdown immediate; 立即关闭数据库

修改为归档模式
SQL> alter database archivelog;

Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 30
Next log sequence to archive 32
Current log sequence 32
SQL>

联机备份

查询表空间
select tablespace_name from dba_tablespaces;

首先确保日志的模式是否是归档模式（Archive Mode）

Alter tablespace tbs_name begin backup;

Select file_name from dba_data_files where tablespace_name = ‘tbs_name’

查明了数据文件后就可以复制

Alter tablespace tbs_name end backup

删除掉文件users01.dbf

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: ‘/oracle/oradata/devdb/users01.dbf’

再把users01.dbf粘贴进来
提示文件需要恢复
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: ‘/oracle/oradata/devdb/users01.dbf’
SQL> alter database open;

这时执行恢复数据库
SQL> recover database;
Media recovery complete.

优点：
当多个表空间出现问题的时候只需要一条命令

缺点：
数据库在mount的时候用户无法访问数据库

单个表空间数据文件备份

SQL> col name format a40
SQL> select file#,name from v$datafile;

 FILE# NAME

 1 /oracle/oradata/devdb/system01.dbf
 2 /oracle/oradata/devdb/sysaux01.dbf
 3 /oracle/oradata/devdb/undotbs01.dbf
 4 /oracle/oradata/devdb/users01.dbf
 5 /oracle/oradata/devdb/example01.dbf

SQL> alter database datafile 4 offline; //令4号数据文件脱机

SQL> alter database open;

Database altered.

SQL> select table_name from user_tables;

TABLE_NAME

DEPT
EMP
BONUS
SALGRADE
TEMP_TABLE
TEMP
HAHA

已选择7行。

SQL> select * from dept;
select * from dept
*
第 1 行出现错误:
ORA-00376: 此时无法读取文件 4
ORA-01110: 数据文件 4: ‘/oracle/oradata/devdb/users01.dbf’

Q：为什么能查询到表名？
A：系统表空间是不能脱机的

SQL> recover datafile 4;
Media recovery complete.

这时访问还是没有数据，因为数据文件还没online
*
第 1 行出现错误:
ORA-00376: 此时无法读取文件 4
ORA-01110: 数据文件 4: ‘/oracle/oradata/devdb/users01.dbf’

SQL> alter database datafile 4 online;
Database altered.

结果：
SQL> select * from scott.salgrade;

no rows selected

SQL> select * from scott.emp;

no rows selected

可以发现已经可以访问该数据文件了，而且也恢复到了之前的状态：表中的数据已被删除。

控制文件备份

首先查询一下控制文件

SQL> select name from v$controlfile;

NAME

/oracle/oradata/devdb/control01.ctl
/oracle/app/oracle/flash_recovery_area/d
evdb/control02.ctl

这时其中一个删除控制文件，然后关闭数据库
发现无法关闭
SQL> shutdown immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/oracle/oradata/devdb/control01.ctl’

SQL> startup
ORACLE instance started.

Total System Global Area 768294912 bytes
Fixed Size 2217304 bytes
Variable Size 562039464 bytes
Database Buffers 201326592 bytes
Redo Buffers 2711552 bytes
ORA-00205: error in identifying control file, check alert log for more info

然后找到
SQL> show parameter control_files;

NAME TYPE VALUE

control_files string /oracle/oradata/devdb/control0
1.ctl, /oracle/app/oracle/flas
h_recovery_area/devdb/control0
2.ctl

这里有另外一个控制文件，我们可以把这个控制文件复制到原来的/oracle/oradata/devdb/然后重命名

[oracle@localhost devdb]$ mv control02.ctl control01.ctl

现在重新打开数据库，感觉整个世界都好了!
SQL> alter database mount;

Database altered.

PS:一般控制文件是不需要备份的，因为oracle会给我们备份多个数据库文件。

表类型

创建目录
SQL> create directory mydir
2 as ‘/oracle/haha’
3 ;

在目录上创建外部表
create table mytb1
(sno char(4),sbane varchar2(8),xk varchar2(20),score number(3))
organization external
(type oracle_loader
default directory mydir
access parameters(fields terminated by ‘:’)
location(‘test.txt’)
)
reject limit unlimited

局限性：

外部表对DBMS是只读的，对操作系统是可读写的

分区表

创建
create table part_tb
(empno number(3),ename varchar2(20),salary number(6,2),deptno number(2))
partition by range(salary)
(partition p1 values less than (2000) tablespace users,
partition p2 values less than (maxvalue) tablespace sysaux)
;

查询
select * from part_tb partition(p1);

分区表是根据指定的列值来分配数据的位置

数据库、用户的分区表的数据字典：
dba_part_tables, all_part_tables, user_part_tables

数据库、用户分区表的分区情况的数据字典：
dba_tab_partitions, all_tab_partitions,user_tab_partitions