oracle工作中常用命令

工作中经常用的一些命令记录下来。慢慢更新，自己做个备份。。。1.查看表空间名称，物理路径，大小，实用状况

select a.tablespace_name,a.bytes/1024/1024||'M' total,b.bytes/1024/1024||'M' used,c.bytes/1024/1024||'M' free,d.file_name,
(b.bytes*100)/a.bytes ||'%'"used",(c.bytes*100)/a.bytes||'%' " free"
from sys.sm$ts_avail a,sys.sm$ts_used b,sys.sm$ts_free c,dba_data_files d
where a.tablespace_name=b.tablespace_name and
a.tablespace_name=c.tablespace_name and a.tablespace_name=d.tablespace_name
order by tablespace_name desc ;

2.查看lock

select b.username,b.sid,b.serial#,b.logon_time,b.osuser
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time;

3.创建表空间

create tablespace test
datafile 'c:\test.dbf' size 100m
autoextend on  next 64m;

4.创建临时表空间

CREATE TEMPORARY TABLESPACE test_temp
  TEMPFILE 'C:\oracle\product\10.1.0\oradata\orcl\test_temp01.dbf'
  SIZE 64M
  AUTOEXTEND ON
  NEXT 32M MAXSIZE 2048M
  EXTENT MANAGEMENT LOCAL;

5.创建用户

create user test identified test123
default tablespace test
temporary tablespace  test_temp;

6.授权

grant connect,resource to test;

7.杀死某个会话

Alter  system  kill session ‘sid,serial#’;

8.移动数据库位置

使表空间离线
alter tablespace test  offline;
移动数据库位置
修改控制文件
alter  tablespace  test
rename 'c:\test.dbf'  to ‘d:\test.dbf';
使数据库在线
alter tablespace  test  online

9.查看用户的默认表空间

select username,user_id,account_status,default_tablespace,TEMPORARY_TABLESP
from  dba_users
order by desc;

10.设置时间格式

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

update 修改

update table_name set post_nu =10 where id =2 ;
update table_name set pos_nu =10 ;insert into test select * from test1 where id =42;

delete

delete from test where id=42;
delete form test ; 没有where条件时说明删除表中所有的值

复制表

create  table  test as  select *  from  test1;
只复制结构
create  table  test as  select *  from  test1
where 1=2;

删除表空间

drop  tablespace  test  [including  conyents  and  datafiles];

删除用户

drop  user  test  cascade;

创建索引

create index  test_index   on  test(字段名）；

建索引的目的就是为了加快查询速度，使表中数据在做insert语句时就进行排序。
索引是DML语句触发的，系统自动运行，也可以强制使用索引。字段

alter table 表名 add(字段字字段类型)alter table 表名 modify( 字段，类型)

查看表

SELECT Owner,
        Tablespace_name,
        count(*) || ' Tables' Object
FROM dba_tables
WHERE owner <> 'SYS' and owner <> 'SYSTEM'
group by owner,tablespace_name
union
SELECT Owner,
        Tablespace_name,
        count(*) || ' Indexes' Object
FROM dba_indexes
WHERE owner <> 'SYS' and owner <> 'SYSTEM'
group by owner,tablespace_name;
SELECT Owner,       Tablespace_name,       count(*) || ' Indexes' ObjectFROM dba_indexesWHERE owner NOT IN ('SYS','SYSTEM')group by owner,tablespace_name;

select (UR*(UPS*overhead)+overhead) as "Bytes"
    from
     (select value as UR
      from v$parameter where name = 'undo_retention'),
     (select (sum(undoblks)/sum(((end_time-begin_time)*86400))) as UPS
      from v$undostat),
     (select value as overhead
      from v$parameter where name='db_block_size');

D:\oracle\ora92\bin\exp userid=test/0909@TNS:XXX filesize=3GB file=(H:\DB_Backup\%date:~6,10%\HUB2a_%date:~6,10%.dmp,H:\DB_Backup\%date:~6,10%\HUB2b_%date:~6,10%.dmp,H:\DB_Backup\%date:~6,10%\HUB2c_%date:~6,10%.dmp,H:\DB_Backup\%date:~6,10%\HUB2d_%date:~6,10%.dmp) log=H:\DB_Backup\%date:~6,10%\HUB2_%date:~6,10%.log
C:\PROGRA~1\WINRAR\RAR a  H:\DB_Backup\%date:~6,10%\HUB2a_%date:~6,10%.rar H:\DB_Backup\%date:~6,10%\HUB2a_%date:~6,10%.dmp

RMAN> run
2> {
3>  allocate channel d1 device type disk;
4>  backup database format 'F:\Datafile_Backup\%T%U.bak';
5>  backup current controlfile format 'F:\Controlfile_Backup\%T%U.ctl';
6>  backup archivelog from time = 'sysdate -7 ' format 'F:\Archive_backup\%T%U.bak';
7>  }