I tested this on Oracle 11.2. I created a profile called FOR_ANDREW with PASSWORD_REUSE_MAX set to 1. This meant that I could not reuse a password until I had used one other password first:
SQL> conn / as sysdba
SQL> create profile for_andrew
2 limit password_reuse_max 1
I created a user called ANDREW and gave him the FOR_ANDREW profile:
I was reading through an old book recently and it said that you could not put a tablespace into hot backup mode if the database was in NOARCHIVELOG mode. This seemed reasonable to me but I wondered what might happen if you tried to do this so I ran the following SQL in an Oracle 11.2 database:
1* alter tablespace users begin backup
I did this worked example on Oracle 11.2. First I created a table:SQL> create table t1
2 as select * from dba_segments
…then I made sure it contained enough data:
2 for a in 1..8 loop
3 insert into t1 select * from t1;
4 end loop;
This was tested on Oracle 11.2. If you need to look at the ORDER column in the above-mentioned table, there only seems to be one way to do it i.e. in upper case and surrounded by double quotes as shown below. I guess this is because ORDER is a reserved word:
SQL> desc mdsys.sdo_coord_axes
Name Null? Type
-------------------------- -------- ------------------
COORD_SYS_ID NOT NULL NUMBER(10)
This happened on Oracle 11.2. I had a problem with a 3rd party application hosted in the UK with users in another country. The front-end application was failing regularly with ORA-01426. I needed to know which SQL was causing these errors. First I logged into a test database as user ORACLE:
SQL> show user
USER is "ORACLE"
Then I started a trace of this session as follows:
Here are a couple of examples with the USER keyword, which I tested on Oracle 11.2. You can use it after the SHOW command or in a SELECT statement as follows. It returns the name of the user running the current session:
SQL> conn fred/bloggs
SQL> show user
USER is "FRED"
SQL> select user from dual
This was tested on Oracle 11.2. I had a session in the middle of a long-running operation:
SQL> conn user1/user1
SQL> exec dbms_lock.sleep(3600);
I tried to kill it like this but Oracle gave me an ORA-00031 instead:
SQL> select sid, serial# from v$session
2 where username = 'USER1'
SQL> alter system kill session '7,62'
I tried to do a FAST refresh of a materialized view in Oracle 11.2 but it failed with an ORA-12004:
ERROR at line 1:
ORA-12004: REFRESH FAST cannot be used for materialized view
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at line 2
I tested this on Oracle 11.2. If you update a table in SQL*Plus then update it again with a different value in a PL/SQL block, the second update replaces the first:
SQL> create table tab1 as
2 select 1 col1 from dual
SQL> update tab1 set col1 = 2
1 row updated.
I saw that the SYSTEM tablespace in an Oracle 11.2 database was getting quite big so I checked the size of SYS.AUD$:
C:\Users\AJ0294094>sqlplus / as sysdba
SQL*Plus: Release 126.96.36.199.0 Production on Fri May 16 13:20:54 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Oracle Database 11g Enterprise Edition Release 188.8.131.52.0 - 64bit Production
Tuning has always being good fun and something like a challenge for me.
From time to time we are being asked to find out why something did run slow while you are sleeping; answering this question is, in most cases, a challenge.
My batch did run slow last night, can you let us know why? Or why did this query run slow? Are questions we, as DBAs, have to answer from time to time.
Oracle has provided us with many tools to dig out information about past operations. We have EM, AWR, ASH, dba_hist_* tables, scripts (more...)
I always found Oracle Enterprise Manager (EM) to be an interesting tool for different reasons. The only thing I missed was an easy way to create my own alerts.
It is very simple to create a KSH, Perl, etc script to do some customised monitoring and notify you by email, Nagios, NetCool, etc.
By integrating your scripts with OEM, you will have an easy way to enhance your monitoring and still have notification by email, SNMP traps, etc. as you would currently have if your company is using OEM for monitoring your systems.
Develop an easy way to (more...)
Couple of days ago I had an interesting request, “How can I see the contents of nfs_dir”?
We were using DBFS to store our exports. This was the perfect solution as the business could “see” the files on the destination folder, but it did not meet our requirements performance wise on our Exadata.
We have decided to mount NFS and performance did improve, but we had a different problem. NFS is mounted on the database server and business do not have access for security reasons and segregation of duties.
Since then, the export jobs run, but business could (more...)
This is a quick post regarding the error on the subject. This is the second time it happens to me, so I thought I will write a bit about it.
I am refreshing one of my UAT environments (happens to be a Full Rack Exadata) using Oracle RMAN duplicate command. Then the following happens (on both occasions).
1.- Duplicate command fails (lack of space for restoring archivelogs, or any other error). This is can be fixed quite easy.
2.- following error while trying to open the database after restore and recover has finished:
SQL> alter database (more...)
I had a requirement of transferring files from our PROD ASM to our UAT ASM as DBFS is proving to be slow.
We are currently refreshing UAT schemas using Oracle Datapump to DBFS and then transferring those files to UAT using SCP.
DBFS does not provided us with the performance we need as datapump files are quite big. Same export onto ASM or NFS proves to be much, much faster.
We are currently testing exports to ASM, but, how to move dmp files from PROD ASM to UAT ASM?
The answer for us is using DBMS_FILE_TRANSFER. (more...)