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 18.104.22.168.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!