oracle数据库处理

1、数据库启停

--切换用户
su - oracle

--监听操作
lsnrctl start
lsnrctl stop

sqlplus / as sysdba
--停止数据库
shutdown immediate
--启动数据库
startup

--查看数据状态
select  status from v$instance

2、强制结束job

exec dbms_scheduler.stop_job('JOB_NAME',true);

3、查看锁表

--查看锁表
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.PADDR,
       t2.TADDR,
       t2.SQL_ADDRESS,
       t1.LOCKED_MODE
  from v$locked_object t1, v$session t2, dba_objects t3
 where t1.session_id = t2.sid
   and t1.object_id = t3.object_id
 order by t2.logon_time;
 
--解决锁表
alter system kill session 'sid,serial#';

4、查看各表空间使用率

select a.tablespace_name 表空间名称,
       total / 1024 / 1024 / 1024 总空间,
       FREE / 1024 / 1024 / 1024 剩余空间,
       (TOTAL - FREE)/ 1024 / 1024 / 1024 使用空间,
       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;

5、查看单个表空间详情

select A.* from DBA_DATA_FILES A where TABLESPACE_NAME = 'TABLESPACE_NAME';

6、新增表空间文件

alter tablespace TABLESPACE_NAME  add datafile '/u01/app/oracle/oradata/orcl/xxx.DBF' size 10G autoextend on NEXT 1024M;

7、查看每个表占用情况

SELECT T.SEGMENT_NAME, T.SEGMENT_TYPE, T.BYTES / 1024 / 1024 / 1024 GB
  FROM USER_SEGMENTS T
 WHERE T.TABLESPACE_NAME = 'TS_DAT_HCP'
 ORDER BY T.BYTES / 1024 / 1024 / 1024 DESC

8、查询正在执行的sql

SELECT A.INST_ID,
       B.SID,
       B.USERNAME,
       B.SERIAL#,
       SPID,
       PADDR,
       SQ.SQL_TEXT,
       SQ.SQL_FULLTEXT,
       B.MACHINE
  FROM GV$PROCESS A, GV$SESSION B, GV$SQLAREA SQ
 WHERE A.ADDR = B.PADDR
   AND B.SQL_HASH_VALUE = SQ.HASH_VALUE
   and B.SID='1486'

9、清空表数据与索引重新加载

--oracle 清空表数据
truncate table table_name

--开启行迁移功能
alter table hcp_pjmx enable row movement;

--(可以再压缩期间进行DM操作和查询) 收缩表、不会降低hwm
alter table hcp_pjmx shrink space compact;

--(调整HWM时将阻塞DML操作) 收缩表、并降低hwm
alter table hcp_pjmx shrink space;

--收缩表并降低hwm,并回收相应的索引
alter table hcp_pjmx shrink space cascade;

--释放已删除的记录表占用的数据块空间
alter table table_name move;

--索引重建
alter index index_name rebuild;

--删除索引
drop index index_name