Subscribe: Comments for Laurent Schneider
http://laurentschneider.com/comments/feed/
Added By: Feedage Forager Feedage Grade A rated
Language: English
Tags:
comment ctas  comment  create  datapump metadata  datapump  dbms datapump  dbms  job state  job  metadata  ora  remap  rows  sql 
Rate this Feed
Rate this feedRate this feedRate this feedRate this feedRate this feed
Rate this feed 1 starRate this feed 2 starRate this feed 3 starRate this feed 4 starRate this feed 5 star

Comments (0)

Feed Details and Statistics Feed Statistics
Preview: Comments for Laurent Schneider

Comments for Laurent Schneider



Oracle Certified Master



Last Build Date: Wed, 21 Feb 2018 07:37:54 +0000

 



Comment on set my ORACLE_HOME, PATH, ORACLE_SID by DBA

Wed, 21 Feb 2018 07:37:54 +0000

Hi, guys! In the following post, there is a PL/SQL script that generates the current database environment file. Also, it contains a good example of an environment file you'd better use for your database. http://dbpilot.net/2018/01/30/generating-an-enviroment-file-from-pl-sql/ ...



Comment on ORA-01466: unable to read data – table definition has changed by Rajesh

Thu, 15 Feb 2018 20:13:14 +0000

I had a similar kind of issue while running the jobs in DataStage When changed Transaction Isolation form Read Only to Read Committed this issue got resolved.



Comment on How do i store the counts of all tables … by Jesus Gutierrez

Tue, 13 Feb 2018 17:18:27 +0000

Laurent I'm using your approach to of your function to get the rows count plus another details i need for one Stats table report select table_name, XXXXr.count_rows(table_name), LAST_ANALYZED, NUM_ROWS, (XXXX.count_rows(table_name)-NUM_ROWS) Diff_rows from dba_tables where owner = 'XXXX'; So there are Big big tables here and when I'm executing it blow up these error ERROR: ORA-00942: table or view does not exist ORA-06512: at "XXXX.COUNT_ROWS", line 6 So this mean the function play a key role on the performance Any idea or advice



Comment on better than CTAS by Laurent Schneider

Thu, 18 Jan 2018 18:33:09 +0000

You shouldn’t need to create it, simply use ora11gr2 I think you got something wrong with quotes, it may be a copy paste issue There are three single quotes before T1



Comment on better than CTAS by quanwenzhao

Thu, 18 Jan 2018 07:41:19 +0000

Hello,Laurent Schneider About your implicit loopback dblink 'DB01',how to create it? and my own is as follows, SQL> set linesize 200 SQL> col global_name for a30 SQL> select * from global_name; GLOBAL_NAME ------------------------------ ORA11GR2 SQL> create database link db01 using 'ora11gr2'; Database link created. SQL> select * from dba_db_links; OWNER DB_LINK USERNAME HOST ---------- ----------- --------------- ----------- SYS DB01 ora11gr2 SQL> set autop on SQL> var job_state varchar2(30) SQL> declare 2 n number; 3 begin 4 n := DBMS_DATAPUMP.open('IMPORT','TABLE','DB01'); 5 DBMS_DATAPUMP.metadata_filter(n,'NAME_LIST','''T1'''); 6 DBMS_DATAPUMP.metadata_remap(n,'REMAP_TABLE','T1','T3'); 7 DBMS_DATAPUMP.start_job(n); 8 DBMS_DATAPUMP.WAIT_FOR_JOB(n,:job_state); 9 end; 10 / declare * ERROR at line 1: ORA-39001: Invalid parameter value ORA-06512: In "SYS.DBMS_SYS_ERROR", line 79 ORA-06512: In "SYS.DBMS_DATAPUMP", line 3507 ORA-06512: In "SYS.DBMS_DATAPUMP", line 5296 ORA-06512: In line 4



Comment on better than CTAS by Adrian Hollay

Tue, 16 Jan 2018 18:12:33 +0000

Working in 12c: create table t2 (x primary key) as select * from t1;



Comment on better than CTAS by Laurent Schneider

Thu, 11 Jan 2018 15:50:43 +0000

thanks for the hint. indeed, you need to rename those too SQL> drop table t1 Table dropped. SQL> drop table t2 Table dropped. SQL> create table t1(x number constraint p1 primary key using index (create unique index i1 on t1(x)) ) Table created. SQL> set autop on SQL> var job_state varchar2(30) SQL> declare n number; begin n := DBMS_DATAPUMP.open ('IMPORT', 'TABLE', 'DB01'); DBMS_DATAPUMP.metadata_filter(n, 'NAME_LIST','''T1'''); DBMS_DATAPUMP.metadata_remap(n, 'REMAP_TABLE','T1','T2'); DBMS_DATAPUMP.metadata_remap(n, 'REMAP_TABLE','P1','P2'); DBMS_DATAPUMP.metadata_remap(n, 'REMAP_TABLE','I1','I2'); DBMS_DATAPUMP.start_job (n); DBMS_DATAPUMP.WAIT_FOR_JOB(n, :job_state); end; PL/SQL procedure successfully completed. job_state ------------------------------ COMPLETED SQL> select dbms_metadata.get_ddl('TABLE','T2') from dual DBMS_METADATA.GET_DDL('TABLE','T2') --------------------------------------- CREATE TABLE "SCOTT"."T2" ( "X" NUMBER, CONSTRAINT "P2" PRIMARY KEY ("X") USING INDEX ( CREATE UNIQUE INDEX "SCOTT"."I2" ON "SCOTT"."T2" ("X") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE "ADMIN" ) ENABLE ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "ADMIN"



Comment on better than CTAS by Outón

Thu, 11 Jan 2018 15:20:33 +0000

I think this method has a problem with named indexes/contraints



Comment on sqlnet.ora, sqlplus.exe and tnsping.exe inconsistencies by Pratik Mehta

Sat, 11 Nov 2017 10:33:58 +0000

interesting observation!



Comment on -ignoreDiskWarning by scorch

Tue, 07 Nov 2017 10:23:57 +0000

thank you, that helped perfectly for the automation I am working on.