oracle常用命令集
oracle11g常用命令.
第一章:日志管理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> executedbms_logmnr.start_logmnr(dictfilename=>'c:\oracle\oradb\log\oradb.ora ';f. sql> select * from v$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameterssql> 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 0sql> [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 200msql> 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 space5.change storage and block utilization parametersql> alter table table_name pctfree=30 pctused=50 storage(next 500k sql> 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 tablespace sql> tablespace_name [pctfree integer] [initrans integer] [maxtrans integer]sql> [logging | nologging] [nosort] storage(initial 200k next 200k pctincrease 0 sql> 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 200k sql> 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 200k sql> 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 constraints3. define constraints while create a tablesql> create table xay(id number(7 constraint xay_id primary key deferrablesql> using index storage(initial 100k next 100k tablespace indx; primarykey/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;3. using sql*loadersql> sqlldr scott/tiger \sql> control = ulcase6.ctl \sql> log = ulcase6.log direct=true第七章:reorganizing data1. using expoty$exp scott/tiger tables(dept,emp file=c:\emp.dmp log=exp.log compress=n direct=y2. using import$imp scott/tiger tables(dept,emp file=emp.dmp log=imp.log ignore=y 3.transporting a tablespacesql>alter tablespace sales_ts read only;$exp sys/.. file=xay.dmp transport_tablespace=y tablespace=sales_ts triggers=n constraints=n$copy datafile$imp sys/.. file=xay.dmp transport_tablespace=ydatafiles=(/disk1/sles01.dbf,/disk2/sles02.dbfsql> alter tablespace sales_ts read write;4. checking transport setsql> DBMS_tts.transport_set_check(ts_list=>'sales_ts' ..,incl_constraints=>true;在表tran sport_set_violati ons 中查看sql> dbms_tts.isselfco ntai ned为true 是,表示自包含第八章: managing password security and resources1. controlling account lock and passwordsql> alter user juncky identified by oracle account unlock;2. user_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_function sql> 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 users sql> temporary tablespace temp quota 10m on data password expire sql> [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$sgasta t2. Rman need set dbwr_IO_slaves or backup_tape_IO_slaves andlarge_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;8. complete 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 online9. 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;10. backup a control file> alter database backup controlfile to 'control1.bkp';> alter database backup controlfile to trace;11. recovery (noarchivelog mode> shutdown abort> cp files> startup 12.recovery of file in backup mode >alter database datafile 2 end backup; 13.clearing redo log file >alter database clear unarchived logfile group 1; >alter database clear unarchived logfile group 1 unrecoverable datafile; 14.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'; 16。
Oracle基本操作《DOC命令》
系统权限在doc命令中启动Oracle(注意下文中<>为不存在只是为了标识)其中的一些权限为系统权限（都是用超级管理员授予的）1：启动SQL*PLUS:在相应盘符下输入：sqlplus / as sysdba;<默认的用户是SYS>2:启动监听器在相应盘符下输入：lsnrctl start（我的电脑默认在D盘下）3：启动实例在相应盘符下输入：oradim -starup -sid sjbitdb（我的电脑默认在D盘下）4:展示当前用户名在SQL语句中输入：show user;5:创建用户在SQL语句中输入：create user <用户名> identified by <用户密码> ;6:为用户创建会话权限在SQL语句中输入：grant create session to <用户名>;7:使用用户登录在相应盘符下语句中输入：sqlplus <用户名>/<密码> 注意后面没有分号。
8：为用户创建表权限在SQL语句中输入：grant create table to <用户名>;9：为用户创建表空间权限在SQL语句中输入：grant unlimited tablespace to <用户名>;（unlimited是没有任何限制的意思）10：创建表在SQL语句中输入：create table <表名称> (<列名> <数据类型>);(举例)11：向表中插入数据在SQL语句中输入：insert into <表名称> values(<值>);（举例）12：查询表中的数据在SQL语句中输入：select * form <表名称>;13:删除表在SQL语句中输入：drop table <表名称>;14:撤销用户的会话权限在SQL语句中输入：revoke create session fr om <用户名>;15：撤销用户的创建表权限在SQL语句中输入：revoke create table from <用户名>;16:撤销用户的创建表空间权限在SQL语句中输入：revoke unlimited tablespace from <用户名>;17:查询当前用户拥有哪些系统权限在SQL语句中输入：select * from user_sys_privs;(实际上展示的是一个视图) 18:删除用户：在SQL语句中输入：drop user <用户名>;19:把某个系统权限权限授权给所有用户：在SQL语句中输入：grant (create session<会话权限也就是允许某管理员登录的权限>) 、(create table<创建表权限>)、(unlimited tablespace<创建表空间权限>) 、(create any table<创建任意表权限>) to public;对象权限简单来说超级管理员拥有的权限叫做系统权限，普通用户拥有的权限叫做对象权限。
Oracle常用SQL命令
Oracle常⽤SQL命令创建表空间1.创建默认表空间WLP_DATCREATE TABLESPACE "MYSPACE" DATAFILE'D:\oracle\oradata\orcl\data/WLP_DAT.dbf' SIZE 32M REUSE AUTOEXTEND ON NEXT 32MLOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;2.创建临时表空间WLP_TEMP（⽤于缓存,可以不建）ALTER TABLESPACE "TEMP" ADD TEMPFILE'D:\oracle\oradata\orcl\data/WLP_TEMP.dbf' SIZE 32m REUSE autoextend on next 32m;创建⽤户并授权1CREATE USER "WLPING" PROFILE "DEFAULT"2 IDENTIFIED BY "123456"3DEFAULT TABLESPACE "MYSPACE" //默认指定表空间4TEMPORARY TABLESPACE "TEMP" //指定缓存表空间5 ACCOUNT UNLOCK; //⽤户⾮锁定67GRANT EXECUTE ON dbms_comparison TO "WLPING";8GRANT UNLIMITED TABLESPACE TO "XIR_MD";9GRANT "CONNECT" TO "XIR_MD";10GRANT "RESOURCE" TO "XIR_MD";1112-- GRANT DBA TO XIR_MD;PROFILE：Oracle系统中的profile可以⽤来对⽤户所能使⽤的数据库资源进⾏限制，使⽤Create Profile命令创建⼀个Profile，⽤它来实现对数据库资源的限制使⽤，如果把该profile分配给⽤户，则该⽤户所能使⽤的数据库资源都在该profile的限制之内。
常用oracle数据库命令
常用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 rac日常基本维护命令
最详细的oracle rac日常基本维护命令以下是一些使用Oracle RAC进行日常基本维护的常用命令：1. CRSCTL命令：- crsctl check crs：检查集群资源状态。
- crsctl start crs：启动整个集群资源服务。
- crsctl stop crs：停止整个集群资源服务。
- crsctl disable crs：禁用整个集群资源服务。
- crsctl enable crs：启用整个集群资源服务。
2. SRVCTL命令：- srvctl start instance -db <database_name> -instance<instance_name>：启动指定的实例。
- srvctl stop instance -db <database_name> -instance<instance_name>：停止指定的实例。
- srvctl status instance -db <database_name> -instance<instance_name>：检查指定实例的状态。
- srvctl start service -db <database_name> -service <service_name>：启动指定的服务。
- srvctl stop service -db <database_name> -service <service_name>：停止指定的服务。
- srvctl status service -db <database_name> -service<service_name>：检查指定服务的状态。
3. SQL*Plus命令：- sqlplus / as sysdba：以SYS用户身份登录数据库。
- startup：启动数据库实例。
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查询语句大全oracle 基本命令大全一1.create user username identified by password;//建用户名和密码oracle ,oracle2.grant connect,resource,dba to username;//授权grant connect,resource,dba,sysdba to username;3.connect username/password//进入。
4.select table_name,column_name from user_tab_columns where table_name='mview_log';//查询表中的表名，字段名等等。
5. 如何执行脚本SQL文件? SQL>@PA TH/filename.sql;6.Oracle oledb 提供者在command中执行多条SQL语句与SQL SERVER有少许差别,SQL Server只需使用";"分割多条SQL语句,而Oracle需要遵守ORACLE调用规范,即除分号分割外,还需以begin /end;包围语句体.使用C#描述应如下所示:mandText = "begin INSERT INTO GROUP_INFO (GROUP_ID, GROUP_NAME) V ALUES (1, \'2\'); INSERT INTO GROUP_INFO(GROUP_ID, GROUP_NAME) V ALUES (2, \'2\'); end;";7.查询用户下的所有表select distinct table_name from user_tab_columns;8.如何搜索出前N条记录？Select a.*,rownum from (select * from cardkind order by cardkind ) a where rownum<n9.查找用户下的所有表：select * from tab;2、显示当前连接用户SQL> show user3、查看系统拥有哪些用户SQL> select * from all_users;4、新建用户并授权SQL> create user a identified by a;(默认建在SYSTEM表空间下)SQL> grant connect,resource to a;5、连接到新用户SQL> conn a/a6、查询当前用户下所有对象SQL> select * from tab;7、建立第一个表SQL> create table a(a number); 8、查询表结构SQL> desc a9、插入新记录SQL> insert into a values(1); 10、查询记录SQL> select * from a;11、更改记录SQL> update a set a=2;12、删除记录SQL> delete from a;13、回滚SQL> roll;SQL> rollback;14、提交SQL> commit;select * from(select t.*,dense_rank() over (order by cardkind) rank from cardkind t)where rank = 2;46. 如何在字符串里加回车?select 'Welcome to visit'||chr(10)||'' from dual ;47. 中文是如何排序的？Oracle9i之前，中文是按照二进制编码进行排序的。
Oracle数据库语句大全
Oracle数据库语句大全一．入门部分1.创建表空间create tablespace schooltbs datafile ‘D:\oracle\datasource\schooltbs.dbf’ size 10M autoextend on;2.删除表空间drop tablespace schooltbs[including contents and datafiles];3.查询表空间基本信息select *||tablespace_name from DBA_TABLESPACES;4.创建用户create user lihuaidentified by lihuadefault tablespace schooltbstemporary tablespace temp;5.更改用户alter user lihuaidentified by 123default tablespace users;6.锁定用户alter user lihua account lock|unlock;7.删除用户drop user lihua cascade;--删除用户模式8.oracle数据库中的角色connect,dba,select_catalog_role,delete_catalog_role,execute_catalo g_role,exp_full_database,imp_full_database,resource9.授予连接服务器的角色grant connect to lihua;10.授予使用表空间的角色grant resource to lihua with grant option;--该用户也有授权的权限11.授予操作表的权限grant select,insert on user_tbl to scott;--当前用户grant delete,update on er_tbl to scott;--系统管理员二．SQL查询和SQL函数1.SQl支持的命令：数据定义语言（DDL）:create,alter,drop数据操纵语言（DML）:insert,delete,update,select数据控制语言（DCL）:grant,revoke事务控制语言（TCL）:commit,savepoint,rollback2.Oracle数据类型字符，数值，日期，RAW，LOB字符型char:1-2000字节的定长字符varchar2:1-4000字节的变长字符long:2GB的变长字符注意：一个表中最多可有一列为long型Long列不能定义唯一约束或主键约束long列上不能创建索引过程或存储过程不能接受long类型的参数。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
1、查看数据库的名字和归档状态SQL> select name,log_mode from v$database;NAME LOG_MODE--------- ------------HB130000 ARCHIVELOG2、查看数据库的instance名字和状态SQL> select instance_name,status from v$instance;INSTANCE_NAME STATUShb130000 OPEN3、查看表空间名称、状态和管理方式SQL> select tablespace_name,status,extent_management from dba_tablespaces;TABLESPACE_NAME STATUS EXTENT_MANSYSTEM ONLINE LOCALUNDOTBS1 ONLINE LOCALSYSAUX ONLINE LOCALTEMP ONLINE LOCALUSERS ONLINE LOCALGFB ONLINE LOCALYSZX130000002004 ONLINE LOCALRMAN ONLINE LOCAL8 rows selected.4、查看控制文件的位置和名字SQL> select name from v$controlfile;NAME/oracle/app/oracle/OraHome/dbs/hb130000/control01.ctl /oradata/hb130000/control02.ctl/oradata/hb130000/control03.ctl5、查看日志文件的组名、成员数量、状态和大小SQL> select group#,members,bytes,status from v$log;GROUP# MEMBERS BYTES STATUS1 2 52428800 INACTIVE2 2 52428800 INACTIVE3 2 52428800 CURRENT6、查看日志文件的位置SQL> select group#,member from v$logfile;GROUP#MEMBER3/oradata/hb130000/redo03.log3/oracle/app/oracle/OraHome/dbs/hb130000/redo03.rdo 2/oradata/hb130000/redo02.logGROUP#MEMBER2/oracle/app/oracle/OraHome/dbs/hb130000/redo02.rdo 1/oradata/hb130000/redo01.log1/oracle/app/oracle/OraHome/dbs/hb130000/redo01.rdo6 rows selected.7、查看数据文件的位置和大小SQL> select name,bytes from v$datafile;NAMEBYTES/oradata/hb130000/system01.dbf492830720/oradata/hb130000/undotbs01.dbf41943040/oradata/hb130000/sysaux01.dbf346030080NAMEBYTES/oradata/hb130000/users01.dbf5242880/oradata/hb130000/GFB.DBF20971520/oradata/hb130000/YSZX130000002004.DBF 176160768NAMEBYTES/oradata/hb130000/rman.dbf1048576007 rows selected.8、查看用户拥有的对象和类型SQL> select object_name,object_type from dba_objects where owner='SCOTT'; OBJECT_NAMEOBJECT_TYPEDEPTTABLEPK_DEPTINDEXEMPTABLEPK_EMPINDEXBONUSTABLESALGRADETABLE6 rows selected.9、查看临时文件的信息SQL> select name,bytes from v$tempfile;NAMEBYTES/oradata/hb130000/temp01.dbf2097152010、查看归档日志信息SQL> select name, FIRST_CHANGE#,next_change# from v$archived_log; NAMEFIRST_CHANGE# NEXT_CHANGE#/oracle/app/oracle/OraHome/dbs/archive/hb130000_1_53_532853380.dbf 1669178 1707392/oracle/app/oracle/OraHome/dbs/archive/hb130000_1_54_532853380.dbf 1707392 1734524/oracle/app/oracle/OraHome/dbs/archive/hb130000_1_55_532853380.dbf 1734524 1772848NAMEFIRST_CHANGE# NEXT_CHANGE#/oracle/app/oracle/OraHome/dbs/archive/hb130000_1_56_532853380.dbf 1772848 1798092/oracle/app/oracle/OraHome/dbs/archive/hb130000_1_57_532853380.dbf 1798092 1831478/oracle/app/oracle/OraHome/dbs/archive/hb130000_1_58_532853380.dbf1831478 18650331.打开一个终端窗口，执行以下命令：cd /home/oracle/wkdirsqlplus hr/hr2.创建程序包STATIC_CONSTANTS以声明可用在条件编译中的程序包常量。
从SQL*Plus 会话中，执行以下脚本：@static_constantsstatic_constants.sql 脚本包含以下内容：CREATE OR REPLACE PACKAGE static_constants isdebug CONSTANT BOOLEAN := FALSE;trace CONSTANT BOOLEAN := FALSE;END ;/3.创建两个过程：CHECK_DEBUG（用于检查程序包常量调试的值是否是TRUE）和CHECK_TRACE（用于检查程序包跟踪的值是否是TRUE）。
从SQL*Plus 会话中，执行以下脚本：@debug_trcdebug_trc.sql 脚本包含以下内容：CREATE OR REPLACE PROCEDURE check_Debug ISBEGIN$IF static_constants.debug $THEN DBMS_OUTPUT.put_line('Debugging ON');$ELSE DBMS_OUTPUT.put_line('Debugging OFF'); $ENDEND;/CREATE or REPLACE PROCEDURE Check_trace ISBEGIN$IF static_constants.trace $THEN DBMS_OUTPUT.put_line('Tracing ON');$ELSE DBMS_OUTPUT.put_line('Tracing OFF'); $ENDEND;/4.Set serveroutput on using the new SIZE UNLIMITED syntax available in Oracle Database 10g Release 2. Nowexecute both the procedures.您将看到它显示跟踪和调试均被关闭。
注意：可以将每一行单独地复制到sqlplus 中，但不要一起复制这 3 个语句。
set serveroutput on size unlimitedexec check_debugexec check_trace5.现在，将更改程序包常量的值。
从SQL*Plus 会话中，执行以下脚本：@reset_constreset_const.sql 脚本包含以下内容：CREATE OR REPLACE PACKAGE static_constants isdebug CONSTANT BOOLEAN := TRUE;trace CONSTANT BOOLEAN := TRUE;END ;/6.现在，再次执行这两个过程。
您将看到调试和跟踪自动启用了。
在重新编译程序包时，所有相关的对象都变为无效，并在下一次执行时重新编译。
因此，程序包常量的新值可即刻适用于所有相关对象。
exec check_debugexec check_trace这可以扩展至任意数量的相关程序。
在需要通过单一机制控制大量程序时，使用这个方法最为有效。
用例的其他示例包括针对同一应用程序按州更改赋税，或根据许可选项更改软件特性等等。
返回子主题示例2：使用编译器警告和PLSQL_CCFLAGS通过设置ALTER SESSION命令或ALTER SESSION语句之后的ALTER SESSION命令可用于影响多个程序。
在以下示例中，要创建一个称为GET_RECORD的过程，以接受 employee_id的值，并显示相应的记录。
为了保持良好的编程实践，该过程将调用。