Required for programmers: Routine Oracle maintenance commands

The previous article talked about the daily maintenance commands of Linux and the daily maintenance commands of oracle. I believe that Oracle databases must be maintained more or less. Should I import and export Oracle databases? (Oh, you said you used PL/SQL tools for import and export, and you couldn't even use the commands. you can do it yourself ...). If there are obvious omissions or errors in this article, please help us to point out, thank you!

Test Environment: Oracle 10g 1. Start and close the database1. Various instance startup Modes 1) Start the instance and load and open the database. This is the most common database operation, meaning that an instance has been started and the database has been loaded and opened. This mode allows any valid user to connect to the database and perform typical data access operations. Start the instance, read the initialization parameters from the default server parameter file location, and load and open the database using the startup command: SQL> startup2) to start the instance, you do not need to load the database to start the instance instead of the database. Generally, you only need to use this mode during the whole database creation process: SQL> startup nomount3) to start the instance, and load the database to start the instance and load the database, but do not open the database. This mode allows you to perform specific maintenance operations. For example, in the following task, you must load the database without opening the database. * Rename the data file * Add cancel or rename the redo log file * allow and disable redo log archiving options * execute the complete data to restore the SQL> startup mount4) during the startup process, restricted access to the database can start the instance and load and open the database in restricted mode, so that only the Administrator rather than the general database user can use the database. Use this database startup mode when you need to complete one of the following tasks: * execute database data import or export operations * execute Database loading operations with SQL * loader * temporarily prevents general users from using data * during a certain migration process and upgrade operation, SQL> startup restrict5) forcing the instance to start in some special environments may encounter some problems when starting the database instance. Generally, do not force the database to start unless the following situations exist: * The current instance cannot be shut down using the shutdown normal, shutdown immediate, and shutdown transactional commands. * Some problems occur when starting the instance. SQL> startup force6) Start the instance, load the database, and start the complete media recovery process. If you know the media recovery process, you can start the instance and load the database for it, you can also use the startup command with the recover option to automatically start the recovery process. Startup open recover2. Change Database Availability 1) load database SQL for the instance> alter database Mount; 2) open a closed Database SQL> alter database open; 3) open Database SQL> alter database open read only in read-only mode; Open Database SQL> alter database open read write in read/write mode; 3. Close database 1) closing the database in normal mode does not allow new connections, waits for the session to end, waits for the transaction to end, makes a checkpoint, and closes the data file. No instance recovery is required at startup. SQL> shutdown normal2) shut down the database in Immediate Mode. Do not wait for the session to end, do not wait for the transaction to end, make a checkpoint, and close the data file. Transactions that are not finished are automatically rolled back. No instance recovery is required at startup. SQL> shutdown immediate3) closes the database in transaction mode and does not allow new connections, does not wait for the session to end, waits for the transaction to end, makes a checkpoint, and closes the data file. No instance recovery is required at startup. SQL> shutdown transactional4) shut down the database in the termination mode. The database does not allow new connections, does not wait for the session to end, does not wait for the transaction to end, does not check points, and does not close the data file. The instance is automatically restored at startup. SQL> shutdown abort 2. User Logon1. Start SQL * Plus # sqlplus/nolog2 without connecting to the database, Log On As sysdba in the command prompt environment, and start SQL * plus1) # sqlplus/nologsql> connect/As sysdba2) # sqlplus/As sysdba3. Log On As a user and start SQL * plus1) # sqlplus jsam/jsam1232) # sqlplus jsam/jsam123 @ orl3) # sqlplus/nologsql> conn jsam/jsam1234) # sqlplus/nologsql> conn jsam/jsam123 @ orcl and so on... Iii. Security Management1. User Management 1) create a user account named jsam with a password of jsam123 and adopt database authentication. The default tablespace is users and the temporary tablespace is Temp. The users quota of the table space is 500 K, indicating that up to K of tablespaces can be used: SQL> create user jsam identified by jsam123default tablespace userstemporary tablespace tempquota 500 k on users; a default tablespace should be allocated to each user. If no tablespace is specified during user creation, the system tablespace is used as the default tablespace. The system tablespace contains data dictionaries and is often used by Oracle. Placing multiple user objects in the same tablespace results in database system performance degradation due to disk competition. If you do not limit the use of tablespaces, You can do not add quota 500 k on users. Generally, after creating a user, the user is authorized to connect to the database, create tables, sequences, processes, packages, functions, views, and Other permissions at the same time: SQL> grant connect, resource, create view to jsam; 2) Change Password SQL> alter user jsam identified by "123456"; 3) delete user * delete user jsamsql> drop user jsam; * If you delete a user, you must also delete the database objects (such as tables, indexes, clusters, and views) owned by the user ), you can use the drop statement SQL with cascade> drop user jsam cascade; 4) Lock and unlock the user account * lock the user account SQL> alter user jsam account lock; * unlock user account SQL> alter user jsam account unlock; 5) view current user identity SQL> show user6) query user mail Information * view all the account information, user ID, and creation time that the current user can access: SQL> select * From all_users; * view all the user account information, configuration files, and status of the current database: SQL> select username, profile, account_status from dba_users; * query the table space usage quota of a user (if no quota is specified, no rows selected is returned ): SQL> select * From dba_ts_quotas where username = 'jsam '; 7) view User status SQL> select username, account_status from dba_users; 8) user Configuration File * view all configurations SQL> select * From dba_profiles; * view default configuration file parameters SQL> select * From dba_pr Ofiles where profile = 'default'; * view password validity period configuration SQL> select * From dba_profiles where profile = 'default' and resource_name = 'password _ life_time '; * view the number of consecutive logon failures that cause a user to be locked. SQL> select * From dba_profiles where profile = 'default' and resource_name = 'failed _ login_attempts '; * set the password validity period to "INDEFINITE" SQL> alter profile default limit password_life_time unlimited; * modify the limit on the number of locks for consecutive logon failures SQL> alter profile default limit failed_login_att Empts unlimited; SQL> alter profile default limit failed_login_attempts 20; 2. Grant and revoke system permissions 1) grant users multiple system permissions SQL> grant create session, CREATE TABLE, create view, create any index, create sequence, create type to appenders 1; 2) If you want a user to manage a specific permission, you can specify the with grant option when granting system permissions. This user can perform the following operations: * you can grant or revoke system permissions to any other user in the database. * You can use the with admin option when granting system permissions to other users. SQL> grant create table to tableadmin with admin option; 3) grant the user all system permissions (except select any dictionary) SQL> grant all privileges to admin; 4) grant user DBA permissions SQL> grant DBA to admin; 5) revoke the create type and create sequence system permissions granted to user appenders 1 SQL> revoke create type, create sequence from appenders 1; 3. Grant and revoke object permissions 1) grant all object permissions to tables SQL> grant all privileges on bookinfo to booksystemdeveloper; 2) grant specified object permissions SQL> grant insert, update, Dele Te, select on authorinfo to booksystemdeveloper; 3) If with grant option is used to grant object permissions, the grantee can grant the object permissions to other users. SQL> grant all privileges on bookinfo to booksystemdeveloper with grant option; 4) revoke the update and delete object permissions granted to users: SQL> revoke update, delete on authorinfo from booksystemdeveloper; 4. View permission information 1) query the system permissions of a user. SQL> select privilege, admin_option from dba_sys_privs where grantee = 'jsam '; view the current user's system permissions SQL> select * From user_sys_privs; 2) query the user's object permissions SQL> select Owner | '. '| table_name object_name, privilege, grantabl Efrom dba_tab_privswhere grantee = 'jsam 'order by owner, table_name, privilege; view the object permissions of the current user SQL> select * From user_tab_privs; 3) query the permissions available for the current session SQL> select * From session_privs; 5. A role management role is a set of system and object permissions with names. 1) grant the connect and resource roles to the user SQL> grant connect, resource to jsam; 2) view the list of roles enabled in the current session SQL> select * From session_roles; 3) view the current user's role list SQL> select * From user_role_privs; 4) view all database roles SQL> select * From dba_roles; 5) view a user's role SQL> select granted_role, admin_optionfrom dba_role_privswhere grantee = 'jsam '; 6) view the system permissions of the role connect SQL> select role, privilege, admin_optionfrom role_sys_privswhere role = 'connect'; 7) view object permissions of a role SQL> select Owner | '. '| table_name object_name, privilege, grantablefrom role_tab_privswhere role = 'connect' order by owner, table_name, privilege; Iv. Transaction Processing1. transaction commit 1) Enable Automatic commit SQL> set autocommit on; 2) Disable Automatic commit SQL> set autocommit off; 3) display submit command SQL> commit; 2. Transaction rollback 1) Save storage point SQL> savepoint storage point name 2) roll back to a storage point SQL> rollback to storage point name 3) roll back the entire transaction SQL> rollback V. Object Management1. display the structure SQL of a table> DESC table_name; SQL> describe table_name; 2. view the object and status to view the current user's functions, stored procedures, and statuses: SQL> select object_name, status from user_objects where object_type = 'function'; SQL> select object_name, status from user_objects where object_type = 'processed'; object_type can be: sequenceprocedurelobpackagepackage bodytriggerindextableviewfunctionjava classjava sourcetype... 3. Viewing the source code of an object can be used to view the source code such as stored procedures, functions, and packages. SQL> select text from All_source where owner = user and name = upper ('& plsql_name'); 4. view all the tables of the current user SQL> select * From user_tables; SQL> select table_name from user_tables; tabs is a synonym for user_tables, so it is simpler to use tabs directly. 5. Compile the object SQL> alter function function_name compile; SQL> alter procedure procedure_name compile; SQL> alter trigger trigger_name compile ;... 6. Lock Object 1) information about all currently locked objects SQL> select * from V $ locked_object; 2) query the locked object, lock mode, and user SQL> select B. owner, B. object_name,. session_id,. locked_modefrom v $ locked_object A, dba_objects bwhere B. object_id =. object_id; 3) session information of the locked object SQL> select B. username, B. sid, B. serial #, logon_timefrom v $ locked_object A, V $ session bwhere. session_id = B. sidorder by B. logon_time; some locks may not be released for a long time. In this case, you may need to forcibly close the lock: Syntax: Alter system kill session 'sid, serial # '; SQL> alter system kill session '123 '; 4) query the statement corresponding to a lock) SQL> select SQL _textfrom v $ sqlwhere hash_value in (select SQL _hash_valuefrom v $ sessionwhere Sid in (select session_id from V $ locked_object); B) SQL> select distinct SQL _textfrom v $ sqlwhere hash_value in (select SQL _hash_valuefrom v $ sessionwhere Sid in (select session_id from V $ locked_object); c) SQL> select SQL _text, count (*) from v $ sqlwhere hash_value in (select SQL _hash_valuefrom v $ sessionwhere Sid in (select session_id from V $ locked_object) group by SQL _text; Vi. tablespace1. view the names of all tablespaces in a database and the default storage parameters SQL> select tablespace_name "tablespace", initial_extent "initial_ext", next_extent "next_ext", min_extents "min_ext ", max_extents "max_ext", pct_increasefrom dba_tablespaces; 2. Create tablespace 1) Create tablespace MySpace, the size is 100 msql> Create tablespace MySpace datafile '/home/Oracle/oradata/ora10/myspace01.dbf' size is 100 MB; 2) create a tablespace MySpace with the size of 100 MB, when the space is insufficient, it is automatically expanded at kb, maximum: 200 msql> Create tablespace MySpace datafile '/home/Oracle/oradata/ora10/myspace01.dbf' size 100 m autoextend on next 128 K maxsize 200 m; 3. Add a tablespace 1) Add a tablespace SQL> alter database datafile '/home/Oracle/oradata/ora10/myspace01.dbf' resize 120 m by adding the tablespace data file size; 2) add tablespace SQL> alter tablespace MySpace add datafile '/home/Oracle/oradata/ora10/myspace02.dbf' size 50 m by adding data files; 4. Modify tablespace 1) enable automatic expansion of data files SQL> alter database datafile '/home/Oracle/oradata/ora10/myspace01.dbf' autoextend on; 2) disable SQL> alter database datafile '/home/Oracle/oradata/ora10/myspace01.dbf' autoextend off; 5. Delete tablespace SQL> drop tablespace MySpace; 6. view the user's default tablespace 1) view the current user's default tablespace SQL> select username, default_tablespace, temporary_tablespace from user_users; 2) view a user's default tablespace SQL> select username, default_tablespace from dba_users where lower (username) = 'jsam '; view all users and the default tablespace relationship SQL> select username, default_tablespace from dba_users; 3) view the tablespace where a table is located. SQL> select tablespace_name from all_tables where table_name = 'company'; SQL> select tablespace_name from user_tables where table_name = 'company '; 7. view the name and size of the tablespace physical file 1) view all data files in a database and related tablespace SQL> select file_name, blocks, tablespace_name from dba_data_files; SQL> select file_name, blocks, tablespace_name, autoextensible from dba_data_files; 2) view the tablespace name and size SQL> select T. tablespace_name, round (sum (Bytes/(1024*1024), 0) ts_sizefrom dba_tablespaces T, dba_data_files dwhere T. tablespace_name = D. tablespace_namegroup by T. tablespace_name; 3) view the name and size of the tablespace physical file SQL> select tablespace_name, file_id, file_name, round (Bytes/(1024*1024), 0) total_spacefrom dba_data_filesorder by tablespace_name; 4) view the name and size of a tablespace physical file. Format: file_name tablespace total (m) used (m) % used statement: SQL> select B. file_name, B. tablespace_name, B. bytes/1024/1024 "Total (m)", (B. bytes-sum (nvl (. bytes, 0)/1024/1024 "used (m)", substr (B. bytes-sum (nvl (. bytes, 0)/(B. bytes) * 100, 1, 5) "% used" from dba_free_space A, dba_data_files bwhere. file_id (+) = B. file_idgroup by B. tablespace_name, B. file_name, B. bytesorder by B. tablespace_name; 5) query idle tablespace SQL> select sum (bytes)/(1024*1024) as free_space, tablespace_namefrom dba_free_spacegroup by tablespace_name; 6) query idle tablespace SQL> select tablespace_name, count (*) as extends, round (sum (bytes)/1024/1024, 2) as "free (MB)", sum (blocks) as blocksfrom dba_free_spacegroup by tablespace_name; 7. View Parameters1. View initialization parameters 1) view all initialization parameters SQL> show parameters; 2) If you only want to query a specific parameter value, run the command: Show parameters [parameter name]. For example, query Control File Information: SQL> show parameters control_files; 2. query Character Set 1) query the current database character set SQL> select userenv ('language') from dual; 2) view the server Character Set Configuration SQL> select * from V $ nls_parameters; SQL> select * From nls_database_parameters; the query result of the second statement has a database version parameter nls_rdbms_version more than that of the first statement. 3) Client Character Set SQL> select * From nls_instance_parameters; 8. Import and ExportExp and IMP can be used either on the client or on the server. Expdp and impdp can only be used on the Oracle server and cannot be used on the client. 1. Export 1) Use DBA to completely export the database SQL> exp system/pass123 @ ora10 file =. /jsam. DMP full = y; 2) export the user's jsam table SQL> exp jsam/jsam123 @ ora10 file =. /jsam. DMP compress = n log = jsam. log; compress = N: indicates that the exported file does not need to be compressed into a data block. log = jsam. log: Specify log file 2. Import 1) import database SQL> imp jsam/jsam123 @ ora10 file =. /jsam. DMP; 2) when exporting and importing different user names, you must specify fromuser, tousersql> imp jsam/jsam123 @ ora10 file =. /jsam. DMP fromuser = jsam_test touser = jsam ignore = y; ignore = Y: indicates that The process of creating a table is to import the data in the table into the table. For example, if a table already exists, you can use this parameter to ignore table creation and import data to the database. 3. Data Pump Import and Export 1) create a file export directory # mkdir/home/Oracle/dbbackup/tempdump/2) the database creates a logical export directory and grants permissions to jsamsql> Create or replace directory tempdump as '/home/Oracle/dbbackup/tempdump/'; SQL> grant read, write on directory tempdump to jsam; 3) export the database (Save the exported file in/home/Oracle/dbbackup/tempdump) # expdp jsam/jsam123 @ ora10 dumpfile = jsam_dp.dmp directory = tempdump4) import database # impdp jsam/jsam123 @ ora10 dumpfile = jsam_dp.dmp directory = tempdump logfi Le = jsam_dp.log remap_schema = "jsam": "New username" remap_tablespace = "users": "New tablespace name" Description: remap_schema: this option is used to load all objects in the source scheme to the Target Scheme. When the source and target user names are different, you need to use remap_tablespace to import all objects in the source tablespace to the target tablespace. If the source and target tablespace names are not required at the same time, if you need to import them to the databases of other servers, you need to create users and tablespaces based on the actual situation, and perform the same steps 1) and 2 ), place the exported file in the corresponding directory (the directory here can be different from the export time) and then perform the import operation. IX. User connection management1. log on to the database as a system administrator and check that the current database has several users connected to SQL> select username, Sid, serial # from V $ session; SQL> select username, Sid, serial # from V $ session where username = upper ('jsam '); 2. Close a connection Syntax: Alter system kill session 'sid, serial #'; SQL> alter system kill session '2010 4'; 3. query the maximum number of connections supported by Oracle SQL> show parameter Processes; 4. Modify the maximum number of connections 1) modify the maximum number of connections SQL> alter system set processes = 300 scope = spfile; 2) create pfilesql> Create pfile from spfile; 3) restart the database 5. Check the number of connections 1) query the current number of connections SQL> select count (*) from V $ session; 2) query the current number of concurrent connections SQL> select count (*) from V $ session where status = 'active '; 3) query the connections of different users SQL> select count (*) from V $ session where username is not null; SQL> select username, count (username) from v $ sessionwhere username is not nullgroup by username; 4) view the session information created by the current database SQL> select Sid, serial #, username, program, machine, status from V $ session; 10. Other commands1. tnsping command 1) Verify the name resolution (name resolution, of course, Oracle's own network service name) 2) check whether the remote listener is started (usage: tnsping <address> [<count>]) # tnsping ora10 # tnsping 192.168.0.103 # tnsping 192.168.0.103 22. Execute an SQL script file SQL> Start file_namesql> @ file_name3. re-run the last SQL statement SQL>/4. Do not exit the SQL statement. * Plus, run an operating system command * in SQL * Plus to switch to the operating system command prompt command: host or! * Switch back to SQL * Plus command: Exit example: SQL> host [/home/Oracle] PWD/home/Oracle [/home/Oracle] exitexitsql> 5. Environment Variable 1) show current environment variable command: Show parameter name show all environment variables: SQL> show all shows the value of a variable, for example, display page size (14 by default): SQL> show pagesize; 2) set the environment variable command: Set the parameter value of the Set parameter name sets pagesize to 100, and enough rows of records can be displayed at a time, which is subject to the title bar interference. SQL> set pagesize 100; master several simple common variable settings to make the query results more readable. The following lists several common variable settings: * set the row width to a smaller display width by default, query results are displayed in rows. If you increase the linesize, the results are displayed on the same line: SQL> set linesize 125; test statement: SQL> selec T username, profile, account_status from dba_users; * setting the column width to adjust the column width also enhances readability. Syntax: Col column name format a width value setting column parameter and value width: 30: SQL> Col parameter format A30; SQL> Col value format A30; test statement: SQL> select * from V $ nls_parameters; * enable/disable the title bar SQL> set heading onsql> set heading off * overlong line display or truncation does not show SQL> set wrap onsql> set wrap off6, buffer 1) display SQL Buffer command SQL> l2) use the input command to add one or more lines of SQL> I1 input content 7. view database version SQL> select * from V $ version; 8. Spool commands store various operations and execution results on disk files. The default file extension is. lst. 1) execute the offline command SQL> spool file name 2) Stop offline SQL> spool off3) display offline SQL> spool example: SQL> spool all_users.txtsql> select * From all_users; many Rows are omitted here... SQL> spool offsql> exit result: the execution result of select * From all_users is saved as the file all_users.txt. 9. SQL> select status, bytes, name from V $ datafile; 10. SQL> select name from V $ controlfile; 11. query the current database type. For SQL> Select Server from V $ session where audsid = userenv ('sessionid'); dedicated: dedicated server shared: multi-thread server reference: oracle 10g entry and improve http://database.51cto.com/art/200910/158936.htmhttp://database.51cto.com/art/201108/284848.htm reproduced see: http://zhanjia.iteye.com/blog/1798052