Oracle常用命令收藏

查看数据库名
SELECT t.NAME FROM V$DATABASE t;

查看实例名
SELECT t.INSTANCE_NAME FROM V$INSTANCE t;

查看SID
select t.INSTANCE from v$thread t;

查看域名
select t.VALUE from v$parameter t where t.name = 'db_domain';

查看服务名
select t.VALUE from v$parameter t where t.name = 'service_names';

查看所有表空间的使用率
set linesize 500
col TABLESPACE_NAME for a20
select a.tablespace_name,
round(total / 1024 / 1024 / 1024, 3),
round(free / 1024 / 1024 / 1024, 3),
round((total - free) / 1024 / 1024 / 1024, 3) ,
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;

修改表空间文件大小
alter database datafile '/DataBase/app/oracle/oradata/orcl/system01.dbf' resize 2G;

查询用户下所有表
/* 查看用户下面的所有的表 */
-- 1 ）方式1：user_tables --
select * from user_tables;
--2 ）方式2: dba_tables --
select table_name from dba_tables where owner='UMS_HW';

查询表空间下的用户
/*查看表空间下有多少用户，tablespace_name表空间 的名字一定要大写 */
select distinct s.owner from dba_segments s where s.tablespace_name ='USERS';

查看表空间的位置和大小
set linesize 200;
col FILE_NAME for a80
col TABLESPACE_NAME for a50
select FILE_NAME,FILE_ID,BYTES/1024/1024/1024,TABLESPACE_NAME FROM dba_data_files where TABLESPACE_NAME='SYSAUX';

删除表空间及其对应的物理文件
drop tablespace TS_VEHICLE_DATA_01 including contents and datafiles cascade constraint;

查看用户所属的表空间
select default_tablespace from dba_users where username='CITIBANK';

删除用户及表空间数据文件
drop tablespace TS_VEHICLE_DATA_01 including contents and datafiles cascade constraint;

扩展已存在用户的表空间
ALTER TABLESPACE USERS ADD DATAFILE
'/DataBase/app/oracle/oradata/orcl/users02.dbf' SIZE 30G AUTOEXTEND off;

查看用户表空间的大小（M）
select tablespace_name, sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

创建用户表空间
create tablespace chn_grid_data datafile
'/oradata01/promjf/chn_grid_data_01.dbf' size 10g autoextend off
logging
extent management local autoallocate
blocksize 8k
segment space management auto
flashback on;

ALTER USER DBCHNGRIDADM_JX QUOTA UNLIMITED ON CHN_GRID_DATA;