对Oracle数据库的一下操作脚本

--进去oracle服务器数据库
su - oracle
sqlplus / as sysdba

--查看所有容器
show pdbs

--查看当前容器
show con_name;

--打开相应容器
alter pluggable database orclpdb open;

--切换容器
alter session set container = ORCLPDB1;

--创建表空间，指定文件
create tablespace OPSCW_DATA datafile '/home/oradata/CLDB/ORCLPDB1/opscw_data_01.dbf' size 500M autoextend on next 10M maxsize unlimited;

--表空间指向另一个数据文件
alter tablespace V7CW_DATA add datafile '/home/oradata/CLDB/ORCLPDB1/v7cw_data_03.dbf' SIZE 1024M AUTOEXTEND ON NEXT 1024M;

--创建用户，指定表空间
create user bedc_jgcw identified by bedc_jgcw default tablespace BEDCCW_DATA;

--删除表空间及数据文件
drop tablespace V7CW_DATA including contents and datafiles;

--赋用户基本权限
grant create user,drop user,alter user,create any view,connect,resource,dba,create session,create any sequence to BEDCCW;

--赋用户读写权限
Grant read,write on directory DUMPDIR to BEDCCW;

--赋权限
grant dba to cmscw identified by cmscw;

--查看数据库用户
SELECT * FROM DBA_USERS;

SELECT 'alter system kill session '''||sid||','||serial#||''';' FROM v$session WHERE USERNAME='V7CW1‘
alter system kill session '26,35207';

--修改用户密码
alter user testdb identified by 123456789;

--删除用户
drop user BEDC_JGCW cascade;

--当前用户使
select * from user_users;
--数据库下所有的表空间
select * from Dba_Tablespaces;
--空间存放位置
select t1.name,t2.name ,t1.*,t2.* from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#;

--为用户指定表空间：
alter user 用户名 default tablespace 表空间名字 ;

--为用户指定临时表空间
alter user 用户名 temporary tablespace 表空间名字;

--查看表空间大小
select owner, sum(bytes / 1024 / 1024) "MB", sum(bytes / 1024 / 1024 / 1024) "GB" from dba_segments group by owner order by GB DESC;

--查询用户对应表空间
select username,default_tablespace from dba_users;

--查找工作空间的路径：
select * from dba_data_files;

-- 检查表空间状态
SELECT tablespace_name, status FROM dba_tablespaces;

-- 关闭表空间
ALTER TABLESPACE tablespace_name OFFLINE;
-- 删除表空间
DROP TABLESPACE tablespace_name INCLUDING CONTENTS;
drop tablespace 表空间名字 including contents and datafiles cascade constraint;

-- 确认删除
PURGE RECYCLEBIN;

--用户解锁
alter user cmscw account unlock;

--查询定时任务
select * from dba_jobs

--根据jobid删除job
BEGIN
DBMS_JOB.REMOVE (2);
END;

--查看表空间占比
SELECT
a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表空间使用大小",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE
a. tablespace_name = b.tablespace_name and a.tablespace_name like '%v7CW%'

--查看表空间对应数据文件及表空间大小
select tablespace_name, file_id,file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files WHERE TABLESPACE_NAME='V7CW_DATA' order by tablespace_name;

--查看表空间带下
select dba.TABLESPACE_NAME, sum(bytes)/1024/1024 as MB
from dba_data_files dba
group by dba.TABLESPACE_NAME having TABLESPACE_NAME='V7CW_DATA'