Blogs

Login with SQLPlus as a DBA without entering password

 

If you have the system user who installed the database you can enter SQL Plus as DBA user, without entering a password as follows:

1. Enters the system with this user. 
2. From the command line, go into SQLPlus typing:

> sqlplus "/as sysdba"

If you need to enter using this way because you forgot the password of a user, you can easily change it: 

SQL> alter user user_name identified by new_password;

It can be more than one Database installed on the server, so you have to validate that the environment variables of the Oracle's user are pointing to your database.

For verifying that you has login into the correct database you can execute this statement: 

SQL> select name from v$database; 

UPDATE with JOIN in ORACLE

 

Suppose we want to update in our ORACLE database the costs fields of the fact table FAC_TABLE with the unit cost of our table COSTS.

We can do this in two ways:

1. (Slow, but valid for a few data or to sporadic uses)

update FAC_TABLE ft  set UNIT_COST = (select distinct UNIT_COST from COSTS ct  where (ft.id_article = ct.id_article);

2. (The best way is this, and the performance is ideal if you have constraints)

UPDATE ( SELECT ft.UNIT_COST AS old_cost,  ct.UNIT_COST AS new_cost FROM FAC_TABLE ft  INNER JOIN COSTS ct ON ft.id_article ct = ct.id_article) )  SET old_cost = new_cost;

To the proper functionality of this second option you need a UNIQUE or PRIMARY KEY constraint on ct.id_articulo. 
If you don't have this constraint, you can use the hint / * + BYPASS_UJVC * / after the word UPDATE (Bypass update join view constraint).

The performance increase if we have the constraint but even without it, the second option should run quite faster than the first option.

 

Remote access using Oracle DBLINK

 

The easiest way to access from an Oracle database objects from another Oracle database is using a DBLINK (being the easiest does not mean that it is always the most desirable, the abuse of DBLINKS can create many problems, both of performance and safety)

To do this it's necessary a user with CREATE DATABASE LINK privilege, and create a DBLINK in the source database (A) by a simple statement such as:


Create database link LNK_from_A_to_B connect to USER identified by PASSWORD USING 'B'; 

'LNK_from_A_to_B' is the name of the link, 'USER' and 'PASSWORD' are the IDs of the user who will use the link to connect, which will inherit the permissions of all access through the link, and B is the name of the database's instance.

Using the DBLINK we can connect to the objects with the remote database's permissions that user has been provided in the creation statement.

To reference an object from the remote database should indicate the name of the object, concatenated with the character '@' and the name that we had given to the DBLINK.

Example: 

select * from TABLA@LNK_from_A_to_B 

How to connect from Oracle SQL Developer with a remote SQL Server

 

The digital story of the Nativity

 

Great video showing through the use of social media an original story of Jesus' birth at Christmas.

Besides being fun, it's a good way to make a quick tour of the main features of Facebook, Twitter, YouTube, Google, Wikipedia, Google Maps, GMail, Foursquare or Amazon ...

How times change:)

BI USABILITY: evolution and tendencies

 

Introduction

What is known today by the name Business Intelligence (BI) has an origin and evolution that should be looked at in order to introduce the concept that will be the subject of this article: “BI Usability”.

One of the principal goals of BI is that users find the information they need to make decisions in due time and proper form. The form includes, among other things, the format in which the information is presented and the level of interaction expected to obtain the desired result. The previous points make up the term “BI Usability”.

 

Usability can be defined as software’s ease of use, in which factors such as the familiarity of the design, comfort, attractiveness, level of interaction permitted, response time, etc. also come into play.

Various definitions of usability have been selected to complement the concept: (*1):

A contest to send a DBA to space

 

Redgate has organized a contest for DBA's with a spectacular prize: A space trip!

The winner will be the first DBA to go into space. A very original initiative. If you want to apply, visit the page www.dbainspace.com

If you take part, count with my vote 

 

This is the promotional video of DBA in space: 

 

Good luck!

BI and General Management

 

This article is the first of a total of three that we are going to publish from LITEBI, each one will be part of a Whitepaper focused on the effects and contributions of Business Intelligence to the company from the point of view of the CEO. 

 

What is Business Intelligence?

Wikipedia gives us the following definition on this concept:
“Computer-based techniques used in identifying, extracting, and analyzing business data, such as sales revenue by products and/or departments, or by associated costs and incomes”

In other words, software tools that enable that all the information of a company (sales, purchases, financial information, production, marketing, etc.) “flows” in an organized way through the business, reaching the right person, at the right time. To this, in LITEBI we would also note that achieving Intelligence needs full-featured and integrated tools to manage and optimize the performance of the company as a whole and to align it with the business strategy.

A tool with all these characteristics makes things easier for those in charge of the decision making in each department: a CFO, a Sales Manager, a CEO or a Controller.

Interesting resources and links about IBM Cognos

 

Documentación de IBM Cognos BIIn a Linkedin debate members have proposed a list of references and useful links about Cognos BI, as well as forums, blogs and resources. I've organized a bit the proposals. I hope you find it useful..

How to connect to a remote MySQL database

 

 

MySQL has some special features when making a connection from a remote client that if we do not know can complicate access to a MySQL database from a different machine that hosts the database.

With other databases such as Oracle or SQL Server, once that no firewall or anything like that prevents us from the client machine access to the server, using normally data acces from a database user we can 'enter'.

With MySQL, although access to the port, usually 3306, is open, the database can be configured to shut out external connections, and the result is the same as if the port was closed by a firewall:

 

telnet mysql.dataprix.es 3306
Trying 188.166.233.199...
telnet: connect to address 188.166.233.199: Connection refused
telnet: Unable to connect to remote host

 

If you get this result should consult the file /etc/my.cnf, and checking for bind-address variable or skip-networking.

If skip-networking is and is not discussed, edit the file and delete it, or make a comment to have no effect and allow external connections:

 

Syndicate content