Oracle常用命令

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  

    tempfile '/opt/oracle/user_temp_001.dbf' 

    size 50m  

    autoextend on  

    next 50m maxsize 20480m  

    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  

    default tablespace user_data  

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

-- 查询表的 object_id, data_object_id
SELECT object_name,object_type,object_id,data_object_id FROM user_objects WHERE object_name LIKE '%NCLOB_%' AND object_type='TABLE';

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;

索引相关操作（创建索引、修改索引、删除索引、查询索引）

CREATE INDEX TEST.DDL_TEST01_IDX_INFO ON TEST.DDL_TEST01 (INFO); -- 创建索引
ALTER  INDEX TEST.DDL_TEST01_IDX_INFO RENAME TO DDL_TEST01_IDX_INFO_NEW; -- 索引重命名
ALTER  INDEX TEST.DDL_TEST01_IDX_INFO_NEW REBUILD ONLINE; -- 重建索引
DROP INDEX TEST.DDL_TEST01_IDX_INFO_NEW; -- 删除索引

-- 查询 index
SELECT OWNER,INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS FROM DBA_INDEXES WHERE OWNER='TEST' AND TABLE_OWNER='TEST' AND TABLE_NAME='DDL_TEST01';

查询所有的表
包括: 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;

查看日志文件位置：
SQL> show parameter dump_dest;

NAME                       TYPE       VALUE
------------------------   ---------  --------------------------------------------------
background_dump_dest       string     /opt/oracle/product/12.1.0.2.0/dbhome_1/rdbms/log
core_dump_dest             string     /opt/oracle/diag/rdbms/orcl/orcl/cdump
user_dump_dest             string     /opt/oracle/product/12.1.0.2.0/dbhome_1/rdbms/log
SQL> 

查看日志错误信息：
$ cd /opt/oracle/diag/rdbms/orcl/orcl/trace

查看诊断日志个数，如果太多，可以清理一些历史日志：
$ ls -l |wc -l
$ find -mtime +5 -delete

按照修改时间现有顺序排序查看文件：
$ ls -lc |more
total 68773712
-rw-r----- 1 oracle oinstall 59459072593 Feb 10 18:17 alert_orcl.log
-rw-r----- 1 oracle oinstall  3108790502 Feb 10 18:17 orcl_arc0_11857.trc
-rw-r----- 1 oracle oinstall   365137634 Feb 10 18:17 orcl_arc0_11857.trm
-rw-r----- 1 oracle oinstall  3101743845 Feb 10 18:17 orcl_arc1_11859.trc
-rw-r----- 1 oracle oinstall   364900969 Feb 10 18:17 orcl_arc1_11859.trm
-rw-r----- 1 oracle oinstall   104817125 Feb 10 18:16 orcl_arc2_11861.trc
-rw-r----- 1 oracle oinstall     6526300 Feb 10 18:16 orcl_arc2_11861.trm

查看告警日志：
$ tail -50f alert_orcl.log
 

其他相关日志文件位置查询命令：

查询 trace file 路径
SQL> select name, value from v$diag_info where name like '%Default%';
NAME                   VALUE
--------------------------------------------------------------------------------
Default Trace File     /opt/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_23843.trc

或者
SQL> select name, value from v$diag_info where name like '%Trace%';
NAME                   VALUE
--------------------------------------------------------------------------------
Diag Trace             /opt/oracle/diag/rdbms/orcl/orcl/trace
Default Trace File     /opt/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_23843.trc

查询 user_dump_dest 路径 && 查询 background_dump_dest 路径:
SQL> show parameter user_dump_dest;
NAME              TYPE       VALUE
------------------------------------------------------------------------------
user_dump_dest    string     /opt/oracle/product/12.1.0.2.0/dbhome_1/rdbms/log

SQL> show parameter background_dump_dest;
NAME                   TYPE      VALUE
----------------------------------------------------------------------------------
background_dump_dest   string    /opt/oracle/product/12.1.0.2.0/dbhome_1/rdbms/log

或者
SQL> select name, value from v$parameter where name like '%dump%';  
NAME                   VALUE
------------------------------------------------------------------------
shadow_core_dump       partial
background_core_dump   partial
background_dump_dest   /opt/oracle/product/12.1.0.2.0/dbhome_1/rdbms/log

NAME                   VALUE
------------------------------------------------------------------------
user_dump_dest         /opt/oracle/product/12.1.0.2.0/dbhome_1/rdbms/log
core_dump_dest         /opt/oracle/diag/rdbms/orcl/orcl/cdump
max_dump_file_size     unlimited

-- 查看表空间的物理文件的名称及大小
set linesize 120;
column tablespace_name format a20;
column file_name format a64;
select tablespace_name,file_id,file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name; 

TABLESPACE_NAME         FILE_ID FILE_NAME                                                        TOTAL_SPACE
-------------------- ---------- ---------------------------------------------------------------- -----------
SYSAUX                        3 /opt/oracle/oradata/orcl/sysaux01.dbf                                   1410
SYSTEM                        1 /opt/oracle/oradata/orcl/system01.dbf                                    830
UNDOTBS1                      4 /opt/oracle/oradata/orcl/undotbs01.dbf                                 16655
USERS                         6 /opt/oracle/oradata/orcl/users01.dbf                                     145
USER_DATA                     2 /opt/oracle/oradata/my_data02.dbf                                      32704
USER_DATA                     5 /opt/oracle/oradata/my_data01.dbf                                      30720

-- 统计某表的大小(按照提示输入要查看的表名)
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&table_name');
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name='TABLE_TEST01';

-- 查看用户的所有（指定表空间的）的表的大小、并排序显示（用于定位哪些表数据量大、占用空间大）
-- tablespace_name='xxx' 用来指定过滤的表空间、可以根据用户对应的表空间进行过滤和排序显示

set linesize 120;
column tablespace_name format a20;
column segment_name format a32;
select bytes, segment_name, tablespace_name from user_segments where tablespace_name='USER_DATA' order by bytes desc; 
-- 查看更多信息可以执行 desc user_segments; 
 

Oracle 删除表并释放表空间：

drop操作：

truncate table xxx;
drop table xxx;
或者指定彻底删除操作：
drop table xxx purge;

如果只是执行普通的drop操作 drop table xxx; 表被放在回收站(user_recyclebin)里，而不是直接删除掉。

清除回收站里的信息：
清除回收站中的指定表： purge table <table_name>;
清除当前用户的回收站： purge recyclebin;
清除所有用户的回收站： purge dba_recyclebin;
不放入回收站直接删除： drop table xxx purge;

truncate操作

truncate table xxx;
-- truncate 不支持回滚，并且不能truncate一个带有外键的表，如果要删除首先要取消外键，然后再删除。
-- truncate table 后，表空间可能不会立即释放，如果要释放表空间，则执行下面命令（指定 KEEP 0 才会立即释放表空间）：
alter table xxx deallocate UNUSED KEEP 0;
 

-- 查看表空间的使用情况：
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;

删除用户和用户所有数据
sqlplus / as sysdba
-- 删除用户TEST & 所有和用户TEST关联的数据
drop user TEST cascade;
-- 重建用户TEST
create user TEST identified by "1q2w3e" default tablespace USER_DATA temporary tablespace USER_TEMP;
-- grant connect,resource to TEST;
grant dba to TEST;

压缩表空间（可分多次尝试压缩）

-- 压缩前先查看表空间的物理文件的名称及大小
set linesize 120;
column tablespace_name format a20;
column file_name format a64;
select tablespace_name,file_id,file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name; 

TABLESPACE_NAME         FILE_ID FILE_NAME                                                        TOTAL_SPACE
-------------------- ---------- ---------------------------------------------------------------- -----------
SYSAUX                        3 /opt/oracle/oradata/orcl/sysaux01.dbf                                   1410
SYSTEM                        1 /opt/oracle/oradata/orcl/system01.dbf                                    830
UNDOTBS1                      4 /opt/oracle/oradata/orcl/undotbs01.dbf                                 16655
USERS                         6 /opt/oracle/oradata/orcl/users01.dbf                                     145
USER_DATA                     2 /opt/oracle/oradata/my_data02.dbf                                      32704
USER_DATA                     5 /opt/oracle/oradata/my_data01.dbf                                      30720

-- 压缩表空间
alter database datafile '/opt/oracle/oradata/my_data01.dbf' resize 15G;
alter database datafile '/opt/oracle/oradata/my_data02.dbf' resize 15G;

查看Oracle的表结构（查看DDL，类似 MySQL 的 show create table; ）：

语法：
select table_name,dbms_metadata.get_ddl('TABLE','TABLE_NAME')from dual,user_tables where table_name='TABLE_NAME'; 

举例：
select table_name,dbms_metadata.get_ddl('TABLE','TB_TEST01')from dual,user_tables where table_name='TB_TEST01'; 

  CREATE TABLE "TEST"."TB_TEST01" 
   (    "ID" NUMBER(38,0), 
    "INFO" VARCHAR2(255), 
    "CNT" NUMBER(38,0), 
    "C_BLOB" BLOB, 
     CONSTRAINT "TB_TEST01_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USER_DATA"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USER_DATA" 
 LOB ("C_BLOB") STORE AS SECUREFILE (
  TABLESPACE "USER_DATA" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES 
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 

注意：

-- 如果在shell界面执行SQL查询会显示不全，需要执行 set long 操作，如果是在 dbeaver 中查询则无需 set long
set linesize 180;
set long 9999;
select table_name,dbms_metadata.get_ddl('TABLE','TB_TEST01') from dual,user_tables where table_name='TB_TEST01';

查看当前登录用户的所有表：
column table_name format a32;
select table_name from user_tables;
更多相关信息可执行 desc user_tables;

也可以执行
select table_name from all_tables where owner='TEST';
更多相关信息可执行 desc all_tables;

DBA_TABLES  显示的是数据库中所有表（所有表DBA都可以操作）
ALL_TABLES  显示与当前用户可访问的表
USER_TABLES 显示当前用户拥有的表

查看表描述（类似 MySQL 的 desc table_name;）
set linesize 120;
column column_name format a32;
column data_type format a10;
column nullable format a4;
--owner指定用户名、table_name指定表名
select column_name,data_type,data_length,data_precision,data_scale,nullable from all_tab_columns where owner='TEST' and table_name='TB_TEST01';

查看所有数据库（类似 MySQL show databases;）
select name as database from v$database;

Oracle中BLOB字段（BLOB数据）插入方法

Oracle中BLOB字段（BLOB数据）插入方法

-- 创建包含BLOB字段的测试表：
-- DROP TABLE TEST.BLOB_TEST01;
CREATE TABLE TEST.BLOB_TEST01 (
    ID NUMBER(38,0),
    INFO VARCHAR2(255),
    CNT NUMBER(38,0),
    C_BLOB BLOB,
    CONSTRAINT BLOB_TEST01_PK PRIMARY KEY (ID)
);

-- 查看表的DDL

set linesize 180;
set long 9999;
select table_name,dbms_metadata.get_ddl('TABLE','BLOB_TEST01') AS ddl from dual,user_tables where table_name='BLOB_TEST01';

-- insert 插入普通字段数据
INSERT INTO TEST.BLOB_TEST01 (ID, INFO, CNT, C_BLOB) VALUES (1, 'aaa', NULL, NULL);
INSERT INTO TEST.BLOB_TEST01 (ID, INFO, CNT, C_BLOB) VALUES (2, 'bbb', NULL, NULL);

-- update 方式插入BLOB字段数据

-- sqlplus 方式从这里(登录)开始
sqlplus  / as sysdba
conn test/1q2w3e

-- dbeaver或navicat方式从这里开始
-- 创建目录对象/目录别名，要注意大小一致
create or replace directory "DIR" as '/home/oracle/blob/';

declare
  l_bfile bfile;
  l_blob blob;
begin
  update BLOB_TEST01 set C_BLOB=empty_blob() where ID=1 
  return C_BLOB into l_blob;
  l_bfile:=bfilename('DIR','a.zip');
  dbms_lob.open(l_bfile,dbms_lob.file_readonly);
  dbms_lob.loadfromfile(l_blob,l_bfile,dbms_lob.getlength(l_bfile));
  dbms_lob.close(l_bfile);
  commit;
end;
/

-- 注意：
-- 1)关于begin ... end定义的代码块之后是否要加"/"执行代码块：dbeaver和navicat工具中不能加"/"执行（加了反而会报错，可能是工具自动提交执行了），但 sqlplus本地命令行终端中：必须要加"/"结束代码块定义并执行代码块
-- 2)关于BLOB文件目录对象名创建和使用时必须大小写保持一致，建议定义时加引号包括起来（大小写敏感），否则create or replace directory定义和bfilename引用的目录变量名如果大小写不一致会报错找不到文件目录：
-- ORA-22285: non-existent directory or file for FILEOPEN operation
-- ORA-06512: at "SYS.DBMS_LOB", line 1014
-- ORA-06512: at line 8

另外：如果创建包含BLOB字段的测试表、想要指定BLOB存储属性 STORE AS BASICFILE | SECUREFILE（Oracle11g默认为BASICFILE、Oracle 12c开始默认为SECUREFILE），则建表语句如下：
-- 创建表时手动指定 BLOB 字段的存储类型 BASICFILE | SECUREFILE
-- 注意：NOCOMPRESS 选项如果用于LOB定义中时，仅 SECUREFILE 支持，BASICFILE 不支持该参数，否则会报错执行报错：SQL 错误 [43856] [99999]: ORA-43856: SECUREFILE LOB 操作不支持的 LOB 类型
-- DROP TABLE TEST.BLOB_TEST04;
CREATE TABLE TEST.BLOB_TEST04
( ID NUMBER(38,0),
INFO VARCHAR2(255),
CNT NUMBER(38,0),
C_BLOB BLOB,
CONSTRAINT BLOB_TEST04_PK PRIMARY KEY (ID)
) 
 LOB (C_BLOB) STORE AS BASICFILE (
 TABLESPACE USER_DATA ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
 NOCACHE LOGGING );

-- 查询表的完整DDL信息

set linesize 180;
set long 9999;
select table_name,dbms_metadata.get_ddl('TABLE','BLOB_TEST04') AS ddl from dual,user_tables where table_name='BLOB_TEST04';

INSERT INTO TEST.BLOB_TEST04 (ID, INFO, CNT, C_BLOB) VALUES (1, 'aaa', NULL, NULL);
INSERT INTO TEST.BLOB_TEST04 (ID, INFO, CNT, C_BLOB) VALUES (2, 'bbb', NULL, NULL);
 

Oracle数据字典（DBA_OBJECTS）的相关信息查询，参考《Oracle数据字典_sunny05296的博客-CSDN博客》

其他LOBs（CLOB、NCLOB）数据插入方法，参考：

Oracle/MySQL数据库中插入图片BLOB字段的方法_oracle创建blob字段_sunny05296的博客-CSDN博客

表结构、表注释、列注释相关操作：

CREATE TABLE TEST.BLOB_TEST01 (
    ID NUMBER(38,0),
    INFO VARCHAR2(255),
    CNT NUMBER(38,0),
    SCORE NUMBER(30,10),
    C_BLOB BLOB,
    CONSTRAINT BLOB_TEST01_PK PRIMARY KEY (ID)
);

-- 表/列注释相关的视图: USER_TAB_COMMENTS, DBA_TAB_COMMENTS, ALL_TAB_COMMENTS, USER_COL_COMMENTS, DBA_COL_COMMENTS, ALL_COL_COMMENTS

-- 设置表注释
COMMENT ON TABLE BLOB_TEST01 IS 'DDL测试表_B';
-- 设置列注释
COMMENT ON COLUMN BLOB_TEST01.ID IS '主键ID_A';

-- 查询表注释
SELECT * FROM USER_TAB_COMMENTS WHERE TABLE_NAME='BLOB_TEST01';
-- 查询列注释
SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME='BLOB_TEST01'; 

-- 修改表、添加字段
ALTER TABLE BLOB_TEST01 ADD (C_SCORE VARCHAR(64) NULL);

-- 修改表、修改字段
ALTER TABLE BLOB_TEST01 MODIFY (INFO VARCHAR2(128));

--修改表、删除字段
ALTER TABLE BLOB_TEST01 DROP COLUMN INFO;
ALTER TABLE BLOB_TEST01 DROP (INFO); -- 删除多列时括号内逗号分割列名
--重命名字段
ALTER TABLE BLOB_TEST01 RENAME COLUMN INFO TO INFO_NEW;

-- 查看表的DDL（查询表结构）
select table_name,dbms_metadata.get_ddl('TABLE','BLOB_TEST01') AS ddl from dual,user_tables where table_name='BLOB_TEST01';

-- 查询 Oracle 数据库的 SQL 执行耗时（u.username 指定查询过滤的用户名）
SELECT  a.sql_text SQL语句, 
       b.etime 执行耗时, 
       c.user_id 用户ID,
       c.SAMPLE_TIME 执行时间, 
       c.INSTANCE_NUMBER 实例数,
       u.username 用户名, a.sql_id SQL编号
  FROM dba_hist_sqltext a,
       (SELECT sql_id, ELAPSED_TIME_DELTA / 1000000 as etime
           FROM dba_hist_sqlstat
         WHERE ELAPSED_TIME_DELTA / 1000000 >= 1) b,
       dba_hist_active_sess_history c,
       dba_users u
  WHERE a.sql_id = b.sql_id
    AND u.username = 'TEST'
    AND c.user_id = u.user_id
    AND b.sql_id = c.sql_id
    -- AND a.sql_text like '%select * from %'
  ORDER BY  SAMPLE_TIME DESC, 
    b.etime DESC;