ORCL 系统相关

conn gal/key@orcl as sysdba
连接 conn sys/asmin as sysdba
断开db shutdown immediate
启动实例 startup nomount
读取control 文件 进行控制 alter database mount
alter database open 
instance
memory structure
background process

database
data file
log file
redo file

查看必须的后台程序
select * from v$bgprocess where paddr<>'00';


查看文件 select * from v$controlfile;

V$datafile
V$logfile

CREATE TABLESPACE "GalSpace" DATAFILE 'D:\Datafile\GalSpace.DBF' SIZE 10M AUTOEXTEND ON NEXT 1K MAXSIZE UNLIMITED DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
alter tablespace oldname rename to new name
alter tablespace aa read only
修改表空间数据文件 alter datebase datafile ‘d：/s.dbf ' resize 30m;
create user gal identified by password default tablespace t_bsnaemn temporary temp;
角色赋值 grant dba to galuname;
alter tablespace aa online/offline
create bigfile tablespace aa datafile filename size <=128t
drop tablespace aa including contents and datafile(删除表空间的同时  删除数据文件）
alterdatabase default temporary tablespace name

create temporary tablespace name

alter tablespace aa



Oracle表移动表空间：
alter table tb_name move tablespace tbs_name;
使用上面语句对表做空间迁移时，只能移动非lob字段以外的数据，如果要同时移动lob字段数据，必需改用下面的语句才行：
alter table tb_name move tablespace tbs_name lob (col_lob1,col_lob2) store as(tablesapce tbs_name);
建立表空间，赋予表空间权限。
create tablespace tablespace_lob datafile 'd:/xxx/lob.dbf' size 100M autoextend on;//所有表的lob字段都用一个专用的lob表空间，导出导入数据时方便。
alter user bbb quota unlimited on tablespace_lob;//给bbb添加表空间权限。

 create or replace procedure galpro is Warning: Procedure created with compilation errors
  2  cursor cur is select * from scott.emp where depno=20;
  3  begin
  4  for i in cur loop ORA-06550: 第 1 行, 第 11 列:
  5  dbms_output.put_line(i.ename) PLS-00905: 对象 SYS.GALOUTPUT 无效
  6  end loop; ORA-06550: 第 1 行, 第 7 列:
  7  end galpro; PL/SQL: Statement ignored



create or replace procedure galoutput(v number ,name varchar(200)) is
  2  begin
  3  dbms_output.put_line(v||','||name);
  4  end galoutput;


A create or replace procedure galempname(v_deptno number) is
  2  cursor cur is select * from scott.emp where deptno=v_deptno;
  3  begin
  4  for i in cur loop
  5  dbms_output.put_line(i.ename)
  6  end loop;
  7  end galpro;

B create or replace procedure galempname(v_deptno number)  is
  2  cursor cur(v_d number) is select * from scott.emp where deptno=v_d;
  3  begin
  4  for i in cur(v_deptno) loop
  5  dbms_output.put_line(i.ename)
  6  end loop;
  7  end galpro;
 
A=B


FUNCTON
create or replace function galgettax(v_deptno number)

return number is
v_sal number,tax number
begin
 
select sal into v_sal from scott.emp where deptno=v_deptno;
tax:=v_sal*0.08 -- 税率
return tax;
end galgettax;






查看proc  func  内容    as  ms sql   sp_helptext
select * from user_procedures;
select * from user_source;
where name='SSSS'
                                       
查看constr 

select * from user_constraints where table_name='SSS'    

show serveroutput

set serveroutput on

查看proc  func  内容    as  ms sql   sp_helptext
select * from user_source where name='SSS' order by line;
select * from ALL_source where name='SSS' order by line;

查看错误proc 细节

SHOW ERRORS PROCEDURE PROCEDURE_NAME;


使用默认参数的proc
CREATE PROCEDURE GAL(NAME IN VARCHAR2 DEFAULT DEFT())

usage EXEC GAL
EXEC GAL('DFAF ')


查询用户名
SELECT * FROM DBA_USERS

查看用户权限

SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='';


查看序列
select * from user_sequences
查看trigger
select trigger_name,trigger_body from user_triggers


删除用户下所有 表  触发器 seq
select 'drop table '||table_name||' cascade constraints;' from user_tables;
select 'drop trigger ' || trigger_name || ';'  from user_triggers;
select 'drop sequence ' || sequence_name || ';'  from user_sequences;

select 'alter sequence '||sequence_name||' maxvalue 1300000000 cycle nocache;' from user_sequences where
sequence_name not like 'SEQ%' and sequence_name not like 'A20%' and sequence_name not like 'F51%';