查看当前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)

根据表数据大小进行排序

-- 根据表的 num_rows 数据行进行降序排列，有的表没有进行统计，该结果可能会漏掉一些表
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;

-- 直接根据表占用空间大小来进行排序（这个查询找到大表更准确）
SELECT 
    owner AS "用户名",
    segment_name AS "表名",
    ROUND(SUM(bytes) / 1024 / 1024, 2) AS "大小(MB)"
FROM 
    dba_segments
WHERE 
    segment_type = 'TABLE' AND  owner != 'SYS' and segment_name  not like '%$%'
GROUP BY 
    owner, segment_name
ORDER BY 
    "大小(MB)" 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%';  

Q.E.D.