Oracle常用语法总结

1.关联表更新：参考：https://blog.csdn.net/Hehuyi_In/article/details/124222769

• Oracle关联表更新

update PP_ORDER_PICKING B
SET B.SALE_CONTRACE_CODE = (SELECT A.SALE_CONTRACE_CODE FROM
PP_ORDER_TASK A WHERE A.CODE = B.PP_ORDER_TASK_CODE)

• SqlServer关联表更新：

UPDATE Table2
    SET Table2.ColB = Table1.ColB
    FROM Table2
    INNER JOIN Table1
    ON (Table2.ColA = Table1.ColA);

2.根据约束条件定位到具体的表名：

--根据违反约束提示定位到表
select constraint_name "约束名",constraint_type AS "类型",table_name AS "表名" FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_NAME='SYS_C0010351'

• 补充：查看失效的索引：

--查看失效的索引
select *  from user_indexes 
where Status = 'UNUSABLE' 
and index_name ='SYS_C0010490'

3. 问题描述：数据库(MOM_SMT)中有两种表数据量都在2千万左右，导致表空间使用率100%(可以通过语句1和语句2查询)，此时再新增数据就提示如下错误：ORA-01653:无法通过表空间扩展，如下图示：

• 分析下来,原因就是表空间使用完了，所以我就删除了这两张表的数据,然后再次查询表空间使用情况发现使用率还是100%，也就是说两张表的数据删除了，但是这两张表占用的空间并没有释放。
  解决方法：执行了语句3：释放那两张数据表占用的空间即可。
• 如果数据库的表空间没有设置自增,可以执行补充语句中的（4） 【查询数据库表空间是否是自增可以执行补充语句中的(3)】

--语句1.Oracle查询数据库的表空间使用情况（这里的表空间名称指的数据库的名称）
SELECT
  a.tablespace_name "表空间名称",
  total / ( 1024 * 1024 ) "表空间大小(M)",
  free / ( 1024 * 1024 ) "表空间剩余大小(M)",
  ( total - free ) / ( 1024 * 1024 ) "表空间使用大小(M)",
  total / ( 1024 * 1024 * 1024 ) "表空间大小(G)",
  free / ( 1024 * 1024 * 1024 ) "表空间剩余大小(G)",
  ( total - free ) / ( 1024 * 1024 * 1024 ) "表空间使用大小(G)",
  round( ( total - free ) / total, 4 ) * 100 "使用率 %"
FROM
  ( SELECT tablespace_name, SUM( bytes ) free FROM dba_free_space GROUP BY tablespace_name ) a,
  ( SELECT tablespace_name, SUM( bytes ) total FROM dba_data_files GROUP BY tablespace_name ) b
WHERE
  a.tablespace_name = b.tablespace_name
    and a.tablespace_name= 'MOM_SMT' --表空间名称(指的是数据库的名称：MOM_SMT库)
				
--2.语句2 Oracle查询数据库中各表空间下的数据表数据量：
select t.owner,t.segment_name,t.tablespace_name,bytes/1024/1024/1024 as sizes,q.num_rows,t.segment_type
from dba_segments t
  left join dba_tables q on t.segment_name = q.table_name and t.owner=q.owner
where t.segment_type='TABLE'
      and t.tablespace_name='MOM_SMT' --表空间名称(指的是数据库的名称：MOM_SMT库)
order by 4 desc
	
--3.语句3 释放数据表的表空间
alter table MES_CHECK_AOI_COLLECT_BAD enable row movement;--开启允许行移动, 此操作后允许rowid改变
alter table MES_CHECK_AOI_COLLECT_BAD shrink space;--回收空间
alter table MES_CHECK_AOI_COLLECT_BAD disable row movement;--开启禁止行移动, 此操作后不允许rowid改变
			 
--4.补充语句：
-- （1）查看当前用户每个表占用空间的大小：
--select segment_name,sum(bytes)/1024/1024 size_M from user_extents group by segment_name order by size_M desc;
--（2））查看每个表空间占用空间的大小：
--Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name
--（3）查询表空间
SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME ='MOM_SMT'
--（4）修改表空间自动增长：1024M
ALTER DATABASE DATAFILE '/data/u01/oradata/ORCL/pdb_mes/mom_smt.dbf' AUTOEXTEND ON NEXT 1024M ;--每次自动增长1024M

4.ORA-01109:数据库未打开，如下图：

解决方法：使用sqlplus连上数据库

输入用户名：sqlplus / as sysdba
输入口令：

查询当前数据库：show con_name pdbs 可以看到有ORCLPDB 
打开插接式数据库即可：alter pluggable database ORCLPDB open；

5.ORA-01502: 索引或这类索引的分区处于不可用状态，如下图：

--查看失效的索引
select *  from user_indexes 
where Status = 'UNUSABLE' 
and index_name ='PK_WMS_PRODUCT_OUT_ITEM'

--解决方法：删除相应约束
alter table WMS_PRODUCT_OUT_ITEM drop constraint PK_WMS_PRODUCT_OUT_ITEM

参考链接：https://blog.csdn.net/Ruishine/article/details/120972330