Oracle数据库常用查询整理

创建表空间
create tablespace TailThermalImaging datafile 'F:\hzy\oracle\data\data_1.dbf' size 2000M;

给该表空间创建用户
create user tf_hzy2020 identified by calcular default tablespace TailThermalImaging;

给用户授权
grant connect,resource to tf_hzy2020;
grant dba to tf_hzy2020;

查看表空间是否开启了自动扩展的功能 ：
SELECT T.TABLESPACE_NAME,
D.FILE_NAME,
D.AUTOEXTENSIBLE,
D.BYTES,
D.MAXBYTES,
D.STATUS
FROM DBA_TABLESPACES T, DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME, FILE_NAME;

开启/关闭表空间自动扩展：
alter database datafile '对应的数据文件路径信息' autoextend on;（开启）
alter database datafile '对应的数据文件路径信息' autoextend off;（关闭）

关闭“180”天密码过期
1.)查看用户的proifle是哪个，一般是default：
SELECT username,PROFILE FROM dba_users;
2.)查看指定概要文件（如default）的密码有效期设置：
SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
3.)将密码有效期由默认的180天修改成“无限制”：
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
修改之后不需要重启动数据库，会立即生效。

查看表空间使用情况
select a.tablespace_name,
total,
free,
total - free as used,
substr(free / total * 100, 1, 5) as "FREE%",
substr((total - free) / total * 100, 1, 5) as "USED%"
from (select tablespace_name, sum(bytes) / 1024 / 1024 as total
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) / 1024 / 1024 as free
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name;

查看表空间文件存储位置
select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name

收缩表空间（由于数据顺序分布可能无规律，所以一点一点缩小）
alter database datafile 'C:\APP\EASYF\ORADATA\TF_HZY2020.DBF' resize 820m

查看数据库表空间位置及空间容量大小
select * from dba_data_files

为表空间自动扩容（最大值时无法扩容）
alter database datafile 'C:\APP\EASYF\ORADATA\TF_HZY2020.DBF' autoextend on next 50M maxsize unlimited

为表空间添加扩充文件
ALTER TABLESPACE "TF_HZY2020" ADD DATAFILE 'C:\APP\EASYF\ORADATA\TF_HZY2021.DBF' SIZE 500M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;

测试数据库配置是否联通
tnsping ORCL164

Ora文件配置

# tnsnames.ora Network Configuration File: D:\app\zhupengcheng\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORACLE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = LAPTOP-65GSO67H)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.211)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oracle)
    )
  )
  
ORCL164 =
(DESCRIPTION =
(ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.113)(PORT = 1521))
)
(CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = orcl)
)
)

Oracle设置主键自增

create sequence YWT_Appointment_Id
increment by 1
start with 1
nomaxvalue
nocache;

create trigger YWT_Appointment_tg_InsertId before 
insert on YWT_Appointment for each row  
begin
select YWT_Appointment_Id.nextval into:new.id from dual;
end;

创建MD5加密存储过程

CREATE OR REPLACE FUNCTION MD5(passwd IN VARCHAR2)
RETURN VARCHAR2
IS
retval varchar2(32);
BEGIN
retval := utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => passwd)) ;
RETURN retval;
END;