201303 Oracle常用命令大全

第一章：日志管理1.forcing log switchessql> alter system switch logfile;2.forcing checkpointssql> alter system checkpoint;3.adding online redo log groupssql> alter database add logfile [group 4]sql> ('/disk3/log4a.rdo','/disk4/log4b.rdo') size 1m;4.adding online redo log memberssql> alter database add logfile membersql> '/disk3/log1b.rdo' to group 1,sql> '/disk4/log2b.rdo' to group 2;5.changes the name of the online redo logfilesql> alter database rename file 'c:/oracle/oradata/oradb/redo01.log' sql> to 'c:/oracle/oradata/redo01.log';6.drop online redo log groupssql> alter database drop logfile group 3;7.drop online redo log memberssql> alter database drop logfile member 'c:/oracle/oradata/redo01.log';8.clearing online redo log filessql> alter database clear [unarchived] logfile 'c:/oracle/log2a.rdo'; ing logminer analyzing redo logfilesa. in the init.ora specify utl_file_dir = ' 'b. sql> execute dbms_logmnr_d.build('oradb.ora','c:\oracle\oradb\log');c. sql> execute dbms_logmnr_add_logfile('c:\oracle\oradata\oradb\redo01.log',sql> dbms_logmnr.new);d. sql> execute dbms_logmnr.add_logfile('c:\oracle\oradata\oradb\redo02.log',sql> dbms_logmnr.addfile);e. sql> execute dbms_logmnr.start_logmnr(dictfilename=>'c:\oracle\oradb\log\oradb.ora');f. sql> select * from v$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameters sql> v$logmnr_logs);g. sql> execute dbms_logmnr.end_logmnr;第二章：表空间管理1.create tablespacessql> create tablespace tablespace_name datafile 'c:\oracle\oradata\file1.dbf' size 100m, sql> 'c:\oracle\oradata\file2.dbf' size 100m minimum extent 550k [logging/nologging] sql> default storage (initial 500k next 500k maxextents 500 pctinccease 0)sql> [online/offline] [permanent/temporary] [extent_management_clause]2.locally managed tablespacesql> create tablespace user_data datafile 'c:\oracle\oradata\user_data01.dbf'sql> size 500m extent management local uniform size 10m;3.temporary tablespacesql> create temporary tablespace temp tempfile 'c:\oracle\oradata\temp01.dbf'sql> size 500m extent management local uniform size 10m;4.change the storage settingsql> alter tablespace app_data minimum extent 2m;sql> alter tablespace app_data default storage(initial 2m next 2m maxextents 999);5.taking tablespace offline or onlinesql> alter tablespace app_data offline;sql> alter tablespace app_data online;6.read_only tablespacesql> alter tablespace app_data read only|write;7.droping tablespacesql> drop tablespace app_data including contents;8.enableing automatic extension of data filessql> alter tablespace app_data add datafile 'c:\oracle\oradata\app_data01.dbf' size 200m sql> autoextend on next 10m maxsize 500m;9.change the size fo data files manuallysql> alter database datafile 'c:\oracle\oradata\app_data.dbf' resize 200m;10.Moving data files: alter tablespacesql> alter tablespace app_data rename datafile 'c:\oracle\oradata\app_data.dbf'sql> to 'c:\oracle\app_data.dbf';11.moving data files:alter databasesql> alter database rename file 'c:\oracle\oradata\app_data.dbf'sql> to 'c:\oracle\app_data.dbf';第三章：表1.create a tablesql> create table table_name (column datatype,column datatype]....)sql> tablespace tablespace_name [pctfree integer] [pctused integer]sql> [initrans integer] [maxtrans integer]sql> storage(initial 200k next 200k pctincrease 0 maxextents 50)sql> [logging|nologging] [cache|nocache]2.copy an existing tablesql> create table table_name [logging|nologging] as subquery3.create temporary tablesql> create global temporary table xay_temp as select * from xay;on commit preserve rows/on commit delete rows4.pctfree = (average row size - initial row size) *100 /average row size pctused = 100-pctfree- (average row size*100/available data space)5.change storage and block utilization parametersql> alter table table_name pctfree=30 pctused=50 storage(next 500ksql> minextents 2 maxextents 100);6.manually allocating extentssql> alter table table_name allocate extent(size 500k datafile 'c:/oracle/data.dbf');7.move tablespacesql> alter table employee move tablespace users;8.deallocate of unused spacesql> alter table table_name deallocate unused [keep integer]9.truncate a tablesql> truncate table table_name;10.drop a tablesql> drop table table_name [cascade constraints];11.drop a columnsql> alter table table_name drop column comments cascade constraints checkpoint 1000;alter table table_name drop columns continue;12.mark a column as unusedsql> alter table table_name set unused column comments cascade constraints;alter table table_name drop unused columns checkpoint 1000;alter table orders drop columns continue checkpoint 1000data_dictionary : dba_unused_col_tabs第四章：索引1.creating function-based indexessql> create index summit.item_quantity on summit.item(quantity-quantity_shipped);2.create a B-tree indexsql> create [unique] index index_name on table_name(column,.. asc/desc) tablespacesql> tablespace_name [pctfree integer] [initrans integer] [maxtrans integer]sql> [logging | nologging] [nosort] storage(initial 200k next 200k pctincrease 0sql> maxextents 50);3.pctfree(index)=(maximum number of rows-initial number of rows)*100/maximum number of rows4.creating reverse key indexessql> create unique index xay_id on xay(a) reverse pctfree 30 storage(initial 200ksql> next 200k pctincrease 0 maxextents 50) tablespace indx;5.create bitmap indexsql> create bitmap index xay_id on xay(a) pctfree 30 storage( initial 200k next 200ksql> pctincrease 0 maxextents 50) tablespace indx;6.change storage parameter of indexsql> alter index xay_id storage (next 400k maxextents 100);7.allocating index spacesql> alter index xay_id allocate extent(size 200k datafile 'c:/oracle/index.dbf');8.alter index xay_id deallocate unused;第五章：约束1.define constraints as immediate or deferredsql> alter session set constraint[s] = immediate/deferred/default;set constraint[s] constraint_name/all immediate/deferred;2. sql> drop table table_name cascade constraintssql> drop tablespace tablespace_name including contents cascade constraints 3. define constraints while create a tablesql> create table xay(id number(7) constraint xay_id primary key deferrable sql> using index storage(initial 100k next 100k) tablespace indx);primary key/unique/references table(column)/check4.enable constraintssql> alter table xay enable novalidate constraint xay_id;5.enable constraintssql> alter table xay enable validate constraint xay_id;第六章：LOAD数据1.loading data using direct_load insertsql> insert /*+append */ into emp nologgingsql> select * from emp_old;2.parallel direct-load insertsql> alter session enable parallel dml;sql> insert /*+parallel(emp,2) */ into emp nologgingsql> select * from emp_old;ing sql*loadersql> sqlldr scott/tiger \sql> control = ulcase6.ctl \sql> log = ulcase6.log direct=true第七章：reorganizing dataing expoty$exp scott/tiger tables(dept,emp) file=c:\emp.dmp log=exp.log compress=n direct=y ing import$imp scott/tiger tables(dept,emp) file=emp.dmp log=imp.log ignore=y3.transporting a tablespacesql>alter tablespace sales_ts read only;$exp sys/.. file=xay.dmp transport_tablespace=y tablespace=sales_tstriggers=n constraints=n$copy datafile$imp sys/.. file=xay.dmp transport_tablespace=y datafiles=(/disk1/sles01.dbf,/disk2 /sles02.dbf)sql> alter tablespace sales_ts read write;4.checking transport setsql> DBMS_tts.transport_set_check(ts_list =>'sales_ts' ..,incl_constraints=>true); 在表transport_set_violations 中查看sql> dbms_tts.isselfcontained 为true 是，表示自包含第八章：managing password security and resources1.controlling account lock and passwordsql> alter user juncky identified by oracle account unlock;er_provided password functionsql> function_name(userid in varchar2(30),password in varchar2(30),old_password in varchar2(30)) return boolean3.create a profile : password settingsql> create profile grace_5 limit failed_login_attempts 3sql> password_lock_time unlimited password_life_time 30sql>password_reuse_time 30 password_verify_function verify_functionsql> password_grace_time 5;4.altering a profilesql> alter profile default failed_login_attempts 3sql> password_life_time 60 password_grace_time 10;5.drop a profilesql> drop profile grace_5 [cascade];6.create a profile : resource limitsql> create profile developer_prof limit sessions_per_user 2sql> cpu_per_session 10000 idle_time 60 connect_time 480;7. view => resource_cost : alter resource costdba_Users,dba_profiles8. enable resource limitssql> alter system set resource_limit=true;第九章：Managing users1.create a user: database authenticationsql> create user juncky identified by oracle default tablespace userssql> temporary tablespace temp quota 10m on data password expiresql> [account lock|unlock] [profile profilename|default];2.change user quota on tablespacesql> alter user juncky quota 0 on users;3.drop a usersql> drop user juncky [cascade];4. monitor userview: dba_users , dba_ts_quotas第十章：managing privileges1.system privileges: view => system_privilege_map ,dba_sys_privs,session_privs2.grant system privilegesql> grant create session,create table to managers;sql> grant create session to scott with admin option;with admin option can grant or revoke privilege from any user or role;3.sysdba and sysoper privileges:sysoper: startup,shutdown,alter database open|mount,alter database backup controlfile, alter tablespace begin/end backup,recover databasealter database archivelog,restricted sessionsysdba: sysoper privileges with admin option,create database,recover database until4.password file members: view:=> v$pwfile_users5.O7_dictionary_accessibility =true restriction access to view or tables in other schema6.revoke system privilegesql> revoke create table from karen;sql> revoke create session from scott;7.grant object privilegesql> grant execute on dbms_pipe to public;sql> grant update(first_name,salary) on employee to karen with grant option;8.display object privilege : view => dba_tab_privs, dba_col_privs9.revoke object privilegesql> revoke execute on dbms_pipe from scott [cascade constraints];10.audit record view :=> sys.aud$11. protecting the audit trailsql> audit delete on sys.aud$ by access;12.statement auditingsql> audit user;13.privilege auditingsql> audit select any table by summit by access;14.schema object auditingsql> audit lock on summit.employee by access whenever successful;15.view audit option : view=> all_def_audit_opts,dba_stmt_audit_opts,dba_priv_audit_opts,dba_obj_audit_opts16.view audit result: view=>dba_audit_trail,dba_audit_exists,dba_audit_object,dba_audit_session,dba_audit_statement第十一章：manager role1.create rolessql> create role sales_clerk;sql> create role hr_clerk identified by bonus;sql> create role hr_manager identified externally;2.modify rolesql> alter role sales_clerk identified by commission;sql> alter role hr_clerk identified externally;sql> alter role hr_manager not identified;3.assigning rolessql> grant sales_clerk to scott;sql> grant hr_clerk to hr_manager;sql> grant hr_manager to scott with admin option;4.establish default rolesql> alter user scott default role hr_clerk,sales_clerk;sql> alter user scott default role all;sql> alter user scott default role all except hr_clerk;sql> alter user scott default role none;5.enable and disable rolessql> set role hr_clerk;sql> set role sales_clerk identified by commission;sql> set role all except sales_clerk;sql> set role none;6.remove role from usersql> revoke sales_clerk from scott;sql> revoke hr_manager from public;7.remove rolesql> drop role hr_manager;8.display role informationview: =>dba_roles,dba_role_privs,role_role_privs,dba_sys_privs,role_sys_privs,role_tab_privs,session_roles 第十二章: BACKUP and RECOVERY1. v$sga,v$instance,v$process,v$bgprocess,v$database,v$datafile,v$sgastat2. Rman need set dbwr_IO_slaves or backup_tape_IO_slaves and large_pool_size3. Monitoring Parallel Rollback> v$fast_start_servers , v$fast_start_transactions4.perform a closed database backup (noarchivelog)> shutdown immediate> cp files /backup/> startup5.restore to a different location> connect system/manager as sysdba> startup mount> alter database rename file '/disk1/../user.dbf' to '/disk2/../user.dbf'; > alter database open;6.recover syntax--recover a mounted database>recover database;>recover datafile '/disk1/data/df2.dbf';>alter database recover database;--recover an opened database>recover tablespace user_data;>recover datafile 2;>alter database recover datafile 2;7.how to apply redo log files automatically>set autorecovery on>recover automatic datafile 4;plete recovery:--method 1(mounted databae)>copy c:\backup\user.dbf c:\oradata\user.dbf>startup mount>recover datafile 'c:\oradata\user.dbf;>alter database open;--method 2(opened database,initially opened,not system or rollback datafile) >copy c:\backup\user.dbf c:\oradata\user.dbf (alter tablespace offline)>recover datafile 'c:\oradata\user.dbf' or>recover tablespace user_data;>alter database datafile 'c:\oradata\user.dbf' online or>alter tablespace user_data online;--method 3(opened database,initially closed not system or rollback datafile) >startup mount>alter database datafile 'c:\oradata\user.dbf' offline;>alter database open>copy c:\backup\user.dbf d:\oradata\user.dbf>alter database rename file 'c:\oradata\user.dbf' to 'd:\oradata\user.dbf'>recover datafile 'e:\oradata\user.dbf' or recover tablespace user_data;>alter tablespace user_data online;--method 4(loss of data file with no backup and have all archive log)>alter tablespace user_data offline immediate;>alter database create datafile 'd:\oradata\user.dbf' as 'c:\oradata\user.dbf'' >recover tablespace user_data;>alter tablespace user_data online5.perform an open database backup> alter tablespace user_data begin backup;> copy files /backup/> alter database datafile '/c:/../data.dbf' end backup;> alter system switch logfile;6.backup a control file> alter database backup controlfile to 'control1.bkp';> alter database backup controlfile to trace;7.recovery (noarchivelog mode)> shutdown abort> cp files> startup8.recovery of file in backup mode>alter database datafile 2 end backup;9.clearing redo log file>alter database clear unarchived logfile group 1;>alter database clear unarchived logfile group 1 unrecoverable datafile;10.redo log recovery>alter database add logfile group 3 'c:\oradata\redo03.log' size 1000k; >alter database drop logfile group 1;>alter database open;or >cp c:\oradata\redo02.log' c:\oradata\redo01.log>alter database clear logfile 'c:\oradata\log01.log';。

常见的Oracle操作命令常见的Oracle操作命令1. 创建⽤户和授权-- 创建⽤户和密码create user username identified by userpassword;-- 创建⽤户并且指定默认表空间create user username identified by userpassword default tablespace tablespaceName;-- 修改密码alter user username identified by by newpassword;-- 删除⽤户drop user username;-- 删除⽤户及对象drop user username cascade;-- 查看当前数据库连接⽤户show user;-- 授予⽤户创建 session 的权限，即登录权限grant create session to username;-- 授予⽤于使⽤表空间的权限grant unlimited tablespace to username;-- 授予创建表的权限grant create table to username;-- 授予删除表的权限grant drop any table to username;-- 授予插⼊表数据的权限grant insert any table to username;-- 授予修改表的权限grant update any table to username;-- 授予⽤户查看指定表的权限grant select on tablename to username;-- 授予删除指定表的权限grant drop on tablename to username;-- 授予插⼊数据到指定表的权限grant insert on tablename to username;-- 授予修改指定表的权限grant update on tablename to username;-- 授予对指定表特定字段的插⼊和修改权限，仅限 insert 和 updategrant insert(id) on tablename to username;grant update(id) on tablename to username;-- 授予⽤户 alert 任意表的权限grant alert all table to username;-- 授予 dba 权限-- CONNECT：拥有Connect权限的⽤户只可以登录ORACLE，不可以创建实体，不可以创建数据库结构-- RESOURCE：拥有Resource权限的⽤户只可以创建实体，不可以创建数据库结构-- DBA：拥有全部特权，是系统最⾼权限，只有DBA才可以创建数据库结构grant connect,resource,dba to username;2. 撤销⽤户权限-- 基本语法同 revoke，撤销权限3. 导⼊数据⽂件-- 进⼊到 sql ⽂件⽬录下，登录需要导⼊的⽤户sql>@D:/oracle.sql;4. 常见的查询命令-- 查询 oracle 的版本select * from v$version;-- 查询所有⾓⾊具有的权限个数select role, count(1) from role_sys_privs group by role;-- 查询某个⾓⾊拥有的权限select grantee,privilege from dba_sys_privs where grantee='username' order by privilege;-- 查询所有的表名select table_name from user_tables;-- 查询所有字段名select column_name from user_col_comments;5. 表空间相关操作-- 创建表空间，初始⼤⼩为 500M，每次⾃增 5Mcreate tablespace practice datafile 'practice.dbf' size 500M autoextend on next 5M maxsize unlimited;-- 查询所有表空间的物理位置select name from v$datafile;-- 查询当前⽤户的表空间select username,default_tablespace from user_users;-- 查询所有的表空间select * from user_tablespaces;-- 修改⽤户的表空间alter user username default tablespace tablespaceName;-- 删除表空间alter tablespace tablespaceName offline;drop tablespace tablespaceName including contents and datafiles;6. 常见表的操作-- 创建表并设置默认值CREATE TABLE student(ID VARCHAR2(10),NAME VARCHAR2(10),sex VARCHAR2(20),address VARCHAR2(100),registerdate DATE DEFAULT SYSDATE);-- 修改表名称rename tablename to newTableName;-- 删除表并重建表truncate table tablename;-- 添加注释comment on column tablename.columnName is 'comment';-- 修改表字段数据类型alter table tablename modify columnName datatype;-- 删除字段alter table tablename drop column columnName;-- 在某个字段上建⽴索引create index indexname on tablename(columnName);7. 表的锁操作-- 查询是否锁表select ERNAME,B.SID,B.SERIAL#,C.OBJECT_NAME,B.OSUSER,B.MACHINE,B.PROGRAM,B.LOGON_TIME,MAND,B.LOCKWAIT,B.S ADDR,B.PADDR,B.TADDR,B.SQL_ADDRESS,A.LOCKED_MODEfrom v$locked_object Ainner join v$session B on A.SESSION_ID = B.SIDinner join dba_objects C on A.OBJECT_ID = C.OBJECT_IDorder by B.LOGON_TIME;-- 0：none-- 1：null 空-- 2：Row-S ⾏共享(RS)：共享表锁，sub share-- 3：Row-X ⾏独占(RX)：⽤于⾏的修改，sub exclusive-- 4：Share 共享锁(S)：阻⽌其他DML操作，share-- 5：S/Row-X 共享⾏独占(SRX)：阻⽌其他事务操作，share/sub exclusive-- 6：exclusive 独占(X)：独⽴访问使⽤，exclusive-- 查询某 session 正在执⾏的 sql 语句select sql_textfrom v$sqltext Awhere (A.hash_value, A.address) in (select decode(sql_hash_value, 0, prev_hash_value, sql_hash_value),decode(sql_hash_value, 0, prev_sql_ad dr, sql_address)from v$session Bwhere B.sid = 'sid')order by piece asc;。

Oracle 操作语句大全1.desc(描述）emp 描述emp这张表2.desc dept 部门表3.desc salgrade 薪水等级4.select *from table 查找表中的元素5.dual 是系统中的一张空表6.select *from dual7.select sysdate from dual 取出系统时间8.select ename,sal*12 "annul sal"(取的别名）from emp; 查找用户姓名和用户的年薪9.任何含有空值的数学表达式的值都是空值select ename,sal*12+comm from emp;10.select ename||sal from emp 其中的||相当于将sal全部转化为字符串11.表示字符串的方法select ename ||'ajjf' from emp;12.如果其中有一个单引号就用2个单引号来代替他select ename||'sakj' 'lds'from emp;13.select distinct deptno from emp (去除部门字段中重复的部分,关键字distinct)14.select distinct deptno,job from emp;(去除这2个字段中重复的组合)15.select *from dept where deptno=10; 取出条件(取出部门编号为10的记录)16.select * from emp where ename='CLIRK'; 取出部门中姓名为clirk的记录(注意取出过程中ename用单引号隔开)17.select ename,sal from emp where sal>1500; 取出部门中薪水大于1500的人的姓名18.select ename,sal,deptno from emp where deptno<> 10 取出部门中的部门号不等于10的19.select ename,sal,deptno from emp where ename>'CBA' 取出部门中员工名字大于CBA的员工(实际比较的是ACIIS码)20.select ename,sal from emp where sal between 800 and 1500select ename,sal from emp where sal>=800 and sal<=1500; (取出800和1500之间的数) 21.select ename,sal,comm from emp where comm is null (选出其中的空值)select enmae,sal,comm from emp where comm is not null(选出其中的非空值)22.select ename,sal,comm from emp where sal in (800,1500,2000);取出这3者之中的select ename,sal,comm from emp where ename in('simth');23.select ename,sal,hiredate from emp where hiredata>'3-04月-81';宣传符合条件的日期24.select ename,sal,from emp where sal>1000 or deptno=10; 找出工资薪水大于1000或者部门号等于10的员工25.select ename,sal from emp where sal not in(500,1000); 查找薪水不在500到1000的员工姓名和月薪26.select ename,sal from emp where ename like '%ALL%';select ename,sal from emp where ename like '_%A%'; 查找姓名中含有ALL的客户信息，一个横线代表一个通配符27.select ename,sal from emp where ename like '_%$%%' escape '$'; 自己指定转易字符select ename,sal from emp where ename like '_%\%%'; 查找中间含有%相匹配的客户信息，运用转易字符28.select * from dept order by deptno 对表中元素按部门号排序select *from dept order by deptno desc 默认为升序，可以用desc 按降序29.select ename,sal from emp where sal <>1000 order by sal desc 按照查询条件来查询，并排序（asc升序排列）30.select ename,sal*12 from emp where ename not like '_%A%' and sal>800 order by sal desc31.select lower(ename) from emp 将ename都转化为小写lower是函数能将字母转化为小写32.select ename from emp where lower(ename) like '_%a%'; 找出ename 中所有的含有a的字符33.select substr(ename,2,3) form emp 从第2个字符开始截取3个字符34.select chr(65) from dual; 将65转化为字符35.select ascii('A') from dual 将ACSII码转化为字符串36.select round(23.565)from dual 四舍五入36.select round(23,4565,2)from dual 四舍五入到第二位37.select to_char(sal,'$99.999.9999') from emp 按指定格式输出select to_char(sal,'L99,999,9999') form emp L代表本地字符38.select hiredate from empselect to_char(hiredate,'YYYY-MM-DD HH:MI:SS) from emp; 时间格式的显示select to_char(sysdate,'YYYY-MM-DD HH:MI:ss) from dual; 十二小时制显示系统时间select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS) from dual 二四小时制显示系统时间39.select ename,hiredate from emp where hiredate > to_date('2005-2-3 12:32:23','YYYY-MM-DD HH:MI:SS');40 select sal from emp where sal>to_number('$1,250.00','$9,999.99'); 取出比它大的一切字符串（把特定格式的数字转化成字符）41 select ename,sal+nvl(comm,0) from emp; 讲comm值为空的用0来替换，单行函数（以一条记录为条件）一条对一条42.select Max(sal) from emp;select Min(sal) from emp;select avg(sal) from emp;select sum(sal) from emp;select count(*) from emp; 查看表中一共有多少条记录select count(*) from emp where deptno=10; 查找部门10一共有多少人；43.select avg(sal),deptno from emp group by deptno; 按部门号进行分组select deptno,job,max(sal) from emp group by job,deptno; 按工作和部门号进行分组；44.select ename from emp where sal=(select max(sal) from emp); 子查询，查找部门中薪水最高的员工姓名45.group by 注意：出现在select列表中的字段，如果没有出现在组函数中必须出现在group by 子句中46.select avg(sal),deptno from emp group by deptno having avg(sal)>2000; 选出部门中平均薪水大于2000的部门，47.select * from emp where sal>100 group by deptno having ..........order by........先取数据--过滤数据------分组----对分组限制-------排序48.select avg(sal) from emp where sal>2000 group by deptno having avg(sal)>1500 order by avg(sal) desc;查找部门中平均薪水打印2000的员工并按部门号进行排序，查询分组后的平均薪水必须大于1500，查询结果按平均薪水从低到高排列49.select ename from emp where sal>(select avg(sal) from emp);查找出员工中薪水位于部门平均薪水之上的所有员工50.select ename,sal from emp join(select max(sal) max_sal from emp group by deptno) t on(emp.sal=t,max_sal and emp.deptno=t.deptno);查找每个部门中薪水最高的51.select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno; 表的自连接52.select dname,ename from emp cross join dept 交叉连接，笛卡尔SQL99中的新语法53.select ename,dname from emp join dept on(emp.deptno=dept.deptno);54.select ename,dname from emp join dept using(deptno); 查找emp和dept表中deptno相同的部分。

Oracle命令大全一.控制文件的管理：创建复合控制文件1.关闭数据库；2.复制控制文件修改名称到不同的位置；3.修改初始化参数文件中CONTROL_FILES参数，使其指向新的控制文件，例如： alter system setcontrol_files=( e:\oradata\control1.ctl,e:\oradata\control2.ctl,e:\oradata\control3.ctl);4.重新启动数据库。

创建新的控制文件语法 CREATE CONTROLFILE SET DATABASE oradb01 LOGFILE GROUP1 ( E:\ORACLE\ORADATA\REDO01_01.LOG , E:\ORACLE\ORADATA\REDO01_02.LOG ),GROUP2( E:\ORACLE\ORADATA\REDO02_01.LOG ,E:\ORACLE\ORADATA\REDO02_02.LOG ),GROUP3( E:\ORACLE\ORADATA\REDO03_01.LOG ,E:\ORACLE\ORADATA\REDO03_02.LOG )NORESTLOGS DATAFILE E:\ORACLE\ORADATA\SYSTEM01.DBF SIZE50M E:\ORACLE\ORADATA\RBS01.DBF SIZE50ME:\ORACLE\ORADATA\TEMP01.DBF SIZE50M MAXLOGFILES50MAXLOGMEMBERS3 MAXDATAFILES200 MAXINSTANCES6 ARCHIVELOG 步骤:1.列出数据库中所有的数据文件和重做日志文件的名称和路径 select member from v$logfile; select name fromv$datafile; select value from v$parameter where name= control_files ;2.关闭数据库3.在操作系统级别备份所有的数据文件和联机重做日志文件4.启动实例5.利用步骤一得到的文件列表执行create controlfile命令创建一个新的控制文件6.在操作系统级别对新建的控制文件进行备份7.修改初始化参数文件中的control_files参数使它指向新的控制文件。

oracle数据库常用操作指令Oracle数据库常用的操作命令Oracle数据库常用的操作命令常用的数据字典表:tab，user_tables,user_objects,user_catalog,user_constraints,user_cons_col umns 1、视图在所有存储过程(数据字典表)中查询是否有某一字符串的,,,语句select * from all_source where type='PROCEDURE' and lower(text) like '%student %'select * from user_source where lower(text) like '%dbms_%'select tname,cname,coltype,width from col where tname='STUDENT'select * from all_tables where owner ='OEMS'select * from all_constraints where constraint_name ='JWTHESIS_R_THESISWRITEBATCH_ID'2、sqlplus连接oracleOracle安装后默认密码:sys(change_on_install),system(manager),internal(oracle),scott(tiger) connect system/manager as sysdba;(as sysoper)connect internal/oracle AS SYSDBA;(scott/tiger)connect sys/change_on_install as sysdba;3、查看当前连接数:select * from v$license查看当前的所有数据库: select * from v$database;查看当前所有的数据库实例: select * from v$instance查看当前库的所有数据表all_tables(用户表user_tables):select * from all_tables where table_name like 'T%' and owner='SYS';查看当前数据库连接用户 sqlplus Show user;或者select uid,user from dual;查看当前数据库系统时间 select sysdate from dual;查看数据库表结构字段: desc v$database;查看哪些用户拥有SYSDBA、SYSOPER权限: select * from v_$PWFILE_USERS;4. 怎样删除用户会话SELECT * FROM v$session WHERE lower(USERNAME) = 'oems';ALTER SYSTEM KILL SESSION 'SID, SERIAL#'; 5、改变一个对象的名字,执行RENAME语句，改变一个表、视图、序列或同义词RENAME dept TO detail_dept;6、检查用户是否将SYSTEM 表空间作为缺省表空间SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS WHEREDEFAULT_TABLESPACE ='SYSTEM';7、怎样查找产生锁的SQL 语句select ername username,a.sid sid,a.owner||'.'||a.objectobject,s.lockwait,t.sql_textSQLfrom v$sqltext t,v$session s,v$access awhere t.address=s.sql_address and t.hash_value=s.sql_hash_value and s.sid = a.sid anda.owner != 'SYS'and upper (substr(a.object,1,2)) != 'V$'8、添加注释到表中,用COMMENT语句添加注释到一个表或列中(注释通过数据字典视图查看CALL_COL_COMMENTS,CUSER_COL_COMMENTS,CALL_TAB_COMMENTS,CUSER_TAB_COM MENTS)COMMENT ON TABLE employees IS 'Employee Information' 9、系统日期sysdate显示，环境变量nls_date_format设置日期显示格式alter session set nls_date_format='yyyy"天"mm"月"dd"日"' --'yyyy-mm-dd hh24:mi:ss'10、日期显示的类型Selectsysdate,to_char(sysdate,'yyyy.mm.dd')col1,to_char(sysdate,'dd-mon-yyyy hh24:mi:ss day')col2from dualSelect trunc(sysdate) + 0.5 col from dualSelect trunc(sysdate) ||' 12:00:00' col From dual11、DBA权限--典型的DBA权限----系统权限----授权的操作--create user 受让人可以创建其他 oracle 用户 (需要有DBA角色权限)。

常用oracle数据库命令
1. 启动Oracle数据库
$ sqlplus /nolog
SQL> connect / as sysdba
SQL> startup
3. 创建表空间
创建表空间的命令是：
SQL> create tablespace 表空间名 datafile '路径名' size 大小;
6. 创建用户
7. 删除用户
删除用户的命令是：
8. 授权用户
SQL> grant 权限 to 用户名;
9. 撤销用户的权限
11. 查看表结构
查看表结构的命令是：
SQL> desc 表名;
SQL> create table 表名 (列名数据类型, 列名数据类型, …);
13. 删除表
14. 插入数据
插入数据的命令是：
SQL> update 表名 set 列名=新值 where 某条件;
18. 创建索引
20. 查看索引
21. 查询相关信息
查询相关信息的命令是：
SQL> select * from v$session; -- 查看会话 22. 查看数据库版本
24. 备份数据库
$ exp 用户名/密码 file=备份文件路径备注：以上命令均需要在Oracle登录后进行。

Oracle常⽤命令⼤全（很有⽤，做笔记）⼀、ORACLE的启动和关闭1、在单机环境下要想启动或关闭ORACLE系统必须⾸先切换到ORACLE⽤户，如下su - oraclea、启动ORACLE系统oracle>svrmgrlSVRMGR>connect internalSVRMGR>startupSVRMGR>quitb、关闭ORACLE系统oracle>svrmgrlSVRMGR>connect internalSVRMGR>shutdownSVRMGR>quit启动oracle9i数据库命令：$ sqlplus /nologSQL*Plus: Release 9.2.0.1.0 - Production on Fri Oct 31 13:53:53 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> connect / as sysdbaConnected to an idle instance.SQL> startup^CSQL> startupORACLE instance started.2、在双机环境下要想启动或关闭ORACLE系统必须⾸先切换到root⽤户，如下su － roota、启动ORACLE系统hareg －y oracleb、关闭ORACLE系统hareg －n oracleOracle数据库有哪⼏种启动⽅式说明：有以下⼏种启动⽅式：1、startup nomount⾮安装启动，这种⽅式启动下可执⾏：重建控制⽂件、重建数据库读取init.ora⽂件，启动instance，即启动SGA和后台进程，这种启动只需要init.ora⽂件。

2、startup mount dbname安装启动，这种⽅式启动下可执⾏：数据库⽇志归档、数据库介质恢复、使数据⽂件联机或脱机，重新定位数据⽂件、重做⽇志⽂件。

Oracle常用命令Oracle命令1.修改显示的长度set linesize [长度]2.修改显示的行数set pagesize [行数]3.创建一个a.sql的文件，文件里是需要调用的SQL语句ed a4.调用*.SQL或者*.txt文件a)默认路径调用如果调用的是a.sql，则用@a即可调用如果调用的是a.txt，则用@a.txt即可调用(除了*.sql可以省略不使用扩展名，其他文件的调用需要加上扩展名)b)其他路径调用如果调用的文件不是在当前位置，则需要加上路径，例如a.txt在D盘根目录下，则用@d:\\a.txt 即可调用5.在SQLPLUS窗口登录用户或更改登录用户第一种conn 用户名/密码; 直接全部录完第二种第一步：conn 用户名第二步：密码;先输入要登录的用户名，在执行后显示输入密码如果是连接超级用户[sys]只能使用第一种方法，而且必须在命令后面加上[AS SYSDBA]conn sys /密码as sysdba ;6.查当前正在联接的用户show user ;7.查看系统拥有哪些用户select * from all_users;8.查询当前用户下所有对象select * from tab;9.重新运行上一次输入的命令Run10.时间的显示a)打开set time onb)关闭set time off ;11.数据列标题的显示a)打开set heading onb)关闭set heading off命令解释PL/SQL菜单命令：Tables 表/目录all objects所有对象all non-system objects所有系统对象My objects我的对象my objects + sys.packages 我的对象和打包的系统文件my invalid objects我的无效对象all objects modlified today重建现在所有的对象Oracle命令解释：针对表、视图等数据库对象的权限Select:执行检索操作Insert:执行数据插入操作update:执行数据更新操作delect:执行删除数据操作使用过程的权限execute:执行过程等操作常见的系统权限create table:可以在当前模式中创建表create any table:可以在任意模式中创建表drop table:可以删除当前模式中的表drop any table:可以在任意模式中删除表create procedure:可以创建过程execute any procedure:可以在任意模式中执行过程create sequence:可以创建序列create session:可以创建对数据库的连接create synonym:可以创建用户帐户drop user 可以删除用户帐户create view:可以创建视图create trigger:可以创建触发器。