创建用户
create user 用户名 identified by 密码 default tablespace 默认表空间 temporary tablespace 临时表空间;
查看表空间大小
select tablespace_name, sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;
查看表空间及利用率
select a.tablespace_name, total, free, total-free as used from
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name;
表空间扩容
select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name
alter database datafile '/oracle/oradata/ts_tmp_f01.dbf' resize 2048m
查看临时表空间及利用率
select tablespace_name, tablespace_size/1024/1024 as total, allocated_space/1024/1024 as used, free_space/1024/1024 as free from dba_temp_free_space;
查看临时表空间信息
select * from dba_temp_files;
改变表的表空间
alter table 表名 move tablespace 表空间名;
查看数据目录
select * from dba_directories;
查看某表的索引信息
select * from user_indexes where table_name = '表名'
查看表的列信息
select * from user_tab_columns where table_name='表名'
修改列名
alter table 表名 rename column 现列名 to 新列名;