8 顯示有用戶名和帳戶的狀態 select username,account_status from dba_users;
9 將SCOTT帳號解鎖(加鎖) alter user scott account unlock(lock);
10 以SCOTT的身份連接并且查看所屬表 connect scott/tiger select * from tab;
11 查看EMP的表結構及記錄內容 desc emp select empno,ename from emp;
12 以OS的身份登看SGA,共享池,CACHE的信息 connect / as sysdba show sga select name,value/1024/1024 from v$sga; show parameter shared_pool_size select value/1024/1024 from v$parameter where name ='shared_pool_size'; show parameter db_cache_size select value/1024/1024 from v$parameter where name ='db_cache_size';
13 查看所有含有SIZE的信息 show parameter size bitmap_merge_area_size integer 1048576 create_bitmap_area_size integer 8388608 db_16k_cache_size big integer 0 db_2k_cache_size big integer 0 db_32k_cache_size big integer 0 db_4k_cache_size big integer 0 db_8k_cache_size big integer 0 db_block_size integer 4096 db_cache_size big integer 33554432 db_keep_cache_size big integer 0 db_recycle_cache_size big integer 0
NAME TYPE VALUE ------------------------------------ ----------- ------------- global_context_pool_size string hash_area_size integer 1048576 java_max_sessionspace_size integer 0 java_pool_size big integer 33554432 large_pool_size big integer 8388608 max_dump_file_size string UNLIMITED object_cache_max_size_percent integer 10 object_cache_optimal_size integer 102400 olap_page_pool_size integer 33554432 oracle_trace_collection_size integer 5242880 parallel_execution_message_size integer 2148
NAME TYPE VALUE ------------------------------------ ----------- ------------- sga_max_size big integer 143727516 shared_pool_reserved_size big integer 2516582 shared_pool_size big integer 50331648 sort_area_retained_size integer 0 sort_area_size integer 524288 workarea_size_policy string AUTO
14 顯示SGA的信息 select * from v$sgastat; POOL NAME BYTES ----------- -------------------------- ---------- fixed_sga 453532 buffer_cache 33554432 log_buffer 656384 shared pool subheap 46884 shared pool KGK heap 3756 shared pool KQR M PO 586792 shared pool KQR S PO 180232 shared pool KQR S SO 5128 shared pool sessions 410720 shared pool sql area 2144664 shared pool 1M buffer 2098176
POOL NAME BYTES ----------- -------------------------- ---------- shared pool KGLS heap 901756 shared pool parameters 8352 shared pool free memory 38687204 shared pool PL/SQL DIANA 420816 shared pool FileOpenBlock 695504 shared pool PL/SQL MPCODE 135692 shared pool library cache 2985576 shared pool miscellaneous 4889396 shared pool MTTR advisory 21164 shared pool PLS non-lib hp 2068 shared pool XDB Schema Cac 4966300
POOL NAME BYTES ----------- -------------------------- ---------- shared pool joxs heap init 4220 shared pool kgl simulator 563260 shared pool sim memory hea 44184 shared pool table definiti 1728 shared pool trigger defini 1896 shared pool trigger inform 1140 shared pool trigger source 448 shared pool type object de 69120 shared pool Checkpoint queue 282304 shared pool VIRTUAL CIRCUITS 265160 shared pool dictionary cache 1610880
POOL NAME BYTES ----------- -------------------------- ---------- shared pool KSXR receive buffers 1033000 shared pool character set object 323724 shared pool FileIdentificatonBlock 323292 shared pool message pool freequeue 834752 shared pool KSXR pending messages que 841036 shared pool event statistics per sess 1718360 shared pool fixed allocation callback 180 large pool free memory 8388608 java pool free memory 33554432
已選擇42行。
15 顯示PGA的信息 select * from v$pgastat; NAME VALUE UNIT ---------------------------------------------------------------- ---------- --------- aggregate PGA target parameter 16777216 bytes aggregate PGA auto target 7640064 bytes global memory bound 838656 bytes total PGA inuse 8293376 bytes total PGA allocated 13106176 bytes maximum PGA allocated 22090752 bytes total freeable PGA memory 0 bytes PGA memory freed back to OS 0 bytes total PGA used for auto workareas 0 bytes maximum PGA used for auto workareas 4096 bytes total PGA used for manual workareas 0 bytes
NAME VALUE UNIT ---------------------------------------------------------------- ---------- --------- maximum PGA used for manual workareas 4096 bytes over allocation count 0 bytes processed 8783872 bytes extra bytes read/written 0 bytes cache hit percentage 100 percent
已選擇16行。
17 在$ORACLE_HOME/sqlplus/admin/glogin.sql中加入環境變量,以后每次啟動生效 define _editor=vi set line 2000
18 將當前命令隨加到文件中 save c:a.sql append
19 將指定文件的命讀出緩沖區 get c:a.sql
20 執行腳本語句 @ c:a.sql
21 將輸入保存到指定文件中 spool c:O.LOG select * from v$sga; spool off
29 啟動Oracle Web Server $ cd $ORACLE_HOME/Apache/Apache/bin $ ./startJServ.sh /database/oracle/product/9i/Apache/Apache/bin/apachectl start: httpd started
30 關閉Oracle Web Server $ cd $ORACLE_HOME/Apache/Apache/bin $ ./stopJServ.sh /database/oracle/product/9i/Apache/Apache/bin/apachectl stop: httpd stopped
31 啟動Oracle Web Server后默認的端口號是7777 unix cd $ORACLE_HOME/Apache/Apache/bin/ htpasswd $ORACLE_HOME/sqlplus/admin/iplusdba.pw admin windows cd D:oracleora92ApacheApachebin htpasswd D:oracleora92/sqlplus/admin/iplusdba.pw admin New password: ***** Re-type new password: ***** Adding password for user admin
33 指定用戶的表空間 SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='用戶名'
34 當前用戶 select user from dual;show user
35 當前用戶的缺省表空間 SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME=(select user from dual);
37 顯示正在使用的初始化參數文件 show parameter spfile
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string %ORACLE_HOME%DATABASESPFILE% ORACLE_SID%.ORA 38 用SPfile轉Pfile create pfile='c:init.ora' from spfile; create pfile from spfile; UNIX將生成在$ORACLE_HOME/dbs目錄下 WINDOWS將生成在$ORACLE_HOME/DATABASE目錄下
39 顯示cache的值 show parameter cache 前面兩個下劃線的是動態調整的參數 前面一個下劃線的是內部參數
40 顯示正在使用的參數和文本參數值 select * from v$parameter; select * from v$spparameter;
41 顯示動態性能視圖的結構 desc v$parameter; 名稱 NUM NAME TYPE VALUE ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE ISMODIFIED ISADJUSTED DESCRIPTION UPDATE_COMMENT
42 有條件的顯示動態參數 select * from v$parameter where name like '%db_cache%';
43 修改參數語句 alter system set sga_max_size=256m scope=spfile; 寫入SPFILE,重啟生效 alter system set sga_max_size=256m scope=memory; 寫入內存區,立即生效 alter system set sga_max_size=256m scope=both; 即寫入內存區,又寫入SPFILE,默認為BOTH.
44 動態調整large_pool_size的語句 alter system set large_pool_size=16M;
45 當前正在使用的SGA的大小 select sum(bytes)/1024/1024 from v$sgastat;
46 設置db_cache_size的大小保存在SPFILE alter system set db_cache_size=128m scope=spfile;
47 設置shared_pool_size的大小保存在SPFILE中 alter system set shared_pool_size=80m scope=spfile;
48 顯示PGA的狀態 select * from v$pgastat; show parameter pga NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_target big integer 16777216
50 顯示當前數據庫的狀態,mount的狀態下就可以查看數據庫的狀態 select open_mode from v$database;
51 顯示當前實例 nomount狀態下就可以查看實例 select * from v$instance;
52 顯示 background_dump_dest的路徑 , show parameter background_dump_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ background_dump_dest string D:oracleadminthwerpbdump
53 顯示當有用戶的表 select * from user_tables;
54 顯示當有用戶可以訪問表 select * from all_tables;
55 顯示用戶為SCOTT的表 select * from dba_tables where owner='SCOTT';
56 顯示所有用戶信息 select * from dba_users;
57 建立用戶并指定密碼 create user edpthw identified by edpthw;
58 給用戶授權 grant connect,resource to edpthw;
59 建立一個表 create table a(a int);
60 顯示數據字典中表名為USER開頭的表名 select table_name from dict where table_name like 'USER%';
61 在UNIX下oerr ora 錯誤號查看錯誤的幫助信息.
62 在unix 查看日志中有哪些錯誤 grep ORA alert_PROD.log|tail -10
63 顯示當前實例 SELECT * FROM V$INSTANCE;
64 顯示當前數據庫信息 SELECT * FROM V$DATABASE;
65 顯示當前ORACLR資料的版本 SELECT * FROM V$VERSION;
66 顯示當前的選項 SELECT * FROM V$OPTION;
67 顯示當前實例的初始化參數 SELECT * FROM V$PARAMETER;
68 顯示數據文件 SELECT * FROM V$DATAFILE;
69 顯示日志文件 SELECT * FROM V$LOGFILE
70 顯示控制文件 SELECT * FROM V$CONTROLFILE;
71 顯示當有有哪些會話 SELECT * FROM V$SESSION;
72 顯示當前有哪些進程 SELECT * FROM V$PROCESS;
73 顯示當前后臺進程 SELECT * FROM V$BGPROCESS;
74 顯示SGA的大小 SELECT * FROM V$SGA;
75 顯示SGA的統計數 SELECT * FROM V$SGASTAT;
76 顯示PGA的統計數 SELECT * FROM V$PGASTAT;
77 顯示有哪些控制文件 SHOW PARAMETER CONTROL_FILES;
78 增加一個控制文件一定要在NOMOUNT狀態下進行.語句如下. alter system set control_files='D:ORADATAMYDBCONTROL01.CTL', 'D:ORADATAMYDBCONTROL02.CTL', 'D:ORADATAMYDBCONTROL03.CTL', 'E:CONTROL04.CTL' SCOPE=SPFILE;
alter system set control_files='D:oracleoradatathwerpCONTROL01.CTL', 'D:oracleoradatathwerpCONTROL02.CTL', 'D:oracleoradatathwerpCONTROL03.CTL', 'D:oracleoradatathwerpCONTROL04.CTL' scope=spfile;
79 數據重新啟動 STARTUP FORCE
80 顯示當前控制文件 SELECT * FROM V$CONTROLFILE; SHOW PARAMETER CONTROL
81 刪除一個控制文件 alter system set control_files='D:ORADATAMYDBCONTROL01.CTL', 'D:ORADATAMYDBCONTROL02.CTL', 'D:ORADATAMYDBCONTROL03.CTL' SCOPE=SPFILE
82 顯示當前使用的日志 SELECT * FROM V$LOG;
83 顯示實例的歸檔模式 ARCHIVE LOG LIST;
84 顯示重做日志文件 SELECT * FROM V$LOGFILE;
85 手工切換重做日志文件 ALTER SYSTEM SWITCH LOGFILE;
86 發出檢查點 ALTER SYSTEM CHECKPOINT;
87 顯示重做日期的歷史記錄 select * from v$log_history;
88 更改會語的日期格式 alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
89 增加日志組 ALTER DATABASE ADD LOGFILE GROUP 4 ( 'D:ORADATAMYDBredo4.log') SIZE 10240K;
92 刪除日志組 ALTER DATABASE DROP LOGFILE GROUP 4 ;
90 增加日志組成員 ALTER DATABASE ADD LOGFILE MEMBER 'D:ORADATAMYDBredo11.log' TO GROUP 1;
91 刪除日志組成員 ALTER DATABASE DROP LOGFILE MEMBER 'D:ORADATAMYDBredo14.log' ;
92 顯示系統表空間 select * from dba_tablespaces;
93 顯示系統表空間所擁有的文件 select * from dba_data_files;
94 顯示系統臨時表空間 select * from dba_temp_files;
95 顯示表空間的已被使用多少 select tablespace_name,sum(bytes)/1024/1024 m from dba_data_files group by tablespace_name;
96 顯示表空間還有多少沒有使用 select tablespace_name, sum(bytes)/1024/1024 m from dba_free_space group by tablespace_name;
97 創建表空間 10G CREATE BIGFILE TABLESPACE "MYTBS1" DATAFILE 'D:ORADATAMYDBmytbs_01.dbf' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ; 9I CREATE TABLESPACE "MYTBS1" DATAFILE 'D:oracleORADATAthwerpmytbs_01.dbf' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ; CREATE SMALLFILE TABLESPACE "MYTBS1" DATAFILE 'D:ORADATAMYDBmytbs_01.dbf' SIZE 100M , 'D:ORADATAMYDBmytbs02.dbf' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
98 在指字表空間里建表 create table t1 (a int) tablespace mytbs1;
99 在指字表空間里建索引 create index t1_ind on t1(a) tablespace mytbs1;
100 在指定表空間里增加數據文件 ALTER TABLESPACE "MYTBS1" ADD DATAFILE 'D:ORADATAMYDBmytbs03.dbf' SIZE 100M
100 修改表空間的大小 ALTER DATABASE DATAFILE 'D:ORADATAMYDBMYTBS_01.DBF' RESIZE 200M
101 表空間改文件的自動擴展 ALTER DATABASE DATAFILE 'D:ORADATAMYDBMYTBS03.DBF' AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
102 刪除表空間和表空間所屬文件 drop tablespace mytbs1 including contents and datafiles;
103 建立臨時表空間 CREATE SMALLFILE TEMPORARY TABLESPACE "MYTEMP" TEMPFILE 'D:oracleORADATAthwerpmytemp01.dbf' SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M CREATE TEMPORARY TABLESPACE "MYTEMP" TEMPFILE 'D:oracleORADATAthwerpmytemp01.dbf' SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
104 更改用戶的臨時表空間 ALTER USER "GARY" TEMPORARY TABLESPACE "MYTEMP"
105 設置成默認的表空間 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "MYTEMP"
106 創建臨時表空間組 ALTER TABLESPACE MYTEMP TABLESPACE GROUP MYTEMP_GROUP ALTER TABLESPACE TEMP TABLESPACE GROUP MYTEMP_GROUP
110 更改重做表空間 alter system set undo_tablespace=myundo;
111 建立一個表 create table a tablespace users as select * from dba_objects;
112 插入一些記錄 insert into a select * from a;
113 有條件查詢表名放在哪個表空間 select * from dba_tables where table_name='A' and owner='SYS';
114 有條件查詢段名放在哪個表空間 select * from dba_segments where segment_name='A' and owner='SYS';
115 查詢有哪些段類型 select distinct segment_type from dba_segments;
116 查詢段和表空間的對應關系 select * from dba_extents where segment_name='A' and owner='SYS';
117 建立一個16K表空間 CREATE TABLESPACE "MYTBS3" DATAFILE 'D:oracleORADATAthwerpmytbs3.dbf' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO BLOCKSIZE 16384;
118 指字參數建立表 create table b (a int,b varchar2(10)) tablespace users INITRANS 2 PCTFREE 5;
119 顯示當前所使用的塊大小 show parameter block_size;
120 釋放表的高水位的空間 alter table a deallocate unused;
121 高水位前移.速度快 truncate table a;
122 分配空間給表 alter table a allocate extent (datafile 'D:ORADATAMYDBusers01.dbf' size 1m);
123 指定參數建立表 create table C (a int) tablespace system pctfree 10 pctused 60 storage(freelists 2); CREATE TABLE "SYS"."D" ( "A" VARCHAR2(10)) TABLESPACE "SYSTEM" PCTFREE 5 PCTUSED 60 INITRANS 2 MAXTRANS 100 STORAGE ( FREELISTS 2) 124 生成一個測試表 create table a tablespace users as select * from dba_objects;
125 查看表行的物理地址 select rowid form a;
126 用包的命令查看表行的物理地址 select DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) FROM A
127 建立表索引 CREATE INDEX A_IND ON A(OBJECT_NAME) TABLESPACE MYTBS2;
128 查看表的索引 SELECt * FROM DBA_INDEXES WHERE TABLE_NAME='A'; SELECT * FROM DBA_SEGMENTS WHERE SEGMENT_NAME='A_IND';
129 移動重組表空間 ALTER TABLE A MOVE TABLESPACE USERS;
130 重建表索引 ALTER INDEX A_IND REBUILD;
131 設置行可以內部移動屬性 ALTER TABLE A ENABLE ROW MOVEMENT;
132 在原地進行行的移動 ALTER TABLE A SHRINK SPACE;
133 查看表所存儲的信息 SELECT BYTES FORM DBA_SEGMENTS WHERE SEGMENT_NAME='A'; SELECT OWNER,SEGMENT_NAME,BYTES FROM DBA_SEGMENTS WHERE SEGMENT_NAME='A';
134 截取表 TRUNCATE TABLE A;
135 刪除表 DROP TABLE A CASCADE CONSTRAINTS;
136 顯示垃圾桶(10G才有) show recyclebin SELECT * FROM DBA_RECYCLEBIN; SELECT * FROM USER_RECYCLEBIN;
137 恢復已刪除的表 flashback table a to before drop;
138 恢復較早已前的已刪除的表 flashback table "BIN$hWrBq1irS0COp10n22NZJg==$0" to before drop rename to a1;
139 查詢指定表的索引 select * from dba_indexs where table_name='A';
140 清除垃圾桶 PURGE RECYCLEBIN; PURGE DBA_RECYCLEBIN;
141 刪除表并清除垃圾桶(10G) DROP TABLE A PURGE;
142 刪除表的列 ALTER TABLE A DROP COLUMN COMMENTS CASCAGE CONSTRAINTES CHECKPOINT 1000;
143 重命名列 ALTER TABLE A RENAME COLUMN HIRE_DATE TO START_DATE;
144 標記不可使用的列 ALTER TABLE A SET UNUSED COLUMN COMMENTS CASCADE CONSTRAINTS;
145 刪除不再使用的的列 ALTER TABLE A DROP UNUSED COLUMNS CHECKPOINT 10000;
146 繼續列的刪除操作 ALTER TABLE A DROP COLUMNS CONTINUE CHECKPOINT 10000;
147 增加表的主索引 ALTER TABLE A ADD PRIMARY KEY (OBJECT_ID);
148 刪除表的主索引 ALTER TABLE A DROP PRIMARY KEY;
149 索引表的主索引并指定索引所使用的表空間 ALTER TABLE A ADD PRIMARY KEY (OBJECT_ID) USING INDEX TABLESPACE USERS;
150 建立一個普通索引 CREATE INDEX A_NAME_IND ON A(OWNER,OBJECT_NAME);
151 建立一個函數索引 SELECT /*+ INDEX(A A_NAME2_IND) */ * FROM A WHERE UPPER(OBJECT_NAME)='A';
152 建立一個位圖索引 CREATE BITMAP INDEX A_NAME_BT ON A(OWNER);
153 查詢數據庫的信息 SELECT /*+ INDEX(A A_NAME_BT) */ * FROM A WHERE OWNER='SYS' OR OWNER='SYSTEM'; SELECT /*+ FULL(A) */ * FROM A WHERE OWNER='SYS' OR OWNER='SYSTEM';
154 生成一個腳本來建立索引重整 SELECT 'ALTER INDEX '||INDEX_NAME||' COALESCE;' FROM DBA_INDEXES WHERE TABLE_NAME='A' AND OWNER='SYS' SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD ONLINE;' FROM DBA_INDEXES WHERE TABLE_NAME='A' AND OWNER='SYS'
155 顯示所有用戶的信息 SELECT * FROM DBA_USERS;
156 顯示當前數據庫的用戶 SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS;
160 建立一個新用戶 create user edpthw identified by "123456" default tablespace users temporary tablespace temp quota unlimited on users quota 10m on mytbs2;
161 查詢限額信息 select * from dba_ts_quotas; select * from user_ts_quotas;
162 用戶改密碼 alter user edpthw identified by edpthw;
163 鎖用戶 alter user edpthw account lock;
164 使密碼過期,迫使改下一次進入改密碼 alter user edpthw password expire;
165 取消限額 alter user edpthw quota unlimited on mytbs2;
166 刪除用戶 drop user hmtong cascade;
167 授權用戶 grant create session,create table,create view to edpthw;
168 分別授權 GRANT CREATE ANY TABLE TO "hmtong" GRANT CREATE TABLE TO "hmtong" GRANT DROP ANY TABLE TO "hmtong" GRANT SELECT ANY TABLE TO "hmtong"
169 折消授權 REVOKE UNLIMITED TABLESPACE FROM "hmtong"
170 對象授權 GRANT SELECT ON "hmtong"."A" TO "edpthw"
171 折消對象授權 REVOKE SELECT ON "hmtong"."A" FROM "edpthw"
173 查詢Pwfile中存放的用戶信息 select * from v$pwfile_users;
174 授權sysdba給指定用戶 grant sysdba to hmtong;
175 取消指定用戶的sysdba權限 revoke sysdba from hmtong;
176 修改系統的授權的屬性 alter system set remote_login_passwordfile=exclusive; 能sysdba登錄,能授權 alter system set remote_login_passwordfile=shared scope=spfile; 只能sysdba登錄,不能授權 alter system set remote_login_passwordfile=NONE; 取消
177 重建口令文件 orapwd file=PWDmydb.ora password=itpub
178 創建一個用戶 create user hmtong identified by abcdefg;
179 授權連接給指定用戶 GRANT "CONNECT" TO "hmtong"; GRANT UNLIMITED TABLESPACE,CONNECT,RESOURCE TO HMTONG;
