oracle之重要语句

ORACLE 很重要的SQL语句

2009-11-16 03:44

select * from all_users;                 ##查看所有用户  select name from v$database;             ##查看当前数据库名 select * from v$instance;                ##查看所有的数据库实例  select username,password from dba_users; ##查看当前实例中的用户和密码  select member from v$logfile;            ##查看日志文件  select * from user_role_privs;           ##查看当前用户的角色  select username,default_tablespace from user_users; ##查看当前用户的缺省表空间 查找数据库中所有列  select C.column_name,C.TABLE_NAME from dba_tab_columns C where owner='' 查看oracle版本命令： select * from product_component_version; 表复制  insert into table_a (id,name,age) select b.id,b.name,b.age from table_b; 查看当前Job的执行计划  select job,next_date,next_sec,failures,broken from user_jobs; 删除一个job  begin  dbms_job.remove(46);--46为job号  end; 查看当前库的所有数据表：  select TABLE_NAME from all_tables;  select * from all_tables;  select table_name from all_tables where table_name like ‘u’; 创建用户并赋予权限  create user mpss  identified by "mpss12"  default tablespace TS_MPSS_DATA  temporary tablespace TEMP;  给用户赋予权限  grant connect to mpss;  grant resource,create session to mpss; 开发角色  grant create procedure to dbuser； ＃这些权限足够用于开发及生产环境 给用户授权  grant dba to spms;--授予DBA权限  grant unlimited tablespace to lxg;--授予不限制的表空间  grant select any table to lxg;--授予查询任何表  grant select any dictionary to lxg;--授予 查询 任何字典 删除用户  drop user mpss cascade; Create the user （用sys执行）  create user xx identified by xx123 default tablespace lmss temporary tablespace TEMP profile DEFAULT; Grant/Revoke role privileges （用sys执行）  grant connect to xx; 创建视图给hy用户（用leon用户）  create or replace view view_tableName as  select column。。。 from table;  Grant/Revoke object privileges  grant select, update on RES_XIM_CARD to hy; 创建同义词  create synonym RES_XIM_CARD for YY.RES_XIM_CARD ; 建表空间  CREATE TABLESPACE "TS_MPSS_DATA"      LOGGING      DATAFILE '/mpss/data/ts_mpss_data.dbf' SIZE 1024M      EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO  建立临时表空间 CREATE      TEMPORARY TABLESPACE "SWVIP" TEMPFILE '/app/oracle/oradata/      sworacle/SWVIP.dbf' SIZE 5M EXTENT MANAGEMENT LOCAL UNIFORM      SIZE 1M  create tablespace TS_MPSS_DATA datafile '/mpss/data/ts_mpss_data.bdf ' size 1024m autoextend on ; 查看表空间  查看表空间大小 SELECT D.TABLESPACE_NAME "Name",      TO_CHAR(((((A.BYTES - DECODE(F.BYTES, NULL, 0, F.BYTES)) / 1024 / 1024)) /(A.BYTES / 1024 /   1024))*100,'99,990.9') "used(%)",  TO_CHAR((DECODE(F.BYTES, NULL, 0, F.BYTES) / 1024 / 1024),'999,990.9') "Free (M)"  FROM SYS.DBA_TABLESPACES D, SYS.SM$TS_AVAIL A, SYS.SM$TS_FREE F  WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME  AND F.TABLESPACE_NAME (+) = D.TABLESPACE_NAME; SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",  ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"  FROM  (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS  FROM DBA_DATA_FILES  GROUP BY TABLESPACE_NAME) D,  (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE  FROM DBA_FREE_SPACE  GROUP BY TABLESPACE_NAME) F  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)  UNION ALL --if have tempfile  SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,  USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",  NVL(FREE_SPACE,0) "FREE_SPACE(M)"  FROM  (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS  FROM DBA_TEMP_FILES  GROUP BY TABLESPACE_NAME) D,  (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,  ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE  FROM V$TEMP_SPACE_HEADER  GROUP BY TABLESPACE_NAME) F  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ; 查看表空间物理文件的名称及大小;  select tablespace_name, file_id, file_name,      round(bytes/(1024*1024),0) total_space      from dba_data_files      order by tablespace_name; 查看数据文件放置的路径  col file_name format a50  select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id; 查看数据库库对象  select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status; 用系统管理员，查看当前数据库有几个用户连接：  select username,sid,serial# from v$session; 扩表空间  alter tablespace G000 add datafile '/dev/vgbilling/rg000_lv03' SIZE 7500m; 检查被长时间锁的对象  select a.session_id,a.process,a.locked_mode,b.object_name,b.object_type,b.status from v$locked_object a,dba_objects b where a.object_id=b.object_id;