1.連接ASM
[grid@xag01 ~]$ /u01/app/oracle/product/12.2.0/db_1/bin/sql / as sysasm
SQL> set sqlformat ansiconsole
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
+ASM2 STARTED
- ASM磁盘空间
#操作系统中查看的方法
[grid@xag01 ~]$ asmcmd -p
ASMCMD [+] > lsdg
State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 512 4096 4194304 7065600 7058464 2355200 2351632 0 N DATA/
MOUNTED NORMAL N 512 512 4096 4194304 6291456 6287376 2097152 2095112 0 N FRA/
MOUNTED NORMAL N 512 512 4096 4194304 460800 392824 153600 119612 0 N MGMT/
MOUNTED NORMAL N 512 512 4096 4194304 30720 29852 10240 9806 0 Y OCR/
#通过SQL语句查看(v$asm_diskgroup)
SQL> select group_number,name,total_mb,free_mb from v$asm_diskgroup;
GROUP_NUMBER NAME TOTAL_MB FREE_MB
1 DATA 7065600 7058464
2 FRA 6291456 6287376
3 MGMT 460800 392824
4 OCR 30720 29852
#通过SQL语句查看(v$asm_disk)
SQL> select name,total_mb,free_mb from v$asm_disk order by 1;
NAME TOTAL_MB FREE_MB
DATA_0000 2355200 2352832
DATA_0001 2355200 2352824
DATA_0002 2355200 2352808
FRA_0000 2097152 2095800
FRA_0001 2097152 2095792
FRA_0002 2097152 2095784
MGMT_0000 153600 130936
MGMT_0001 153600 130948
MGMT_0002 153600 130940
OCR_0000 10240 9956
OCR_0001 10240 9948
OCR_0002 10240 9948
#查看是否有数据库实例连接上ASM实例
SQL> select instance_name,db_name,status from v$asm_client;
INSTANCE_NAME DB_NAME STATUS
+ASM2 +ASM CONNECTED
MYRAC12 MYRAC1 CONNECTED
MYRAC12 MYRAC1 CONNECTED
+ASM2 +ASM CONNECTED
+ASM2 +ASM CONNECTED
xag01.mp.com _OCR CONNECTED
#记录BALANCE操作
SQL> select operation,state,power,actual,sofar from v$asm_operation;
no rows selected
- 表空間管理
[oracle@xag02 ~]$ sql / as sysdba
#單個數據文件
SQL> create temporary tablespace temp1 tempfile '+DATA/MYRAC1/DATAFILE/TEMP1.dbf' size 256M autoextend on next 256M maxsize unlimited extent management local;
#兩個數據文件（本次未測試，生產環境要遠大於本次預設空間）
SQL> create temporary tablespace temp2 tempfile
'+DATA/MYRAC1/DATAFILE/TEMP2_1.dbf' size 256M reuse autoextend on next 128M maxsize 1G,
'+DATA/MYRAC1/DATAFILE/TEMP2_2.dbf' size 256M reuse autoextend on next 128M maxsize 1G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
SQL> ALTER TABLESPACE temp2 TABLESPACE GROUP TEMP_GP;
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_GP;
SQL> drop tablespace TEMP including contents and datafiles;
#創建用戶表空間
create tablespace XAG_UD datafile
'+DATA/MYRAC1/DATAFILE/XAG_UD1.dbf' size 128M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED,
'+DATA/MYRAC1/DATAFILE/XAG_UD2.dbf' size 128M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED
extent management local;
4.創建用戶
#創建用戶
create user c##xag identified by 123456 default tablespace XAG_UD temporary tablespace TEMP_GP;
#赋权限
grant dba to c##xag;
[oracle@xag01 ~]$ sql c##xag/123456
SQL> create table t2 as select sysdate as a from dual;
