Oracle常用DDL、DML SQL语句

Oracle常用DDL、DML、DCL SQL语句

一、DML

DML（data manipulation language）数据操纵语言：
主要有：
SELECT
INSERT
UPDATE
DELETE
MERGE
CALL
EXPLAIN PLAN
LOCK TABLE

数据库运维最经常用到的 SELECT、EXPLAIN PLAN；

常用DML语句

select：

1、查询数据库实例名和实例状态

select instance_name,status from gv$instance;

2、查询数据库名和数据库状态

select name,open_mode from v$database;

3、查看数据库连接数信息

select INST_ID,count(*) from gv$instance group by inst_id;

4、查看用户名默认表空间和临时表空间

select  USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users;

5、查看用户拥有的角色

select * from session_roles;
6、查看用户权限 
```sql
  select PRIVILEGE from dba_sys_privs where GRANTEE='&username';

7、查询用户拥有的表

select TABLE_NAME , OWNER , TABLESPACE_NAME from dba_tables where OWNER='&username';

8、查询用户索引状态

select INDEX_NAME, INDEX_TYPE,TABLE_OWNER,TABLE_NAME,STATUS from dba_indexes where TABLE_OWNER in ('&username1','&username2') order by 3,4;

9、查询存储过程状态:

SQL> select OBJECT_NAME ,STATUS  from  dba_objects where  OBJECT_TYPE='PROCEDURE' ；

10、查看用户数据量

SQL> select sum(BYTES)/1024/1024 M ,TABLESPACE_NAME  from  dba_segments where OWNER='&username' group by TABLESPACE_NAME;

11、查询表空间大小

SQL>select tablespace_name,file_name,bytes/1024/1024 mb ,autoextensible from dba_data_files order by tablespace_name;

12、查询表空间使用情况

SELECT dts.tablespace_name, NVL(ddf.bytes / 1024 / 1024, 0) avail,
       NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024 used,
       NVL(dfs.bytes / 1024 / 1024, 0) free,
       TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00') "Used %"
FROM
dba_tablespaces dts,
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) ddf,
(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) dfs
WHERE dts.tablespace_name = ddf.tablespace_name(+)
AND dts.tablespace_name = dfs.tablespace_name(+)
AND NOT (dts.extent_management like 'LOCAL' AND dts.contents like 'TEMPORARY')
order by 5;

13、查看临时表空间数据文件剩余空间

SELECT TABLESPACE_NAME, FREE_SPACE/1024/1024 AS "FREE SPACE(M)"
  FROM DBA_TEMP_FREE_SPACE
 WHERE TABLESPACE_NAME = '&tablespace_name';

14、查看临时表空间数据文件大小

select tablespace_name,file_name,bytes/1024/1024 mb ,autoextensible from dba_temp_files;

15、查看数据文件存放路径，大小：

select tablespace_name,file_name,bytes/1024/1024 mb ,autoextensible from dba_data_files order by tablespace_name;

16、查看表空间是否为大文件表空间

select name ,bigfile from v$tablespace;

17、查看数据文件个数，数据文件个数如果超过db_file设置，添加数据文件将无法添加

show parameter db_files;
select count(*)  from  dba_data_files  where   tablespace_name='&tablespace_name';

18、查看asm磁盘组使用情况

 select name name2,state,type,free_mb,total_mb,usable_file_mb from v$asm_diskgroup;

19、ASM磁盘组大小及剩余空间

set lines 150 pages 200
col path for a40
col HEADER_STATUS for a9
col disk_name for a12
col type for a16
col bytes for 999,999,999,999
col name for a52
col name2 for a15
col group_number heading 'Group|_NO' format 99
col file_number heading 'FILE|NO' format 9999
col redundancy format a6 noprint
col striped format a6 noprint
col FAILGROUP for a10
col disk_number heading 'Disk|_NO' format 9999
col MOUNT_STATUS heading 'Mount|_Status'
col FAILGROUP heading 'FAIL|_GROUP'
col TOTAL_MB heading 'Total|_MB' format 99,999,999
select name name2,state,type,free_mb,total_mb,usable_file_mb from v$asm_diskgroup；

20、ASM使用磁盘信息

select GROUP_NUMBER,free_mb,total_mb,FAILGROUP,disk_number,
MOUNT_STATUS,mode_status,STATE,HEADER_STATUS,name name2,PATH from v$asm_disk order by 4,5;

21、查询asm磁盘组负载均衡状态

select *  from  v$asm_operation;

22、查看生成的sql执行计划：

SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
或
select * from table(dbms_xplan.display);

23、查看数据库内存分配情况

select component,current_size,min_size,max_size from v$memory_dynamic_components;

EXPLAIN PLAN :

1、生成sql执行计划

EXPLAIN PLAN for SQL语;

二、DDL

DDL(Data Definition Language)数据定义语言，包含以下操作：
CREATE
ALTER
DROP
TRUNCATE
COMMENT
RENAME
GRANT
REVOKE
PURGE
ANALYZE
ASSOCIATE STATISTICS
AUDIT
DISASSOCIATE STATISTICS
FLASHBACK
NOAUDIT

数据库运维最经常用到的 CREATE、ALTER、DROP，GRANT，REVOKE，PURGE；

CREATE：

1、创建数据表空间：

create tablespace $tablespace_name datafile '$datafile' size 31G;

2、创建临时表空间：

SQL> create temporary tablespace temp1 tempfile ‘/u01/app/oracle/oradata/orcl/temp11.dbf’ size 10M;

3、创建用户：

create user &user_name identified by &password default
tablespace &tablespace_name temporary tablespace &tmp_tablespace_name;

4、创建表：主要用于收集数据库信息

create table &tablename as select * from &tablename  where ... ;

5、创建磁盘组

CREATE DISKGROUP ARCH
EXTERNAL REDUNDANCY
DISK '/dev/rhdisk9';

ALTER：

1、修改用户密码：

alter    user    &username    identified   by    &password ;

2、解锁用户：

alter user scott account  unlock ;

3、修改用户默认表空间

alter user &username default tablespace &tablespace_name;

4、修改默认临时表空间

alter database default temporary tablespace temp1;

所有用户的默认临时表空间都将切换为新的临时表空间：
5、更改某一用户的临时表空间：

alter user &username  temporary tablespace &temp_tablespace_name;

6、直接修改数据文件大小说明：datafile关键字为数据表空间存放路径

 alter  database  datafile ' &datafile_name'  resize    xxx；
  alter database tempfile  ' &datafile_name'  resize    xxx；

7、数据表空间添加数据文件

  alter tablespace  &tablespace_name ADD datafile  '&datafile_name'    SIZE   xxx;
  alter tablespace "XXX"
 ADD
    DATAFILE '+DATA'  SIZE  表空间大小 REUSE AUTOEXTEND ON NEXT  扩展大小 MAXSIZE UNLIMITED,
            '+DATA''   SIZE  表空间大小  REUSE AUTOEXTEND ON NEXT  扩展大小 MAXSIZE UNLIMITED;

8.临时表空间添加数据文件

alter temporary tablespace $tablespace_name add tempfile '+DATA' size 2G;

9、ASM磁盘组扩容

alter diskgroup DATA add disk '/dev/rdhiskn'; 

10、删除裸盘

alter diskgroup DATA drop disk  DATA_0003;

11、设置reblance 参数：

alter diskgroup  DATA rebalance power  10；

12、dismount asm磁盘组

ALTER DISKGROUP ALL DISMOUNT;
alter diskgroup DATA dismount;

13、mount asm 磁盘组

ALTER DISKGROUP dgroup1 MOUNT;

14、取消drop操作

ALTER DISKGROUP dgroup1 UNDROP DISKS; 

15、设置asm磁盘组reblance参数

alter diskgroup  fra reblance power  10；

DROP：

1、删除表空间

drop tablespace &tablespace_name including contents ;

2、删除用户

drop user &username cascade;

3、删除表,主要用于删除一些数据库收集信息的临时表。

drop table  &table_name;

4、删除磁盘组

DROP DISKGROUP dgroup1;
DROP DISKGROUP dgroup1  including content;

PURGE:

1、清理数据库回收站

 purge dba_recyclebin;

2、清理某个用户的回整收站

 purge recyclebin;

3、清理回收站内指定表

purge table t1

4、清理回收站内的指定索引

 purge index &indexName;

5、删除指定表空间相关的回收站对象

purge tablespace & &tablespace_name 

6、删除指定表空间中指定用户的回收站对象

   purge TABLESPACE &tablespace_name USER &username; 

三、DCL

GRANT：

1、授权

grant &privs to  &username;

GRANT privilege[, ...] ON &username.&object[, ...] TO { PUBLIC | GROUP group| username}
 grant unlimited tablespace to  &username;

REVOKE：
1、回收用户权限：

revoke &privs from &username;