Oracle的功能性sql

--创建表空间

create tablespace fsnew
datafile 'e:\oracle\oracledata\oradata\fsnew' size 30g
extent management local
uniform size 1m;

--查看当前用户所有表

select * from user_tables;

--查看所有用户及其表空间

select   username,default_tablespace from   dba_users ;
select * from all_users;

--删除用户及其所有配置

drop user zjh cascade;  

--修改用户的表空间

alter user fsnew default tablespace fsnew;

--删除表空间

drop tablespace fsnew including contents and datafiles cascade constraint;

--修改表空间状态

alter tablespace fsnew online;
select * from dba_tablespaces;
select name from v$datafile;
alter tablespace fsnew rename datafile 'e:\oracledata\fsnew' to 'c:\oracle\product\10.2.0\oradata\xxx';
alter database rename file 'c:\oracle\product\10.2.0\oradata\orcl\system01.dbf' to 'e:\oracledata\orcl\system01.dbf';

 

create user case  identified by case default tablespace xxx;

grant resource,dba,connect to case ;

 

grant alter any table to case with admin option;
grant create session to case with admin option;
grant delete any table to case with admin option;
grant select any table to case with admin option;
grant unlimited tablespace to case with admin option;
grant update any table to case with admin option;

 

select 'create or replace  synonym ' || synonym_name || ' for fsnew.' || table_name || ';'
  from user_synonyms;
 

--create or replace  synonym t_cz_fax for fsnew.t_cz_fax;

 

 

select * from t_srhs_hsxtcs t for update;

select banner||':'||(select utl_inaddr.get_host_name() from dual) version from v$version where rownum=1

 

--解决锁表
select l.session_id sid,
   s.serial#
from v$locked_object l, all_objects o, v$session s
where l.object_id = o.object_id
   and l.session_id = s.sid
order by sid, s.serial# ;

alter system kill session '128,3778';
alter system kill session '153,2841';