Oracle 常用SQL

查看当前oracle中正在执行的sql语句

-查看当前oracle中正在执行的sql语句
select a.program, b.spid, c.sql_text,c.SQL_ID
  from v$session a, v$process b, v$sqlarea c
 where a.paddr = b.addr
   and a.sql_hash_value = c.hash_value
   and a.username is not null;
 
--如果上面语句的sql_text列没有显示完整的sql语句,可根据sql_id再查询v$sql
select a.* from v$sql a where a.SQL_ID='9zsv4q14mq932';
————————————————
复制

去除空格、换行、回车符

--去除换行
update zhzl_address t set t.add_administration_num=replace(t.add_administration_num,chr(10),'');
--去掉回车
update zhzl_address t set t.add_administration_num=replace(t.add_administration_num,chr(13),'');
--去掉空格
update zhzl_address t set t.add_administration_num=trim(t.add_administration_num);
 
制表符 chr(9) 
换行符 chr(10) 
回车符 chr(13)
复制

根据表数据大小进行排序

select table_name,blocks,num_rows
from dba_tables
where owner not like '%SYS%' and table_name not like '%$%' and num_rows is not null
order by num_rows desc;
复制

批量删除Oracle数据库表

#生成  “删除 OWNER 为EAST的所有表” 的语句
SELECT 'DROP TABLE '|| TABLE_NAME||';' FROM ALL_TABLES WHERE OWNER = 'EAST' ORDER BY OWNER
复制

查询数据库编码

# AL32UTF8
select * from nls_database_parameters where parameter ='NLS_CHARACTERSET';
select userenv('language') from dual;
复制

创建用户

 /* 创建用户*/
  create user your_user_name 
  IDENTIFIED BY root --用户密码
  default tablespace TABLESPACE_QCJ-- 表空间是上面创建的
  temporary tablespace TEMP -- 临时表空间默认 TEMP
  profile DEFAULT;
复制

给用户授权

 /* 用户授权_1*/
  grant connect,resource,dba to your_user;
  
   /* 用户授权_2*/
  grant connect to your_user with admin option;
  grant dba to your_user with admin option;
  grant resource to your_user with admin option;
复制

查询多个表及其字段信息

SELECT
	C.TABLE_NAME,
	T.COMMENTS AS TABLE_COMMENT,
	C.COLUMN_NAME,
	C.DATA_TYPE,
	C.DATA_LENGTH,
	C.CHAR_LENGTH,
	C.DATA_PRECISION,
	C.DATA_SCALE,
	C.NULLABLE,
	COM.COMMENTS 
FROM
	"SYS"."ALL_TAB_COLS" C,
	"SYS"."ALL_COL_COMMENTS" COM,
	"SYS"."ALL_TAB_COMMENTS" T 
WHERE
	COM.OWNER ( + ) = C.OWNER 
	AND T.OWNER ( + ) = C.OWNER 
	AND COM.TABLE_NAME ( + ) = C.TABLE_NAME 
	AND COM.COLUMN_NAME ( + ) = C.COLUMN_NAME 
	AND T.TABLE_NAME ( + ) = C.TABLE_NAME 
	AND C.USER_GENERATED = 'YES' 
	AND C.OWNER = 'ZML' 
	AND C.TABLE_NAME = 'TABLE_NAME' 
ORDER BY
	C.TABLE_NAME,
	C.COLUMN_ID ASC;
复制

解锁锁定用户

   ALTER USER YOUR_USER_NAME ACCOUNT UNLOCK;
复制

修改用户密码

ALTER USER 用户名 IDENTIFIED BY 新密码;  
复制

例如，要将用户 scott 的密码修改为 tiger，则执行：

ALTER USER scott IDENTIFIED BY tiger; 
复制

需要注意的是，执行此语句需要具有 ALTER USER 权限或者以具有 DBA 角色的用户身份登录。

查询数据库版本号

-- 查询所有的版本号
SELECT * FROM v$version;
-- 只查询 oracle 数据库的版本号
SELECT banner FROM v$version WHERE banner LIKE 'Oracle%';  
复制