L database management materials

Oracl Database Management Information Displays Oracle sga information: SQL> show sga Total System Global Area 105978600 bytesFixed Size 453352 bytesVariable Size 50331648 bytesDatabase Buffers 54525952 bytesRedo Buffers 667648 bytesSQL> Fixed Size: different oracle platforms and versions may be different, but it is a fixed value for determining the environment. It stores information about each part of the SGA component, which can be seen as a region for guiding the establishment of SGA. Variable Size: Contains shared_pool_size, java_pool_size, large_pool_size, and other memory settings Database Buffers: index data buffer, which contains db_block_buffer * db_block_size, buffer_pool_keep. 9i contains db_cache_size, db_keep_cache_size, db_recycle_cache_size, and db_nk_cache_size. Redo Buffers: indicates the log buffer, log_buffer. Here, it is worth noting that the query values of v $ parameter, v $ sgastat, and v $ sga may be different. The value in v $ parameter refers to the value set in the initialization parameter file, v $ sgastat is the size of the log buffer actually allocated by oracle (because the Buffer Allocation value is actually discrete and is not allocated in the smallest unit of block ), the value queried in v $ sga is after the log buffer is allocated in oracle, some protection pages are set to protect the log buffer, usually we will find that the page size is 8 k (different environments may be different ). ========================================================== ===================== description of the show sga result Total System Global Area AAAAA bytes Fixed Size BBBBB bytes Variable Size CCCCC bytes Database Buffers DDDDD bytes Redo Buffers EEEEE bytes fi Xes size: Different oracle platforms and versions may be different, but it is a fixed value for determining the environment, which stores information about various SGA components, it can be seen as the region Variable Size for guiding the establishment of SGA: including shared pool, java pool, large pool, memory for managing DB_BLOCK_BUFFERS, and memory for controlling file information, other management and control of oracle internal structure memory redo buffer is divided into 1: Set the parameter SQL> show parameters log_buffer name type value ------- ------------------------------ log_buffer integer 524288 2: log memory size SQL> select * From v $ sgastat where name like '% log %'; pool name bytes ----------- -------------------------- ---------- log_buffer 656384 3: To protect the log memory, auxiliary memory is added, that is to say, protection Page SQL> show sga Total System Global Area 496049552 bytes Fixed Size 454032 bytes Variable Size 109051904 bytes Database Buffers 385875968 bytes Redo Buffers 667648 bytes SQL> for databases, log_buffer is a discrete set of values on different platforms. Assume It is a set of R, and it is not based on OS blockck or db block. Increase the step size (for example, it may be 65 k, 128 k, 512 k, 641k .... when the parameter is set to a value, the actual size selected by the database is min (R) greater than or equal to the value. According to this set of values, for example, if you set log_buffer = 600 k, you actually select 641 k. Then, when actually allocating memory, in order to provide some protection for the log buffer, A small part of space is allocated, usually 11 kb. 641 + 11 = 652 k is the final memory size, that is, the display size during show SGA. How to view the database name sid of Oracle Log On as sysdba, for example, sqlplus toto/123456 as sysdba; execute select name form V $ database; or execute select * from V $ database; however, if the second display contains too much content, you can use desc V $ database to find the desired result; statement to check the fields in V $ database and select the desired field to view the instance name (sid): select instance_name from V $ instance; by default, sid is the same as your database name! View the relationship between the user and the default tablespace: select username, default_tablespace from user_users; modify the tablespace name (at this time, log on to sqlplus sys/oracle as SYSDBA as DBA ;) alter tablespace tablespacename rename To misps; To increase the size of a data file or increase the size of a data file, you can expand the tablespace, for example, modify the size of a data file: alter database datafile 'data file path and name 'resize 300 m; 1. view table structure: desc table name 2. view the table of the current user: select table_name from user_tables; 3. view the table names of all users: select table_name from all_tables; 4. view All table names (including system tables) select table_name from all_tables; 5. view All Tables: select * from tab/dba_tables/dba_objects/cat. The following describes how to query user tablespaces in Oracle. ◆ query user tablespaces in Oracle: select * from user_all_tables ◆ Oracle queries all functions and stored procedures: select * from user_source ◆ Oracle queries all users: select * from all_users.select * from dba_users ◆ Oracle views current user connections: select * from v $ Session ◆ Oracle view current user permissions: select * from session_privs ◆ Oracle view User tablespace usage: select. file_id "FileNo",. tablespace_name "Tablespace_name",. bytes "Bytes",. bytes-sum (nvl (B. bytes, 0) "Used", sum (nvl (B. bytes, 0) "Free", sum (nvl (B. bytes, 0)/. bytes * 100 "% free" from dba_data_files a, dba_free_space B where. file_id = B. file_id (+) group by. tablespace_name,. file_id,. bytes order by. tablespace_name; 1. view All users: select * from dba_user; select * from all_users; select * from user_users; 2. view user system permissions: select * from dba_sys_privs; select * from all_sys_privs; select * from user_sys_privs; 3. view user object permissions: select * from dba_tab_privs; select * from all_tab_privs; select * from user_tab_privs; 4. view All roles: select * from dba_roles; 5. view User Roles: select * from dba_role_privs; select * from user_role_privs; 6. view the permissions of a role: select * from role_sys_privs; select * from role_tab_privs; 7. view All system permissions select * from system_privilege_map; 8. view All object permissions select * from table_privilege_map; the preceding statements show user permissions in Oracle, and select * from syscat in DB2. dbauth or get authorizations to view sid select * from v $ instance