Sometime is necessary to move all database objects from one tablespace to another.
Basically are tables, indexes and lobs.
This script permit move database objects from one user and/or tablespace to another:
set echo off
set heading off
var tbs_source varchar2;
var tbs_dest varchar2;
var schema_user varchar2;
-- '' if it isn't relevant.
exec :tbs_source := 'SOURCE_TBS';
-- '' if it isn't relevant.
exec :schema_user := 'SOURCE_USER';
exec :tbs_dest := 'DEST_TBS';
select 'Transporting tablespace ' || :tbs_source || ' or user ' || :schema_user || ' to tables
Security is gaining importance. As per the reports in the year 2008, loss of confidential information has increased to 63%. This loss of confidential information is a breach of Service Level Agreement (SLA). Organizations will have to compensate these loses in the form of financial penalties.
A recent information protection law from a state in U.S.A claims that information of residents of this state stored in databases across the globe will have to be encrypted. Upon failure, the database owners will have to pay $5000 penalty.
Original blog post in here.
Give the password template to this function and get random password. It acts like dbms_random.string() function but takes more than one character as a parameter.
Here is a scenario (template)
- First char must be UPPERCASE
- Second and third one must be NUMBER
- Make fourth a NON-ALPHANUMERIC character
- Fifth one must be LOWERCASE
- Sixth is a NUMBER again
- Seventh is any character
KILLING USERS SESSIONS IN ORACLE
Author JP Vijaykumar Oracle DBA
Date Apr 14th 2010
A detailed discussion on redo, undo and Oracle's read consistency are
beyond the scope of this document.
A detailed discussion on setup, troubleshooting of user connections
in MTS environment is beyond the scope of this document.
Before embarking on a killing spree of Oracle sessions, let us explore
the options, limitations, dos and don'ts.
This note is an attempt to summarize some of the 11gR2 facilities that allow for greater virtualization of resources, reducing reliance on networking facilities supplied by the operating system. All corrections welcome.
I have been dealing with dynamic SQL for some time. And that is something that is still a mystery for many newcomers (and experienced Oracle guys as well).
Here I am going to tell how you can use dynamic SQL. How, but more important is "why", because when creating a comment you should never describe "how" - you always should describe "why".
One common problem: You are allocated to a new project and find that nothing is documented.
By running this script, you get four important points of information: Tablespace status, installed products and its versions, oracle parameters different to default and status of tables:
SET LINESIZE 1000
SET PAGESIZE 1000
prompt You can leave this script copied at $ORACLE_HOME\rdbms\admin\
prompt - Remember to use also statspack o bstats depending on the Oracle version
prompt - Review alert.log
prompt TABLESPACE STATUS
Select t.tablespace_name "Table
SQL> rem Ejemplo con TRUNCATE:
SQL> rem Para empezar hay que crear una tabla:
SQL> create table truncate_example as select * from dba_tables
SQL> rem La tabla tiene muchas líneas:
SQL> select count(*) from truncate_example
SQL> rem Y 10 pedazos en el disco:
This is my first entry here.
I would like to criticize criticism for case sensitive object naming in Oracle and PL/SQL.
As you might know - Oracle is converting object names to uppercase by default. Whenever you create object like
CREATE TABLE my_table(my_column NUMBER);
it would be created as
At the same time some people might create table
CREATE TABLE myTable(myColumn NUMBER);
which would become
What is tricky about the above is the way Oracle is parsing queries.
Installing Oracle 11g 64 bit on Windows Server 2008
When installing Oracle 126.96.36.199 on Windows Server 2008 64 bit, the installer gave a an error saying the Operating System ( OS ) was not supporting. In looking at the install log on c:\program files\oracle, I noticed the installer thinks the OS is windows vista.
To work around this error, I selected the check box for user verified and continued with the install.