–创建表空间
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’;