ORACLE语句记录

select *
  from (Select a.tablespace_name,
               to_char(a.bytes / 1024 / 1024, '99,999.999') total_bytes,
               to_char(b.bytes / 1024 / 1024, '99,999.999') free_bytes,
               to_char(a.bytes / 1024 / 1024 - b.bytes / 1024 / 1024,
                       '99,999.999') use_bytes,
               to_char((1 - b.bytes / a.bytes) * 100, '99.99') || '%' use
          from (select tablespace_name, sum(bytes) bytes
                  from dba_data_files group by tablespace_name) a,
               (select tablespace_name, sum(bytes) bytes
                  from dba_free_space group by tablespace_name) b
         where a.tablespace_name = b.tablespace_name union all
          select c.tablespace_name,
                 to_char(c.bytes / 1024 / 1024, '99,999.999') total_bytes,
                 to_char((c.bytes - d.bytes_used) / 1024 / 1024,
                         '99,999.999') free_bytes,
                 to_char(d.bytes_used / 1024 / 1024, '99,999.999') use_bytes,
                 to_char(d.bytes_used * 100 / c.bytes, '99.99') || '%' use
                  from (select tablespace_name, sum(bytes) bytes
                    from dba_temp_files group by tablespace_name) c,
                 (select tablespace_name, sum(bytes_cached) bytes_used
                    from v$temp_extent_pool group by tablespace_name) d
                 where c.tablespace_name = d.tablespace_name
        ) order by tablespace_name

--第一步
alter database tempfile 'C:\app\Administrator\oradata\orcl\TEMP01.DBF' drop;
--第二步：
alter tablespace temp add tempfile
'C:\app\Administrator\oradata\orcl\TEMP01.DBF'
size 4096M reuse autoextend on next 100M;
--第三步：
select d.file_name, d.file_id, d.tablespace_name, d.bytes 
from dba_temp_files d;
--第四步：
alter database tempfile 'C:\app\Administrator\oradata\orcl\TEMP01.DBF' autoextend off; 

C:\app\Administrator\diag\tnslsnr\t1-201806\listener\alert
C:\app\Administrator\diag\tnslsnr\t1-201806\listener\trace
C:\app\Administrator\diag\rdbms\orcl\orcl\alert
C:\app\Administrator\diag\rdbms\orcl\orcl\trace

---解锁用户
alter user username account unlock
--查找删除的数据
select * from tableName as of timestamp to_timestamp('2021-04-27 17:16:00','yyyy-mm-dd hh24:mi:ss') 
--循环数据根据业务添加
begin
for wf in(select '0' AS ID from dual)
 loop
     IF wf.ID='A'  THEN
       insert into USERINFO(username,FULLNAME,Password) values('c'||wf.ID,wf.ID,'123456');
    ELSIF wf.ID=0 THEN
        insert into USERINFO(username,FULLNAME,Password) values('z'||wf.ID,wf.ID,'123456');
    END IF;
  end loop;
end;
/
--总连接数
select value from v$parameter where name = 'processes';
--当前的连接数
select count(*) from v$process;
--数据库的各个连接数
select  b.MACHINE, b.PROGRAM, b.USERNAME, count(*) from v$process a, v$session b where a.ADDR = b.PADDR and  b.USERNAME is not null   
group by  b.MACHINE, b.PROGRAM, b.USERNAME order by count(*) desc;
--创建表空间和用户
create tablespace space_name 
logging 
datafile 'D:\oracle_space\space_name.dbf' 
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local;
--创建用户
create user username identified by 123456
--分配表空间
default tablespace space_name;
--分配权限
grant dba to username;
--导出
$exp username/123456@ip:1521/ORCL file=d:/111.dmp log=d:\explogname.log OWNER=(username);
--导入      
$imp username/123456@ip:1521/ORCL file=d:/111.dmp log=d:\implogname.log ignore=y full=y;
--查询锁定的表
select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time;
--解锁表，解除表锁定
alter system kill session '56,13307';--sid,serial#
---删除用户下的所有表数据
declare
  cursor c_t is (select table_name from user_tables);--声明一个游标
  table_name user_tables.table_name%type;--声明一个表名的变量
begin 
  open c_t;
  loop--循环
       fetch c_t into table_name;
       exit when c_t%notfound;
       execute immediate 'delete from ' || table_name;--动态sql删除
  end loop;
  close c_t;
end;
/
--查看占用
SELECT *
  FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB
          FROM DBA_SEGMENTS
         WHERE TABLESPACE_NAME = 'SYSTEM'
         GROUP BY SEGMENT_NAME
         ORDER BY 2 DESC)
 WHERE ROWNUM < 10;
--关闭system表空间审计功能(需要重启数据库生效)
alter system set audit_trail=none scope=spfile;
--清空system审计数据
truncate table  SYS.AUD$;