Archive

Archive for the ‘SQL’ Category

Changing sys password in RAC databases

May 24, 2011 5 comments

If you ever have worked with a RAC database, and you have changed a normal user password, you will notice that it has no difference between a single instance database. You just have to perform “ALTER USER xxx IDENTIFIED BY yyyy” and your password will be changed.

However, today I had a problem with a RAC database. I had to change the SYS password, and I did it the same way I would do in a single-instance database. But when I tried to connect with sys user, the error “ORA-01017: invalid username/password; logon denied” appeared. What I was doing wrong?

After a few thoughts, I found the solution. SYS password is instance specific in RAC databases, so you have to change it in every single instance. That’s all.

What is the data dictionary?

The scope of this article is to clarify what exactly is the data dictionary. Probably all of you have heard of it, and you know it’s a bunch of metadata that Oracle uses to work, but if you want to know more about this Data Dictionary, then I hope this article teaches you something new.

The data dictionary is a repository of metadata (Information about information), about all the information inside the database. This repository is owned by SYS, and is stored principally in the SYSTEM tablespace, though some components are stored in the SYSAUX tablespace (in Oracle 10+).

The data dictionary is composed of tables and views. Some of these tables are inside of the Oracle kernel, so you would never work directly with them unless you are working for Oracle support or performing a disaster recovery scenario. But instead you can access to the views in order to know the “information about the information”.

For example, a possible usage of this data dictionary would be to know all the tables owned by a single user, or the list or relationships between all the tables of the database.

The main view of the data dictionary is the view DICT (or DICTIONARY):

SQL> DESC DICT
Nombre                                    ?Nulo?   Tipo
—————————————– ——– —————————-
TABLE_NAME                                         VARCHAR2(30)
COMMENTS                                           VARCHAR2(4000)

Through the DICT view, you can access to all the data dictionary views that could provide you the information that you need.

For example, if you are looking for information related to db_links, but you don’t know where to look for, then query the DICT view:

SQL>  SELECT TABLE_NAME FROM DICT WHERE TABLE_NAME LIKE ‘%LINK%’;

TABLE_NAME
——————————
USER_DB_LINKS
ALL_DB_LINKS
DBA_DB_LINKS
V$DBLINK
GV$DBLINK

Now, you have just to query one of these views to find the data you are looking for. GV$ views are very useful when you are working with RAC, and V$ views are instance related.

Remember that the data dictionary provides critical information of the database, and it should be restricted to users. However, if a user really needs to query the data dictionary, you can use the following sentence:

GRANT SELECT_CATALOG_ROLE TO user_name;

A script to check the frequency of Oracle log switches

May 11, 2010 6 comments

I was looking for a script that showed me the frequency of my redo log switching through a SQL query. Although I can use Grid Control to monitor my redo logs, and use his several advisors, I prefer to use SQLPlus when it’s possible to keep the control of what I’m doing.

What I found is a fancy script written by Donald K. Burleson, that can be found in the following link

http://www.remote-dba.cc/oracle_tips_log_switches.htm

The script, in resume will show the frequency of your redo logs by hour, but unfortunately the view v$log_history has changed since the time that this script was done (I’m using Oracle 10g), so the select fails with the following error:

group by substr(time,1,5)
*
ERROR at line 27:
ORA-00904: “TIME”: invalid identifier

So I tried to fix the script to adapt it to Oracle 10g, but being unable to make the conversion, I decided to make a new script.

This is the resultant script:

COL DAY FORMAT a15;
COL HOUR FORMAT a4;
COL TOTAL FORMAT 999;
SELECT TO_CHAR(FIRST_TIME,’YYYY-MM-DD’) DAY,
TO_CHAR(FIRST_TIME,’HH24′) HOUR,
COUNT(*) TOTAL
FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,’YYYY-MM-DD’),TO_CHAR(FIRST_TIME,’HH24′)
ORDER BY TO_CHAR(FIRST_TIME,’YYYY-MM-DD’),TO_CHAR(FIRST_TIME,’HH24′)
ASC;

And there is an output example:

DAY             HOUR TOTAL
————— —- —–
2010-04-30      01      24
2010-04-30      02       1
2010-04-30      13       1
2010-04-30      22       1
2010-05-01      00       1
2010-05-01      01      23
2010-05-01      03       1
2010-05-01      22       1
2010-05-02      00       1
2010-05-02      01      23
2010-05-02      22       1
2010-05-03      00       1
2010-05-03      01      23
2010-05-03      11       1
2010-05-03      22       1
2010-05-04      00       1
2010-05-04      01      24
2010-05-04      02       1
2010-05-04      12       1
2010-05-04      14       1
2010-05-05      00       1
2010-05-05      01      24
2010-05-05      02       1
2010-05-05      12       1
2010-05-05      22       1
2010-05-06      00       1
2010-05-06      01      23
2010-05-06      02       1
2010-05-06      12       1
2010-05-06      16       1
2010-05-07      00       1
2010-05-07      01      24
2010-05-07      02       1
2010-05-07      12       1
2010-05-07      22       1
2010-05-08      01      24
2010-05-08      03       1
2010-05-09      00       1
2010-05-09      01      24
2010-05-10      00       1
2010-05-10      01      24
2010-05-10      12       1
2010-05-10      22       1
2010-05-11      01      24
2010-05-11      02       1

Categories: oracle, Scripts, SQL Tags: , ,

Indiana Jones and the Lost Table

April 29, 2010 2 comments

Let’s put it this way: you have some data that you want to find in the database, but you have no idea of what table it is. You don’t even know the name of the column, so you have to search a single number IN EVERY COLUMN OF EVERY TABLE. At least you know the schema where it is…

So, we have to find where is the data 1662 only knowing the name of the schema. The name of the schema is Absys. Don’t worry, we can still do it, but it won’t be an easy task.

We can do one of these two things:

1. Search manually on every table, looking in every column for a coincidence of the numer 1161.

2. Create a script that does it.

I chose the nº2, so there is my script in case it may help you:

sqlplus “/as sysdba”
set lines 200;
set pages 50000;
spool search.sql
SELECT ‘SELECT * FROM ABSYS.’|| TABLE_NAME||’ WHERE ‘||COLUMN_NAME||’=1161;’ FROM ALL_TAB_COLUMNS WHERE OWNER=’ABSYS’;
spool off;
spool search_findings.txt
@search.sql
spool off;

Ét voilà! You will have the coincidences of this data in every column of every table of the schema named ABSYS. Although the execution of your script may take a while 🙂

Categories: oracle, Scripts, SQL Tags: , ,