Oracle常用命令

﹏ヽ暗。殇╰゛Y 2022-07-12 13:19 328阅读 0赞

Oracle常用功能相关命令，涉及：oracle启动关闭，表空间&限额，系统权限，创建临时表空间、表空间，用户，授权，压缩表空间。

-———————— oracle启动和关闭————————

1，查找listener.ora

#find $ORACLE_BASE -name listener.ora

2，启动oracle数据库

[oracle@localhost ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 20-SEP-2016 20:14:20

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Starting /opt/oracle/product/11.2.0.1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /opt/oracle/product/11.2.0.1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
-———————————-
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 20-SEP-2016 20:14:20
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/oracle/product/11.2.0.1/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
The listener supports no services
The command completed successfully

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 20 20:37:12 2016

Copyright (c) 1982, 2009, Oracle. All rights reserved.

SQL> connect / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 6680915968 bytes
Fixed Size 2213936 bytes
Variable Size 3623880656 bytes
Database Buffers 3019898880 bytes
Redo Buffers 34922496 bytes
Database mounted.
Database opened.

立即关闭oracle数据库
SQL> shutdown immediate

注意：

很多时候,使用 shutdown immediate命令之后会出现假死的显现,到网上查了一些资料,说是可能是因为有大事务要回滚,具体原因不清楚,给出的建议就是在shudown immediate之前用alert system checkpoint命令先保存一下检查点.试了一下,貌似真了可以.

-——————- oracle 登录 ———————————

oracle登陆

登录sys账户：

sqlplus / as sysdba;

sqlplus sys as sysdba;

sqlplus sys/123456 as sysdba

登录普通账户testuser：

sqlplus testuser/123456;

-—————————————— 表空间配合和UNLIMITED TABLESPACE权限 ———————————————
对于一个新建的用户，如果没有分配给unlimited tablespace系统权限的用户，必须先给他们指定限额，之后他们才能在表空间中创建对象，限额有两种：

A、以兆字节或者千字节为单位的特定值

B、无限制的

限额是指定表空间中允许的空间容量，默认的情况下，用户在任何表空间中都是没有限额的，可以使用一下三个选项来为用户提供表空间限额：

A、无限制的：允许用户最大限度的使用表空间中的可用空间

B、值：用户可以使用的表空间，以千字节或者兆字节为单位。这并不能保证会为用户保留该空间。因此此值可能大于或小于当前可用表空间

C、UNLIMITED TABLESPACE系统权限：此系统权限会覆盖所有的单个表空间限额，并向用户提供所有表空间（包括SYSTEM和SYSAUX）的无限制限额（注：授予resource角色的时候也会授予此权限）

注意：千万不要为用户提供system或sysaux表空间的限额。通常，只有sys和system用户才能在system或sysaux表空间中创建对象。对于分配的临时表空间或临时还原表空间则不需要限额。

如果需要为一个用户指定一个限额，有下面两种方法：

1、在创建用户的时候指定限额：

CREATE USER ABC IDENTIFIED BY ABC

DEFAULT TABLESPACE TEST

TEMPORARY TABLESPACE TEMP

QUOTA 3M ON TEST;

2、在创建用户完成之后对用户限额进行指定：

CREATE USER BCD IDENTIFIED BY BCD

DEFAULT TABLESPACE TEST;
ALTER USER BCD QUOTA 3M ON TEST;

更改用户的表空间限额

全局：

grant unlimited tablespace to abc;

针对某个表空间：

alter user abc quota unlimited on test;

回收：

revoke unlimited tablespace from abc;

alter user abc quota 0 on test;

-—————————- 系统权限DBA、RESOURCE、CONNECT ——————————————————

DBA：拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。

RESOURCE：拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
CONNECT：拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

对于普通用户：授予connect, resource权限。
grant connect, resource to user;

对于DBA管理用户：授予connect, resource, dba权限。

grant connect, resource, dba to user;

-—————————- 创建临时表空间|表空间|用户|授权 ——————————————————
创建oracle普通账户

#sqlplus /nolog

SQL> conn / as sysdba;
SQL>create user username identified by password
SQL> grant dba to username;
SQL> conn username/password
SQL> select * from user_sys_privs;

1：创建临时表空间

SQL>create temporary tablespace user_temp 

  1. tempfile '/opt/oracle/user\_temp\_001.dbf' 
  3. size 50m  
  5. autoextend on  
  7. next 50m maxsize 20480m  
  9. extent management local;

2：创建数据表空间30G
SQL>create tablespace user_data
logging
datafile ‘/opt/oracle/user_data_001.dbf’
size 50m
autoextend on
next 50m maxsize 30720m
extent management local;

第3步：创建用户并指定表空间

SQL>create user username identified by password 

  1. default tablespace user\_data  
  3. temporary tablespace user\_temp;

第4步：给用户授予权限

SQL> grant dba to username;

或

SQL>grant connect,resource,dba to username;

第5布：查看用户权限

$conn username/password

SQL>select * from user_sys_privs;

-———————————————其他常用操作———————————————

查看用户所在的表空间

查看当前用户的缺省表空间：

$sqlplus testuser/123456

SQL>select username,default_tablespace from user_users;

查看表属于哪个表空间

$sqlplus testuser/123456

SQL>select table_name,tablespace_name from user_tables; //查看所有表的

SQL>select table_name,tablespace_name from user_tables where table_name=upper(‘&table_name’); //查看指定表的

查询所有临时表空间：

select f.file#,t.ts#,f.name “File”,t.name “Tablespace” from v$tempfile f,V$tablespace t where f.ts# = t.ts#;

查看所有表空间总大小、已使用大小、剩余大小

SQL>select a.tablespace_name,total “Total(M)”,free “Free(M)”,total-free “Used(M)”,round(((total-free)/total)*100,2) “Used(%)” from
( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
group by tablespace_name) a,
( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name;

查看物理表空间(增加表空间的数据文件)总大小、已使用大小、剩余大小

先了解一下两种增加表空间大小的方式：

1：格式化数据文件初始大小并设置自增长到最大值

create tablespace user_data datafile ‘/opt/oracle/data.dbf’ size 50m autoextend on next 50m maxsize 30720m;

2：格式化数据文件初始大小不设置自增长，当然也就没有最大值

alter tablespace user_data add datafile ‘/opt/oracle/data_02.dbf’ size 50m;

统计命令：

SQL>SELECT A.TABLESPACE_NAME,B.TOTAL/1024/1024||’M’,(B.TOTAL-A.USE)/1024/1024||’M’ FREE FROM
(
select TABLESPACE_NAME,sum(bytes) as USE from dba_segments
where tablespace_name NOT IN (‘USERS’,’SYSAUX’,’UNDOTBS1’,’SYSTEM’,’UNDOTBS2’)
GROUP BY TABLESPACE_NAME
) A ,
(
WITH TABLESPACE_TOTAL AS
(
SELECT tablespace_name,sum(MAXBYTES) TOTAL FROM DBA_DATA_FILES T
WHERE T.TABLESPACE_NAME NOT IN (‘USERS’,’SYSAUX’,’UNDOTBS1’,’SYSTEM’,’UNDOTBS2’)
AND T.AUTOEXTENSIBLE=’YES’ group by tablespace_name
UNION ALL
SELECT tablespace_name,sum(bytes) TOTAL FROM DBA_DATA_FILES T
WHERE T.TABLESPACE_NAME NOT IN (‘USERS’,’SYSAUX’,’UNDOTBS1’,’SYSTEM’,’UNDOTBS2’)
AND T.AUTOEXTENSIBLE=’NO’ group by tablespace_name
)
SELECT TABLESPACE_NAME,SUM(TOTAL) TOTAL FROM TABLESPACE_TOTAL GROUP BY TABLESPACE_NAME
) B
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME;

查看表空间物理文件的名称及大小

select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;

压缩表空间

SQL>alter database datafile ‘/opt/oracle/data.dbf’ resize 5G; //首先尝试压缩为5G，原来总大小为15G

SQL>alter database datafile ‘/opt/oracle/data.dbf’ resize 1500m; //再次尝试压缩为1.5G

查看当前用户的角色
SQL>select * from user_role_privs;

查看当前用户的系统权限和表级权限

SQL>select * from user_sys_privs;

SQL>select * from user_tab_privs;

查看用户下所有的表

SQL>select * from user_tables;

查看某表的大小（按照提示输入要查看的表名）

SQL>select sum(bytes)/(1024*1024) as “size(M)” from user_segments where segment_name=upper(‘&table_name’);

查看某表的创建时间（按照提示输入要查看的表名）

SQL>select object_name,created from user_objects where object_name=upper(‘&table_name’);

Oracle表名、列名、约束名的长度限制查询

查询用户所有的表
包括: TABLE_NAME, TABLESPACE_NAME, NUM_ROWS 等详细信息
select * from USER_TABLES;

查询用户所有表的列
包括: TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PERCISION, DATA_SCALE, CHAR_COL_DECL_LENGTH, CHAR_LENGTH, CHAR_USED 等详细信息
select * from USER_TAB_COLUMNS;

查询用户所有的约束
select * from USER_CONSTRAINTS;

查询所有的表
包括: OWNER, TABLE_NAME, TABLESPACE_NAME, NUM_ROWS 等详细信息
select * from ALL_TABLES;

查询所有的列
select * from ALL_TAB_COLUMNS;

查询所有的约束
select * from ALL_CONSTRAINTS;

查看数据库系统的表名长度的限制
select DATA_LENGTH as MAX_TABLE_NAME from ALL_TAB_COLUMNS where table_name = ‘USER_TABLES’ and column_name =’TABLE_NAME’;
Oracle 11g 默认是30，Oracle 19c默认是128

查看数据库系统的列名长度的限制
select DATA_LENGTH as MAX_COLUMN_NAME from ALL_TAB_COLUMNS where table_name = ‘USER_TAB_COLUMNS’ and column_name =’COLUMN_NAME’;
Oracle 11g 默认是30，Oracle 19c默认是128

查看数据库系统的约束名称的长度限制
select DATA_LENGTH as MAX_CONST_NAME from ALL_TAB_COLUMNS where table_name = ‘USER_CONSTRAINTS’ and column_name =’CONSTRAINT_NAME’;
Oracle 11g 默认是30，Oracle 19c默认是128

1.查看所有用户：
select * from dba_users;
select * from all_users;
select * from user_users;

2.查看用户、角色的系统权限(直接赋值给用户或角色的系统权限)
select * from dba_sys_privs;
查看当前用户所拥有的系统权限
select * from user_sys_privs;

3.查看角色(只能查看登陆用户拥有的角色)所包含的权限
sql>select * from role_sys_privs;

4.查看用户对象权限
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;

5.查看所有角色
select * from dba_roles;

6.查看用户或角色所拥有的角色
select * from dba_role_privs;
select * from user_role_privs;

7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from V$PWFILE_USERS

8.SqlPlus中查看一个用户所拥有权限
SQL>select * from dba_sys_privs where grantee=’username’; 其中的username即用户名要大写才行。
eg:
SQL>select * from dba_sys_privs where grantee=’TOM’;

9.查看哪些用户有DBA权限
select * from dba_role_privs where granted_role=’DBA’;

10.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from V$PWFILE_USERS;
SQL> select * from V$PWFILE_USERS;

USERNAME SYSDBA SYSOPER SYSASM
SYS TRUE TRUE FALSE
MYDBAUSER TRUE FALSE FALSE

表授权
Oracle把某用户USERA下的表权限授权给另一用户USERB

把USERA读写权限 授权给USERB
select ‘Grant all on ‘||table_name||’to USERB ;’ from all_tables where owner = upper(‘USERA’);
把当前登陆的用户USERA表查询权限授权给USERB用户
select ‘GRANT SELECT ON ‘||table_name||’ to USERB;’ from user_tables;

Oracle表授权给其他用户

GRANT： 赋予一个用户，一个组或所有用户访问权限
GRANT 语法：GRANT privilege [, …] ON object [, …] TO { PUBLIC | GROUP group | username }

privilege：可能的权限有：
SELECT 访问声明的表/视图的所有列/字段
INSERT 向声明的表中插入所有列字段
UPDATE 更新声明的所有列/字段
DELETE 从声明的表中删除所有行
RULE 在表/视图上定义规则 （参见 CREATE RULE 语句）
ALL 赋予所有权限
object：赋予权限的对象名．可能的对象是：
table 表
view 视图
sequence 序列
index 索引
PUBLIC：代表是所有用户的简写
GROUP group：将要赋予权限的组 group 。目前的版本中，组必须是用下面方法显式创建的
username：将要赋予权限的用户名．PUBLIC 是代表所有用户的简写

--授权
grant select, insert, update, delete on USER01.TB_TEST to frank;

--回收
revoke select, insert, update, delete on USER01.TB_TEST from frank;

--truncate授权
grant drop any table to frank;

--truncate回收
revoke drop any table from frank;

如果要授权某个用户下面的所有表给 frank，建议先用下面的SQL，查询出授权拼接的SQL执行一下：

select ‘GRANT SELECT ON ‘ || table_name || ‘ to frank;’ from user_tables;

查找Oracle执行某张表的操作操作记录、主机、用户

--Step1 查找表的操作记录
select * from v$sqlarea a where a.SQL_TEXT like ‘%TABLE_TEST01%’;
--也可以自己加对应的条件筛选（例如筛选：delete from）
select * from v$sqlarea a where a.SQL_TEXT like ‘%TABLE_TEST01%’ and a.SQL_TEXT like ‘delete from%’;

--Step2 从上面的记录中找到对应的关键操作语句对应的SQL_ID，执行下面查询
select * from v$sqltext a,v$sqlarea b where a.SQL_ID=b.SQL_ID and b.SQL_ID in(‘4cmdargynhwwn’) order by b.LAST_ACTIVE_TIME desc;

--Step3 从上面的记录中找到最新的SQL操作记录，然后找到用户名和主机
select * from sys.v_$session l,sys.v_$sql s where s.SQL_ID=’4cmdargynhwwn’ and l.USERNAME is not null;

发表评论

表情：
评论列表 （有 0 条评论，328人围观）

还没有评论，来说两句吧...

相关阅读