oracle日常管理维护sql语句的实例参考

oracle日常管理维护sql语句的实例参考

------------索引
创建普通索引:用itpux登录，先创建itpux表，根据表的id字段来创建索引
create table  itpux(id int,sex char(1),name char(10));
create index itpux_idx1 on itpux(id);
根据性别名字创建索引
create index itpux_idx2 on itpux(sex,name);  
创建位图索引
create bitmap index  itpux_bit_idx on itpux(sex)
查询创建的索引
select object_name,object_type from user_objects
更改索引下一段的大小
alter index itpux_bit_idx storage(next 2m);
更改索引名字itpux_idx1改为itpux_index_id
alter index itpux_idx1  rename to  itpux_index_id;
重建索引
alter index itpux_index_id  rebuild tablespace index1
删除索引(只是被标记，但是实际还占用了很多块)
exec dbms_stats.gather_table_stats('itpux','itpux');
进行碎片整理后，删除的占用空间就被释放了
alter index  itpux_index_id coalesce;
查询索引状态
select * from index_stats


------------表空间
创建表空间
create tablespace itpux01 datafile '/oracle/oradata/db01/itpux01.dbf' size 2m autoextend off segment space management auto;
创建临时表空间
create temporary tablespace temp1 tempfile '/oracle/oradata/db01/temp1.dbf' size 5m autoextend off;
创建undo表空间
create undo tablespace itpuxundo1 datafile '/oracle/oradata/db01/itpuxundo1.dbf' size 2m autoextend off;
查看表空间
select * from dba_tablespaces;
select name from  v$tablespace;
查看数据文件
select * from dba_data_files
select  name from  v$datafile;
查看临时文件信息
select * from dba_temp_files
select * from v$tempfile
创建多个数据文件表空间
create tablespace itpux02 datafile '/oracle/oradata/db01/itpux03.dbf' size 2m autoextend off,
'/oracle/oradata/db01/itpux02.dbf' size 2m autoextend off,
'/oracle/oradata/db01/itpux04.dbf' size 1m autoextend on next 1m maxsize 10m
extent management local segment space management auto;
根据表空间名字查看数据文件
select * from dba_data_files where tablespace_name='itpux02';
创建大表空间
create bigfile tablespace itpuxbig datafile '/oracle/oradata/db01/itpux05.dbf' size 1m;
表空间扩展（添加一个2m数据文件到表空间）
alter tablespace itpux02 add datafile '/oracle/oradata/db01/itpux10.dbf' size 2m autoextend off;
扩展临时表空间
alter tablespace temp1 add tempfile '/oracle/oradata/db01/itpux11.dbf' size 2m autoextend off;
修改表空间里面数据文件的大小
alter database datafile '/oracle/oradata/db01/itpux10.dbf'  resize 3m;
alter database tempfile '/oracle/oradata/db01/itpux11.dbf'  resize 3m;
修改完表空间可以查看下
select  name，bytes/1024/1024 from  v$datafile;
select  name，bytes/1024/1024 from  v$tempfile;
表空间重命名
alter tablespace itpux02 rename to  itpux03;
删除表空间
drop tablespace itpux03 including contents and datafiles;
更改表空间模式为只读 
alter tablespace itpux01 read only;
更改表空间模式为只读写模式
alter tablespace itpux01 read write;
更改表空间在线
alter tablespace itpux01 online
更改表空间离线
alter tablespace itpux01 offline
将数据文件改为离线
alter datafile  6 offline
将数据文件改为离线并删除
alter database datafile '/oracle/oradata/db01/itpux03.dbf' offline for drop;
查出这些表空间哪些是写重做日志文件的那些是不写的
select tablespace_name,logging from dba_tablespaces;
创建一个不写重做日志文件的表空间
create tablespace itpux06 datafile '/oracle/oradata/db01/itpux16.dbf' size 2m autoextend off nologging;
把表空间修改为写重做日志
alter tablespace itpux06 logging;
------------------------------------------------表空间在线迁移
        案例：表空间在线迁移
1.查询这个表空间文件状态是 online
select * from dba_data_files where tablespace_name='itpux01';
2.把表空见状态修改为 offline 在查询下
alter tablespace itpux01 offline;
3.更改操作系统层面数据文件名字 
host cp  '/oracle/oradata/db01/itpux01.dbf'   '/oracle/oradata/db01/itpux001.dbf' 
4.重新命名数据文件，我们在把系统中文件名字改了但是数据库表空间还不知道我们需要更新表空间信息
alter tablespace itpux01 rename datafile '/oracle/oradata/db01/itpux01.dbf' to  '/oracle/oradata/db01/itpux001.dbf' 
5 改为online 在查询下数据文件
alter tablespace itpux01 online;
------------------------------------------------表空间离线迁移
shutdown immediate;
startup mount;
host cp  '/oracle/oradata/db01/itpux001.dbf'   '/oracle/oradata/db01/itpux01.dbf' 
重命名这个文件
alter database rename  file '/oracle/oradata/db01/itpux001.dbf' to  '/oracle/oradata/db01/itpux01.dbf' ;
alter database open
-----------------------------------------------把users表空间itpx用户的对象迁移到itpux01表空间去
默认创建的itpux,该用户对象是存在users表空间的
plsql：
create table itpux.table01(id number(12),c_data date)
insert into itpux.table01 values(1,sysdate);
insert into itpux.table01 values(1,sysdate);
为该表创建索引
create index idx_table01_id on itpux.table01('id')
查询下发现该对象该用户存在users表空间 需要将该对象迁移到itpux01表空间里
select * from dba_segments where owner='itpux'
检查对象信息状态记录下来
......
迁移
alter table itpux.table01 move tablespace itpux01;
重建索引
alter index  itpux.idx_table01_id rebuild tablespace itpux01;
迁移完成之后在查询，做各种检查判断是是否全部迁移，具体见2.3.16表空间与对象迁移
.......
如果这个表中有大字段 就这样子修改下存储位置就好了
alter table itpux.table01 move  lob(date) store as (tablespace itpux01);


----------------------------------------------------------用户管理
-建立用户
create user itpux01 identified by itpux01
default tablespace users
temporary tablespace temp;
select * from dba_users where username='itpux01';
-授权
grant dba to  itpux01
-查看用户信息
select * from dba_users where username='itpux01'
select * from all_users where username='itpux01'
-查询用户下的对象
select* from dba_objects where owner='itpux'
-修改用户密码
alter user itpux01 identified by  itpux01;
-锁用户或解锁用户
alter user itpux01 account lock
alter user itpux01 account unlock
-删除用户
drop user  itpux cascade;
--------------用户权限
--授予itpux能创建会话，创建表的权限
grant create session,create table to  itpux01;
--授予itpux01能授予别人创建会话的权限
grant create session to itpux01 with admin option;
--授予所有用户dba权限，不能做
grant dba to public
--回收所有用户的dba权限
revoke dba from  public;
--回收创建表的权限
revoke create table from itpux;
--让所有用户都有权限去访问这张表，给所有用户授权这张表
grant select on  scott.emp to public;
---------------------------用户角色
--先创建itpux_role角色  在查询看下
create role itpux_role;
select * from dba_roles where role='itpux_role'
--执行角色授权，就是说在角色itpux_role下的所有用户都对scott.emp表具有查询权限
grant select on  scott.emp  to  itpux_role
--把这个角色给删掉
drop role  itpux_role;
--检查角色授权信息
select * from dba_role_privs where granted_role='itpux_role'
--查询用户拥有哪些角色，这是在itpux用户下查询的，拥有itpux_role角色
select * from user_role_privs;
--查询角色授权信息
select * from dba_role_privs where granted_role='itpux_role'
--取消角色
revoke itpux_role  from itpux;
--删除角色
drop role  itpux_role;
--设置默认的角色
grant resource,connect to itpux;
grant dba to  itpux;
--启用所有角色
set role  all;
--禁用所有角色
set role none;
--启用某个角色
set  role  itpux_role;