晴风

取当前时间前一小时的时间段

SELECT TRUNC(LOCALTIMESTAMP-1/24,'hh24') s1,TRUNC(LOCALTIMESTAMP,'hh24') s2 FROM dual

表空间等操作

-- 查看表空间使用率
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 4 DESC;

-- 查询空间文件
SELECT tablespace_name,file_id,file_name,round(bytes/(1024*1024),0) total_space FROM dba_data_files ORDER BY tablespace_name,file_id;

SELECT FILE_ID,FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE,BYTES/1024/1024/1024 GB FROM dba_data_files ORDER BY 1;

-- 创建表空间
CREATE TABLESPACE EXDB DATAFILE '/data/oracle/app/oracle/oradata/orcl/EXDB.dbf' SIZE 30G;
ALTER TABLESPACE EXDB DEFAULT COMPRESS;
ALTER TABLESPACE EXDB NOLOGGING;
-- 添加表空间文件(可变大小)
ALTER TABLESPACE EXDB ADD DATAFILE  '/data/oracle/app/oracle/oradata/orcl/EXDB15.dbf' SIZE 10240M  AUTOEXTEND ON NEXT 10M MAXSIZE 30G;
-- 添加表空间文件(固定大小)
ALTER TABLESPACE EXDB ADD DATAFILE  '/data/oracle/app/oracle/oradata/orcl/EXDB91.dbf' SIZE  30G;
-- 调整大小
ALTER DATABASE DATAFILE '/data/oracle/app/oracle/oradata/orcl/EXDB91.dbf' RESIZE 10M;
-- 下线表空间文件
ALTER DATABASE DATAFILE '/data/oracle/app/oracle/oradata/orcl/EXDB91.dbf' offline;
-- 上线表空间文件
ALTER DATABASE DATAFILE '/data/oracle/app/oracle/oradata/orcl/EXDB91.dbf' online;

-- 表迁移表空间
ALTER TABLE EX_XINXIZHONGXIN_243_XGBDJCXX MOVE TABLESPACE BAKEDATA;
ALTER TABLE EX_ZHENGWU_120_TXJLB MOVE PARTITION SYS_P761 TABLESPACE BAKEDATA;
-- 索引迁移表空间
ALTER INDEX exdb.SYS_C0012883 REBUILD TABLESPACE BAKEDATA;

-- 调整临时表空间大小
ALTER DATABASE TEMPFILE '/datassd/oracle/app/oracle/oradata/orcl/temp01.dbf' RESIZE 30G;
-- 调整undo表空间大小
ALTER DATABASE datafile '/datassd/oracle/app/oracle/oradata/orcl/undotbs01.dbf' resize 10G;

备份文件夹目录

-- 创建备份文件目录
create directory dmp_dir as '/datahdd/oracle/dmp';
-- 目录授权
grant read,write on directory dmp_dir to exdb;

ORACLE 元数据表

/**
数据字典视图分类
DBA_*** 该视图包含数据库整个对象信息，只能由数据库管理员查看
ALL_*** 包含某个用户所能看到的全部数据库信息
USER_*** 包含当前用户访问的数据库对象信息
*/

-- 查询表信息 
SELECT TABLE_NAME,TABLESPACE_NAME,PARTITIONED,STATUS,NUM_ROWS,LAST_ANALYZED FROM tabs 
WHERE TABLE_NAME = 'EX_XINXIZHONGXIN_243_XGBDJCXX'; 

-- # 查询表索引
-- user_indexes 表索引
SELECT
    T.*,
    I.INDEX_TYPE 
FROM
    USER_IND_COLUMNS T
    LEFT JOIN USER_INDEXES I ON T.INDEX_NAME = I.INDEX_NAME
WHERE T.TABLE_NAME = '****'

-- # 查询表结构
-- USER_TAB_COMMENTS 表注释
-- USER_TAB_COLUMNS 表字段
-- USER_COL_COMMENTS 字段注释
SELECT
    TC.TABLE_NAME,
    TC.COMMENTS TABLE_COMMENT,
    TCOL.COLUMN_NAME,
    TCOL.DATA_TYPE,
    TCOL.DATA_LENGTH,
    TCOL.NULLABLE,
    CC.COMMENTS COLUMN_COMMENTS 
FROM USER_TAB_COMMENTS TC
JOIN USER_TAB_COLUMNS TCOL ON TC.TABLE_NAME=TCOL.TABLE_NAME
LEFT JOIN    USER_COL_COMMENTS CC ON TCOL.TABLE_NAME=CC.TABLE_NAME AND TCOL.COLUMN_NAME=CC.COLUMN_NAME
WHERE TC.COMMENTS IS NOT NULL
AND TC.TABLE_NAME =UPPER('')

-- # 查询表主键
-- user_cons_columns 主键
-- user_constraints 主键信息
SELECT
    cc.* 
FROM
    user_cons_columns cc,
    user_constraints cs 
WHERE
    cc.constraint_name = cs.constraint_name 
    AND cs.constraint_type = 'P' 
    AND cc.table_name = 'TD_BANKTYPE'

-- # 分区表
-- 查询一个表的所有分区
SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = UPPER('EX_ZHENGWU_120_TXJLB')
-- 查看表的分区字段
SELECT * FROM DBA_PART_KEY_COLUMNS WHERE NAME ='EX_ZHENGWU_120_TXJLB' AND OWNER = 'EXDB';

命令行sqlplus登录

# 需要切换到ORACLE用户
# 用户登录
sqlplus USERNAME/PASSWORD@192.168.XX.XX:1521/orcl
# DBA登录
sqlplus / as sysdba

查看建表语句

dbms_metadata.get_ddl('OBJECT_TYPE','NAME','SCHEMA','VERSION','MODEL')

--  使用示例
SELECT to_char(dbms_metadata.get_ddl('TABLE','DWS_RY_JCXX')) FROM dual
SELECT to_char(dbms_metadata.get_ddl('TABLE','DWS_RY_JCXX','schema')) FROM dual

ORA-00054:资源正忙

-- 查询锁表任务SID,SERIAL#,SQL
SELECT 
sess.sid, sess.serial#, ao.object_name, sq.SQL_TEXT, lo.oracle_username, lo.os_user_name, lo.locked_mode
FROM v$locked_object lo, dba_objects ao, v$session sess, v$sqlarea sq
WHERE ao.object_id = lo.object_id AND lo.session_id = sess.sid AND sess.prev_sql_addr=sq.address
ORDER BY sess.sid,sess.serial#; 

-- kill session - SID,SERIAL#
ALTER SYSTEM KILL SESSION '0,62103';

清理归档日志

su - oracle
rman
connect target username/password
--删除x天前的归档日志
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-2';