SQL语句整理

查询所有表

select * from dba_tables;--查询dba拥有的或可以访问的表
select * from all_tables;--查询当前用户拥有的或可以访问的表
select * from user_tables;--查询当前用户拥有的表

查询表有哪些字段

select * from dba_tab_columns t;--查询dba拥有的或可以访问的表
select * from all_tab_columns t;--查询当前用户拥有的或可以访问的表
select * from user_tab_columns t;--查询当前用户拥有的表

查询表注释

select * from dba_tab_comments t;--查询dba拥有的或可以访问的表
select * from all_tab_comments t;--查询当前用户拥有的或可以访问的表
select * from user_tab_comments t;--查询当前用户拥有的表

查询数据库实例名和数据库名

select instance_name from v$instance;--实例名
select name from v$database;--数据库名

查看数据库版本

select * from v$version;

查看数据库字符集

select userenv('language') from dual;

查询字段长度

select length('147258369') from dual;

截取字符串

select substr('123456789101',5,2) from dual;--从第5位开始截取2位

创建dblink

create public database link dblink名字  connect to 连接的用户名 identified by 用户密码
using '(DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = 数据库实例名)))';

查看各类型占用空间

--BYTES是占用大小
select * from dba_segments t;

--当表有blob字段时，会生成LOBINDEX和LOBSEGMENT
--可以通过dba_lobs判断LOBSEGMENT属于哪个表，LOBINDEX和LOBSEGMENT是系统自动命名的，可以根据名字判断LOBINDEX属于哪一个LOBSEGMENT
select * from dba_lobs t;
LOBSEGMENT命名遵循下面的规则：
SYS_LOB(10 digit object_id)C(5 digit col#)$$
LOBINDEX命名遵循下面的规则：
SYS_IL(10 digit object_id)C(5 digit col#)$$

查看表空间使用情况

--查询依次为表空间名、总大小、已使用大小、剩余大小、使用百分比
select a.tablespace_name,
       a.bytes / 1024 / 1024 "Sum MB",
       (a.bytes - b.bytes) / 1024 / 1024 "used MB",
       b.bytes / 1024 / 1024 "free MB",
       round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
  from (select tablespace_name, sum(bytes) bytes
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes, max(bytes) largest
          from dba_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 order by ((a.bytes - b.bytes) / a.bytes) desc;

创建表空间

--创建表空间SPACEA，使用xxx路径下xxx.DBF数据文件，初始大小10G，自动扩充100M，最大20G
CREATE TABLESPACE SPACEA logging DATAFILE 'xxx\xxx.DBF' size 10240M autoextend on next 100M maxsize 20G extent management local;

查看表空间数据文件位置

select * from dba_data_files t;

修改表空间、增加表空间大小的四种方法

--1、给表空间增加数据文件
ALTER TABLESPACE app_data ADD DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' SIZE 50M;
 
--2、新增数据文件，并且允许数据文件自动增长
ALTER TABLESPACE app_data ADD DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF' SIZE 50M
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
 
--3、允许已存在的数据文件自动增长
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF'
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
 
--4、手工改变已存在数据文件的大小
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF'
RESIZE 100M;

创建用户和授权

create user 用户名 identified by 用户密码;--创建用户
grant create session TO 用户名;--赋予创建会话的权限
grant create table to 用户名;--授予创建表的权限
grant drop on 表名 to 用户名;--授予删除表的权限
grant insert on 表名 to 用户名;--插入表的权限
grant update on 表名 to 用户名;--修改表的权限
grant connect,resource,dba to 用户名;--授予connect,resource,dba权限
grant unlimited tablespace to 用户名;--授予表空间权限

将查询结果插入表中

--将表名2的查询结果直接建表，这样比较快
create table 表名1 as SELECT 字段 FROM 表名2 where 条件

--将表名2的查询结果插入已存在的表中
insert into 表名1(a,c,d) select a,c,d from 表名2

查询是否有锁表

select t2.USERNAME,
       t2.SID,
       t2.SERIAL#,
       t3.object_name,
       t2.OSUSER,
       t2.MACHINE,
       t2.PROGRAM,
       t2.LOGON_TIME,
       t2.COMMAND,
       t2.LOCKWAIT,
       t2.SADDR,
       t2.SQL_ADDRESS
  from v$locked_object t1, v$session t2, dba_objects t3
 where t1.SESSION_ID = t2.SID
   and t1.OBJECT_ID = t3.object_id;

将多行查询结果在一行中显示

按条件查询有多行时，将每行内容用’,'隔开显示在一行中

select listagg((要查的字段), ',') within group(order by(要查的字段))
  from 表名
 where 条件