Common oracle maintenance and oracle maintenance at work

Common oracle maintenance and oracle maintenance at work

Http://blog.csdn.net/lili72

Background: due to historical reasons, some of the company's businesses are still using oracle. If something goes wrong, it is very troublesome. Since mysql, hive, Hbase, and so on are used for a long time, some oracle commands are slowly forgotten, but some businesses on oracle suddenly encounter problems, every time you search for a problem, you can find out frequently used commands.

Common oracle problems:

1.0 log on to oracle remotely.

1.0.1 ssh root@192.168.119.132-p22330

Su oracle

1.0.2 ps-ef | grep oracle

Locate oracle

Go to sqlplus

Sqlplus/nolog

Conn/as sysdba

1.1 oracle password expired, connection failed

1.1.1 view users

Select * from dba_profiles where resource_type = 'Password ';

1.1.2 set no expiration

Alter profile DEFAULT limit unlimited;

Alter profile DEFAULT limit password_reuse_time unlimited;

1.2 oracle table partitions exceed the limit and data insertion fails due to oracle range partitions

Add Table partitions:

Alter table bi_test_1215_test_del add partition t_range_p132 values less than (to_date ('201312', 'yyyymmdd') tablespace USERS storage (initial 64 K minextents 1 maxextents unlimited );

 

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

1.3.2 view the tablespace usage:

SELECT tbs tablespace name,
Sum (totalM) total size M,
Sum (usedM) used space M,
Sum (remainedM) remaining space M,
Sum (usedM)/sum (totalM) * 100 percentage used,
Sum (remainedM)/sum (totalM) * 100 percentage 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 ),
(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 extended tablespace:

-- Add data files

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

1.4 oracle 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 TABLE statement:

Alter system kill session '123 ';

1.5 oracle statement optimization, new indexes, and Partition Table Creation

1.5.1 create an index based on the query conditions.

Create: create index inde_name on table_name (col_name) tablespace users storage (initial 500 k next 500 k pctincrease 0)

Query: select * from dba_indexes where table_name = 't_ li72 ';

1.5.2 if the data volume is large, create a partition table.

Create table li72_test_deal

(

Logdate date,

FILENAME VARCHAR2 (600)

)

Partition by range (logdate )(

Partition t_range_p1 values less than (to_date ('201312', 'yyyymmdd ')),

Partition t_range_p2 values less than (to_date ('201312', 'yyyymmdd ')),

 

 

Sqoop to oracle statements: From hive to oracle

Synchronize data from selected columns by partition to oracle

Sqoop export -- connect jdbc: oracle: thin: @ 192.1.118.210: 1521: orcl -- username biuser -- password biusertmp -- table li72.tmp _ values -- 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