declare i number := 0; begin for c1 in ( select address, hash_value, sql_id from v$sql where last_active_time < sysdate - 1/24 and executions < 2 ) loop begin dbms_shared_pool.purge(c1.
address || ' ' || to_char(c1.hash_value), 'C'); i := i + 1; exception when others then dbms_output.put_line(c1.sql_ id); end; end loop; dbms_output.put_line('Cursors purged = ' || to_char(i)); end; /
It’s not needed to say how important this is to have all the password reset, even though on new Exa hardware the SSH is usually disabled. How ever, oftenly we need to have access in some of the consoles knowing the password was never changed but unaware on what it should be.
For those cases (until you change it, ASAP), here is a quick reference:
Exadata Storage Servers:
Power distribution units (PDUs):
Database server ILOMs:
Exadata Storage Server ILOMs:
Keyboard, video, mouse (KVM):
I hope this was useful for you.
This seems to be a simple request, right? Let’s go to the regular cases / simple answers:
- Use DBMS_METDATA.ET_DDL to get the view code: DBMS_METADATA.get_ddl (‘VIEW’, <VIEW_NAME>, <OWNER>)
- Use datapump with include=VIEW Or even specifying the list INCLUDE=VIEW:\”IN (\’VW_EXAMPLE1\’, \’VW_EXAMPLE2\’)\” Or part of the name: INCLUDE=VIEW:”LIKE ‘VW_%EXEMPLE%'”
However, when supporting a client with this need I see this may have some caveats. The options above load the view purely, not the “content” of the view from an application perspective.
And here let’s leave it clear, a view doesn’t have any data but instead stores a query to retrieve the data from regular tables. We could have underlying tables as support mechanisms in case of materialized views, but in essence, they are transitory build from the actual database tables. Now how to load views to other databases without the source tables including the underlying data?
This question has 2 answers:
- Exporting the views as tables.
In general lines I’d do it manually: Create tables from views and then export them. This can mean I would need to have space for creating those tables, which can be a lot, though.
But then I see we have VIEWS_AS_TABLES clause in Datapump which makes exactly that. As a reference: https://docs.oracle.com/database/121/SUTIL/GUID-E4E45E81-5391-43BE-B27D-B763EF79A885.htm#SUTIL3904
However, in this case, the import will bring the views as tables, not as views. Which will theoretically resolve the issue from a data perspective, but may not be what is required: What if I need the views as views on the destination database?
Well, in this case it’s needed to load all the underlying tables. How to extract them? The hard way is opening all the view codes and listing them. The smart way is the second option:
- Listing the tables referenced by Views from dba_dependencies and exporting them.
select owner as schema_name,
name as view_name,
referenced_owner as referenced_schema_name,
where type = 'VIEW'
-- excluding some Oracle maintained schemas
and owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
order by owner, name, referenced_name, referenced_owner, referenced_type;
Aaaand, that’s what I actually needed. With the list in place, it’s a matter of exporting with datapump including the tables on the list and the views (as mentioned above).
I hope it helps!
Well, when you are sure all the TNS, listener (SID_LIST_LISTENER on this case), services, db_domain are fine but still facing this issue when trying to access remotely an open database… what to do?
[oracle@greporasrv admin]$ sqlplus sys/******@MYDB as sysdba SQL*Plus: Release 184.108.40.206.0 Production on Wed Feb 3 17:45:29 2021 Copyright (c) 1982, 2013, Oracle. All rights reserved. ERROR: ORA-12537: TNS:connection closed
My friend here is a quick tip that, if you are an Oracle DBA for time enough you know that solves several issues, maybe affecting this case again:
[oracle@greporasrv admin]$ cd $ORACLE_HOME/bin [oracle@greporasrv bin]$ ls -lrt oracle -rwxr-x--x. 1 oracle oinstall 242993632 Dec 18 19:20 oracle [oracle@greporasrv bin]$ chmod 6751 oracle [oracle@greporasrv bin]$ ls -lrt oracle -rwsr-s--x. 1 oracle oinstall 242993632 Dec 18 19:20 oracle
The CHMOD 6751 did it again!
It happened to me because, somehow, these permissions were wrong in my LOCAL host (the one I was trying to use the SQLPlus* from.
I hope it helps!
So, have you ever been in the need to drop a user from the SQL Server database, but you stuck with the following related error?
Error: 15138 The database principal owns a schema in the database, and cannot be dropped.
If you don’t have access to the SSMS to see which schema or objects the user owns, the following SQL should do the job.
USE [DATABASENAME] GO SELECT so.name OBJECT, su.name OWNER FROM sys.schemas so INNER JOIN sysusers su ON (so.principal_id = su.uid) WHERE su.name = 'username' GO SELECT so.name OBJECT, su.name OWNER, so.xtype TP FROM sys.sysobjects so INNER JOIN sysusers su ON (so.uid = su.uid) WHERE su.name = 'username' GO
Once you have the owned objects/schemas, you can change them with the following SQL (schema example):
USE [DATABASENAME] GO ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [newowner] /* new owner username */ ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [newowner] GO
Then you’re ready to drop the user DROP USER [username].
Bonus SQL: The following will list the schemas and their owner.
SELECT schema_name(schema_id) as SCHEMANAME, user_name(s.principal_id) as USERNAME FROM sys.schemas AS s
We are going to start one series of posts where we are going to show the AWS CloudFormation usage.
The nexts post going to abord a creation one RDS, DMS using AWS cloudFormation stack. In this series we’re going to showcase AWS CloudFormation by creating one RDS and one DMS.
First one fast introduction about AWS cloudFormation.
Let start to explain AWS CloudFormation
” AWS CloudFormation provides a common language for you to model and provision AWS and third party application resources in your cloud environment.”
In practice it’s a json/yaml file, where we can describe instructions to create AWS services.
First step that we need is connect to AWS Console. In the search field type CloudFormation like the picture below.
Click on CloudFormation to open the service console.
Click on Stacks.
After this, click on create a new stack and select “with new resources”
We are going to click on Create template in designer and you will be redirected to page like below.
Click on Template and the code editor is going to open.
The next step will be to create one script to deploy a service. In this example, we going to use a DMS script and a RDS Postgresql. The examples used in this article are available in the next article.
To execute the script the first step is to validate by clicking the highlighted button in the below image.
The return can be OK or error. If the return is OK you can create the stack. To do this clicking the highlighted button in the below image.
You can check execution events by clicking in the Events page. The return is similar to the image below.
In the next articles, we are going see the source code and to use the AWS DMS Service to replicate data from Oracle Database to RDS PostgreSQL.
From time to time, I get a customer request to configure/optimize a MySQL server, and usually, when the customer has already installed the MySQL Server, usually, it’s an “old” version. It’s ok if your software uses features that only work on some specific version of a Database, but keep an eye open for its end of life support. And that EOF is coming to MySQL 5.6. Below you can see a table with the MySQL versions and their ending date.
So, next time you need to configure a new server, keep an eye on the version you’re using.
On the other day when I got to the office I was called to check a database that was running slow. They had implemented a new process there and wanted to make sure it was not impacted.
When checked I saw this issue using OEM
User SYS causing a strange wait event Failed Logon Delay
Someone had created a process running with the user SYS but they did not fully configured and a part of the process was trying to connect with the wrong password.
While they were looking in the configuration files and servers to see from where the issue was coming from, I started my own investigation to speed up the process.
1st I had to enable audit as it was disabled for unsuccessful loging attemps
SQL> audit session whenever not successful; Audit succeeded.
Than I was able to see from where the failed connection came from, I just needed to look for the return code 1017 as ORA-1017 is invalid username/password; logon denied on sys.aud$
col ntimestamp# for a30 heading "Timestamp" col userid for a6 heading "Username" col userhost for a15 heading "Machine" col spare1 for a10 heading "OS User" col comment$text for a80 heading "Details" select ntimestamp#, userid, userhost, spare1, comment$text,returncode from sys.aud$ where returncode=1017 or returncode=28000;
Oldie but goldie =)
Hope it helps,
Failed to start Keycloak – Version 11.0.2 on Centos 7
…bad argument for IP_MULTICAST_IF: address not bound to any interface
In an attempt to solve this case, searches always return to add the parameter “-Djava.net.preferIPv4Stack = true”. But for me, it didn’t solve.
The only way that solved my problem was to edit the /etc/sysctl.conf file. disabling IPV6.
Add the following at the bottom of the file:
net.ipv6.conf.all.disable_ipv6 = 1 net.ipv6.conf.default.disable_ipv6 = 1 net.ipv6.conf.lo.disable_ipv6 = 1
Save and close the file.
Reboot the machine.
have you ever received a call for a MySQL on windows that stopped working after someone did something at their my.cnf? Then you try to start the service by cmd and get the following error.
mysqld: [ERROR] Found option without preceding group in config file C:\ProgramData\MySQL\MySQL Server 8.0\my.ini at line 1.
mysqld: [ERROR] Fatal error in defaults handling. Program aborted!
Well, for some reason, the editor that was used (no idea which one was), threw some random byte at the beginning of the file. To solve that (on windows at least), open the file on Notepad++, go to Format > Convert to ANSI. Save the file and start again the service.
What was the weirdest thing that happened to you on a Windows Server?