Oracle常用概念及命令

由于有新数据需要入库和处理,现在需要来一波Oracle命令。之前刚刚入门,也不懂技术,现在稍微懂一点了,备份一下一些常用命令,以后再用到就不用再到处百度谷歌了,来自https://www.hsli.top

用户

创建用户

identified by *** ,用户的密码。

default tablespace *** 默认用户表空间。

temporary tablespace *** 默认临时空间。

1
2
3
create user test identified by test
default tablespace tsusertest
temporary tablespace tstemptest;

修改密码:

1
2
alter user test
identified by test;

删除用户以及跟用户关联的对象:

1
drop user test CASCADE;

授权

Oralce的特殊权限

  • 系统权限unlimited tablespace是隐含在dba, resource角色中的一个系统权限. 当用户得到dba或resource的角色时, unlimited tablespace系统权限也隐式受权给用户.

  • 系统权限unlimited tablespace不能被授予role, 可以被授予用户.

  • 系统权限unlimited tablespace不会随着resource, dba被授予role而授予给用户.

特殊角色

  • DBA角色,是授权数据库管理员的权限

  • CONNECT角色, 是授予最终用户的典型权利,最基本的 一个(CREATE SESSION)

  • RESOURCE角色,是授予开发人员的 默认有八个权限(CREATE SEQUENCE,CREATE TRIGGER,CREATE CLUSTER,CREATE PROCEDURE,CREATE TYPE,CREATE OPERATOR,CREATE TABLE,CREATE INDEXTYPE)

  • exp_full_database角色,拥有导出数据库的权限

  • imp_full_database角色,拥有导入数据库的权限

授权命令

1
grant resource to test;
1
2
3
grant select on username.tablename to user;
grant create view to user;
grant create table to user;

查询权限

以下的查询中,基本都要以DBA的角色进行查询,如果权限不够,有可能查询不出来数据。

  • 查看用户拥有那些角色
1
select * from dba_role_privs a where a.grantee='test';

1
select * from dba_sys_privs a where a.grantee='test';
  • 查看角色拥有那些权限
1
select ROLE, PRIVILEGE from role_sys_privs where role='RESOURCE'; --RESOURCE,CONNECT,DBA

1
select grantee,privilege from dba_sys_privs where grantee='RESOURCE';
  • 收回角色
1
2
revoke resource from test;
revoke select username.tablename from user;
  • 收回权限
1
revoke unlimited tablespace from test;
  • 查看Oracle的版本号
1
select * from v$version where rownum <=1;

表空间

创建表空间

创建

1
create tablespace private_db datafile '/export/DataBase/data/private_db01.dbf' size 1G;'

创建大文件表空间

1
create bigfile tablespace private_db datafile '/export/DataBase/data/private_db.dbf' size 2G;

分配给用户

1
alter user private_db default tablespace private_db;

修改数据文件大小

1
alter database datafile '/export/DataBase/data/private_db01.dbf' resize 2G;

由于涉及IO,每弄一次数据文件都超级慢啊!!!

可以自动扩展

删除表空间,数据对象和OS文件

1
drop tablespace private_db including contents and datafiles;

设置数据文件自动扩展

1
alter database datafile '/export/DataBase/data/private_db01.dbf' autoextend on next 200M maxsize unlimited;

对于大文件表空间,只有一个数据文件,也可以设置表空间自动扩张来设置数据文件自动扩展

1
alter tablespace private_db autoextend on next 500M maxsize unlimited;

查询表空间

  • 查看当前用户的缺省表空间
1
SQL>select username,default_tablespace from user_users;
  • 查看当前用户的角色
1
SQL>select * from user_role_privs;
  • 查看当前用户的系统权限和表级权限
1
2
SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;
  • 查看用户下所有的表
1
SQL>select * from user_tables;

修改表空间存储路径(Oracle中数据文件是按照表空间来区分的)

  • 查看数据文件的存储路径:
1
2
3
4
5
6
7
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oracle/oradata/yoon/system01.dbf
/u01/oracle/oradata/yoon/sysaux01.dbf
/u01/oracle/oradata/yoon/undotbs01.dbf
/u01/oracle/oradata/yoon/users01.dbf
  • 修改表空间为offline:
1
2
SQL> alter tablespace users offline;
Tablespace altered.
  • 拷贝数据文件
1
$ cp users01.dbf /u01/oracle/oradata/yoondata/
  • 修改表空间存储路径
1
2
SQL> alter tablespace users rename datafile '/u01/oracle/oradata/yoon/users01.dbf' to '/u01/oracle/oradata/yoondata/users01.dbf';
Tablespace altered.
  • 修改表空间为online
1
2
SQL> alter tablespace users online;
Tablespace altered.
  • 重新查看数据文件路径
1
2
3
4
5
6
7
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oracle/oradata/yoon/system01.dbf
/u01/oracle/oradata/yoon/sysaux01.dbf
/u01/oracle/oradata/yoon/undotbs01.dbf
/u01/oracle/oradata/yoondata/users01.dbf

  • 查看用户下所有的表
1
SQL>select * from user_tables;
  • 查看名称包含log字符的表
1
SQL>select object_name,object_id from user_objects where instr(object_name,'LOG')>0;
  • 查看某表的创建时间
1
SQL>select object_name,created from user_objects where object_name=upper('&table_name');
  • 查看某表的大小
1
SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&table_name');
  • Drop掉的表恢复

今天一不小心把库里一个500W数据量的表drop掉了,吓得半死,上网一查,有方法恢复的

查看回收站

1
2
3
SQL>show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
TEST_DROP BIN$b+XkkO1RS5K10uKo9BfmuA==$0 TABLE 2005-05-07:14:30:47

恢复

1
SQL>flashback table test_drop to before drop

  • 用dba权限的用户查看数据库都有哪些锁
1
select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time;
  • 根据sid查看具体的sql语句,如果sql不重要,可以kill
1
select sql_text from v$session a,v$sqltext_with_newlines b where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value and a.sid=&sid order by piece;

输入SID即可查询

  • kill该事务
1
alter system kill session '339,13545';

视图

创建视图

1
2
3
4
5
6
create or replace view nsrxx_limited as
select "NSRMC","NSRZT_DM","PROVINCE" from (
select nsrmc,nsrzt_dm,'河南' as province from DJ_NSRXX t1
union all
select nsrmc,nsrzt_dm,'四川' as province from DJ_NSRXX_LS_SICHUAN t2
);
如果文章有用,请随意打赏