Issues encountered by Oracle

1, ORA-01502: Index ' Dbepms. sys_c009390 ' or partition of such index is in an unusable state

WORKAROUND: [Note index naming rules ix_ table name _ Column name/ix_ table name _ ordinal (index length within 30 characters)]

(1) Rebuilding index: Alter index INDEX_NAME rebuild;

(2) If it is a partition index, only need to rebuild that failed partition alter INDEX INDEX_NAME rebuild partition partition_name;

(3) or change the name of the current index alter index index_name rename to New_index_name

2, query the current user which index is invalid (vaild indicates that the index is available, unusable indicates that the index is not available, USABLE indicates that the partition of the index is usable)

Select Index_name from user_indexes where status <> ' VALID ';------all rebuild is detected for all unusable indexes

3. Modify the Index Table space (the official library index tablespace is I_JSEPMS)

ALTER INDEX index name rebuild tablespace tablespace_name;

4. Create INDEX syntax [Note: Index naming rules ix_ table name _ Column name/ix_ table name _ ordinal (index length within 30 characters)]

--General index (tablespace tablespace_name; Specify index space, official library index space is I_jsepms) create index name on table name (column name) tablespace tablespace_name; --Single row

Create index I index name on table name (column name [, column name, ....]) tablespace tablespace_name; --can have multiple columns

--Unique index create unique index name on table name (column name) tablespace tablespace_name; --Single row

Create unique index name on table name (column name [, column name, ....]) tablespace tablespace_name; --can have multiple columns

5. Delete Index

Drop index index_name;

6, if you want to delete the table operation, please release the table space before deleting the table

ALTER TABLE Eaf_uploadfile deallocate UNUSED KEEP 0;--release tablespace

drop TABLE TableName;

7, inadvertently create the sequence, need to delete

Drop sequence sequence_name;

8. Query table space size and path

Select B.file_name physical file name, B.tablespace_name table space, b.bytes/1024/1024 size M, (B.bytes-sum (NVL (a.bytes, 0)))/1024/ 　　 1024 used M, substr ((B.bytes-sum (NVL (a.bytes, 0))/(b.bytes) * 100, 1, 5) utilization from Dba_free_space A,dba_data_files b where a.file_id = b.file_id GROUP by b.tablespace_name,b.file_name, b.bytes order by B.tablespace_name;

9. Query the number of data bars in the table in Oracle's official database

Select TABLE_NAME, COUNT_ROWS (table_name) nrows from User_tables ORDER by table_name ASC;

10. Query the number of data bars in SQL database tables

SELECT a.name, b.rows from sysobjects a INNER joins sysindexes b on a.id = b.ID WHERE (a.type = ' u ') and (B.indid in ( 0, 1)) ORDER by A.name,b.rows DESC

11, if the execution of the SQL statement times wrong ORA-00054: The resource is busy, requires the designation of NOWAIT (first with DBA-granted V$lock and V$session object permissions)

Solution:--View the user that process shone as dead lock select B.username, B.sid, b.serial#, logon_time from V$locked_object a,v$session b where a.session_id = B.sid ORDER by B.logon_time; D

This statement will find the lock generated by all DML statements in the database, and it can be found that any DML statement actually produces two locks, one is a table lock and one is a row lock.

--Let's kill the deadlock process sid,serial#

Alter system kill session ' sid,serial# ';

12. Find out the table name, index name, index column

SELECT table_name, index_name, COLUMN_NAME, column_position from User_ind_columns WHERE (index_name like ' sys_% ' or index_name like ' ix_% ') and table_name don't like '%$% '

--Query the number of new indexes does not include the system default index creation

Select distinct index_name from User_ind_columns WHERE index_name like ' ix_% ' and table_name don't like '%$% '

Issues encountered by Oracle