ORACLE startup and use data dictionary

ORACLE startup and utilization data dictionary 1. ORACLE startup and shutdown 1. To start or shut down an ORACLE System in a standalone environment, you must first switch to an ORACLE user, the following are su-oracle a and ORACLE> svrmgrl SVRMGR> connect internal SVRMGR> startup SVRMGR> quit B. Disable oracle> svrmgrl SVRMGR> connect internal SVRMGR> shutdown SVRMGR> quit run the following command to start the oracle9i database: $ sqlplus/nolog SQL * Plus: Release 9.2.0.1.0-Production on Fri Oct 31 13:53:53 2003 Copyright (c) 1982,200 2, Oracle Corporation. all ri Ghts reserved. SQL> connect/as sysdba Connected to an idle instance. SQL> startup ^ c SQL> startup ORACLE instance started. 2. to enable or disable the ORACLE System in a dual-host environment, you must first switch to the root user, the following are the startup methods for su-root a, ORACLE hareg-y oracle B, and ORACLE hareg-n oracle database: 1. startup nomount is not installed and started. In this mode, execute re-build the control file and re-build the database to read init. ora file, start the instance, that is, start the SGA and background processes. ora file. 2. startup mount dbname installation and startup. In this mode, you can execute: Database Log archiving, database media recovery, online or offline data files, reposition data files, and redo log files. Execute "nomount", open the control file, and confirm the location of the data file and the on-line log file. However, the data file and log file are not verified at this time. 3. startup open dbname: first execute "nomount", then execute "mount", and then open all database files including Redo log files. In this way, you can access data in the database. 4. startup is equal to the following three commands: startup nomount alter database mount alter database open 5 and startup restrict. In this way, the database can be started, but when only privileged users are allowed to access non-privileged users, the following prompt appears: ERROR: ORA-01035: ORACLE only allows users with restricted session permissions to use 6. startup force forced start mode when the database cannot be closed, you can use startup force to close the database first, execute the commands for starting the database normally. 7. startup pfile = parameter file name the startup method with initialization parameter file first reads the parameter file, and then starts the database according to the settings in the parameter file. Example: startup pfile = E: oracleadminoradbpfileinit. ora 8, start Up EXCLUSIVE 2. How to effectively use the data dictionary of ORACLE is an important part of the database. It is generated with the emergence of the database and changes with the changes of the database, it is reflected in some tables and Views under the sys user. The data dictionary name is an uppercase English character. The data dictionary contains user information, user permission information, all data object information, table constraints, and views of the statistical analysis database. We cannot manually modify the information in the data dictionary. Generally, ORACLE users do not know how to use it effectively. The name and description of all data dictionary tables in dictionary. It has a synonym, dict dict_column. the name and description of all fields in the data dictionary table. If we want to query the data dictionary related to the index, you can use the following SQL statement: SQL> select * from dictionary where instr (comments, 'index')> 0; if you want to know the detailed meaning of each field name in the user_indexes table, you can use the following SQL statement: SQL> select column_name, comments from dict_columns where table_name = 'user _ INDEXES '; so on, you can easily know the detailed name and explanation of the data dictionary, you do not need to view other ORACLE documents. The following describes how to use common data dictionaries for ORACLE users by category. 1. the user views the current user's default tablespace SQL> select username, default_tablespace from user_users; view the current user's role SQL> select * from user_role_privs; view the current user's system permissions and table-level permissions SQL> select * from user_sys_privs; SQL> select * from user_tab_privs; 2. view all the tables under the user SQL> select * from user_tables; view the table SQL with the name containing log characters> select object_name, object_id from user_objects where instr (object_name, 'log')> 0; view the creation time of a table SQL> select object_name, created from user_objects where object_name = upper ('& table_name'); view the size of a table SQL> select sum (bytes)/(1024*1024) as "size (M) "from user_segments where segment_name = upper ('& table_name'); view the table SQL in the ORACLE memory partition> select table_name, cache from user_tables where instr (cache, 'y')> 0; 3. Check the number and category of indexes. SQL> select index_name, index_type, table_name from user_indexes order by table_name; view the indexed field SQL> select * from user_ind_columns where index_name = upper ('& index_name'); view the index size SQL> select sum (bytes)/(1024*1024) as "size (M)" from user_segments where segment_name = upper ('& index_name'); 4. Check the serial number. last_number is the current value. SQL> select * from user_sequences; 5. view name SQL> select view_name from user_views; view the select statement SQL> set view_name, text_length from user_views; SQL> set long 2000; description: you can set the size of set long based on the text_length value of the view. SQL> select text from user_views where view_name = upper ('& view_name '); 6. View synonym name SQL> select * from user_synonyms; 7. View constraints for a table SQL> select constraint_name, constraint_type, search_condition, r_constraint_name from user_constraints where table_name = upper ('& table_name'); SQL> select c. constraint_name, c. constraint_type, cc. column_name from user_constraints c, user_cons_columns cc where c. owner = upper ('& table_owner') and c. table_name = upper ('& table_name') and c. owner = cc. owner and c. constraint_name = cc. constraint_name order by cc. position; 8. view the status of stored functions and processes SQL> select object_name, status from user_objects where object_type = 'function'; SQL> select object_name, status from user_objects where object_type = 'processed'; view the source code SQL of the function and process> select text from all_source where owner = user and name = upper ('& plsql_name '); iii. view the SQL of the database 1. view the table space name and size select t. tablespace_name, round (sum (bytes/(1024*1024), 0) ts_size from dba_tablespaces t, dba_data_files d where t. tablespace_name = d. tablespace_name group by t. tablespace_name; 2. view the name and size of the tablespace physical file select tablespace_name, file_id, file_name, round (bytes/(1024*1024), 0) total_space from dba_data_files order by tablespace_name; 3. Check the rollback segment name and size. select segment_name, tablespace_name, r. status, (initial_ext Ent/1024) InitialExtent, (next_extent/1024) NextExtent, max_extents, v. curext CurExtent From dba_rollback_segs r, v $ ro