Oracle常用DDL、DML SQL语句

您所在的位置：网站首页 › oracle函数设计 › Oracle常用DDL、DML SQL语句

Oracle常用DDL、DML SQL语句

2023-12-10 02:27| 来源: 网络整理| 查看: 265

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;

【本文地址】

Oracle常用DDL、DML SQL语句

Oracle常用DDL、DML SQL语句

今日新闻

推荐新闻