--創建表空間 CREATE TABLESPACE FSNEW DATAFILE 'E:\oracle\oracledata\oradata\FSNEW' SIZE 30GEXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; --創建表空間 --查看當前用戶所有表 sel ...
--創建表空間
CREATE TABLESPACE FSNEWDATAFILE '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';
