卸载

1. 停止Oracle所有相关服务。
2. Oracle Universal Installer卸载产品页面，卸载产品-全部展开，OraDb11g_home1外的全部目录删除。
3. 删除注册表信息： HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE。 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services下的所有ORA开头的。 HKEY_LOCAL_MACHINE\SYSTEM\Cur​rentControlSet\Services\Eventlog\Application下的所有ORA开头的 HKEY_CLASSES_ROOT下所有ORA、ORCL等相关的。 HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\MenuOrder\Start Menu\Programs下ORA开头的。 HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI中除Microsoft ODBC for Oracle注册表键以外的所有含有Oracle的键。
4. 删除环境变量中有关Oracle的设定。
5. 删除所有与Oracle相关的目录(如果删不掉，重启计算机后再删就可以了)包括： C:\Program file\Oracle目录。 ORACLE_BASE目录(oracle的安装目录)。 C:\WINDOWS\system32\config\systemprofile\Oracle目录。 C:\Users\Administrator\Oracle或C:\Documents and Settings\Administrator\Oracle目录。 C:\WINDOWS下删除以下文件ORACLE.INI、oradim73.INI、oradim80.INI、oraodbc.ini等等。 C:\WINDOWS下的WIN.INI文件中若有[ORACLE]的标记段，删除该段。

DDL

表空间

• 创建临时表空间 create temporary tablespace tablespace_name tempfile 'C:\...' size 50m autoextend on next 50m maxsize 20480m extent management local
• 创建表空间 create tablespace tablespace_name datafile 'C:\' size 50m autoextend on next 50m maxsize 2048m extent management local
• 删除表空间 drop tablespace tablespace_name including contents and datafiles
• 修改表空间名 alter tablespace tablespace_name1 rename to tablespace_name2

用户

• 创建用户 create user user_name identified by password default tablespace JC_DATA temporary tablespace JC_TEMP
• 创建和system相同表空间的用户 create user bqft identified by crmoracle default tablespace SYSTEM temporary tablespace TEMP profile DEFAULT
• 删除用户 drop user user_name [cascade]
• 解锁用户 alter user user_name account unlock
• 修改用户密码 alter user old_password identified by new_passord
• 修改用户表空间 alter user user_name defalut tablespace tablespace_name
• 修改用户表空间限额 alter user user_name quota unlimited on tablespace_name

角色

• 创建角色 create role role_name
• 删除角色 drop role role_name

表

• 修改表名 rename table_old to table_new
• 创建表 create table table_name ( id int primary key not null, name varchar(32) )
• 删除表 drop table table_name
• 修改表名 alter table table_name rename table_name_new
• 添加字段 alter table table_name add column_name column_type
• 修改字段名 alter table table_name rename column column_name to column_name_new
• 修改字段类型 alter table table_name modify column_name coumn_type
• 删除字段 alter table table_name drop column column_name
• 添加注释 comment on column/table is ''
• 展示表 show table
• 查询表结构 desc table_name
• 内部写法表关联 a foreign key (cid) references (id)
• 外部写法表关联 alter table table_name1 add constraint fk_name foreign key(column_name1) references table_name2(column_name_2)
• 新增主键约束 alter table ECC_FND.RESOURCE_TYPE_CFG add constraint P_RESOURCE_TYPE_CFG primary key (RESOURCE_CODE, RSRC_LOOKUP_TYPE, RSRC_LOOKUP_CODE, REGISTER_ID) using index tablespace ECC_FND pctfree 10 initrans 2 maxtrans 255 storage ( initial 448K next 128K minextents 1 maxextents unlimited pctincrease 0 )
• 删除约束 alter table table_name drop constraint constraint_name

同义词

• 创建 CREATE SYNONYM T_YCPORDER_FLOW FOR TELECOM.T_YCPORDER_FLOW ###视图
• 创建 create [or replace] view view_name[(a, b, c)] as select ...
• 删除 drop view veiw_name
• 创建物化视图 create [or replace] materialized view view_name refresh force on demand start with sysdate next to_date(concat(to_char(sysdate +1, 'yyyy-mm-dd'), '10:25:00'), 'yyyy-mm-dd hh24:mi:ss')) as select * from table_name

序列

• 创建 create sequence sequence_name minvalue 1 maxvalue 9999999 increment by 1 start with 1 nocahe noorder nocycle
• 删除 drop sequence sequence_name
• 修改
  • 查询序列接下来的值 select sequence_name.nextval from dual
  • 修改序列增量 alter sequence sequence_name increment by 80
  • 再次查询 select sequence_name.nextval from dual
  • 还原序列增量 alter sequence sequence_name increment by 1
  • 再次查询 select sequence_name.nextval from dual

索引

• 用途
  • 提高对表select速度
  • 降低insert,update,delete速度
  • 对有关联的字段取值进行检查
  • 索引可以为空
• 创建 CREATE INDEX IDX_TZMS_CD ON T_ZNFX_MRZJ_SNAP(CREATE_DATE)
• 修改 alter [unique] index [user.]index_name [initrans n] [maxtrans n] rebuild [storage n]
• 删除 drop index index_name

目录

• 创建 create directory directory_name as 'file_address'

DML

• 新增 insert into tabe_name （...,...,...）values (...,...,...);
• 更新 update tabe_name set table_column='...' where
• 删除 delete from tabe_name where ...
• 清空 truncate table table_name
• 融合 * merge into table_name t1 using table_name t2 on (ti.id = t2.id) when matched then update set t1.column1 = t2.column1, t1.column2 = t2.column2 when not matched then insert (t1.column1, t1.column2 ) values (t2.column1， t2.column2)

DQL

• 查询 select * from table_name
• 去重 select distinct table_column from table_name
• 大小 select table_column from tabe_name where table_column = '' = <> > < >= <= between in
• 逻辑 select table_column from tabe_name where table_column1 = '' and table_column2 = '' and or
• 排序 select table_column from tabe_name order by table_column（desc）
• 条数 select column_name from table_name where rownum <= number
• 模糊 select * number from table_name where table_column like 'a%' -- 通配符 字符：[] [!] 数量：% _
• 递归 * select t.subid, t.parentid from table_name t start with t.subid = '1' connect by prior t.subid = t.parentid

多表操作

• 别名 select column_name as cn number from table_name as tn
• 多表关联 select number from table_name as tn1 join table_name as tn2 on t1.column = t2.column -- left join | right join | full join | inner join
• 合并结果 * select * from table_name tn1 where ... union (all) select * from table_name tn2 where ...
• 查询插入 select into table_name_new from table_name_old

约束

• 查询约束 select TABLE_NAME from all_constraints where CONSTRAINT_NAME='Reference_460'

用户

• 查询用户下的表 select * from user_tablesselect * from dba_tables dt where dt.owner='owner_name'
• 查询用户下的索引 select * from user_indexsselect * from dba_indexes di where di.owner = 'owner_name'
• 查询用户下的视图 select * from user_viewsselect * from dba_views dv where dv.owner = 'owner_name'
• 查询用户下的约束 select * from user_constraints

DCL

• 登陆权限 create session
• 使用表空间 unlimited tablespace
• 创建表 create table
• 删除表 drop table
• 插入字段 insert table
• 修改表 update table
• 所有权限 all
• 角色授予用户 grant role_name to user_name;
• 权限授予用户/角色 grant create session to {use_name/role_name};
• 操作特定表的权限 grant select on table_name to {use_name/role_name};
• 权限从用户撤销 revoke create session to {use_name/role_name};

存储过程

输出

• 输出语句 dbms_output.put_line('Hello World');
• 设置输出打印 set serveroutput on;

创建存储过程

create or replace procedure test_procedure is
begin
  dbms_output.put_line('测试存储过程');
end test_procedure;
参数、变量
create or replace proceduce proceduce_name (param1 in type, param2 out type) -- 参数不需要取值范围，in传入，out传出
as --定义变量
vs_msg varchar2(512);
vs_ym_begin char(6);
vs_ym_end char(6);
vs_ym_sn_begin char(6);
vs_ym_sn_end char(6);

if判断

create or replace procedure test(x in number) is
begin
  if x > 0 then
    begin
      x := 0 - x;
    end;
  end if;
  if x = 0 then
    begin
      x := 1;
    end;
  end if;
end test;

for循环

• 遍历游标

create or replace proceduce test()
as
  Cursor cursor is
    select
    from student;
  name varchar(20);
begin
  for name in cursor LOOP
    begin
      dbms_output.putline(name);
    end;
  end LOOP;
end test;

• 遍历数组

create or replace proceduce test(varArray in myPackage, TestArray)
as
  i number;
begin
  i := 1;
  for i in i.varArray.count LOOP
    dbms_output.putline('The No.'||i||'reoord in varArray is:'||varArray(i));
  end LOOP;
end test;
while循环
create or replace proceduce test(i in number) as
begin
  while i < 0 LOOP
    begin
      i := i + 1;
    end;
  end LOOP;
end test;
CASE

定义数组

-- Oracle自带的数组类型
create or replace procedure test(y out array) is
x array;
begin
  x := new array();
  y := x;
end test;
-- 自定义数组类型
create or replace package myPackage is --创建包
Public type declarations info is record( name varchar(20), y number ); --type声明了两个字段
type TestArray is table of info index by binary_integer; --TestArray是一张表，一条记录就是type,index by binary_integer是表的索引。没有索引时，使用需要初始化：varArray := new myPackage.TestArray();
end TestArray;
-- 生命数组
DECLARE
  TYPE V_ARRY_TYPE IS VARRAY(2) OF VARCHAR2(10);
  V_ARRY_NAME V_ARRY_TYPE;
BEGIN
  V_ARRY_NAME := V_ARRY_TYPE('tom', 'jim','tim');

  DBMS_OUTPUT.PUT_LINE(V_ARRY_NAME(1));
  DBMS_OUTPUT.PUT_LINE(V_ARRY_NAME(2));
END;

游标

declare
  -- 声明静态游标
  cursor c_test is select id,name from t_user t where  t.id =id ;
  c_t c_test%rowtype; --定义游标变量，该变量的类型为基于游标c_test的记录
-- 声明动态游标
  type my_cur_type is ref cursor;
  my_sql long;
  my_cur my_cur_type;
  my_obj my_cur%rowtype;
begin
--for 循环
  for c_t in c_test
  loop
    dbms_output.put_line(c_t.id|| '-1-' || c_t.name);
  end loop;
--while 循环
  open c_test;--必须要明确的打开游标
    fetch c_test into c_t; while c_test%found
    loop
      dbms_output.put_line(c_t.id|| '-3-' || c_t.name);
      fetch c_test into c_t;
    end loop;
  close c_test;   --必须要明确的关闭游标
--fetch 循环
  open c_test;--必须要明确的打开游标
  loop
    fetch c_test into c_t;
    exit when c_test%notfound;
    dbms_output.put_line(c_t.id|| '-2-' || c_t.name);  --必须要明确的关闭游标
  end loop;
-- 执行动态sql
  execute immediate v_sql into v_data;
close c_test;

执行存储过程

execute test_procedure;

删除存储过程

drop procedure test_procedure;

查询存储过程

-- 查询所有存储过程
select
  name
from
  user_source
where
  type = 'PROCEDURE';
-- 查询存储过程内容
select
  text
from
  user_source
where
  name = 'XXX'

函数

round(1234.3565, 2) -- 四舍五入保留精度
trunc() -- 截取字符
to_char() -- 转字符串
trim() -- 去除空格
EXISTS() NOT EXISTS() -- 判断是否存在
decode(value,'a','b''c',value) -- 如果value==a,return a;如果value==b,return b;...;else return value。
over(order by salary) -- 按照salary顺序排序
over(partition by deptno) -- 安装部门分区
row_number() OVER(PARTITION BY fldphy ORDER BY flddate desc) as row_flg -- 分组设置编号

定时

• 查询 SELECT * FROM ALL_JOBS WHERE LOWER(WHAT) LIKE '%PRO_USER_ACCESS%';
• 新增

DECLARE JOB_USER_ACCESSNUMBER;
BEGIN
DBMS_JOB.SUBMIT(JOB_USER_ACCESS,'PRO_USER_ACCESS;',SYSDATE+14/24,'TRUNC(SYSDATE+1)');
COMMIT;
END;

• 删除

BEGIN
DBMS_JOB.REMOVE(696391);
COMMIT;
END;

常用遇到的情况解决

DBLink

tnsnames.ora中配置数据库连接 SELECT * FROM ALL_DB_LINKS; select * from dba_db_links;

闪回

-- 更新删除操作的闪回查询
SELECT
  *
FROM
  ecc_oc.order_header
as of timestamp
  to_timestamp('2017-08-14 16:41:00', 'yyyy-mm-dd hh24:mi:ss')
minus  -- 取交集
select
  *
from
  ecc_oc.order_header
-- 插入操作的闪回查询
select
  *
from
  ecc_oc.order_header
minus
select
  *
from
  ecc_oc.order_header
as of timestamp
  to_timestamp('2017-08-14 16:41:00', 'yyyy-mm-dd hh24:mi:ss')
-- 更新删除操作的闪回
MERGE INTO
  tab a
USING
  (
    SELECT
      *
    FROM
      tab
    AS OF TIMESTAMP
      to_timestamp('time_point', 'yyyy-mm-dd hh24:mi:ss')
    MINUS
    SELECT
      *
    FROM
      tab
  ) b
ON
  (a.unique_id = b.unique_id)
WHEN MATCHED THEN
  UPDATE
  SET
    a.col1 = b.col1,
    a.col2 = b.col2,
WHEN NOT MATCHED THEN
  INSERT
  VALUES
  (
    b.unique_id,
    b.col1,
    b.col2
  );

用户锁死

-- 查询锁死语句的相关信息
select
  vs."USERNAME",  -- 锁死语句所用的数据库用户
  vs."LOCKWAIT",  -- 锁死状态
  vs."STATUS",  -- 状态，active表示被锁死
  vs."MACHINE",  -- 锁死语句所在机器
  vs."PROGRAM"  -- 锁死语句应用来源
from
  v$session vs
where
  vs."SID" in
  (
    select
      session_id
    from
      v$locked_object
  );
-- 查询锁死的语句 (dba用户登录)
select
 vsql."SQL_TEXT"
from
  v$sql vsql
where
  vsql."HASH_VALUE" in
  (
    select
      vsession."SQL_HASH_VALUE"
    from
      v$session vsession
    where
      vsession."SID" in
      (
        select
          vl."SESSION_ID"
        from
          v$locked_object vl
      )
  );
-- 查询锁定时间
select
  username,
  lock_date
from
  dba_users
where
  username='TEST';
-- 查询允许失败次数
select
  *
from
  dba_profiles;
-- 修改允许失败次数
alter profile default limit FAILED_LOGIN_ATTEMPTS 30;
-- 解锁用户
alter user
  user_name
account unlock;
用户表锁死
-- 查看被锁的表
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
-- 查看哪个用户或进程造成的锁表
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
-- 杀死进程
alter system kill session '651,11757';
--查看连接的进程
sELECT sid, serial#, username, osuser FROM v$session;

oracle版本

select * from v$version;

测试连接

tnping tnsname

修改错误次数限制

alter profile default limit FAILED_LOGIN_ATTEMPTS number|unlimited; -- 次数|无限制

分组去重去第一条

SELECT *
FROM
  (
    SELECT ROW_NUMBER() OVER(PARTITION BY fi.account_no ORDER BY fi.fldid DESC) rn, fi.*
    FROM telecom.t_fix_importaccount fi
    where fi.account_no is not null
  )
WHERE rn = 1;

存储过程中dblink效率慢

表分区查询 3123548 * 367 = 1146342116 SELECT COUNT(1) from user_tab_partitions where table_name='IPTV_ZTE_DATA SELECT t.partition_name,t.num_rows from user_tab_partitions t where table_name='IPTV_ZTE_DATA' select COUNT(1) from IPTV_ZTE_DATA PARTITION (SYS_P1146)

常用函数

-- md5加密函数
create or replace function md5(in_src in varchar2) return varchar2 is
retval varchar2(128);
begin
  retval := convert(in_src,'ZHS16GBK');
  retval := convert(retval,'UTF8');
  retval := Utl_Raw.Cast_To_Raw(sys.dbms_obfuscation_toolkit.md5(INPUT_STRING  => retval));
RETURN upper(retval);
end md5;
-- 按中文首字母排序
nlssort(worker,'NLS_SORT=SCHINESE_PINYIN_M')
-- 特殊字符
select ascii('&') from dual
Nvl() -- 空值判断
常用表、视图
dba_users -- 用户信息

去除表中重复数据

DELETE
FROM T_FIX_COMMUNICATION_GZLTJINFO
WHERE (RID) IN (
  SELECT RID FROM T_FIX_COMMUNICATION_GZLTJINFO WHERE FLDYEAR = '2019' AND FLDMONTH = '01' GROUP BY RID HAVING COUNT(RID) > 1
)
AND ROWID NOT IN (SELECT MIN(ROWID) FROM T_FIX_COMMUNICATION_GZLTJINFO WHERE FLDYEAR = '2019' AND FLDMONTH = '01' GROUP BY RID HAVING COUNT(*) > 1);

并行

/*+parallel(T,8)*/

文件导出

CREATE OR REPLACE DIRECTORY MYDIR AS 'D:\test';
DECLARE
FILE_NAME VARCHAR2(32) := '装机单';
COUNT_NUMBER NUMBER; -- 定义总量，接收每次根据组织名称查询时 RYXX 表中匹配的数据总量
LOOP_TIMES NUMBER; -- 定义每个组织名称关联的数据需要循环次数（因为每次导出只能导出30000条数据，需要多次导出）
LOOP_I NUMBER; -- 定义当前循环到第几次（同上）
DATA_CUR SYS_REFCURSOR; -- 定义根据 ORG_NAME 匹配查询出的具体数据，为SYS_REFCURSOR类型，即动态游标
COUNT_CUR SYS_REFCURSOR; -- 定义匹配查询出的具体数据的总量（同上）
CSV_OUTPUT UTL_FILE.FILE_TYPE; -- 定义文件输出
DATA_ROW T_FIX_COMMUNICATION%ROWTYPE; -- 定义 RYXX 的行类型
MAX_LINE NUMBER := 30000; -- 每个文件导出的最大行数
DIR VARCHAR(20) := 'MYDIR'; -- 输出位置
BEGIN
  -- 查询数据总量
  OPEN COUNT_CUR FOR
  'SELECT COUNT(1) ' ||
  'FROM T_FIX_COMMUNICATION TFC ' ||
  'WHERE TFC.COMPLETE_DATE BETWEEN TO_DATE(''2018-06-16 00:00:00'', ''YYYY-MM-DD HH24:MI:SS'') AND TO_DATE(''2018-07-15 23:59:59'', ''YYYY-MM-DD HH24:MI:SS'') ' ||
  'AND TFC.SUB_ORDER_STATUS = ''已完成'' '||
  'AND TFC.BUSI_TYPE_NAME IN (''新增用户'', ''外移-装'') ORDER BY COMPLETE_DATE DESC';
  FETCH COUNT_CUR INTO COUNT_NUMBER;
  CLOSE COUNT_CUR;

  -- 计算此单位的数据总共需要导出几次
  LOOP_TIMES := COUNT_NUMBER/MAX_LINE;

  -- 开始循环导出数据
  LOOP_I := 0;

  LOOP
    EXIT WHEN LOOP_I > LOOP_TIMES;
    IF LOOP_I <= LOOP_TIMES THEN
      -- 打开查询数据的DATA_CUR游标，导出数据（需要分页查询，所以外层不能直接用*，否则不能把数据放入DATA_ROW）
      OPEN DATA_CUR FOR --'SELECT id, name, age FROM (SELECT t.*, rownum rn FROM ryxx WHERE ST_CODE_NAME LIKE :org_name_like) WHERE rn <= ' || TO_CHAR((LOOP_I) * MAX_LINE) || ' AND rn > ' || TO_CHAR(LOOP_I * MAX_LINE) USING ORG_NAME_LIKE;
      'SELECT * FROM ( ' ||
        'SELECT TAL.ACCOUNT, TFC.*, ROWNUM AS RN ' ||
        'FROM T_FIX_COMMUNICATION TFC ' ||
        'LEFT JOIN TELECOM.T_ACCOUNT_LIST TAL ON TFC.ACCOUNT_ID = TAL.ACCOUNTID ' ||
        'WHERE TFC.COMPLETE_DATE BETWEEN TO_DATE(''2018-06-16 00:00:00'', ''YYYY-MM-DD HH24:MI:SS'') AND TO_DATE(''2018-07-15 23:59:59'', ''YYYY-MM-DD HH24:MI:SS'') ' ||
        'AND TFC.SUB_ORDER_STATUS = ''已完成'' ' ||
        'AND TFC.BUSI_TYPE_NAME IN (''新增用户'', ''外移-装'') ORDER BY COMPLETE_DATE DESC ' ||
      ') WHERE RN <= ' || TO_CHAR((LOOP_I) * MAX_LINE) || ' AND RN > ' || TO_CHAR(LOOP_I * MAX_LINE);

      CSV_OUTPUT := UTL_FILE.FOPEN('MYDIR', FILE_NAME || LOOP_I || '.csv', 'W', MAX_LINE);
      LOOP
        FETCH DATA_CUR INTO DATA_ROW;
        EXIT WHEN DATA_CUR%NOTFOUND;
        UTL_FILE.PUT_LINE(CSV_OUTPUT, DATA_ROW.RID || ',' || DATA_ROW.ORDER_NUMBER || ',' || DATA_ROW.ORDER_VERSION);
      END LOOP;
      LOOP_I := LOOP_I + 1;
      UTL_FILE.FCLOSE(CSV_OUTPUT);
      CLOSE DATA_CUR;
    END IF;
  END LOOP;
END;

空位0补全

REPLACE(LPAD(SUBSTR(ACCOUNT_NO, 2, 3) + 1，3), ' ', '0')

转义

& ' || chr(38) || '

触发器

CREATE OR REPLACE TRIGGER TEST_TRIGGER
 AFTER DELETE OR INSERT OR UPDATE ON TEST
DECLARE
 V_TYPE TEST_LOG.L_TYPE%TYPE;
BEGIN
 IF INSERTING THEN
  --INSERT触发
  V_TYPE := 'INSERT';
  DBMS_OUTPUT.PUT_LINE('记录已经成功插入，并已记录到日志');

 ELSIF UPDATING THEN
  --UPDATE触发
  V_TYPE := 'UPDATE';
  DBMS_OUTPUT.PUT_LINE('记录已经成功更新，并已记录到日志');

 ELSIF DELETING THEN
  --DELETE触发
  V_TYPE := 'DELETE';
  DBMS_OUTPUT.PUT_LINE('记录已经成功删除，并已记录到日志');

 END IF;
END;

注释

SELECT * FROM USER_TAB_COMMENTS;

BLOB 字符串 转换

TO_BLOB(UTL_RAW.CAST_TO_RAW(''))
utl_raw.cast_to_varchar2('')
hextoraw('')
rawtohex('')
-- 拼接
create or replace function blob_join(i_blob1 in blob, i_blob2 in blob) return blob is
v_rt blob := empty_blob();
begin
dbms_lob.createtemporary(v_rt, TRUE);  -- 分配临时的 blob .
dbms_lob.append(v_rt, i_blob1);        -- 拼接 i_blob1 .
dbms_lob.append(v_rt, i_blob2);        -- 拼接 i_blob2 .
return v_rt;
end;

XMLTYPE字段

-- 查询
SELECT EXTRACTVALUE(VALUE(I),'/string') AS GLPSZ
FROM I_TKJT_XXJS_XMGLBGS_ITXMJXPFB T1, TABLE(XMLSEQUENCE(EXTRACT(T1.GLPSZ,'/ArrayOfString/string'))) I
WHERE T1.OBJECTID = #{params.OBJECTID}

保留一位小数

DECODE(TO_CHAR(ROUND(T4.FINISHTIME - E.CREATEDTIME,2),'FM99999999999990.0')

分组聚合

LISTAGG(TO_CHAR(IZ.ZZDJJWT),',') within GROUP (ORDER BY IZ.PARENTOBJECTID) AS ZZDJJWT

表空间调整

-- 查看表空间使用情况
SELECT
B.FILE_ID                                 文件ID号,
B.TABLESPACE_NAME                         表空间名,
B.BYTES                                 字节数,
(B.BYTES-SUM(NVL(A.BYTES,0)))                 已使用,
SUM(NVL(A.BYTES,0))                         剩余空间,
SUM(NVL(A.BYTES,0))/(B.BYTES)*100         剩余百分比
FROM DBA_FREE_SPACE A,DBA_DATA_FILES B
WHERE A.FILE_ID=B.FILE_ID"田胜"
GROUP BY B.TABLESPACE_NAME,B.FILE_ID,B.BYTES
ORDER BY B.FILE_ID;


-- 查看表空间的名字及文件所在位置
SELECT
TABLESPACE_NAME,
FILE_ID,
FILE_NAME,
ROUND(BYTES / (1024 * 1024), 0) TOTAL_SPACE
FROM SYS.DBA_DATA_FILES
ORDER BY TABLESPACE_NAME;

-- 扩展表空间
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/XE/tkbpm.dbf' RESIZE 4048M;

链接数调整

-- 数据库允许的最大连接数
select value from v$parameter where name ='processes';

-- 查看当前的数据库连接数
select count(*) from v$process;

-- 查看连接消耗情况
SELECT  B.MACHINE, B.PROGRAM, B.USERNAME, COUNT(*) FROM V$PROCESS A, V$SESSION B
WHERE A.ADDR = B.PADDR AND  B.USERNAME IS NOT NULL
GROUP BY  B.MACHINE, B.PROGRAM, B.USERNAME
ORDER BY COUNT(*) DESC;