This is the second day of my participation in the August More text Challenge. For details, see: August More Text Challenge
A lifelong learner, practitioner, and sharer committed to the path of technology, an original blogger who is busy and sometimes lazy, and a teenager who is occasionally boring and sometimes humorous.
Welcome to search “Jge’s IT Journey” on wechat!
Common Oracle database commands in Linux
Oracle database instance, user, directory, and session view
1.1 ORACLE SID Viewing Settings
Check the SID, user name $env | grep SID, the select * from v $instance, select instance_name, host_name from v $instance; SQL> select usernames,account_status from dba_users; Set SID $export ORACLE_SID=hisvhfs to check database DBID: SQL>select * from v$DBIDCopy the code1.2 Querying and Setting the maximum number of processes and sessions for an Oracle Database instance
SQL>select count(*) from v$bgprocess select count(*) from V $bgprocess SQL>select count(*) from v$session select count(*) from v$session SQL> select sid,serial#,username,program,machine,status from v$session; SQL: query the number of connections in the application SQL> SELECT b.MACHINE, b.PROGRAM, COUNT (*) FROM v$process a, v$session b WHERE a.ADDR = b.PADDR AND b.USERNAME IS NOT NULL GROUP BY b.MACHINE, b.PROGRAM ORDER BY COUNT (*) DESC; SQL> select count(*) from v$session where status='ACTIVE'; To see which users are currently using the data: select osuser,a.username,cpu_time/executions/1000000||'s',sql_fulltext,machine from v$session a,v$sqlarea b where a.sql_address = b.address order by cpu_time/executions desc; SQL> select sid,serial# from v$session where username='XX'; SQL> select sid,serial# from v$session where username='BSPDEV'; SID SERIAL# ---------- ---------- 204 4609 399 5841 590 6041 SQL> ALTER system kill session '204,4609'; SQL> alter system kill session '399,5841'; SQL> alter system kill session '590,6041'; SQL> ALTER system set processes=300 scope=spfile; The system has been changed. SQL> ALTER system set sessions=335 scope=spfile; The system has been changed. The number of oracle connections (sessions) is related to the number of processes (processes) in the parameter file. Sessions =(1.1*process+5)Copy the code1.3 Viewing the Database Directory
SQL> select * from all_directories;
Copy the code1.4 Checking the Existing Schema and Archiving status of the Database
SQl>select name,log_mode from v$database; You can also use the following statement archive log list; SQL> Select Created, Log_Mode, Log_Mode From V$Database;Copy the code1.5 Setting the Password Expiration Time
alter profile "default" limit password_life_time unlimited; Alter profile "default" limit password_life_time 100; The user password expires in 100 daysCopy the code1.6 Creating and Configuring a New User and Viewing user Properties
SQL> alter user Scott account unlock; SQL> alter user scott identified by tiger; SQL>drop user username CASCADE; (Delete all objects associated with the user.) This user includes the tables under the user, attempts, synonyms, procedures, indexes, and everything else is deleted. Create user and assign permissions and set default tablespaces. Log in as the sysdba user and perform the following Settings: -- Create the user Create user VHFSM IDENTIFIED by vhnj1FSM default tablespace MGRVHFSTBSDEF Temporary TABLESPACE TEMP Profile DEFAULT Quota UNLIMITED on mGRvHFstbs2010 Specifies the quota unlimited on of other tablespaces that can be operated mgrvhfstbsdef; -- Grant/Revoke role privileges grant connect to VHFSM; grant dba to VHFSM; -- Grant/Revoke system privileges grant unlimited tablespace to VHFSM; View user and role permissions --1. View all users: select * from dba_users; 2. select * from all_users; select * from user_users; Select * from dba_sys_privs; select * from dba_sys_privs; select * from user_sys_privs; SQL >select * from role_sys_privs; SQL >select * from role_sys_privs; --4. Query user object permissions: select * from dba_tab_privs; select * from all_tab_privs; select * from user_tab_privs; --5. Query all roles: select * from dba_roles; --6. Run the following command to view the role owned by the user or role: select * from dba_role_privs; select * from user_role_privs; Select * from V$PWFILE_USERS select * from V$PWFILE_USERS select * from V$PWFILE_USERS select * from V$PWFILE_USERS You can run the following command to view the system permissions provided by Oracle: select name from sys. system_PRIVILege_map --2 dba_sys_privs WHERE grantee = 'DATAUSER' UNION SELECT privilege FROM dba_sys_privs WHERE grantee IN (SELECT granted_role FROM dba_role_privs WHERE grantee = 'DATAUSER');Copy the codeCreate and manage Oracle tablespaces
2.1 Querying Free Space first
select tablespace_name,file_id,block_id,bytes,blocks from dba_free_space;
Copy the code2.2 Adding an Oracle tablespace
Query the name, size, and path of the data file in the following statement:
select tablespace_name,file_id,bytes,file_name from dba_data_files;
Copy the code2.3 Modifying the file size
Alter database datafile '; alter database datafile '; alter database datafile '; alter database datafile ';Copy the code2.4 Creating an Oracle tablespace
create tablespace test datafile '/home/app/oracle/oradata/oracle8i/test01.dbf' size 8M autoextend on next 5M maxsize 10M; create tablespace sales datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M autoextend on next 50M Maxsize unlimited Indicates that the size is not limited. Create TABLESPACE sales datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M autoextend on next 50M maxsize 1000M extent management local uniform; Unform indicates that the size of the area is the same, Defaults to 1 m the create in tablespace sales datafile '/ home/app/oracle/oradata/oracle8i/sales01. DBF' size 800 m autoextend on next 50 m maxsize 1000M extent management local uniform size 500K; Unform size 500K indicates that the area size is the same, To create 500 k in tablespace sales datafile '/ home/app/oracle/oradata/oracle8i/sales01. DBF' size 800 m autoextend on next 50 m maxsize 1000M extent management local autoallocate; Autoallocate indicates that the extent size changes automatically and dynamically with the size of the table, Big table using regional small table using community create in tablespace sales datafile '/ home/app/oracle/oradata/oracle8i/sales01. DBF' 800 m autoextend on size next 50M maxsize 1000M temporary; Temporary Create a dictionary to manage a temporary tablespace To create a locally managed temporary tablespace, add the temporary TABLESPACE keyword create TEMPORARY TABLESPACE sales tempfile '/ home/app/oracle/oradata/oracle8i/sales01. DBF' size 800 m autoextend on next 50 m maxsize 1000 m to create local management temporary table space, if it is a temporary table space, Change datafile (tempfile8i) from datafile (tempfile8i) to datafile (tempfile8i); alter tablespace sales add datafile '/home/app/oracle/oradata/oracle8i/sales02.dbf' size 800M autoextend on next 50M maxsize 1000M;Copy the code2.5 Checking whether the Tablespace automatically Expands
SQL> select file_id,file_name,tablespace_name,autoextensible,increment_by from dba_data_files order by file_id desc; Change automatic extension properties: alter database datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf', '/home/app/oracle/oradata/oracle8i/sales02.dbf' '/home/app/oracle/oradata/oracle8i/sales01.dbf autoextend off;Copy the code2.6 Viewing and modifying a tablespace
2.6.1 Viewing the user default tablespace
SQL> select username,default_tablespace from dba_users; SQL> select username, default_TABLESPACE from user_users; To view the default tablespace of a user, connect the user. SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username = 'hr'; View the default tablespace corresponding to the userCopy the code2.6.2 Modifying the default user tablespace
SQL> alter user zhanghr default tablespace test;
Copy the code2.6.3 Setting the Default Temporary Tablespace of the database
SQL>Alter database default temporary tablespace temp_tbs_name;
Copy the code2.6.4 Viewing the Relationship between the User and the Default tablespace
SQL>select username,default_tablespace from dba_users;
Copy the code2.6.5 Viewing temporary Tablespaces
SQL> select file_name,file_id,blocks,user_blocks from dba_temp_files；
SQL> select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment;
Copy the code2.6.7 Viewing the undo tablespace
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
Copy the code2.6.8 Viewing the size of the undo tablespace
SQL> select sum(bytes)/1024/1024 "current undo size(M)" from dba_data_files where tablespace_name='UNDOTBS1';
Copy the code2.6.9 Changing the size of the undo tablespace by adding data files
SQL> alter tablespace undotbs1
add datafile '/oracle/oradata/orc6/undo02.dbf' size 10M;
Copy the code2.6.10 Use resize to change the data file size
SQL> alter database datafile '/oracle/oradata/orc6/undo02.dbf' resize 100M;
Copy the code2.6.11 Viewing the data files of a tablespace
SQL> select file_name,tablespace_name,bytes/1024/1024 "bytes MB",maxbytes/1024/1024 "maxbytes MB" from dba_data_files where tablespace_name='ORA1TBS'; Specify that the tablespace name be uppercaseCopy the code2.6.12 Viewing the Size of all table Spaces
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files
group by tablespace_name;
Copy the code2.6.13 Viewing the Size of the Used Tablespace
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space
group by tablespace_name;
Copy the codeSelect * from oracle where tablespace size and tablespace usage are displayed.
Methods a
SELECT UPPER(F.TABLESPACE_NAME),D.TOT_GROOTTE_MB,D.TOT_GROOTTE_MB,F.TOTAL_BYTES,TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.total_bytes)/D.to_grootte_MB * 100, 2),'990.99'), F.total_bytes, F.MAX_BYTES FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC;Copy the codeMethod 2
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS "SUM_BLOCKS",
SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
"USED_RATE(%)",
FREE_SPACE || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE || 'M' "USED_SPACE(M)",
ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1;
Copy the codeMethods three
SELECT * FROM (SELECT a.tablespace_name, to_char(a.tablespace_name, a.tablespace_name, to_char(a.tabytes / 1024/1024, '99,999.999') total_bytes, To_char (B.bytes / 1024/1024, '99,999.999') free_bytes, to_char(a.bytes / 1024/1024 - B.bytes / 1024/1024, '99999999') use_bytes, to_char ((1 - b.b ytes/a. ytes) * 100, '99.99') | | '%' USE the FROM (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name UNION ALL SELECT c.tablespace_name, to_char(c.bytes / 1024/1024, '99,999.999') total_bytes, To_char ((c.byte -d.byte used) / 1024/1024, '99,999.999') free_bytes, to_char(d.byte used / 1024/1024, '99,999.999') free_bytes, to_char(d.byte used / 1024/1024, '99999999') use_bytes, to_char (db ytes_used * 100 / mount ytes, '99.99') | | '%' USE the FROM (SELECT tablespace_name, SUM(bytes) bytes FROM dba_temp_files GROUP BY tablespace_name) c, (SELECT tablespace_name, SUM(bytes_cached) bytes_used FROM v$temp_extent_pool GROUP BY tablespace_name) d WHERE c.tablespace_name = d.tablespace_name) ORDER BY tablespace_name;Copy the code3.1 Viewing tablespace Usage (1)
SQL> select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",
b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name ) b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc
;
TABLESPACE_NAME Sum MB used MB free MB percent_used
------------------------------ ---------- ---------- ---------- ------------
SYSAUX 490 461.0625 28.9375 94.09
UNDOTBS1 75 66.8125 8.1875 89.08
USERS 5 1.3125 3.6875 26.25
ORA1TBS 400 2 398 .5
ORA2TBS 400 2 398 .5
Copy the code3.2 Viewing tablespace Usage (2)
SQL> select a.tablespace_name,total,free,total-free used from 2 ( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files group by tablespace_name) a, ( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space group by tablespace_name) b where a.tablespace_name=b.tablespace_name; 3 4 5 6 TABLESPACE_NAME TOTAL FREE USED ------------------------------ ---------- ---------- ---------- USERS 5 .9375 4.0625 TESTTB 500 499 1 SYSAUX 550 30 520 EXAMPLE 100 20.75 79.25 UNDOTBS1 110 96.625 13.375 Output the result to the specified file in SQL command. SQL> spool out.txt SQL> select * from v$database; SQL> spool offCopy the code4. View and manage ORACLE data files
4.1 Viewing data Files
SQL> select name from v$datafile;
Copy the code4.2 Changing the Data File Size
SQL> alter database datafile '/oradata2/hisvhfs/undotbs01.dbf' resize 30G;
Copy the code4.3 Viewing All User Tablespaces and their Corresponding Data files and data file sizes
SQL> select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;
Copy the code4.4 Viewing the Status and Size of data Files
SQL> select name,BYTES,STATUS ,FILE# from v$datafile;
Copy the code4.5 Viewing all Data Files
SQL> select name from v$datafile;
Copy the code4.6 Viewing data Files corresponding to all tablespaces
SQL> select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;
Copy the code5. View and manage ORACLE tables
5.1 Viewing the Size of all Segments
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name
Copy the code5.2 Querying the Table Size and tablespace Size
Table size has two meanings. One is the amount of physical space allocated to a table, regardless of whether the space is being used. The number of bytes can be queried like this:
The current example is to log in as user Scott and create a copy of the DEPT table dept_copy2.
SQL> select segment_name, bytes 2 from user_segments 3 where segment_type = 'TABLE'; SEGMENT_NAME BYTES -------------------------------------------------------------------------------- ---------- DEPT_COPY2 65536 DEPT_COPY 65536 BIN$7Sa/taXJEKHgQ2kFqMCxMQ==$0 65536 ITEMS 65536 SALGRADE 65536 EMP 65536 DEPT 65536 7 Rows selected or SQL> Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name; SEGMENT_NAME SUM(BYTES)/1024/1024 -------------------------------------------------------------------------------- -------------------- DEPT 0.0625 PK_DEPT 0.0625 EMP 0.0625 DEPT_COPY 0.0625 DEPT_COPY2 0.0625 ITEMS 0.0625 PK_EMP 0.0625 SALGRADE 0.0625 8 ROWS SELECTED SALGRADE 0.0625 8 ROWS SELECTED SQL> select table_name from user_tables; TABLE_NAME ------------------------------ DEPT EMP BONUS SALGRADEanalyze table emp compute statistics; ITEMS DEPT_COPY DEPT_COPY2 SQL> analyze table DEPT_COPY2 compute statistics;Copy the code5.3 Viewing the Tablespace to which a Table belongs
SQL> select tablespace_name from all_tables where table_name='SYS_EXPORT_FULL_01';
Copy the code5.4 Specifying a tablespace when Creating a Table
SQL>create table a (name varchar(10)) tablespace test;
Copy the code5.5 Viewing the Database Table Structure
SQL> desc dept_copy2;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(2) Y
DNAME VARCHAR2(14) Y
LOC VARCHAR2(13) Y
Copy the code5.5 Changing the Column Names of a Table
SQL> alter table users rename column ID to PID;
Copy the code5.6 Changing the character size of a table column
SQL> alter table student modify class varchar2(10);
Copy the code5.7 Creating a Primary Key
SQL> alter table users add constraint pk_users primary key(name);
Copy the code5.8 Changing the column Data type of a Table
SQL> select * from users; NAME AGE PID ---------- ---------- ---------- SQL> alter table users add(newColumn varchar2(20)); SQL> update users set newColumn = PID; SQL> commit; SQL> alter table users drop column PID cascade constraints; SQL> alter table users rename column newColumn to PID; SQL> commit; SQL> desc users; Is the name empty? Type -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- the NAME NOT NULL VARCHAR2 (10) AGE NUMBER PID VARCHAR2 (20) SQL> select * from users; NAME AGE PID -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- to create sequence: SQL> create sequence seq_PID start with 3201231988001010101 maxvalue 3201231999999999999 minvalue 3201231988001010101 nocycle cache 100; Create trigger: SQL> create or replace trigger trigger_users before insert on users for each row begin select seq_PID.nextval into :new.PID from dual; end; SQL> select sequence_name from USER_SEQUENCES; SEQUENCE_NAME -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- SEQ_ID view the user triggers SQL > select * from user_triggers where table_name = upper('job'); SQL> select ID,avg(age),sum(age), Max (age),count(*) 2 from job group by rollup(ID); ID AVG(AGE) SUM(AGE) MAX(AGE) COUNT(*) ---------- ---------- ---------- ---------- ---------- 1583 25 25 25 1 1584 24 24 24 1 1585 25 25 25 1 1586 25 25 25 1 1587 23 23 23 1 1683 22 22 22 1 1684 24 24 24 1 1685 25 25 25 1 1686 24 24 24 24 1 1687 22 22 22 1 1688 28 28 28 1 ID AVG(AGE) SUM(AGE) MAX(AGE) COUNT(*) ---------- ---------- ---------- ---------- ---------- 1689 26 26 26 1 1690 26 26 26 1 1783 35 35 35 1 1784 1 1883 24 24 24 1 1884 1 25.2 378 35 17 Having clause and group The BY clause is generally used together to further filter the aggregated data after the relevant data has been aggregated. SQL> select ID,avg(age),sum(age),max(age),count(*) 2 from job group by id having avg(age)>25; ID AVG(AGE) SUM(AGE) MAX(AGE) COUNT(*) ---------- ---------- ---------- ---------- ---------- 1690 26 26 26 1 1783 35 35 35 1689 26 26 26 1 1688 28, 28, 28 1 multi-table join query SQL > select job. The name and job. Id, job. The job, the job. The age, the users. The name, the users, the age the from job,users 2 where job.name=users.name; The NAME ID JOB AGE NAME AGE -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- desc in oracle database, there are two kinds of usage, Order by column_name desc; column_name desc; column_name desc; column_name desc; column_name desc; Look at the segment size of the table, the actual physical size of the table, whether it is in use or not. SQL> select segment_name,partition_name,bytes,blocks,extents from user_segments where segment_name='JOBS'; (name of the segment, SQL> select segment_name,partition_name,count(extent_id),sum(bytes),sum(blocks) from user_extents WHERE segment_name,partition_name,count(extent_id),sum(bytes),sum(blocks) from user_extents where segment_name='JOBS' group by segment_name,partition_name;Copy the code6. View and manage common parameters and configurations of ORACLE
6.1 Viewing control Files
SQL> select name from V $controlfile;Copy the code6.2 Viewing redo Log Files (log files for short)
SQL> select * from v$logfile;
Copy the code6.3 Viewing table Indexes
SQL>select index_name,index_type,table_name from user_indexes where table_name=' table name ';Copy the code6.4 Changing the Oracle Time Format
SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
Copy the code6.5 Enabling a Listener
SQL> LSNRCTL start orc5_lisenterCopy the code6.6 Querying the DATABASE SCN and Time
SQL> select sysdate from dual; Query the database time SYSDATE ----------------- 20131216 23:52:55 SQL> select dbMS_flashback. get_system_change_number from dual; View the current database of SCN GET_SYSTEM_CHANGE_NUMBER number -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 1583042 SQL > select to_char(scn_to_timestamp(1583042),'yyyy-mm-dd hh24:mi:ss') from dual; Based on the database of SCN find corresponding database time TO_CHAR (SCN_TO_TIME -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- the 2013-12-16 23:45:38 SQL > select timestamp_to_scn(to_date('2013-12-16 15:30:19','yyyy-mm-dd hh24:mi:ss')) from dual; TIMESTAMP_TO_SCN(TO_DATE('2013-12-1615:30:19','YYYY-MM-DDHH24:MI:SS')) ---------------------------------------------------------------------- 1519388 SQL> select * from (select time_dp,scn from smon_scn_time order by time_dp desc) where rownum<10; Search for SCNS with rownum less than 10 by the corresponding time. TIME_DP SCN ----------------- ---------- 20131216 15:53:02 1584574 20131216 15:46:45 1583098 20131216 15:45:20 1583022 20131216 15:39:08 1582399 20131216 15:37:01 1581998 20131216 15:31:08 1579915 20131216 14:46:02 1577210 20131216 14:40:16 1576196 20131216 14:35:12 1575597 9 rows selected.Copy the code6.7 Database Cursor Settings
SQL> show parameter open_cursors; SQL> select count(*) from V $open_cursor; SQL> select name,value from V $sysstat where name ='opened cursors current'; SQL> alter system set open_cursors=3000 scope=both; Change the default cursor numberCopy the code7. View and configure the ORACLE redo log
7.1 Creating a Log File Group
SQL> alter database add logfile group 4
(
'/oracle/oradata/orc6/redo04.log'
)
size 10M;
Copy the code7.2 Viewing log File Groups
SQL> select group#,status,member from v$logfile;
Copy the code7.3 Adding log Files to a Log file group
SQL> alter database add logfile member '/oracle/oradata/orc6/redo05.log' to group 3;
Copy the code7.4 Viewing the Log File Size
SQL> select GROUP#,BYTES,BLOCKSIZE,MEMBERS from v$log;
Copy the code8, commonly used query desc
SQL> desc dba_temp_files; SQL> select * from temporary tablespace where v$database = desc; SQL> desc dba_data_files; SQL> desc user_segments; Oracle segment SQL> desc dBA_segments; ORACLE segment SQL> desc dba_tables; SQL> desc dBA_objects SQL> desc dBA_users; SQL> desc dBA_tablespaces; SQL> desc user_segments; SQL> desc dba_jobs; Job SQL> desc dba_role_privs; SQL> desc dba_constraints View the constraint. SQL> desc dba_cons_columns View the column constraint. SQL> show parameter log_archive_dest; SQL> archive log list; Check the archive directory and log sequence SQL> select * from V$FLASH_RECOVERY_AREA_USAGE; View the flash_RECOVERy_area usage. SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage; Calculate the space occupied by the flash_Recovery_area. SQL> show parameter recover; SQL> 0 v$process = 0 v$process = 0 v$process = 0 v$process = 0 v$process = 0 SQL> select addr, program from v$process; SQL> select paddr, paddr, paddr, paddr, paddr, paddr, paddr, paddr name from v$bgprocess where paddr<>'00'; If v$bgprocess.paddr is associated with v$process.addr, SQL> select a.addr, a.name, a.description from v$bgprocess a, v$process b where a.paddr=b.addr;Copy the codeView information about ASM disk groups
SQL > desc v $asm_disk; SQL> desc v$asm_diskgroup;Copy the code10. Common SQL command line Settings
1, set set wrap on/off 1, set set wrap on/off 2, set set wrap on/off Set linesize N Sets the width of each row of records returned by the query, beyond which it is truncated, but this width is complemented by Spaces.Copy the codeStart the database
SQL> startup
Copy the codeStop the database
SQL> shutdown
Copy the code13. View orACL E version
SQL> select * from v$version;
Copy the codeView database information
SQL> select name from v$database;
Copy the codeXv. View the user
SQL> select * from all_users;
Copy the code16. Check the tablespace
SELECT t.tablespace_name FROM dba_tablespaces t;
Copy the codeDelete tablespaces
SQL> DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;
Copy the codeView the number of concurrent connections
SQL> select count(*) from v$session where status='ACTIVE';
Copy the codeQuery the memory allocation
SQL> select * from v$sga;
Copy the codeView the number of real-time connections
SQL> select count(*) from v$session;
Copy the codeView the location of the pfile file
SQL> show parameter pfile;
Copy the codeIn this paper, to the end.
Original is not easy, if you think this article is a little useful to you, please give me a like, comment or forward for this article, because this will be my power to output more quality articles, thanks!
By the way, dig friends remember to give me a free attention yo! In case you get lost and you can’t find me next time.
See you next time!