Commands that Oracle DBAs should be familiar with _oracle
Last Update:2017-01-18
Source: Internet
Author: User
1 Running Sqlplus Tools
Sqlplus
2 The default identity of the OS Connection
/As Sysdba
3 displaying the current user name
Show user
4 go directly to the Sqlplus command prompt
Sqlplus/nolog
5 connecting at the command prompt with OS identity
Connect/as SYSDBA
6 Connected as System
Connect system/xxxxxxx@ Service Name
7 show which tables are of course users
SELECT * from tab;
8 shows the status of a user name and account
Select Username,account_status from Dba_users;
9 Unlock the Scott account (lock)
Alter user Scott account unlock (lock);
10 Connect as Scott and view the owning table
Connect Scott/tiger
SELECT * from tab;
11 View the EMP table structure and record content
DESC EMP
Select Empno,ename from EMP;
12 to see the SGA, shared pool, cache information as OS
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 View all information that contains 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 Displaying the SGA information
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 rows have been selected.
15 Display the PGA information
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 percent
16 rows have been selected.
17 Add environment variables to the $oracle_home/sqlplus/admin/glogin.sql and take effect every time it starts
Define _editor=vi
Set Line 2000
18 Add the current command to the file
Save C:a.sql Append
19 reads the specified file's life out of the buffer
Get C:a.sql
20 Executing script statements
@ c:a.sql
21 save input to the specified file
Spool C:o. LOG
SELECT * from V$SGA;
Spool off
22 Set Line Size
Set Linesize 2000
23 Set Page Size
Set PageSize 10
24 Set character Fuleg type
Col ename format A30
25 Set Digital column format
Col sal format 999,999.999
10G View File $oracle_home/install/protlist display port
Http://127.0.0.1:5560/isqlplus
9i View File $oracle_home/apache/apache/ports.ini display port
Http://127.0.0.1:7778/isqlplus
Http://127.0.0.1:7778/isqlplusdba
27 Start Oracle 9i Listener
Oracle's listener primarily provides interfaces for client connections
$ lsnrctl Start
28 Turn off Oracle 9i Listener
$ lsnrctl Stop
29 Starting Oracle Web Server
$ cd $ORACLE _home/apache/apache/bin
$./startjserv.sh
/database/oracle/product/9i/apache/apache/bin/apachectl START:HTTPD started
30 turning off Oracle Web Server
$ cd $ORACLE _home/apache/apache/bin
$./stopjserv.sh
/database/oracle/product/9i/apache/apache/bin/apachectl STOP:HTTPD stopped
31 The default port number after starting Oracle Web server is 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
Pfile: $ORACLE _base/admin/Instance Name/pfile
SPFile: $ORACLE _home/dbs
Listener configuration file: Listener.ora: $ORACLE _home/network/admin/
Tnsnames.ora: $ORACLE _home/network/admin/
33 specifying the user's table space
SELECT default_tablespace from dba_users WHERE username= ' username '
34 Current User
Select User from Dual;show user
35 Default table space for the current user
Select Default_tablespace from Dba_users WHERE username= (select User from dual);
37 displaying the initialization parameter file being used
Show Parameter SPFile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
SPFile string%oracle_home%databasespfile%
oracle_sid%. ORA
38 Turn pfile with SPFile
Create pfile= ' C:init.ora ' from SPFile;
Create Pfile from SPFile;
UNIX will be generated in the $oracle_home/dbs directory
Windows will be generated in the $oracle_home/database directory
39 showing the cache value
Show parameter cache
The front two underscores are dynamically adjusted parameters
An underscore in the front is an internal parameter
40 displaying parameters and text parameter values in use
SELECT * from V$parameter;
SELECT * from V$spparameter;
41 showing the structure of the dynamic performance view
Desc V$parameter;
Name
Num
NAME
TYPE
VALUE
IsDefault
Isses_modifiable
Issys_modifiable
IsModified
isadjusted
DESCRIPTION
Update_comment
42 conditional display of dynamic parameters
SELECT * from V$parameter where name like '%db_cache% ';
43 Modifying parameter statements
Alter system set sga_max_size=256m Scope=spfile; && write SPFile, reboot effective
Alter system set sga_max_size=256m scope=memory; && write to memory area, effective immediately
Alter system set sga_max_size=256m Scope=both; && writes to the memory area and writes to SPFile, which defaults to both.
44 Dynamic adjustment of Large_pool_size statements
alter system set large_pool_size=16m;
45 the size of the SGA currently in use
Select sum (bytes)/1024/1024 from V$sgastat;
46 set the size of the db_cache_size to save in SPFile
Alter system set db_cache_size=128m Scope=spfile;
47 set the size of the shared_pool_size to be saved in SPFile
Alter system set shared_pool_size=80m Scope=spfile;
48 showing the PGA status
SELECT * from V$pgastat;
Show parameter PGA
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Pga_aggregate_target Big Integer 16777216
49 with Pfile to start the database,? represents Oracle's Home directory
Startup Pfile=?databaseinitmydb.ora
50 shows the status of the current database, the status of the database can be viewed under the Mount
Select Open_mode from V$database;
51 Displays the current instance Nomount state to view the instance
SELECT * from V$instance;
52 shows the path of the Background_dump_dest,
Show Parameter Background_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Background_dump_dest string D:oracleadminthwerpbdump
53 Show when there is a user's table
SELECT * from User_tables;
54 Show when a user can access the table
SELECT * from All_tables;
55 table showing the user as Scott
SELECT * from Dba_tables where owner= ' SCOTT ';
56 Displaying all user information
SELECT * from Dba_users;
57 Create user and specify password
Create user edpthw identified by EDPTHW;
58 authorized to the user
Grant Connect,resource to EDPTHW;
59 Establish a table
CREATE Table A (a int);
60 Display the table name in the data dictionary that starts with the table name user
Select table_name from DICT where table_name like ' user% ';
61 Oerr The ORA error number under UNIX to view the wrong help information.
62 What are the errors in viewing logs on Unix
grep ORA alert_prod.log|tail-10
63 displaying the current instance
SELECT * from V$instance;
64 Displaying current database information
SELECT * from V$database;
65 Display the current ORACLR data version
SELECT * from V$version;
66 displaying the current options
SELECT * from V$option;
67 Display initialization parameters for the current instance
SELECT * from V$parameter;
68 Displaying Data files
SELECT * from V$datafile;
69 Displaying log files
SELECT * from V$logfile
70 Display control files
SELECT * from V$controlfile;
71 shows what sessions are available when
SELECT * from V$session;
72 show which processes are currently
SELECT * from V$process;
73 displaying the current background process
SELECT * from V$bgprocess;
74 show the size of the SGA
SELECT * from V$SGA;
75 shows the number of SGA statistics
SELECT * from V$sgastat;
76 showing the PGA statistics
SELECT * from V$pgastat;
77 What control files are displayed
Show PARAMETER Control_files;
78 Adding a control file must be done in the Nomount State. The statement is as follows.
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 Data reboot
STARTUP FORCE
80 displaying the current control file
SELECT * from V$controlfile;
Show PARAMETER Control
81 Delete a control file
Alter system set control_files= ' D:oradatamydbcontrol01. CTL ',
' D:oradatamydbcontrol02. CTL ',
' D:oradatamydbcontrol03. CTL ' Scope=spfile
82 displaying the log currently in use
SELECT * from V$log;
83 archiving mode for displaying instances
ARCHIVE LOG LIST;
84 displaying redo log files
SELECT * from V$logfile;
85 Manually switch redo log files
ALTER SYSTEM SWITCH LOGFILE;
86 issue checkpoints.
ALTER SYSTEM CHECKPOINT;
87 shows the history of the redo date
SELECT * from V$log_history;
88 Change the date format of the language
Alter session set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss ';
89 Increase the Log group
ALTER DATABASE ADD LOGFILE GROUP 4 (' D:oradatamydbredo4.log ') SIZE 10240K;
92 Delete Log Group
ALTER DATABASE DROP LOGFILE GROUP 4;
90 adding log Group members
ALTER DATABASE ADD LOGFILE member ' D:oradatamydbredo11.log ' to GROUP 1;
91 Delete log Group members
ALTER DATABASE DROP LOGFILE member ' D:oradatamydbredo14.log ';
92 Display System Tablespace
SELECT * from Dba_tablespaces;
93 displaying the files owned by the system table space
SELECT * from Dba_data_files;
94 Display System Temp Table space
SELECT * from Dba_temp_files;
95 shows how much of the table space has been used
Select Tablespace_name,sum (bytes)/1024/1024 m
From Dba_data_files GROUP by Tablespace_name;
96 shows how much of the table space is not used
Select Tablespace_name,
Sum (bytes)/1024/1024 m
From Dba_free_space GROUP by Tablespace_name;
97 Creating a table space
10G CREATE bigfile tablespace "MYTBS1" datafile ' d:oradatamydbmytbs_01.dbf ' SIZE 100M LOGGING EXTENT MANAGEMENT Local SEGM ENT space MANAGEMENT AUTO;
9I CREATE tablespace "MYTBS1" datafile ' d:oracleoradatathwerpmytbs_01.dbf ' SIZE 100M LOGGING EXTENT MANAGEMENT Local Segme NT space MANAGEMENT AUTO;
CREATE smallfile tablespace "MYTBS1" datafile ' d:oradatamydbmytbs_01.dbf ' size 100M, ' d:oradatamydbmytbs02.dbf ' size 100 M LOGGING EXTENT MANAGEMENT SEGMENT space MANAGEMENT AUTO;
98 building a table in the space of the finger table
CREATE TABLE T1 (a int) tablespace MYTBS1;
99 indexing in the space of the finger table
CREATE index T1_ind on T1 (a) tablespace mytbs1;
100 adding data files to the specified tablespace
ALTER tablespace "MYTBS1" ADD datafile ' d:oradatamydbmytbs03.dbf ' SIZE 100M
100 Modify the size of the table space
ALTER DATABASE datafile ' d:oradatamydbmytbs_01.dbf ' RESIZE 200M
Automatic extension of 101 table space for file modification
ALTER DATABASE datafile ' D:oradatamydbmytbs03. DBF ' autoextend on NEXT 5M MAXSIZE Unlimited
102 Delete the table space and the table space belongs to the file
Drop tablespace mytbs1 including contents and datafiles;
103 Create a temporary table space
CREATE smallfile Temporary tablespace "MYTEMP" Tempfile ' d:oracleoradatathwerpmytemp01.dbf ' SIZE 200M autoextend on NEXT 1 0M 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 Change the user's temporary table space
ALTER USER "GARY" temporary tablespace "MYTEMP"
105 set to the default table space
ALTER DATABASE DEFAULT Temporary tablespace "MYTEMP"
106 Create a temporary table space group
ALTER tablespace MYTEMP tablespace GROUP mytemp_group
ALTER tablespace TEMP tablespace GROUP mytemp_group
107 Creating a redo table space
CREATE UNDO tablespace "Myundo" datafile ' d:oradatamydbmyundo01.dbf ' SIZE 200M
108 Display Redo Parameters
Show parameter Undo
109 The use of rolling back section
SELECT * from Dba_rollback_segs;
110 Change Redo Table space
alter system set Undo_tablespace=myundo;
111 Establish a table
Create Table A tablespace users as select * from Dba_objects;
112 Insert Some Records
INSERT into a SELECT * from A;
113 Conditional query table name in which table space
SELECT * from Dba_tables where table_name= ' A ' and owner= ' SYS ';
114 Conditional query segment name in which table space
SELECT * from dba_segments where segment_name= ' A ' and owner= ' SYS ';
115 What segment types are queried
Select distinct Segment_type from dba_segments;
116 Correspondence between the query segment and the table space
SELECT * from dba_extents where segment_name= ' A ' and owner= ' SYS ';
117 Create a 16K table space
CREATE tablespace "MYTBS3" datafile ' d:oracleoradatathwerpmytbs3.dbf ' SIZE 100M LOGGING EXTENT MANAGEMENT local SEGMENT SP ACE MANAGEMENT AUTO BLOCKSIZE 16384;
118 reference parameters to establish a table
CREATE Table B (a int,b varchar2 (a)) tablespace users Initrans 2 PCTFREE 5;
119 Display the block size currently in use
Show parameter block_size;
120 free table of high water level space
ALTER TABLE a deallocate unused;
121 High water level forward, fast
TRUNCATE TABLE A;
122 allocating space to the table
ALTER TABLE A allocate extent (datafile ' d:oradatamydbusers01.dbf ' size 1m);
123 Specify parameters to establish a table
CREATE TABLE C (a int) tablespace system pctfree pctused Storage (freelists 2);
CREATE TABLE "SYS". D "(" A "VARCHAR2 ()) tablespace" SYSTEM "PCTFREE 5 pctused Initrans 2 Maxtrans STORAGE (freelists 2)
124 Generate a test table
Create Table A tablespace users as select * from Dba_objects;
125 View the physical address of a table row
Select rowID form A;
126 View the physical address of a table row with a package command
Select Dbms_rowid. ROWID_RELATIVE_FNO (ROWID), Dbms_rowid. Rowid_block_number (ROWID), Dbms_rowid. Rowid_row_number (ROWID) from A
127 establishing a Table index
CREATE INDEX a_ind on A (object_name) tablespace MYTBS2;
128 View the index of a table
SELECt * from dba_indexes WHERE table_name= ' A ';
SELECT * from dba_segments WHERE segment_name= ' a_ind ';
129 Move the reassembly table space
ALTER TABLE A move tablespace USERS;
130 Rebuild Table Index
ALTER INDEX A_ind REBUILD;
131 set lines can move properties internally
ALTER TABLE A ENABLE ROW MOVEMENT;
132 in situ movement of the line
ALTER TABLE A SHRINK space;
133 View the information that the table stores
SELECT BYTES FORM dba_segments WHERE segment_name= ' A ';
SELECT owner,segment_name,bytes from dba_segments WHERE segment_name= ' A ';
134 Intercept Table
TRUNCATE TABLE A;
135 Deleting a table
DROP TABLE A CASCADE CONSTRAINTS;
136 Show Trash Bin (10G only)
Show RecycleBin
SELECT * from Dba_recyclebin;
SELECT * from User_recyclebin;
137 Recovering a deleted table
Flashback table A to before drop;
138 Restore a table that was already deleted earlier
Flashback table "Bin$hwrbq1irs0cop10n22nzjg==$0" to Before drop rename to A1;
139 querying the index of a specified table
SELECT * from Dba_indexs where table_name= ' A ';
140 Clear Trash cans
PURGE RecycleBin;
PURGE Dba_recyclebin;
141 Delete the table and clear the Trash bin (10G)
DROP TABLE A PURGE;
142 Deleting a table's columns
ALTER TABLE A DROP COLUMN COMMENTS cascage constraintes CHECKPOINT 1000;
143 Renaming columns
ALTER TABLE A RENAME COLUMN hire_date to start_date;
144 columns not available for marking
ALTER TABLE A SET unused COLUMN COMMENTS CASCADE CONSTRAINTS;
145 Delete columns that are no longer in use
ALTER TABLE A DROP unused COLUMNS CHECKPOINT 10000;
146 Continue column Delete operation
ALTER TABLE A DROP COLUMNS CONTINUE CHECKPOINT 10000;
147 increase the primary index of the table
ALTER TABLE A ADD PRIMARY KEY (object_id);
148 Delete a table's primary index
ALTER TABLE A DROP PRIMARY KEY;
149 The primary index of the index table and specify the tablespace used by the index
ALTER TABLE A ADD PRIMARY KEY (object_id) USING INDEX tablespace USERS;
150 establish a normal index
CREATE INDEX a_name_ind on A (owner,object_name);
151 Establish a function index
SELECT/*+ INDEX (a a_name2_ind) * * from a WHERE UPPER (object_name) = ' a ';
152 Create a bitmap index
CREATE BITMAP INDEX a_name_bt on A (OWNER);
153 querying the database for information
SELECT/*+ INDEX (a a_name_bt) * * from a WHERE owner= ' SYS ' or owner= ' SYSTEM ';
SELECT/*+ Full (A) * * * from WHERE owner= ' SYS ' or owner= ' SYSTEM ';
154 generate a script to create an index reorganization
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 Displaying information for all users
SELECT * from Dba_users;
156 users displaying the current database
SELECT username,account_status,default_tablespace,temporary_tablespace from Dba_users;
160 Create a new user
Create user edpthw identified by "123456"
Default Tablespace users
Temporary Tablespace Temp
Quota Unlimited on users
Quota 10m on MYTBS2;
161 Query Limit Information
SELECT * from Dba_ts_quotas;
SELECT * from User_ts_quotas;
162 User Change Password
Alter user EDPTHW identified by EDPTHW;
163 Lock User
Alter user EDPTHW account lock;
164 to expire the password, forcing the next entry to change the password
Alter user edpthw password expire;
165 Cancellation Limit
Alter user EDPTHW quota unlimited on MYTBS2;
166 Delete User
Drop user Hmtong cascade;
167 Authorized Users
Grant Create session,create table,create view to EDPTHW;
168 Separate authorization
GRANT CREATE any TABLE to "Hmtong"
GRANT CREATE TABLE to "Hmtong"
GRANT DROP any TABLE to "Hmtong"
GRANT SELECT any TABLE to "Hmtong"
169 percent Authorization
REVOKE Unlimited tablespace from "Hmtong"
170 Object Authorization
GRANT SELECT on "Hmtong". A "to" EDPTHW "
171 Percent Elimination Object authorization
REVOKE Select on "Hmtong". A "from" EDPTHW "
172 cancel into the system directly with the default password, and must enter the password you can enter
Unix $ORACLE _homenetworkadminsqlnet. ORA
Windows D:oracleproduct10.1.0db_1networkadminsqlnet. ORA
Sqlnet. Authentication_services= (Nts,none)
modifying $oracle_home/network/admin/sqlnet.ora files
The original: sqlnet.authentication_services= (NTS)
Instead: Sqlnet. Authentication_services= (NONE)
or direct comment: #SQLNET. Authentication_services= (NTS)
173 query Pwfile of the user information stored in
SELECT * from V$pwfile_users;
174 authorized SYSDBA to the specified user
Grant SYSDBA to Hmtong;
175 Remove SYSDBA permissions for the specified user
Revoke SYSDBA from Hmtong;
176 Modify the properties of the system's authorization
alter system set remote_login_passwordfile=exclusive; Can SYSDBA login, can authorize
Alter system set remote_login_passwordfile=shared Scope=spfile; can only SYSDBA login, cannot authorize
alter system set Remote_login_passwordfile=none; Cancel
177 Rebuilding password files
Orapwd File=pwdmydb.ora password=itpub
178 Create a user
Create user Hmtong identified by ABCDEFG;
179 authorized connection to the specified user
GRANT "CONNECT" to "Hmtong";
GRANT Unlimited Tablespace,connect,resource to Hmtong;
180 Creating a table
CREATE TABLE T1 (a int);
CREATE TABLE t2 (a int);
181 View System Privileges
SELECT * from User_sys_privs;
182 Viewing object privileges
SELECT * from User_tab_privs;
183 View the granted role
SELECT * from User_role_privs;
184 See what permissions the role has
SELECT * from Role_sys_privs;
185 See what tables the role has
SELECT * from Role_tab_privs;
186 View permissions for the current session
SELECT * from Session_privs;
187 Status of the listener
Lsnrctl start
$ORACLE _home/network/admin/listener.ora
188 start and shutdown of listeners
Lsnrctl start [name]
Lsnrctl stop [first name]
189 Status and service information of the listener
LSNRCTL Stat
Lsnrctl Service
190 Tools for configuring listeners
Netca
Netmgr
192 Dynamic Registration Listener
alter system register;
193 Configure the listener's two parameters
Show Parameter Local_listener
Show Parameter Remote_listener
194 Append set a service name, service name can be multiple corresponding one instance
Alter system set SERVICE_NAMES=MYDB,APPDB
195 Client Connection Configuration file
$ORACLE _home/network/admin/tnsname.ora
196 Check if the specified listener exists
Tnsping MYDB
197 will be sqlnet.ora configured to connect SYSTEM/TESTDB1@192.168.100.80:1521/TESTDB1 mode connection.
Sqlnet. Authentication_services= (Nts,none)
NAMES. Directory_path= (Tnsnames,ezconnect)
198 Fast Restart Database
Shutdown immediate
199 Open the database to the state of the Mount
Startup Mount
200 show whether the database is archived or not archive mode
Archive Log List
Database log Mode archive mode
AutoArchive disabled
Archive Endpoint D:oracleora92rdbms
The earliest summary log sequence 64
Next Archive log sequence 69
Current log Sequence 69
201 Changing the archive mode of the database in the state of the database mount
ALTER DATABASE Archivelog; Archive
ALTER DATABASE Noarchivelog; Not archived
202 Open the Database
ALTER DATABASE open;
203 to view the archived path
Sql> Show Parameter log_archive_dest_x
204 Dynamic Configuration archive paths are archived at the same time in two different directories
Alter system set log_archive_dest_1= ' location=e:archive ';
Alter system set log_archive_dest_2= ' location=d:archive ';
205 switching the system's next log file
alter system switch logfile; (10G will produce archive)
206 Defining automatic archiving
Alter system set LOG_ARCHIVE_START=TRUE Scope=spfile; (9i) (10G not set)
207 View the archived information of the database
SELECT * from V$log;
208 See what files and paths have been done
SELECT * from V$archived_log;
209 to see what information is not yet archived
SELECT * from V$archive;
210 Set Archive Path 2 do not archive and enable archiving temporarily
alter system set Log_archive_dest_state_2=defer;
alter system set log_archive_dest_state_2=enable;
211 to view the number of minimum path archive parameters
Show Parameter Log_archive_min_succeed_dest
212 view file name format parameters for the archive path
Show Parameter Log_archive_format
213 format of filing file name
Alter system set log_archive_format= ' Mydb_%s_%r_%t.log ' scope=spfile;
214 shows the size and path of the fast recovery area
Show Parameter Db_recovery_file_dest
Show Parameter Db_recovery_file_dest_size
215 Change the archive path to the Quick recovery area
Alter system set log_archive_dest_1= ' Location=use_db_recovery_file_dest ';
216 Changing the path of the quick recovery area
Alter system set db_recovery_file_dest= ' E:recover ';
217 Viewing the use of the quick recovery area
SELECT * from V$recovery_file_dest;
218 change the size of the quick recovery area
alter system set db_recovery_file_dest_size=10g;
219 View the archive mode of the database
Select Log_mode from V$database;
Archive Log List
220 to view archive paths for archived files
Show Parameter Log_archive_dest
SELECT * from V$archive_dest;
221 View Archive
SELECT * from V$archive;
222 View records that have been archived
SELECT * from V$archived_log;
223 Start online Backup table space
Alter TABLESPACE users begin backup;
224 querying the file path for the specified table space
Select file_name from Dba_data_files where tablespace_name= ' USERS ';
225 temporarily left in the SQL state to return to the shell
Host Ocopy user*.dbf E:backup
226 End Build Backup table space
Alter TABLESPACE users end backup;
227 Control files for the backup database
ALTER DATABASE backup Controlfile to ' E:backupcontrol01.ctl ';
228 generating scripts to create control files
ALTER DATABASE backup Controlfile to trace as ' e:backupcontrol01.sql ';
229 query which file needs to be restored
SELECT * from V$recover_file;
230 query for details of the specified file number
SELECT * from V$datafile where file#=6;
231 What needs to be archived
SELECT * from V$recovery_log;
232 Recover the data file of the word
Recover DataFile 6;
233 Create an example table
CREATE TABLE A11 tablespace MYTBS3 as SELECT * from Dba_objects;
233 query There are no specified files
SELECT * from dba_extents where file_id=6;
224 automatically find archived log recover corresponding data file
Recover AUTOMATIC datafile 6;
225 setting a table space to be unavailable
ALTER tablespace USERS OFFLINE IMMEDIATE
226 Restore the specified table space
RECOVER tablespace USERS;
227 Set the specified table file to be unavailable
ALTER DATABASE datafile 6 OFFLINE;
228 set the specified table file to online
ALTER DATABASE datafile 6 ONLINE;
229 Creating a Test table
CREATE TABLE TEST (A DATE);
230 Insert Current system date
INSERT into TEST VALUES (sysdate);
231 The value of the query test
SELECT * from TEST;
232 format of date set in the registration table
NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS
233 revert to a specified point in time
RECOVER DATABASE UNTIL time ' 2006-11-26 10:49:43 ';
234 open repository and reset redo log
ALTER DATABASE open resetlogs;
235 Recovery control files
RECOVER DATABASE using Backup Controlfile UNTIL cancel;
236 Append temporary table space
Alter tablespace temp add tempfile ' d:oradatamydbtemp01.dbf ' size 10m reuse;
237 Rman Connection
Connect Target/
238 displaying the database files to be backed up
a schema;
239 backing up files at the specified location in Rman
Run {
Allocate channel CH1 type disk format ' E:rman%u.bak ' maxpiecesize=2g;
Backup database;
}
240 Show protesters in Rman default settings
Show All;
Backup database;
241 Configuring the default path
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ' E:rman%u.bak ' maxpiecesize 2G;
242 Clear Default configuration
CONFIGURE CHANNEL DEVICE type DISK clear;
243 Configuring parallel numbers
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
244 Configuring automatic Backup control files
CONFIGURE Controlfile autobackup on;
245 Configuration Benefits
CONFIGURE RETENTION POLICY to redundancy 2;
246 configuration can be restored to the retention window within 7 days
CONFIGURE RETENTION POLICY to recovery window of 7;
247 Backup Table Space
Backup tablespace users;
248 Backup Data Files
Backup DataFile 6;
249 Redo Log before backup
Backup Archivelog until time ' sysdate-1 ';
250 Backup SP Files
Backup SPFile;
251 backing up the current control file
Backup current controlfile;
252 list all the backup sets
List backupset;
253 List all Summary backup sets
List Backupset summary;
254 list the specific backup information
List Backupset 16
255 Delete Backup Set 13
Delete Backupset 13;
256 do not prompt to delete all backup sets
Delete NoPrompt backupset;
257 Backups of all users are deleted
Delete Backup of tablespace users;
258 What unwanted backup sets are listed
the obsolete;
259-side Backup Edge compression database
Backup as compressed backupset database;
260 Backup of a table space at level 0 of an incremental backup
Backup Incremental level 0 tablespace users;
261 Level 2 Backup Cumulative backup
Backup Incremental Level 2 cumulative tablespace users;
262 Delete archived logs for a specified date
DELETE archivelog UNTIL time ' sysdate-1/24 ';
263 Restore the specified table space in Rman
RUN {
SQL ' ALTER tablespace USERS OFFLINE IMMEDIATE ';
RESTORE tablespace USERS;
RECOVER tablespace USERS;
SQL ' ALTER tablespace USERS ONLINE ';
}
264 recovery control files in Rman
STARTUP Nomount
RESTORE controlfile from Autobackup;
ALTER DATABASE MOUNT;
RESTORE DATABASE VALIDATE;
RECOVER DATABASE;
ALTER DATABASE OPEN resetlogs;
265 the character set of the query database
SELECT * from database_properties where property_name like ' nls_cha% ';
Nls_lang=simplified Chinese_china. Zhs16gbk
266 exporting data to a file
Exp userid=system/oracle file=full.dmp full=y
Exp userid=system/oracle file=full1.dmp,full2.dmp,full3.dmp filesize=2g full=y direct=y log=full.log
267 exporting a specified user's table
Exp userid=system/oracle file=gary.dmp Owner=gary,wei direct=y log=gary
Exp userid=system/oracle file=gary.dmp owner=gary direct=y log=gary.log
268 Exporting the specified table
Exp userid=system/oracle file=gary_a.dmp tables=gary.a direct=y log=gary
269 Exporting the specified table
Exp userid=system/oracle file=gary_a1.dmp tables=gary.a query= ' where owner= ' ' Gary ' log=gary. LOG
270 View the exported Help information
Exp-help
271 Delete Gary's users
Drop user Gary Cascade;
272 Rebuilding user Gary
CREATE USER ' Gary ' profile ' DEFAULT ' identified by ' Gary ' account UNLOCK
273 Authorized users Gary
GRANT Unlimited Tablespace,connect,resource to "GARY";
274 Import user Gary's data
Imp userid=system/oracle fromuser=gary touser=gary show=y
Imp userid=system/oracle fromuser=gary touser=gary file=full.dmp log=imp.log
Imp userid=system/oracle fromuser=gary Touser=wei file=gary.dmp log=imp.log
275 Query Status
Select Object_name,object_type,owner from dba_objects where status= ' INVALID ';
EXEC Utl_recomp. Recomp_parallel
276 creating objects that allow you to import the export using this directory
Create directory Mydir as ' E:expdata ';
277 Permissions for authorized users
Grant Read,write on directory Mydir to Gary;
278 Exporting data
EXPDP userid=gary/gary dumpfile=gary directory=mydir parallel=2 schemas=gary
EXPDP userid=gary/gary Attach=gary_job
268 Query Tasks
SELECT * from V$datapump_job;
269 Importing Data
IMPDP userid=system/oracle dumpfile=gary Directory=mydir job_name=gary_job