Commands that Oracle DBAs should be familiar with _oracle

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