常用Oracle命令.md · zhaofeng/NNB

# 常用 Oracle 命令

## 用户操作

```SQL
-- 创建用户
create user trade identified by "trade";
-- 赋权
grant dba to logminer;
-- 删除用户
drop user trade;
```

## 表空间操作

### 新增表空间

```SQL
-- 使用自增长数据文件创建表空间
CREATE TABLESPACE trade --表空间名称
     LOGGING
     DATAFILE '/home/oracle/product/oradata/mikan/trade.dbf' --数据文件路径
     SIZE 50M --表空间初始大小
     AUTOEXTEND ON --自增长开
     NEXT 50M --自增长大小
     MAXSIZE 20480M --最大大小
     EXTENT MANAGEMENT LOCAL;

-- 使用固定大小数据文件创建表空间
CREATE TABLESPACE trade DATAFILE '/home/oracle/product/oradata/mikan/trade.dbf' SIZE 2000M;

-- 使用已有的数据文件创建表空间
CREATE TABLESPACE trade2 DATAFILE '/home/oracle/product/oradata/mikan/trade2.dbf'

-- 创建临时表空间
CREATE TEMPORARY TABLESPACE TEMP01
     TEMPFILE '/home/oracle/product/oradata/mikan/temp01.dbf'
     SIZE 50M
     AUTOEXTEND ON
     NEXT 50M MAXSIZE 20480M
     EXTENT MANAGEMENT LOCAL;

CREATE SMALLFILE
    TABLESPACE "TRADE"
    LOGGING
    DATAFILE '/home/oracle/product/oradata/mikan/trade.dbf' SIZE 1024M
    EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO;
```

### 删除表空间

```SQL
-- 删除表空间并清空数据文件
DROP TABLESPACE trade2 INCLUDING CONTENTS;

-- 删除表空间同时删掉数据文件
DROP TABLESPACE trade2 INCLUDING CONTENTS AND DATAFILES;
```

### 查看数据文件列表

```SQL
SELECT * FROM dba_data_files t ORDER BY t.TABLESPACE_NAME, t.FILE_NAME;
```

### 数据文件操作

```SQL
-- 给表空间增加数据文件
ALTER TABLESPACE trade ADD DATAFILE '+DATA/tzjydb/trade21.dbf' SIZE 10240M;
-- 给UNDOTBS增加数据文件
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '+DATA/tzjydb/undotbs01_2.dbf' SIZE 10240M;
-- 重新调整数据文件大小
ALTER DATABASE DATAFILE '+DATA/tzjydb/undotbs01.dbf' RESIZE 32767M;
-- 关闭数据文件自增长
ALTER DATABASE DATAFILE '+DATA/tzjydb/undotbs01.dbf' AUTOEXTEND OFF;
-- 开启数据文件自增长
ALTER DATABASE DATAFILE '+DATA/tzjydb/undotbs01.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE 32767M;
-- 给临时表空间增加数据文件
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/tzjydb/temp02.dbf' SIZE 4G AUTOEXTEND ON NEXT 128M MAXSIZE 6G;
```

### 表空间使用情况（快速）

```SQL
SELECT *
  FROM (SELECT A.TABLESPACE_NAME,
               TO_CHAR(A.BYTES / 1024 / 1024, '99,9999.999') TOTAL_BYTES,
               TO_CHAR(B.BYTES / 1024 / 1024, '99,9999.999') FREE_BYTES,
               TO_CHAR(A.BYTES / 1024 / 1024 - B.BYTES / 1024 / 1024,
                       '99,9999.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,9999.999') TOTAL_BYTES,
               TO_CHAR((C.BYTES - D.BYTES_USED) / 1024 / 1024, '99,9999.999') FREE_BYTES,
               TO_CHAR(D.BYTES_USED / 1024 / 1024, '99,9999.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)
 WHERE TABLESPACE_NAME NOT IN ('SYSAUX', 'SYSTEM', 'USERS')
 ORDER BY TABLESPACE_NAME;
```

### 对象空间占用情况（按从大到小）

```SQL
SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB
  FROM USER_EXTENTS
 GROUP BY SEGMENT_NAME
 ORDER BY MB DESC;
```

## 归档日志操作

```sql
-- 设置归档日志保存时间
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
-- 清理归档日志
rman targat /
delete archivelog until time 'sysdate-1';
delete archivelog until time 'sysdate';
```

## 备份恢复

### 带压缩数据泵备份指定表空间

```bash
expdp trade/trade@tradedb directory=dumpdir dumpfile=all20190223_0429B.dmp schemas=trade compression=ALL
```

zhaofeng/NNB

简介

发行版

贡献者

近期动态

zhaofeng/NNB .gitee-modal { width: 500px !important; }

简介

发行版

贡献者

近期动态

搜索帮助

zhaofeng/NNB