DBA Blogs

Active Schema objects

Tom Kyte - Sat, 2018-02-10 04:46
How to find a list of active views in an Oracle schema? The dba_objects, all_views and other sys tables included the dropped views as well, but we would like to get active list.
Categories: DBA Blogs

The Transition (or: How I Learned to Stop Worrying and Love PostgreSQL)

Don Seiler - Fri, 2018-02-09 14:31
Note: Yes I re-(ab)used the title.



As some of you may know (if you follow me on twitter), after 16 years as an Oracle DBA, I made a career shift last summer. I hung up the Oracle spurs (and that sweet Oracle Ace vest) and threw on a pair of PostgreSQL chaps. I had always been a fan and very casual user of PostgreSQL for many years, even gently lobbying the folks at Pythian to add PostgreSQL DBA services to their offering (to no avail).

I'm taking this fresh start to also jump-start my blogging, hoping to write about interesting notes as I dive deeper in the PostgreSQL world (and even some Hadoop as I get into that). I'm just over 6 months into my new adventure and loving it. I'm excited to be dealing a lot more with an open source community, and interacting daily with some contributors via Slack.

Over the past 6 months, I've compiled a list of topics to write about. If I can remember the details or find my notes, I'll hopefully be writing regularly on these topics!

So, I hope to see more of you regularly here. Stay tuned!
Categories: DBA Blogs

instance,Database&DB Components.

Tom Kyte - Fri, 2018-02-09 10:26
Hi, I know the difference between Instance and Database. but i have few doubts about version of Instance and Data Base. 1). Can the versions be different for Instance and Database or they must have same version. 2). i did not understand what ar...
Categories: DBA Blogs

EXTPROC querying Oracle User

Tom Kyte - Fri, 2018-02-09 10:26
I've been using EXTPROC functions for a while but recently came across a use case that requires one to determine if the EXTPROC call should have access to external resources based on who the calling Oracle user is. The first thing that comes to mi...
Categories: DBA Blogs

expdp command subquery

Tom Kyte - Fri, 2018-02-09 10:26
Good Morning..!! Q: I've come up with a requirement where I will need to do table export where status ='YES'. My requirement is to get this WHERE clause from input table into variable and put it at expdp command in shell script Ex: SELECT...
Categories: DBA Blogs

Does a 10g oracle client work with 12.2c server?

Tom Kyte - Fri, 2018-02-09 10:26
Does a 10g oracle client work with 12.2c server?
Categories: DBA Blogs

Table Level Privileges for Read Only Access

Tom Kyte - Thu, 2018-02-08 16:06
Hi, I have new DB installed and configured by sys user. then i will execute following script to create new schema identified by CS4 <i>create user cs4 identified by cs4; grant connect to cs4; grant create session to cs4; grant dba to cs4;...
Categories: DBA Blogs

Issues with a query returning occasionally a ORA-00932: inconsistent datatypes: expected - got -

Tom Kyte - Thu, 2018-02-08 16:06
Dear Tom-successors, I have a problem which I cannot track down as it seems to happen randomly. First of all, I'm using: <code>Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production...
Categories: DBA Blogs

Best Practice to convert 400 million rows table with 3 TB of CLOB to partition table

Tom Kyte - Thu, 2018-02-08 16:06
Hi, I am converting a non-partitioned table to Partitioned(range or Ref). For test scenario, I have a table with 50 GB data (300 million rows) and 100 GB CLOB column data. I tested below options and timings are not as per my satisfaction level. 1. ...
Categories: DBA Blogs

external jobs with dbms_scheduler ORA-27369 exit code Bad address

Tom Kyte - Thu, 2018-02-08 16:06
Hello, I have this job (EXECUTABLE) that exec a bash script. Here's a resume of what it does connect to a FTP server, get a file, call a pkg via sqlplus using the files as external table in order to import data. The job fails with 'ORA-27369:...
Categories: DBA Blogs

Does a sys ref cursor have performance overheads

Tom Kyte - Thu, 2018-02-08 16:06
Hello, Currently I'm working on a procedure which returns data (big volume of data) to a calling procedure (java). The pl/sql procedure opens a sys ref cursor like pr_get_transactions ( i_from_date IN DATE ,o_transactions...
Categories: DBA Blogs

https request failed

Tom Kyte - Thu, 2018-02-08 16:06
Hi Team, We are using http request which is working fine now we are planning to move into https for that i have performed below steps On 12c- Create a wallet - $ orapki wallet create -wallet /u01/app/oracle/wallet -pwd password@123 -auto_login...
Categories: DBA Blogs

With Spool command generate output from two different query and if it match create dummy column "result" as match or successful

Tom Kyte - Thu, 2018-02-08 16:06
Hello Tom, Required your help in below scenario: Requirement : we have primary and DR server where we need to monitor synchronization between this two server. Method used: I have planned to use SPOOL command which connect to primary server ...
Categories: DBA Blogs

Garbage question mark appear from time to time when insert clob via jdbc

Tom Kyte - Wed, 2018-02-07 21:26
Hello, We are using JAVA JRE 5 and we add rows using jdbc thin client that connect to 12.1 server We call a store procedure. The problem was also ocurred when we used oci driver and in previous servers 10.xxx We found that when we insert a ...
Categories: DBA Blogs

Spool path with correct quarternr

Tom Kyte - Wed, 2018-02-07 21:26
I have problem to determine the quarter dynamically for the spool path in the form W:\SG-323\TeamZentraleKatalogredaktion\Regelupdate\Q4_2017\Sonderlocken\dst_polizei-GDP.csv SQL Developer Execution: @test set echo on column filename new_val f...
Categories: DBA Blogs

When does STALE_STAS changes from NO to YES

Tom Kyte - Wed, 2018-02-07 21:26
Hi, I'm trying to understand the trigger that changes the column value STALE_STATS in DBA_TAB_STATISTICS from NO to YES. I have duplicated a table, gathered stats, deleted 35% of the records, and the stats aren't STALE='YES'. I'd appreciate your ...
Categories: DBA Blogs

Using SYS_GUID() has primary key in large table

Tom Kyte - Wed, 2018-02-07 21:26
Hey, I have a large table 600 million rows, approximately 70gb, not partitioned (yet), a few indices, etc. I have reports that query this table often but they're very slow (20-60 minutes at times). I want to create this materialized view but ...
Categories: DBA Blogs

please help understanding this MERGE JOIN CARTESIAN

Tom Kyte - Wed, 2018-02-07 21:26
Hi Gurus, I have below query and exec plan. all tables E-rows are correct. as you can see table ACCT_STAT join to table ACCT and ACCT_DTL, but in plan, looks like MERGE JOIN CARTESIAN happened between table ACCT_STAT and ACCT_TYP table. Do I misunde...
Categories: DBA Blogs

Development DBA certificate

Tom Kyte - Wed, 2018-02-07 21:26
Hi Team, I am working as Development DBA and PLSQL developer. I wanted to complete Certificate in development DBA. didnt find any development certificate in Oracle website. Could you please advise which certificate is suitable for Oracle develo...
Categories: DBA Blogs

Help understanding SQL exec plan

Tom Kyte - Wed, 2018-02-07 21:26
Hi Gurus, I have a query it runs really long. after adding hint use_hash, it runs less than one minutes. data in table: det ---239968 bal -- -239968 inst -- 244713 PT_INST --168745 the query as below: below 3 plans are 1, using hash hint, t...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs