平时常用的【oracle】sql语句(随时更新，供日常查阅)

1、查询一个用户使用了哪些表空间

select distinct tablespace_name from dba_segments where owner='user_name';

2、删除用户及数据

drop user username cascade；

3、创建一个用户

create user user_name identified by pass_word;

修改用户密码

alter user user_name identified by 123456;   --修改用户名user_name密码为123456

4、给用户赋权限

grant connect,resource,dba to user_name;

5、创建表空间

create tablespace TABLESPACE_NAME datafile '路径\TABLESPACE_NAME.DBF' size 10240m autoextend on ;

查看存放表空间的数据文件的路径

SELECT ddf.file_name FROM dba_data_files ddf where ddf.tablespace_name = 'tablespace_name ';

6、将用户和用户下所有的表导出

expdp user_name/pass_word directory=DATA_PUMP_DIR dumpfile=DMP_NAME.dmp logfile = LOG_NAME.log;

7、扩展表分区（不存在unlimited）

alter table TABLE_NAME add partition PART_NAME values less than (某时间列值)  tablespace TABLESPACE_NAME pctfree 10 initrans 1 maxtrans 255 strage

(

　　initial 64k

　　next 1M

　　minextents 1

　　maxextents unlimited

);

8、扩展表分区（存在unlimited）

alter table TABLE_NAME split partition PART_UNLIMITED_NAME at (某时间列值)  into (partition PART_NAME, partition PART_UNLIMITED_NAME);

9、查询所有表空间名称

select tablespace_name from dba_data_files；

10、创建序列

create sequence SEQ_LOG_ID minvalue 1 --增长最小值

maxvalue 9999999999 --增长最大值,也可以设置

NOMAXvalue -- 不设置最大值

start with 101 --从101开始计数

increment by 1 --自增步长为1

cache 50 --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为---NOCACHE防止跳号

cycle; --循环,当达到最大值时,不是从start with设置的值开始循环。而是从1开始循环

11、多表更新方法

update test1 set (test1.name,test1.age)= (select test2.name,test2.age from test2 where test2.id=test1.id)

12、给表添加索引

create unique index idx_Id     -- idx_Id 是索引名字，unique表示是唯一索引， 只写create index 就是普通索引

on table_name(Id，name);            -- table_name 是表名，Id 和name是字段名称,多个列就用    ，  分开。

13、删除索引

drop index idx_Id_Name;   --  格式为： drop index 索引名。

14、查询已建立的索引

select index_name from all_indexes where table_name = 'table1';

15、给表添加字段

-- Add/modify columns
alter table table_name add people_num NUMBER(16);
-- Add comments to the columns
comment on column table_name.people_num
is '人数';

16、查询用户下的所有表的大小

SELECT OWNER as "用户名",
sum(BYTES) / 1024 / 1024 / 1024 as "所有表的大小(GB)"
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME in (select t2.OBJECT_NAME
from dba_objects t2
where t2.OBJECT_TYPE = 'TABLE')
group by OWNER
order by 2 desc