Wednesday, December 3, 2014

Create orapassword file

in init.ora file add 
remote_login_passwordfile EXCLUSIVE/SHARE
EXCLUSIVE - file is available only to one database and you can have more than one user
SHARE - file is available for multiple databases, only SYS user can be in this file.

For LINUX system file name has to be orapw (11g)

remote_login_passwordfile EXCLUSIVE/SHARE
SQL> show parameter password;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

SQL> select * from v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

now I can login to DB using
sqlplus sys/PASS@SID as sysdba


Thursday, November 20, 2014

Partitions

Select partition list for table

select * from dba_tab_partitions
where 
table_owner='OWNER' and table_name = 'TABLE_NAME'

Select min/max date in partition 

select min(trx_date),max(trx_date) from TABLE_NAME partition (SYS_P25020)

Split partition base on date

ALTER TABLE P_TRX_TICKETS
    SPLIT PARTITION FOR(TO_DATE('01-MAY-2014','dd-MON-yyyy'))
    AT (TO_DATE('01-May-2014','dd-MON-yyyy'))
    INTO (PARTITION p_trx_12014,PARTITION p_trx_1a2014);

Split partition using partition name

  ALTER TABLE xms.P_TRX_TICKETS
    SPLIT PARTITION SYS_P25020
    AT (TO_DATE('01-May-2014','dd-MON-yyyy'))
    INTO (PARTITION tic_042014,PARTITION tic_05_062014);

Links
Oracle Introduction to Partitioning

Monday, October 6, 2014

Mysql Store Procedure

Use curson and print output in store procedure

DECLARE _output TEXT DEFAULT '';
DECLARE _ID INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT ID FROM CodeID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur1;

REPEAT
  FETCH cur1 INTO _ID;
  IF NOT done THEN
    SET _output = CONCAT(",", _ID); /*SEE ALT BELOW*/

  END IF;
UNTIL done END REPEAT;
CLOSE cur1;

SELECT _output;

Loop in store procedures
http://www.mysqltutorial.org/stored-procedures-loop.aspx 

Friday, October 3, 2014

Create CSV table

Create CSV table in mysql.

Create database CSV (or you can use any existing DB)


CREATE DATABASE CSV;
USE CSV;
CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL) ENGINE = CSV;
Query OK, 0 rows affected (0.12 sec) 
INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.00 sec
Records: 2 Duplicates: 0 Warnings: 0
SELECT * FROM test;



When you check table DB structure you will see files:
-rw-rw---- 1 mysql mysql 65 Oct 2 13:58 db.opt
-rw-rw---- 1 mysql mysql 8578 Oct 3 11:35 test.frm
-rw-rw---- 1 mysql mysql 35 Oct 3 11:36 test.CSM
-rw-rw---- 1 mysql mysql 60 Oct 3 11:37 test.CSV
test.CSV is a data file.

Now you can replace this file with prepared file with data. 
After upload new file you need execute 

FLUSH TABLE test

to refresh data in table.

CSV table not allow NULL value, you will need replace all null values in csv file to empty string "" or 0 for numeric values.

I used mysqldump to export table from one DB and import it to second DB. null value are display as "\N"
To change "\N" to empty string I used following command:
sed ':a;N;$!ba;s/\\N/""/g' p_trx_tickets.txt > p_trx_tickets.CSV
 After update data file execute:
FLUSH TABLE p_trx_tickets
Now data are available to do select and insert it to other DB.

When I did't replaced "\N" to "" (empty string) then I was receiving message "Table corrupted, need repair"




Monday, September 29, 2014

Grep commands example

#######################################################################
# This file contains the grep demo commands we did in the pattern
# matching lecture.
#######################################################################




#######################################################################
# the input data files all consist a number of lines, each of which
# has the following format.
#######################################################################
mschreib:02 :Schreiber:Marc H.:10/11/10/60:25.3333:1:1:1:1




#######################################################################
# simplest uses.
#######################################################################

# Looks for the word "David" in the file "rank.txt"
grep David rank.txt

# Looks for the word "Rebecca" in a number of files.
# grep will print the names of the files as well as the lines found.
grep Rebecca ??.txt




#######################################################################
# Interaction with other commands
#######################################################################

# Looks for the total summary line from the "wc" command.
wc ??.txt | grep total

# midterm scores start with a slash ("/") in my file.
# count the number of lines that include "/5", sufficient for 
# finding the number of people who scored above 50 for my purpose.
grep /5 rank.txt | wc

# count the number of people who scored a perfect 60.
grep /6 rank.txt | wc




#######################################################################
# Regular expression special characters
#######################################################################

# Shows what the character "^" does.
# "^s" looks for a line that starts with a login name whose first
# character is "s".
grep s rank.txt
grep ^s rank.txt

# Shows what the character "$" does.
# ":1$" looks for a line that ends with aranking of ":1"
grep 1 rank.txt
grep :1$ rank.txt

# Shows what the character "." does.
# "." allows any letter to take its place in "Cr.nin"
grep Cr.nin rank.txt

# Another example of what "." does.
# "/5.:" is a better way of searching for people who made a score of
# 50 or higher than the one used above because we now require some
# other letter, denoted by the "." to follow the number "5".  This
# prevents a score of "5" being miscounted as above 50.
grep /5.: rank.txt | wc

# Shows what the characters "[]" do.
# Looks for all people who have made scores between 57 and 59.
grep '/5[7-9]:' rank.txt | wc

# Shows what the characters "[^]" do.
# Looks for all people who have not made scores between 57 and 59.
grep '/5[^7-9]:' rank.txt | wc

# Shows what the character "*" does.
# Looks for a login name that repeats the letter "e" zero or more
# times in the form of "anl", "anel", "aneel", or "aneeel".
grep 'ane*l' rank.txt

# Another example of what "*" does.
# Looks for a score between 58-59, followed by a bunch of arbitrary
# letters denoted by ".*", and with a ranking between 1 and 5 at the
# end of the line.
grep '/5[8-9].*:[1-5]$' rank.txt

# Shows the difference between "*" and "+"
# The first one matches a ranking of either 1 or 11.
# The second one matches only a ranking of 11.
egrep ':11*$' rank.txt
egrep ':11+$' rank.txt

# Shows more features of egrep: () |
# This one looks for people who are ranked 5 or 6.
egrep '(:5|:6)$' rank.txt

# Shows the use of escape characters.
# If the intention is to look for numbers of the form "25.XXX", the
# first one doesn't quite work because the dot would be interpreted as
# a special character of regular expressions, which denotes any
# character.  To make it work, it needs to be prefixed by a backward
# slash "\".
grep '25.' rank.txt
grep '25\.' rank.txt




#######################################################################
# More command line options.
#######################################################################

# To find out more information about these commands, type these.
man grep
man egrep


# Shows what "-v" does.
# We look for lines containing the word David, but we want to exclude
# those lines that have the word "Stavens".
grep David rank.txt | grep -v Stavens

# Shows what "-n" does.
# It tells me the line number of a line that is a match.  In this
# case, the line number and the ranking coincides.
grep crloose rank.txt
grep -n crloose rank.txt

Wednesday, September 24, 2014

Move partition table to different tablespace


This alter command allow you to move not partition table from one tablespace to another tablespace

ALTER TABLE [TABLE NAME to be moved] MOVE TABLESPACE [destination TABLESPACE NAME]


Move partition from one tablespace to another tablespace:

ALTER TABLE XXX MOVE PARTITION PARTITION_1 TABLESPACE YOUR_TABLESPACE;
ALTER TABLE  P_TRX_TEST MOVE PARTITION SYS_P26246 TABLESPACE TSP_DATATRX

If you are using subpartitions:

ALTER TABLE [TABLE NAME] MOVE SUBPARTITION [SUBPARTITION_NAME] TABLESPACE [NEW_TABLESPACE];

Default tablespace is a tablespace which is used when table was created. 
How to change default tablespace this way new partition will be created in requred tablespace?


Saturday, September 20, 2014

Encrypt mysql password

To encrypt mysql password use mysql_config_editor. Program will create file .mylogin.cnf which is recognize by mysql. 

Example of command:

[mantonik@web2 ~]$ mysql_config_editor set --login-path=mariusz.12 --user=mariusz --host=10.2.5.12  -p

Enter password:

Dispaly list of all login paths

[mantonik@web2 ~]$ mysql_config_editor print --all
[client]
[backup]
user = bkuser
password = *****
host = 10.2.5.12
[mariusz.11]
user = mariusz
password = *****
host = 10.2.5.11
[mariusz.12]
user = mariusz
password = *****

host = 10.2.5.12

More informaiton abount this function you can find in mysql documentation 


 

Thursday, July 31, 2014

centos7 - stop iptables

Remove iptables from startup:

systemctl disable iptables