【正文】 tified by EYRE profile LIMITED_PROFILE。alter user THUMPER default role CONNECT。grant select on EMPLOYEE to PUBLIC。alter rollback segment R1 shrink。mit。alter rollback segment SEGMENT_NAME online。grant APPLICATION_USER to username。alter database rename file ’/db05/oracle/CC1/’ to ’/db02/oracle/CC1/’。//在表空間上增加一個(gè)自動(dòng)擴(kuò)展的數(shù)據(jù)文件: alter tablespace DATA add datafile ’/db05/oracle/CC1/’ size 50M autoextend ON maxsize 300M。create user USERNAME identified by PASSWORD default tablespace DATA temporary tablespace TEMP。//生成數(shù)據(jù)字典 catalog catproc // 中備份數(shù)據(jù)庫的位置log_archive_dest_1 = ’/db00/arch’ log_archive_dest_state_1 = enable log_archive_dest_2 = “service= mandatory reopen=60” log_archive_dest_state_2 = enable //對(duì)用戶的表空間的指定和管理相關(guān)的語句create user USERNAME identified by PASSWORD default tablespace TABLESPACE_NAME。//增加一個(gè)新的日志文件組的語句 connect internal as sysdba alter database add logfile group 4(’/db01/oracle/CC1/’, ’/db02/oracle/CC1/’)size 5M。6339。 回收權(quán)限REVOKE CONNECT, RESOURCE FROM 用戶名。查詢遠(yuǎn)端數(shù)據(jù)庫里的表SELECT …… FROM 表名數(shù)據(jù)庫鏈接名。/u01/oracle/oradata/mynewdb/39。/u01/oracle/oradata/mynewdb/39。/u01/oracle/oradata/mynewdb/39。(DATABASE LINK)CREATE DATABASE LINK數(shù)據(jù)庫鏈接名CONNECT TO 用戶名 IDENTIFIED BY 密碼 USING ‘?dāng)?shù)據(jù)庫連接字符串’。(VIEW)CREATE VIEW 視圖名AS SELECT ….FROM …..。Create index indexname on tablename(columnname DESC[columnname...])（4）位圖索引。ALTER INDEX 索引名 REBUILD。兩個(gè)以上表之間的連接查詢SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE = [ AND ……]。字段名可以帶入函數(shù)例如: COUNT(*), MIN(字段名), MAX(字段名), AVG(字段名), DISTINCT(字段名), TO_CHAR(DATE字段名,39。ALTER TABLE 表名 NOCACHE。修改表里字段的定義描述ALTER TABLE表名 MODIFY字段名 字段名描述。(修改數(shù)據(jù)表里記錄的語句)UPDATE表名 SET 字段名1=值1, 字段名2=值2, …… WHERE 條件。39。第一篇：黑馬程序員c語言教程：ORACLE 常用的SQL語法和數(shù)據(jù)對(duì)象ORACLE 常用的SQL語法和數(shù)據(jù)對(duì)象 (DML)部分(往數(shù)據(jù)表里插入記錄的語句)INSERT INTO 表名(字段名1, 字段名2, ……)VALUES(值1, 值2, ……)。.字符串類型的字段值超過定義的長度會(huì)出錯(cuò), , 精確到秒或者用字符串轉(zhuǎn)換成日期型函數(shù)TO_DATE(‘20010801’,’YYYYMMDD’)TO_DATE()還有很多種日期格式, 可以參看ORACLE 小時(shí):分鐘:秒 的格式Y(jié)YYYMMDD HH24:MI:SS INSERT時(shí)最大可操作的字符串長度小于等于4000個(gè)單字節(jié), 如果要插入更長的字符串, 請考慮字段用CLOB類型, , 應(yīng)該先建立一個(gè)序列號(hào)CREATE SEQUENCE 序列號(hào)的名稱(最好是表名+序列號(hào)標(biāo)記)INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE。如果修改的值N沒有賦值或定義時(shí), 將把原來的記錄內(nèi)容清為NULL, 最好在修改前進(jìn)行非空校驗(yàn)。給表里的字段加上約束條件ALTER TABLE 表名 ADD CONSTRAINT 約束名 PRIMARY KEY(字段名)。(刪除表, 索引, 視圖, 同義詞, 過程, 函數(shù), 數(shù)據(jù)庫鏈接等)刪除表和它所有的約束條件DROP TABLE 表名 CASCADE CONSTRAINTS。YYYYMMDD HH24:MI:SS39。SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE = (+)[ AND ……]。一個(gè)表的索引最好不要超過三個(gè)(特殊的大表除外), 最好用單字段索引, 結(jié)合SQL語句的分析執(zhí)行情況, 也可以建立多字段的組合索引和基于函數(shù)的索引 單字節(jié) 單字節(jié)MartriWang 10/05/2007（1）*Tree索引。Create BITMAP index indexname on tablename(columnname[columnname...])（5）函數(shù)索引。ALTER VIEW視圖名 COMPILE。MartriWang 10/05/2007create database mynewdb user sys IDENTIFIED BY sys_id user system IDENTIFIED BY system_id LOGFILE GROUP 1(39。)SIZE 100M, GROUP 3(39。 SIZE 325M REUSE EXTENT MANAGEMENT LOCAL SYSAUT DATAFILE 39。 SIZE 20M REUSE UNDO TABLESPACE undotbs DATAFILE 39。(DCL)語句 賦于權(quán)限常用的系統(tǒng)權(quán)限集合有以下三個(gè): CONNECT(基本的連接), RESOURCE(程序開發(fā)), DBA(數(shù)據(jù)庫管理)常用的數(shù)據(jù)對(duì)象權(quán)限有以下五個(gè): ALL ON 數(shù)據(jù)對(duì)象名, SELECT ON 數(shù)據(jù)對(duì)象名, UPDATE ON 數(shù)據(jù)對(duì)象名, DELETE ON 數(shù)據(jù)對(duì)象名, INSERT ON 數(shù)據(jù)對(duì)象名, ALTER ON 數(shù)據(jù)對(duì)象名GRANT CONNECT, RESOURCE TO 用戶名。REVOKE SELECT ON 表名 FROM 用戶名。查詢數(shù)據(jù)庫中開戶用戶最大提交和最大下發(fā)數(shù)： select MSISDN,TCOS,OCOS from ms_usertable；查詢數(shù)據(jù)庫中各種錯(cuò)誤代碼的總和：select error_code,count(*)from sm_histable0513 group by error_code order by error_code。alter database add logfile member ’/db03/oracle/CC1/’ to group 4。alter user USERNAME default tablespace TABLESPACE_NAME。alter user USERNAME temporary tablespace TEMP。//修改參數(shù): alter database datafile ’/db05/oracle/CC1/’ autoextend ON maxsize 300M。alter database datafile ’/db05/oracle/CC1/’ resize 80M。//回滾段的管理create rollback segment SEGMENT_NAME tablespace RBS。//回滾段上指定事務(wù) mit。//查詢回滾段的 大小和優(yōu)化參數(shù) select * from DBA_SEGMENTS where Segment_Type = ’ROLLBACK’。//例子set transaction use rollback segment SEGMENT_NAME alter tablespace RBS default storage(initial 125K next 125K minextents 18 maxextents 249)create rollback segment R4 tablespace RBS storage(optimal 2250K)。//用戶和角色create role ACCOUNT_CREATOR。alter user THUMPER default role all except ACCOUNT_CREATOR。grant CREATE SESSION to JANE。alter user jane password expire。grant CREATE SESSION to APPLICATION_USER。grant select, insert on to DATA_ENTRY_CLERK。//設(shè)置角色set role DATA_ENTRY_CLERK。//回收角色: revoke ACCOUNT_CREATOR from HELPDESK。revoke SELECT on EMPLOYEE from MCGREGOR。00%39。export ORACLE_SID ORAENV_ASK=NO。archive log start。alter system set log_archive_format=39。//在Server Manager上設(shè)置為archivelog mode: connect internal as sysdba startup mount cc1。//聯(lián)機(jī)備份的腳本 Sample Hot Backup Script for a UNIX File System database Set up environment variables: ORACLE_SID=cc1。REM REM The SYSTEM tablespace has now been written to a REM tar saveset on the tape device /dev/rmt/ REM rest of the tars must use the “rvf” clause to append REM to that REM 備份 RBS tablespace REM alter tablespace RBS begin backup。REM REM 備份 INDEXES tablespace REM alter tablespace INDEXES begin backup。REM REM Follow the same pattern to back up the restREM of the REM REM Step log stop REM REM Exit Server Manager, using the indicator set EOFarch1 Record which files are in the destination directory. Do this by setting an environment variable that is equal to the directory listing for the destination directory. For this example, the log_archive_dest is /db01/oracle/arch/CC1. FILES=`ls /db01/oracle/arch/CC1/arch*.dbf`。’ from DBA_TABLESPACES where Status ’INVALID’ spool / spool off //自動(dòng)生成備份結(jié)束的腳本set pagesize 0 feedback off select ’alte