select o.object_type,o.owner,o.object_name, CASE WHEN D.REFERENCED_NAME = O.OBJECT_NAME THEN NULL
ELSE D.REFERENCED_NAME || ' --> ' || d.referenced_type
END "dependency to"
from dba_objects o join dba_dependencies d
on o.object_name = d.name
where object_name in
(
'MYVIEW1',
'MYVIEW2',
)
and object_type <> 'SYNONYM'
order by object_type desc, "dependency to";
Wednesday, December 7, 2016
How to find dependencies between views and the tables they're based upon
Use the following Query to list the base tables on which the
Monday, December 5, 2016
Is it possible to temporarily change the system time on my server, without the Oracle instance complaining?
Yes, it's possible. Oracle doesn't care about the wall-clock time, and will keep the redo log stream intact, regardless of what time the hosting server is set to.
The whole procedure is quite simple:
1. shutdown the database and listener
3. Disable NTP på serveren:
4. Open the instance and the listener
5. Verify that the database has picked up the new system time
6. Have a look at the archived logs:
Change back again:
7. shutdown the database and the listener
The whole procedure is quite simple:
1. shutdown the database and listener
sqlplus / as sysdba shutdown immediate exit lsnrctl stop2. as root, change the system time. In my example, I am setting it to 10 days into the future:
Thu Dec 15 12:27:00 CET 2016 [root@myserver ~]# date Thu Dec 15 12:27:01 CET 2016
3. Disable NTP på serveren:
service ntpd stop
4. Open the instance and the listener
sqlplus / as sysdba startup exit lsnrctl start
5. Verify that the database has picked up the new system time
SQL> select startup_time from v$instance; STARTUP_TIME ------------------- 15.12.2016 12:28:17
6. Have a look at the archived logs:
select name,sequence#, archived,applied, status, completion_time from v$archived_log; NAME SEQUENCE# ARCHIVED APPLIED STA COMPLETION_TIME ------------------------------------------------------------------------- ---------- --------- ---------- --- ------------------- /u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12538__1480935048084626_.arc 12538 YES NO A 05.12.2016 11:50:48 /u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12539__1480935059227231_.arc 12539 YES NO A 05.12.2016 11:50:59Force a log switch and note the time stamp of the last log:
alter system archive log current;
NAME SEQUENCE# ARCHIVED APPLIED STA COMPLETION_TIME
------------------------------------------------------------------------- ---------- --------- ---------- --- -------------------
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12538__1480935048084626_.arc 12538 YES NO A 05.12.2016 11:50:48
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12539__1480935059227231_.arc 12539 YES NO A 05.12.2016 11:50:59
/u04/fra/mydb/archivelog/2016_12_15/o1_mf_1_12540__1481801585872720_.arc 12540 YES NO A 15.12.2016 12:33:06 Change back again:
7. shutdown the database and the listener
sqlplus / as sysdba shutdown immediate exit lsnrctl stop8. enable ntp to have the server syncronized with the current time:
service ntpd start9. Open the database
sqlplus / as sysdba startup exit lsnrctl start10. Check the archived logs again. Note that the log sequence doesn't care what the registered time was at the time of the log switch:
select name,sequence#, archived,applied, status, completion_time from v$archived_log;
NAME SEQUENCE# ARCHIVED APPLIED STA COMPLETION
------------------------------------------------------------------------- ---------- --------- ---------- --- ----------
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12538__1480935048084626_.arc 12538 YES NO A 05.12.2016
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12539__1480935059227231_.arc 12539 YES NO A 05.12.2016
/u04/fra/mydb/archivelog/2016_12_15/o1_mf_1_12540__1481801585872720_.arc 12540 YES NO A 15.12.2016
alter system archive log current;
select name,sequence#, archived,applied, status, completion_time from v$archived_log;
NAME SEQUENCE# ARCHIVED APPLIED STA COMPLETION
------------------------------------------------------------------------- ---------- --------- ---------- --- ----------
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12538__1480935048084626_.arc 12538 YES NO A 05.12.2016
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12539__1480935059227231_.arc 12539 YES NO A 05.12.2016
/u04/fra/mydb/archivelog/2016_12_15/o1_mf_1_12540__1481801585872720_.arc 12540 YES NO A 15.12.2016
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12541__1480937880624137_.arc 12541 YES NO A 05.12.2016 Friday, November 25, 2016
How to find the page size for a Linux installation
In order to set one of the mandatory kernel parameters in a Red Hat flavored Linux distribution, you need to know the page size.
To find it:
To find it:
[root@myserver]$ getconf PAGESIZE 4096
Monday, November 21, 2016
How to install and use the character set scanner
Although deprecated since the release of DMU (Database Migration utility for Unicode), there are still cases where you need to run csscan.
One such incident happened to me when migrating a database used for Oracles E-Business Suite.
Some errors were found in DMU, but when clicking on the number indicating the number of rows with errors, the Cleansing Editor showed up empty. To get the rowids of the offending rows, I had to resort to the character scanner.
If not installed do as follows:
1. edit the script $ORACLE_HOME/rdbms/admin/csminst.sql so that it has a valid password, and that its object will be saved in a separate tablespace
That's it. The csscanner tool is now installed.
A simple example of usage would be when I scanned one - 1 - table.
First, I created a parameter file
Execute it as follows:
This process creates three files for you:
1. scan.out - Screen log file
2. scan.err - individual exception report. This is where you would typically find your rows that contains errors
3. scan.txt - contains the Database Scan Summary Report.
Here's how my scan.err looked like after I scanned the table mentioned above:
For more information, look up the official documentation here
One such incident happened to me when migrating a database used for Oracles E-Business Suite.
Some errors were found in DMU, but when clicking on the number indicating the number of rows with errors, the Cleansing Editor showed up empty. To get the rowids of the offending rows, I had to resort to the character scanner.
If not installed do as follows:
1. edit the script $ORACLE_HOME/rdbms/admin/csminst.sql so that it has a valid password, and that its object will be saved in a separate tablespace
create user csmig identified by ***** account lock / alter user csmig default tablespace tools quota unlimited on tools /2. execute the script as sysdba
sqlplus / as sysdba @csminst.sql
That's it. The csscanner tool is now installed.
A simple example of usage would be when I scanned one - 1 - table.
First, I created a parameter file
userid='sys/**** as sysdba' table=scott.emp fromchar=WE8ISO8859P1 tochar=al32utf8 array=4096000 process=4 feedback=1000
Execute it as follows:
csscan parfile=myscan.par
This process creates three files for you:
1. scan.out - Screen log file
2. scan.err - individual exception report. This is where you would typically find your rows that contains errors
3. scan.txt - contains the Database Scan Summary Report.
Here's how my scan.err looked like after I scanned the table mentioned above:
Database Scan Individual Exception Report [Database Scan Parameters] Parameter Value ------------------------------ ------------------------------------------------ CSSCAN Version v2.1 Instance Name proddb01 Database Version 11.2.0.4.0 Scan type Selective tables Scan CHAR data? YES Database character set WE8ISO8859P1 FROMCHAR WE8ISO8859P1 TOCHAR al32utf8 Scan NCHAR data? NO Array fetch buffer size 4096000 Number of processes 4 Capture convertible data? NO ------------------------------ ------------------------------------------------ [Data Dictionary individual exceptions] [Application data individual exceptions] User : SCOTT Table : EMP Column: ATTRIBUTE15 Type : VARCHAR2(150) Number of Exceptions : 2 Max Post Conversion Data Size: 153 ROWID Exception Type Size Cell Data(first 30 bytes) ------------------ ------------------ ----- ------------------------------ AAS/WmAHwAACI5ZAAC exceed column size 153As expected, the offending rows contained Scandinavian characters, which will use 2 bytes after conversion to a Unicode database. Therefore the text will no longer fit, and actions need to be taken by the data owner before the migration.AAS/WmAHwAACI5ZAAD exceed column size 153 ------------------ ------------------ ----- ------------------------------
For more information, look up the official documentation here
Thursday, November 17, 2016
How to create a blackout in Cloud Control using the command-line interface
Source your environment to point to the correct ORACLE_HOME. Put the following in a file called .agentenv
If you need to create the blackout as a part of a script which needs root privileges, execute the following:
For usage by the oracle software installation owner, skip the first part:
To stop the blackout use
export ORACLE_BASE=/software/oracle export ORACLE_HOME=/software/oracle/product/agent13c/agent_13.2 export PATH=$ORACLE_HOME/bin:$PATH echo "\n\nOH is : $ORACLE_HOME"
If you need to create the blackout as a part of a script which needs root privileges, execute the following:
su - oracle -c ". .agent1?c;emctl start blackout-nodeLevel -d 15;emctl stop agent"
For usage by the oracle software installation owner, skip the first part:
. .agent1?c;emctl start blackout-nodeLevel -d 15;emctl stop agent
To stop the blackout use
. /home/oracle/.agent1?c;emctl stop blackout
Monday, November 14, 2016
A short history of the Automated Maintenance Tasks in Oracle
Predefined Automatic System Tasks were introduced in Oracle 10g, and consisted of two jobs:
1. Automated statistics collection
2. The Automated segment space advisor
Two scheduler jobs were set up by default to run these automated tasks: The GATHER_STATS_JOB and the AUTO_SPACE_ADVISOR_JOB.
You could enable or disable the jobs using the procedures enable or disable in the dbms_scheduler package:
In Oracle 11g, Oracle redesigned the the Automatic System Tasks and introduced the concept of Automated Maintenance Tasks.
At the same time, a third automated task was introduced: The Automated SQL Tuning advisor.
Oracle removed the GATHER_STATS_JOB and AUTO_SPACE_ADVISOR_JOB altogether and instead introduced a new package called DBMS_AUTO_TASK_ADMIN.
To enable or disable an Automatic tasks using DBMS_AUTO_TASK_ADMIN, see this short post.
To check the status of your automated maintenance tasks, see this post.
1. Automated statistics collection
2. The Automated segment space advisor
Two scheduler jobs were set up by default to run these automated tasks: The GATHER_STATS_JOB and the AUTO_SPACE_ADVISOR_JOB.
You could enable or disable the jobs using the procedures enable or disable in the dbms_scheduler package:
EXECUTE DBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB');
EXECUTE DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
In Oracle 11g, Oracle redesigned the the Automatic System Tasks and introduced the concept of Automated Maintenance Tasks.
At the same time, a third automated task was introduced: The Automated SQL Tuning advisor.
Oracle removed the GATHER_STATS_JOB and AUTO_SPACE_ADVISOR_JOB altogether and instead introduced a new package called DBMS_AUTO_TASK_ADMIN.
To enable or disable an Automatic tasks using DBMS_AUTO_TASK_ADMIN, see this short post.
To check the status of your automated maintenance tasks, see this post.
How to check the status of the Automated Maintenance Tasks in Oracle 11g
To see the currently enabled jobs in your database, use the dictionary view DBA_AUTOTASK_OPERATION
which displays all automated maintenance task operations for each client:
which displays all automated maintenance task operations for each client:
select client_name,operation_name,status from DBA_AUTOTASK_OPERATION;Example from one of my 11g databases:
| CLIENT_NAME | OPERATION_NAME | STATUS |
|---|---|---|
| auto optimizer stats collection | auto optimizer stats job | DISABLED |
| auto space advisor | auto space advisor job | ENABLED |
| sql tuning advisor | automatic sql tuning task | DISABLED |
Subscribe to:
Posts (Atom)