Shulou(Shulou.com)06/03 Report--

Basic operation of oracle

Desc all_tables;-View the table structure

Select * from all_tables;-- view all tables in the current database

Select table_name from user_tables; looks at the table of currently logged-in users:

Select * from dba_users; to see which users there are.

Sqlplus system/oracle as sysdba login dba user

-- View-related tables

Select * from user_views

Select * from dba_views

-several tables that involve viewing tablespaces and data files

SELECT * FROM dba_free_space-- View the remaining space in the tablespace

Select * from dba_temp_files;-View temporary tablespaces

Select * from dba_tablespaces-View tablespaces

Select * from dba_data_files-View the location of the data file

SELECT * FROM user_source-- View stored procedures

-- tables related to indexes

Select * from user_tables

Select * from all_indexes where table_name = 'TEST1'

Select* from all_ind_columns where table_name = 'TEST1'

Select * from user_ind_columns where index_name='TIME_IDX1'

Select * from user_indexes where table_name='TEST1'

View occupied tables in oracle, analyze AWR reports time-consuming, and use when analyzing bottlenecks

For example, if you want to delete a table or change it, you can check it with the following statement

(1) check to see if any SQL statements are occupying this table.

Find out who is occupying it.

Select sess.INST_ID, sess.machine

Sess.program, sess.sql_id

Sess.sid, sess.serial#

Sess.PROCESS

Lo.oracle_username, lo.os_user_name

Lo.locked_mode

Ao.object_name, ao.object_type

Ao.status

From gv$locked_object lo, dba_objects ao, gv$session sess

Where ao.object_id = lo.object_id

And lo.session_id = sess.sid

And sess.username = 'SCOTT'

-- kill the process sid,serial#

Alter system kill session'10,11562'

(2) check to see if there are any sessions that have not ended.

SQL > select username,sid,serial#,paddr,status from v$session where username='SCOTT'

USERNAME SID SERIAL# PADDR STATUS

SCOTT 1 281 000000008E51C510 KILLED

SCOTT 20 362 000000008E491150 INACTIVE

SCOTT 21 175 000000008E48D050 INACTIVE

SCOTT 28 169 000000008E51C510 KILLED

SQL > select PROGRAM from v$process where addr='000000008E490110'

PROGRAM

Oracle@master.example.com

-- kill the process sid,serial#

Alter system kill session '1281'

Alter system kill session '20362'

Alter system kill session '21175'

Alter system kill session '28169'

SQL > drop user scott cascade

User dropped.

Oracle Database Tips:

The difference between rebuild and rebuild online

Alter index rebuild online: essentially scan the table instead of scanning the existing index blocks to rebuild the index

Alter index rebuild: only existing index blocks are scanned for index reconstruction.

Rebuild index online does not block DML operations during execution, but TM locks with a mode of 4 are required during the start and end phases. Therefore, if something else is running for a long time before or at the end of the rebuild index online, it is likely to cause a lot of lock waiting. In other words, blocking still occurs before execution, and exclusive locks should be avoided, so you need to stop the application at night.

Rebuild index will block DML operations during execution, but at a faster speed.

The scanning methods are different when rebuilding the index.

Rebuild uses "INDEX FAST FULL SCAN"

Rebuild online uses "TABLE ACCESS FULL"

That is, rebuild index is the scan index block, while rebuild index online is the data block that scans the whole table.

Put the index in a different tablespace from the corresponding table.

When reading a table, the table and the index are performed at the same time. If the table competes with the index and in one table space, it can be executed in parallel in two table spaces. If a table is large and takes a long time to index, it can be set to not generate redo information when indexing.

Oracle defaults to five blocks, which is set to an integer multiple of 5.

-- View-related tables

Select from user_views

Select from dba_views

-several tables that involve viewing tablespaces and data files

SELECT FROM dba_free_space;-View the remaining space in the tablespace

Select from dba_temp_files;-view temporary tablespaces

Select from dba_tablespaces;-View tablespaces

Select from dba_data_files;-View the location of the data file

SELECT * FROM user_source;-- View stored procedures

-- tables related to indexes

Select from user_tables

Select from all_indexes where table_name = 'TEST1'

Select from all_ind_columns where table_name = 'TEST1'

Select from user_ind_columns where index_name='TIME_IDX1'

Select * from user_indexes where table_name='TEST1'

1. Create a tablespace

Create the table test_data and the index space test_idx, and put the data file here in / oracle/oracle/oradata/orcl/ with a size of 1G.

Create tablespace test_data datafile'/ oracle/oracle/oradata/orcl/test_data01.dbf' size 1024m

Create tablespace test_idx datafile'/ oracle/oracle/oradata/orcl/test_idx01.dbf' size 1024m

Select * from datafile

two。 Create a user

Create a user test1 and put it in the tablespace test_data

Create user test1 identified by test1 default tablespace test_data

DROP USER test1 CASCADE-Delete a user

3. Authorization to new users

Grant connect, resource,create session,CREATE SYNONYM, create view,select any table TO test1

Revoke xxx on xxxtab to test1;-revoke permissions

4. Log in to a new user to create a table to specify a tablespace

Create a table test1 that specifies the tablespace test_data

Create an index for table Test1, specifying the index space test_idx

Create table test1 (id number (5), create_date varchar2 (20), charg_date varchar2 (20)) tablespace test_data

Create index time_idx1 on test1 (create_date) tablespace test_idx

Create index time_idx2 on test1 (create_date,charg_date) tablespace test_idx

Create index time_idx3 on test1 (chage_date) tablespace test_idx

Drop index TIME_IDX1;-Delete the index

Alter table test1.test1 rename column charg_date to chage_date;-- modifies the field name

Alter table test1.test1 modify (chage_date nvarchar2 (20));-- modify the field type

Comment on column T_00970001.C_009700010003 is's' cause of punishment'

Comment on column test1.id is' create id'

Comment on column test1.create_date is' creation time'

Comment on column test1.chage_date is' modification time'

5. Create a view

Both indexes and views occupy real table space, so plan as much as possible when creating

Create view v_test1 as select * from test1.test1;-mainly because you don't want to enter the previous test1 every time

Commit

DROP VIEW vault Test1;-- Delete the view

6. Import data

Beginfor i in 1..80loopinsert into test1.TEST1 (id,create_date,chage_date) values (SYSDATE-i), to_char (SYSDATE-i,'yyyymmddhh34miss'); END LOOP;commit

END

7. test

Several examples that can be changed from a full table scan to an index scan:

Contains function transformations and operators are all TABLE ACCESS FULL

1.select from v_test1 where to_char (sysdate,'yyyymmdd') + 7 < to_char (sysdate,'yyyymmdd')

1.select from v_test1 where chage_date < to_char (sysdate,'yyyymmddhh34miss')-7

1.select from v_test1 where to_char ('chage_date') <' 20191003073258; or

Select from v_test1 where create_date < to_date ('20191003 copyright wow yyyyymuri MMMMurdd`)

2.select from v_test1 where create_date < '03muroctMuth1900; or

Select from v_test1 where create_date <'03-October-19'

-- check the data within 1 hour

1.select from test1 where (sysdate-to_date (chage_date,'yyyymmddhh34miss')) 24 = TO_CHAR ((sysdate-1 hand 24), 'yyyymmddhh34miss')

Alter Table test1 Add name varchar2 (10);-- add a field name to the table

Update test1 set name='t1' where id ='1'

Update test1 set name='t2' where id ='2'

Update test1 set name='t3' where id ='3'

COLUMN can change column headings

1)。 Change the default column header

2)。 Change the column name NAME to the new column name EMPLOYEE NAME and put the new column name on two lines:

3)。 Change the display length of the column:

4)。 Set the alignment of column headings

5)。 Do not let a column appear on the screen

7)。 When displaying column values, if the column value is null, replace the null value with the text value

8)。 Sets the winding mode of a column

9)。 Displays the current display property value of the column

10)。 Set the display property of all columns to the default value

For more information, please see https://blog.csdn.net/xiazaixiazai2010/article/details/102622347.

COLUMN

Full name of col command column

Varchar2

Col name for a20

Number

Col id for 9999

Col ID for A60

Col CREATE_DATE for A60

Col CHAGE_DATE for A60

Col NAME for A60

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 MERGE JOIN

2 1 SORT (JOIN)

3 2 NESTED LOOPS

4 3 TABLE ACCESS (FULL) OF'B'

5 3 TABLE ACCESS (BY INDEX ROWID) OF'A'

7 1 SORT (JOIN)

8 7 TABLE ACCESS (FULL) OF'C'

8. The idea of index creation

Multi-table association

The idea of optimization is from small to large, that is, starting from the join with the strongest restriction and the least number of records, basically using the nested loop join, completing the join of other tables in turn, and using the index reasonably when accessing each table, especially the composite index technology.

Composite index

Composite index is much more efficient than single-field index, but the internal principle of composite index is more complex than single-field index. Composite index has two important principles to grasp: prefix and selectivity.

Many domestic IT system developers do not realize that we should give priority to the design of composite index, let alone fully understand the two important principles of prefix and selectivity of composite index.

Prefix:

The composite index is used in the query as long as there is a query value after the first field of the compound index condition is where.

Optional:

Those with more field values are at the top, and the more optional they are, the fewer records are located, and the higher the query efficiency is.

9. Monitoring index

Find these unreasonable indexes:

1. Judge according to the principle

There must be many compound indexes in this situation. According to the two principles of prefix and selectivity, we can analyze the record distribution of each field of this table and make our own merging and integration.

2. Use the monitoring feature of oracle index.

A more secure approach is to make use of the index monitoring feature provided by oracle9i, execute the index monitoring function before the start of a typical business cycle, end the monitoring after the typical business cycle, check the v$object_usage view, and delete which index has not been adopted.

Index fragmentation analysis and defragmentation

3. Frequent delete and update operations on the index fields will make the index produce a large number of fragments, which will greatly affect the efficiency of the index, and cause the increase of the index iCompo.

1. For index fragmentation analysis, if the fragmentation space of the index exceeds 20%, it is understood that the index fragmentation is very serious, and the index is rebuilt.

2. The methods of rebuild and coalesce can be used to rebuild the index.

Excerpt:

Collect statistics used by the index:

ANALYZE INDEX time_idx1 VALIDATE STRUCTURE

View statistics:

SELECT name, (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100

AS wastage FROM index_stats

When the wastage exceeds 20%, the index needs to be rebuilt

ALTER INDEX time_idx1 REBUILD

Consolidate the index (choose one with rebuild):

ALTER INDEX time_idx1 REBUILD

Export the awr report:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.