Oracle表空间系列

 

1.查询所有表的数据量（行数）

select b.table_name,b.num_rows from user_tables b;

2.--查询表空间使用情况

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",

       D.TOT_GROOTTE_MB "表空间大小(M)",

       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",

       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,

                     2),

               '990.99') || '%' "使用比",

       F.TOTAL_BYTES "空闲空间(M)",

       F.MAX_BYTES "最大块(M)"

  FROM (SELECT TABLESPACE_NAME,

               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,

               ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES

          FROM SYS.DBA_FREE_SPACE

         GROUP BY TABLESPACE_NAME) F,

       (SELECT DD.TABLESPACE_NAME,

               ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB

          FROM SYS.DBA_DATA_FILES DD

         GROUP BY DD.TABLESPACE_NAME) D

 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

 ORDER BY 1  

3.--查询表空间的free space

            SELECT TABLESPACE_NAME,

                   COUNT(*) AS EXTENDS,

                   ROUND(SUM(BYTES) / 1024 / 1024, 2) AS MB,

                   SUM(BLOCKS) AS BLOCKS

              FROM DBA_FREE_SPACE

             GROUP BY TABLESPACE_NAME;

4.--查询表空间的总容量

SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 AS MB

  FROM DBA_DATA_FILES

 GROUP BY TABLESPACE_NAME;

5.--查询表空间使用率

SELECT TOTAL.TABLESPACE_NAME,

       ROUND(TOTAL.MB, 2) AS TOTAL_MB,

       ROUND(TOTAL.MB - FREE.MB, 2) AS USED_MB,

       ROUND((1 - FREE.MB / TOTAL.MB) * 100, 2) || '%' AS USED_PCT

  FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 AS MB

          FROM DBA_FREE_SPACE

         GROUP BY TABLESPACE_NAME) FREE,

       (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 AS MB

          FROM DBA_DATA_FILES

         GROUP BY TABLESPACE_NAME) TOTAL

 WHERE FREE.TABLESPACE_NAME = TOTAL.TABLESPACE_NAME;

6.--表空间扩容 允许已存在的数据文件自动增长

ALTER DATABASE datafile '/oradata/oracle/orcl/ybdw08.dbf'

  autoextend on next 1024M maxsize 32640M;

7.--查看所有表空间

SELECT A.TABLESPACE_NAME, TOTAL_SPACE, FREE_SPACE

FROM (SELECT A.TABLESPACE_NAME,SUM(A.BYTES) / 1024 / 1024 / 1024 TOTAL_SPACE FROM DBA_DATA_FILES A GROUP BY A.TABLESPACE_NAME)

A,(SELECT A.TABLESPACE_NAME,

SUM(A.BYTES) / 1024 / 1024 / 1024 FREE_SPACE FROM DBA_FREE_SPACE A

GROUP BY A.TABLESPACE_NAME) B WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+);

7.1--查询表空间及每个数据文件的使用情况

select a.tablespace_name 表空间名字,

a.file_name 表空间对应的数据文件,

a.bytes / 1024 / 1024 / 1024 该数据文件总量G,

(a.bytes - sum(nvl(b.bytes, 0))) / 1024 / 1024 / 1024 该数据文件已使用量G,

(sum(nvl(b.bytes, 0))) / 1024 / 1024 / 1024 该数据文件剩余可用量G,

sum(nvl(b.bytes, 0)) / a.bytes * 100 该数据文件剩余可用量占比

from dba_data_files a, dba_free_space b

where a.file_id = b.file_id(+)

group by a.tablespace_name, a.file_id, a.bytes, a.file_name

order by a.tablespace_name

8.-- 查询数据库中不同用户的分区表的数目

select owner,count(1) from dba_tables where partitioned='YES' group By owner;

9.-- 查询数据库中用户的分区表

select * from dba_tables where partitioned='YES'  and owner='JH_DW' ;

10.--查询数据库中 该用户下的对应表的分区字段

select * from dba_part_key_columns where name='表名' and owner ='数据库用户名';

11.---查看该数据库中 所有用户的 所有分区表的和对应分区字段

SELECT * FROM all_PART_KEY_COLUMNS;

12.--查询dblink

select * from dba_db_links;

create database link JDSH_1

connect to JDSH identified by JDSH

using '192.168.100.241:1521/ORCL';

13.--正在执行的SQL

SELECT b.sid oracleID, 

       b.username Oracle用户, 

       b.serial#, 

       spid 操作系统ID, 

       paddr, 

       sql_text 正在执行的SQL, 

       b.machine 计算机名 

FROM v$process a, v$session b, v$sqlarea c 

WHERE a.addr = b.paddr 

   AND b.sql_hash_value = c.hash_value;

14.--查询某个存储过程是否被锁

select * from v$db_object_cache

where locks != 0 and upper(name) = upper('xxx');-- pro

15.--查看被锁过程的sid

select * from v$access  where upper(object) = upper('xxx');

16.--拿到被锁过程的sid去查serial#

select * from v$session where sid in (sid);

17.--杀进程

alter system kill session 'sid,serial#'；

18.--查询表容量g

SELECT SUM(BYTES)/1024/1024 "SIZE(MB)" FROM DBA_SEGMENTS WHERE SEGMENT_NAME='TABLE_NAME';

--每张表的容量

SELECT K.SEGMENT_NAME, SUM(K.BYTES) / 1024 / 1024 / 1024 G

FROM DBA_SEGMENTS K

WHERE K.OWNER = 'HAYB_SH'

GROUP BY SEGMENT_NAME

ORDER BY G DESC;

19.--查看是否锁表

SELECT A.OBJECT_NAME,

       B.SESSION_ID,

       C.SERIAL#,

       C.PROGRAM,

       C.USERNAME,

       C.COMMAND,

       C.MACHINE,

       C.LOCKWAIT

  FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, V$SESSION C

 WHERE A.OBJECT_ID = B.OBJECT_ID

   AND C.SID = B.SESSION_ID;

20.--查询锁表原因：

SELECT L.SESSION_ID SID,

       S.SERIAL#, 

       L.LOCKED_MODE,

       L.ORACLE_USERNAME,

       S.USER#,

       L.OS_USER_NAME,

       S.MACHINE,

       S.TERMINAL,

       A.SQL_TEXT,

       A.ACTION

  FROM V$SQLAREA A, V$SESSION S, V$LOCKED_OBJECT L

 WHERE L.SESSION_ID = S.SID

   AND S.PREV_SQL_ADDR = A.ADDRESS

 ORDER BY SID, S.SERIAL#;

21.--查看被锁的表

SELECT P.SPID,

       C.OBJECT_NAME,

       B.SESSION_ID,

       B.ORACLE_USERNAME,

       B.OS_USER_NAME

  FROM V$PROCESS P, V$SESSION A, V$LOCKED_OBJECT B, ALL_OBJECTS C

 WHERE P.ADDR = A.PADDR

   AND A.PROCESS = B.PROCESS

   AND C.OBJECT_ID = B.OBJECT_ID;

杀掉：

kill -9 spid

22.--查询锁表

SELECT * FROM V$LOCKED_OBJECT A,DBA_OBJECTS B

WHERE A.OBJECT_ID = B.OBJECT_ID AND UPPER(OBJECT_NAME) = UPPER()

23.查询正在执行的存储过程

select b.sid,

b.SERIAL#,

a.OBJECT,

'alter system kill session   ' || '''' || b.sid || ',' || b.SERIAL# ||

''';' kill_command

from SYS.V_$ACCESS a, SYS.V_$session b

where a.type = 'PROCEDURE'

and (a.OBJECT like upper('%P_DWD_B_PRESCRIPT_ALL%') or

a.OBJECT like lower('%P_DWD_B_PRESCRIPT_ALL%'))

and a.sid = b.sid

and b.status = 'ACTIVE';

24.数据库远程连接

sqlplus 用户名/密码@/ip:port/数据库实例名

25.查看数据库的创建日期及归档模式

SELECT created, log_mode FROM v$database;

26.查询高水位线

SELECT table_name,

ROUND ( (blocks * 8), 2) "高水位空间 k",

ROUND ( (num_rows * avg_row_len / 1024), 2) "真实使用空间 k",

ROUND ( (blocks * 10 / 100) * 8, 2) "预留空间(pctfree) k",

ROUND (( blocks * 8 - (num_rows * avg_row_len / 1024)- blocks * 8 * 10 / 100),2) 浪费空间k

FROM user_tables

WHERE temporary = 'N'

ORDER BY 5 DESC;

https://blog.csdn.net/shiyu1157758655/article/details/78051637

27.临时表空间查询

select tablespace_name,

round(free_space / 1024 / 1024 / 1024, 2) "free(GB)",

round(tablespace_size / 1024 / 1024 / 1024, 2） "total(GB)", round(nvl(free_space, 0) * 100 / tablespace_size, 3) "Free percent"from dba_temp_free_space;

 

--查询表空间
select tablespace_name from dba_tablespaces
--创建表空间
create tablespace data datafile 'data.file.dbf' size 10m;
--设置默认存储的表空间
alter user system default tablespace data;
--查询默认表空间
select default_tablespace,temporary_tablespace from dba_users  where USERNAME = 'SYSTEM';
--查看用户

select * from dba_users where username='SYSTEM'

--查看用户下有几张表

select * from user_tables;

--查询默认表空间 用户名需大写
select default_tablespace,temporary_tablespace from dba_users  where USERNAME = 'SYSTEM';
--查询表空间的路径
select * from dba_data_files
--查询表空间下有几张表
select * from all_tables where tablespace_name = 'DATA'
--查询一张表属于哪个表空间
select table_name,tablespace_name from user_tables where TABLE_NAME='USERS';
--更改表空间的大小
alter database datafile 'D:\APP\ROC\PRODUCT\11.2.0\DBHOME_1\DATABASE\DATA.FILE.DBF' resize 100m;
--行转列
select deptno 部门号, WMSYS.WM_CONCAT(ename) from emp group by deptno 


select * from dba_tables where tablespace_name='data'
select * from dba_tablespaces