oracle 表空间 连接数 杀死进程，关闭连接等常用sql记录

1.创建表空间
表空间会自动扩，但是删除数据虽然会释放占用空间，但是不会减少dbf，暂时使用resize
create tablespace FUND60PUB_TBS datafile '/opt/oracle/oradata/orcl/fund60pub_tbs01.dbf' size 50m autoextend on next 50m maxsize 30600m extent management local;

创建临时表空间：（后续可以alter）

create temporary tablespace plncontrol_temp tempfile '/oswbb/temptablespace/plncontrol_temp.dbf' size 1024M reuse autoextend on next 1024M maxsize unlimited;

注意没有加bigfile，到了32g就不行了，哪怕你是不限制增长
2.查询表空间位置：
select tablespace_name,File_Name from dba_data_files;--这种是从sqlplus用超管查所有
查询当前用户的表空间
select  username,default_tablespace from user_users;

select  username,default_tablespace from dba_users ；
3.当前用户的表空间大小
select  sum(bytes)/1024/1024||'MB' from user_extents;
SELECT a.tablespace_name as "表空间名",
       total/ (1024 * 1024 * 1024) as "表空间大小(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
 order by a.tablespace_name;

AOP_PUB_01_TBS
AOP_CENTER_01_TBS

FUND60PUB_TBS
FUND60ACCO1_TBS
FUND60TRANS1_TBS
FUND60QUERY_TBS

/opt/oracle/oradata/orcl/aop_pub_01_tbs01.dbf
/opt/oracle/oradata/orcl/aop_center_01_tbs01.dbf
/opt/oracle/oradata/orcl/aop_iar_01_tbs01.dbf

4   unlock
alter user fund60pub account unlock;
alter user fund60acco1 account unlock;
alter user fund60trans1 account unlock;
alter user fund60query account unlock;

5.连接数
select count(*) from v$process;
select count(*) from v$session;
select value from     v$parameter where name = 'processes';
alter system set processes = 5000 scope = spfile;
shutdown immediate;
startup;

6.创建用户，授权

create user test  identified by test123 default tablespace FUND60;

grant connect,resource,dba,create view to test;

grant read,write on directory DATA_PUM_DIR to test;--给一下备份目录的权限

--grant select on user.tablename to test;

7.查询oracle的使用某个用户登录的sid、进程

select sid,serial# from v$session where username='AOP_PUB_01';

alter system kill session IMMEDIATE；

11.查询这个用户所开启的会话连接，并打出杀死命令

select 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid in (select sid from v$lock where block = 1);

select 'alter system kill session '''||sid||','||serial#||''';' from v$session where username='testuser';

8.删除及创建用户及名下表

drop user aop_center_01 cascade;

create user aop_center_01  identified by "01aop_center" default tablespace AOP_CENTER_01_TBS;

grant connect,resource,dba,create view  to aop_center_01 ;

--导入imp

imp aop_center_01/01aop_center  file=/home/oracle/backup/aopcenter20220609.dmp log=/home/oracle/backup/aopcenter20220609.log full=y ignore=y;

9.查询锁表，及解锁，杀死session

SQL> select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

SQL> select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;

alter system kill session 'sid,serial';

10.忘记表名，查询当前用户下的表，及列名

select * from user_tab_comments

select * from user_col_comments;

如果太多的话开启spool 1.sql;进行记录，使用spool off;关闭，但是要编辑下

执行这个@1.sql

11删除表空间、dbf文件等

DROP TABLESPACE tablespace_name 
                                  [ including contents [ and datafiles ] [ CASCADE CONSTRAINT ] ]; 
   无选项 -- 当表空间为空才能删除； 
   including contents — 删除表空间及对象； 
   including contents and datafiles — 删除表空间、对象及数据文件； 
   includingcontents CASCADE CONSTRAINT — 删除关联； 
   including contents and datafiles cascade constraint -- 含前两项。 
 
DROP TABLESPACE see including contents and datafiles cascade constraint；
之后最好是关闭oracle然后删除dbf

lsof |grep deleted--删除了却还没有释放空间

12.查询临时表空间

select username,temporary_tablespace from dba_users;--查询临时表空间

select tablespace_name,bigfile from dba_tablespaces;--查询表空间属性
alter user aa temporary tablespace plncontrol_temp;--修改默认临时表空间

select file_name,tablespace_name,bytes/1024/1024"MB",autoextensible from dba_temp_files;--查看临时表空间大小
alter database default temporary tablespace plncontrol_temp;--修改默认临时表空间，如果你是dba会改所有

13.undo表空间使用查询

SELECT seg.tablespace_name "Tablespace Name",
ts.bytes / 1024 / 1024 "TS Size(MB)",
ue.status "UNDO Status",
count(*) "Used Extents",
round(sum(ue.bytes) / 1024 / 1024, 2) "Used Size(MB)",
round(sum(ue.bytes) / ts.bytes * 100, 2) "Used Rate(%)"
FROM dba_segments seg,
DBA_UNDO_EXTENTS ue,
(SELECT tablespace_name, sum(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) ts
WHERE ue.segment_NAME = seg.segment_NAME
and seg.tablespace_name = ts.tablespace_name
GROUP BY seg.tablespace_name, ts.bytes, ue.status
ORDER BY seg.tablespace_name;

14.查询oracle数据库某时间段的操作记录

select t.SQL_TEXT, t.FIRST_LOAD_TIME
from V$sqlarea t
where t.SQL_TEXT like 'insert%' and t.FIRST_LOAD_TIME like '2023-01-12/23:00:00%'
order by t.FIRST_LOAD_TIME desc;