Oracle Common maintenance at work

http://blog.csdn.net/lili72

Background: For historical reasons, some of the company's business is still being used in Oracle . A problem is a lot of trouble. Due to the long-term use of MySQL and hive,Hbase , etc., some Oracle The order slowly is also forgotten, but some of the company's business on Oracle , a sudden problem, every time to find the problem is more laborious, now the commonly used commands recorded.

Frequently encountered problems with Oracle :

1.0 telnet to Oracle.

1.0.1 ssh root@192.168.119.132 -p22330

Su Oracle

1.0.2 Ps-ef | grep Oracle

Find the location of Oracle

Enter Sqlplus

Sqlplus/nolog

Conn/as SYSDBA

1.1 The Oracle password expires and the connection fails

1.1.1 Viewing users

SELECT * from Dba_profiles where resource_type= ' PASSWORD ';

1.1.2 Setting no expiration

Alter profile DEFAULT Limit unlimited;

Alter profile DEFAULT limit Password_reuse_time unlimited;

1.2 Oracle Table partition overrun, insert data failure due to Oracle 's range partitioning

New Table partition:

ALTER TABLE Bi_test_1215_test_del add partition t_range_p132 values less than (to_date (' 20150110 ', ' YYYYMMDD ') Tablespace USERS Storage (initial 64K minextents 1 Maxextents Unlimited);

1.3 Oracle 's tablespace is full and needs to be expanded.

1.3.2 View Table space usage:

SELECT TBSTable space Name,                                    
SUM (Totalm)Total sizeM,
SUM (USEDM)space already usedM,
SUM (REMAINEDM)remaining spaceM,
SUM (USEDM)/sum (Totalm) *100percent used,                            
SUM (REMAINEDM)/sum (Totalm) *100percent remaining                            
From (
SELECT b.file_id ID,
B.tablespace_name TBS,
B.file_name name,
b.bytes/1024/1024 Totalm,
(B.bytes-sum (NVL (a.bytes,0)))/1024/1024 usedm,
Sum (NVL (a.bytes,0)/1024/1024) remainedm,
Sum (NVL (a.bytes,0)/(b.bytes) *100),
(+-(SUM (NVL (a.bytes,0))/(b.bytes) *100))
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.file_id,b.bytes
ORDER by B.tablespace_name
)
GROUP by TBS

1.3.2 Extension Table space :

-- Add data Files

alter tablespace USERS add datafile '/data2/oracle/users02.dbf ' size 50m ;

1.4 Oracle 's lock table processing

Select Sess.sid,

sess.serial#,

Lo.oracle_username,

Lo.os_user_name,

Ao.object_name,

Lo.locked_mode

From V$locked_object Lo,

Dba_objects AO,

V$session Sess

where ao.object_id = lo.object_id and lo.session_id = Sess.sid;

Or:

SELECT * from V$session T1, v$locked_object t2 where t1.sid = t2. session_id;

Delete the lock list statement:

Alter system kill session ' 198 ';

1.5 Oracle Statement optimization, new index, build partition table

1.5.1 first considers the query criteria to build the index.

Created: Create index inde_name on table_name (col_name) tablespace users Storage (initial 500k next 500k Pctincreas E 0)

query:select * FROM dba_indexes WHERE table_name = ' t_li72 ';

1.5.2 data volume is relatively large, consider building a partitioned table.

CREATE TABLE Li72_test_deal

(

Logdate DATE,

FILENAME VARCHAR2 (600)

)

Partition by Range (Logdate) (

Partition T_RANGE_P1 values less than (to_date (' 20140901 ', ' YYYYMMDD ')),

Partition T_RANGE_P2 values less than (to_date (' 20140902 ', ' YYYYMMDD ')),

Sqoop to Oracle statement: from hive to Oracle

Select columns by partition to synchronize data to Oracle

Sqoop export--connect jdbc:oracle:thin:@192.1.118.210:1521:orcl--username biuser--password biusertmp--table Li72.tmp_src_play_lh_1203_temp--columns Logdate,filename,singer,song,searchoverplay,searchplay,overplay, Uninputkey,inputpartkey,inputallkey,mp3overplay,mvoverplay,mvrandomplay,mp3randomplay,tryplay,download, Searchclick,search,play--export-dir/user/hive/warehouse/bi_test.db/li72_hoer_sear_play/dt=${vday}-- Input-fields-terminated-by ' | '

From Oracle to Hive:

Sqoop import--hive-import--connect jdbc:oracle:thin:@192.116.80.118:1521:customer2--username biuser--password Biusertmp--verbose-m 1--table class_type

Oracle Common maintenance at work