PL/SQL :: Run 5 Scripts To Create 5 Tables

Nov 10, 2012

I need to run 5 scripts to create 5 tables, seems each table needs to take 2 hrs, so I plan to put all 5 commands together

so i would have tables.sql to contain the following

do you know how to add time in beginning and end , so I can figure it out how long does this take to create 5 tables, use spool too?

create table a as select * from ........ ;
create table b as select * from ........ ;
create table c as select * from ........ ;
create table d as select * from ........ ;
create table e as select * from ........ ;

View 9 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Create A View Using Five Tables

Oct 30, 2012

I am trying to create view ..Using totally Five tables....

The main driven table almost 10 GB Data

When i am creating a view..

End of communication channel Error raise...

At the same time I am running query ..Running Fine...

View 5 Replies View Related

SQL & PL/SQL :: Create Tables From Different Schema?

Mar 18, 2012

I have created 2 different databases and each have their own schema user for eg we have database ALIVE with user allive and the other database RLIVE with user rllive , actually we have implemented new module in ALIVE database and in the process we created many tables , synonyms , index and other objects now we want to list out all the tables ,sequences as scripts which are not present in RLIVE and create them in RLIVE as new objects.

View 3 Replies View Related

Create One Trigger Before DML On All Schema Tables

Nov 28, 2011

We have an application is must be connected to our database for specific requirements in our company but this application has a very bug thing as we must write the super DB password "Like HR password as example" clear in some files and these files must be shared so developers can use the HR password to do any action !!! I know that this application is a problem but we have to install

I can do this by creating trigger on each table will restrict DML. As example: if the operating system user is XXX, the trigger restrict the action. But not logic at all to create more than 1000 triggers on schema (This will impact badly on DB performance).

So, i need to create one trigger to fire before doing any DML on all schema tables. As example: If "MMM" the administrator operating system user trying to do insert action, he can do the action. BUT If "DEV" the developer operating system user trying to do insert action, the trigger must fire here to restrict this action.

Be noted also, i need this trigger not depend on any specific tool like Toad as any user can simply rename the exe file for toad then he can pass the trigger. At least, trigger must depend on (Operating system user & Action_type)

View 11 Replies View Related

Automatically Create Many Tables From 1 Big Table?

Aug 30, 2007

I have a table with over 100000 records.

The format and data is something like this:

Region Code,Name,Surname,ID_Number
1,John,Doe,67
1,Sarah,Jason,45
2,Bob,Marley,69
3,Steven,Johnson,900
2,Harry,Potter,890
3,Sandy,Bay,567
3,Else,Taylor,789 .....

I have about over 100 region codes and each region would like to get their own data. I can manually do the extraction and create a new table for every region, but it's going to take too long.

View 7 Replies View Related

Create Synonyms Of Another User's Tables

Jan 10, 2013

Suppose two users (CONNECT and RESOURCE roles) A and B who work on the same project. How can B create synonyms of all tables of A without asking admin to do that? Of course, we assume that these users have the CREATE SYNONYM privilege. My problem is that B does not have access to user_tables of A, so he cannot obtain the list of A's tables to create synonyms.

View 3 Replies View Related

Oracle - Create Tables Using Variables In PL/SQL?

Feb 3, 2010

is it possible to create tables using variables in pl/sql such as the following example which is actually in php. i would like to create a table dynamically.

$create_samp_table = CREATE TABLE $MCSCHEMA.".$samp_id." AS SELECT * FROM $MCSCHEMA.".$samp_src_table." WHERE ROWNUM = 0

View 1 Replies View Related

SQL & PL/SQL :: CREATE Table From Existing Tables

Oct 31, 2013

I have 2 tables that doesn't have primary keys. These 2 tables have same number of rows. I want to create a new table from getting some columns from table1 and some columns from table 2. I want to combine first row from table1 and first row from table2.

Below is example

TABLE1

ACOL1 ACOL2 ACOL3
A1 A2 A3
B1 B2 B3
C1 C2 C3

TABLE2

BCOL1 BCOL2 BCOL3
11 12 13
21 22 23
31 32 33

COMBINED_TABLE

ACOL1 BCOL2 BCOL3
A1 12 13
B1 22 23
C1 32 33

I tried below query but no luck. It gives below error:

Query : create table COMBINED_TABLE AS select a.ACOL1, b.BCOL2, b.BCOL3 from (select ACOL1,rownum from TABLE1) a, (select BCOL2, BCOL3, rownum from TABLE2) b WHERE a.rownum = b.rownum

Error : ORA-01747:"invalid user.table.column, table.column, or column specification"

View 7 Replies View Related

SQL & PL/SQL :: Create Index On Multiple Tables?

May 21, 2013

we create index on multiple tables in oracle

View 12 Replies View Related

SQL & PL/SQL :: Create Tables Error Statement

Nov 3, 2012

I'm trying to do is create 4 simple tables. This is my first project using SQL so I'm totally new to it, not just SQL itself but database design/management, including foreign/primary key concepts. I think that's why the errors are being generated due to a duplication of foreign key names (perhaps?) but I really don't where I'm going wrong with the design structure in terms of the FK's and the relationships.

Added the error report in the attachment.

Create Table Hotel
(Hotel_No Char(4 Byte) Not Null,
H_Name VarChar2(20 Byte) Not Null,
H_Address VarChar2(30 Byte),
Constraint Hotel_PK Primary Key (Hotel_No));

Create Table Room
(Room_No VarChar2(4) Not Null,
Hotel_No Char(4) Not Null,
R_Type Char(1),
R_Price Number(5,2),
Constraint Room_PK Primary Key (Room_No, Hotel_No),
Constraint Hotel_No_FK (Hotel_No) References Hotel(Hotel_No));

Create Table Booking
(Hotel_No Char(4) Not Null,
Guest_No Char(4) Not Null,
Date_From Date Not Null,
Date_To Date,
Room_No VarChar2(4),
Constraint Booking_PK Primary Key (Hotel_No, Guest_No, Date_From),
Constraint Guest_No_FK Foreign Key (Guest_No) References Guest(Guest_No),
Constraint Hotel_No_Room_No_FK (Hotel_No, Room_No) References Room(Hotel_No, Room_No),
Constraint Hotel_No_FK (Hotel_No) References Hotel(Hotel_No));

Create Table Guest
(Guest_No Char(4) Not Null,
G_Name VarChar2(30),
G_Address VarChar2(35),
Constraint Guest_PK Primary Key (Guest_No));

View 31 Replies View Related

SQL & PL/SQL :: How To Create A View Having All Rows From Tables

Apr 21, 2010

I have a table as shown below,

SQL> select * from Main_Table;

AA
--------------------
SUB1
SUB2

Here the SUB1 and SUB2 are "tables" and are similar in their structure. The "Main_Table" will be update dynamically and the no of rows in this table will vary.

Now my question , i need to create a view which will have all the rows from these tables ,in the current case it is something like

create or replace view sample
as
select * from SUB1 union all
select * from SUB2

How can this be achived. I tried as shown below:

spool file_to_exe.sql
select 'select * from ' || AA ||' union all ' from Main_table;
spool off

i will end up in a union all "extra" , if i do like this.

how can achieve this ?..

View 5 Replies View Related

SQL & PL/SQL :: Create Stored Procedure Where Tables Of Two Different Databases Are To Be Used?

Feb 22, 2012

I want to create a stored procedure where tables of two different databases are to be used.

View 4 Replies View Related

How To Create SP For Taking Count Of Multiple Tables

Dec 7, 2012

i have 20 tables in schema and wanted to take count using store procedure.

how to create a sp for taking count of multiple tables

View 5 Replies View Related

SQL & PL/SQL :: Using Create View And Joining Multiple Tables In Oracle

Apr 8, 2012

I'm stuck on the CREATE VIEW part and the SELECT statement to get the output of the CREATE VIEW. We were instructed not to use AND and simply use JOIN on this exercise.

Question - For employee John Smith, give the project(s) he is working on, his department's name and the name of his division. See crows foot diagram on a separate file.

CREATE TABLE DIVISION
(div_code NUMBER(5),
emp_num NUMBER(5),
div_name VARCHAR(25),
CONSTRAINT pk_division primary key (div_code));
[code].......

SELECT - this SELECT statement is supposed to generate the output of the CREATE VIEW CODE above. SEE Crows Foot diagram attached.

View 9 Replies View Related

PL/SQL :: Create Multiple External Tables From Same Flat File?

Nov 28, 2012

using oracle 10g currently create many external tables like so..

CREATE TABLE "XT_UNITS"
(

"Q1_2012" VARCHAR2(25 BYTE),
"Q2_2012" VARCHAR2(25 BYTE),
"Q3_2012" VARCHAR2(25 BYTE),
"Q4_2012" VARCHAR2(25 BYTE)
[code]....

is there any way I can use 1 flat file (csv) to populate many external tables ?

View 4 Replies View Related

Create Trigger On Table / Populate With Data From Additional Tables

Nov 7, 2010

I would like to create a trigger on a table which populates a log table. In addition to using the table where the trigger will exist, I would like to populate a couple more fields in the log table with with data from 2 other tables.

e.g.

NAME_TABLE
-reg_id
-name

ADDRESS_TABLE *trigger to be fired when a new record is created here.
-reg_id
-srv_id

PROCESS_TABLE
-srv_id
-start_time
-end_time

This is what I would like the logging table to look like:

LOGGING_TABLE
-address_table_reg_id
-address_table.srv_id
-name_table.name
-process_table.start_time
-process_table.end_time

How can I go about creating this type of trigger?

View 2 Replies View Related

Performance Tuning :: Create Hash Partition On Fact Tables?

Aug 5, 2010

I have to create a hash partition on fact tables.. we can use temp tablespace or permanent tablespace.

View 10 Replies View Related

SQL & PL/SQL :: Create Block Of Code That Would Search In All Tables In Schema For Column_name

Jun 14, 2011

I want to create a block of code that would search in all tables in a schema for a column_name where its data_length is like 4000 let's say. This data_length is actually dedicated for a comment column. If found, all not null column with like 4000 data_length will be changed by string "Comment has been removed". I have the following script below but it seemed lacking.

begin
for rec in (select table_name, column_name from user_tab_columns where column_name like ?data_length? = 4000 order by 1,2) loop
begin
execute immediate 'update '||rec.table_name||' set '||rec.column_name||' = ''Comment has been removed'' where '||rec.column_name||' is not null';
commit;
[code]........

View 6 Replies View Related

Performance Tuning :: Create Small Functional Indexes For Special Cases In Very Large Tables

Apr 5, 2012

Create small functional indexes for special cases in very large tables.

When there is a column having one values in 99% records and another values that have to be search for, it is possible to create an index using null value. Index will be small and the rebuild fast.

Example

create index vh_tst_decode_ind_if1 on vh_tst_decode_ind
(decode(S,'I','I',null),style)

It is possible to do index more selective when the key is updated and there are many records to create more levels in b-tree.

create index vh_tst_decode_ind_if3 on vh_tst_decode_ind
(decode(S,'I','I',null),
decode(S,'I',style,null)
)

To access the record can by like:

SQL> select --+ index(vh_tst_decode_ind_if3)
2 style ,count(*)
3 from vh_tst_decode_ind
4 where
5 decode(S,'I','I',null)='I'
6 group by style
7 ;

[code]....

View 2 Replies View Related

Server Utilities :: Append Tables Content To Existing Tables?

Nov 9, 2010

problem on oracle 11gR2 where i have to import data from a source database to an existing table without truncate or drop the target table in the target database.

we have found something called table_exist_action=append in impdp.

View 2 Replies View Related

Maintain Large Tables / Cleanup Data From Our Tables

May 18, 2011

I have to cleanup data from our tables (Production Environment) that contain millions of rows. The question is apart from the solution of the partitioned tables what alternative recommended solution suggests Oracle?

To delete these tables by using a cursor PL/SQL block or to import all the database and in the tables that we want to remove the old rows to use the QUERY option of the data pump utility.

I have used both ways and i have to admit that datapump solution is much much faster than the deletion that suffers from I/O disk.The question again is which method from these two is more reliable and less risky for the health of the database.

View 5 Replies View Related

Implementation Where Data From DB2 Tables Are Moved To Oracle Tables

Sep 3, 2012

I came across an implementation where data from DB2 tables are moved to Oracle tables, for BI solutioning, using some oracle procedures called from MS SQL DTS packages which are scheduled jobs.Just being curious, can this be done using OWB or ODI rather than the above detour. I suppose there are some changes being done in those procedures before the data is being loaded into Oracle tables, can't this be done using OWB/ODI? Can it be scheduled too as jobs using OWB/ODI?

View 1 Replies View Related

SQL & PL/SQL :: Audit Tables For Most Of Production Tables

May 29, 2013

In our schema we have corresponding audit tables for most of the production tables

Ex Table name Audit Table
EMP EMP_AU
DEPT DEPT_AU

Audit tables will have all the columns of production table along with audit columns AUDIT_DATE , AUDIT_OPERATION There are few production tables which are not having audit tables.I need to write a script to identify

1) Production tables where corresponding audit table is missing

2) Where there is column difference (In case any column missing in audit table) between Production table and Audit table

View 11 Replies View Related

Create Procedure To Create User In Oracle

Jan 19, 2012

I need to create PROCEDURE to create user in oracle

CREATE OR REPLACE PROCEDURE "CREATE_USER_ORACLE8"
(
USER_ID in VARCHAR2,
PASSWORD in VARCHAR2,
ROLES in VARCHAR2,
nReturnCode OUT NUMBER
)
BEGIN
[code].......

Compilation errors for PROCEDURE NOG.CREATE_USER_ORACLE8

Error: PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:

; is with authid deterministic parallel_enable as
Line: 9
Text: BEGIN

i want that the customer execute PROCEDURE (user_id,password,PROCEDURE )

View 5 Replies View Related

Ora-06502 - Create Generate Procedure That Create Dynamic Procedure Through DBMS_SQL

Mar 31, 2004

ORA-06502...I have database on oracle 9i on Solaris 9. I create a generate procedure that create dynamic procedure through DBMS_SQL. On this database I got the ORA-06502 error. When I tried to run the same procedure on the same database on oracle 8i on NT this work fine.

View 3 Replies View Related

Enterprise Manager :: What Is The Difference Between Create External Table Vs Create Table

Apr 29, 2011

What is the difference between CREATE EXTERNAL TABLE Vs CREATE TABLE .?

Is CREATE EXTERNAL TABLE included in CREATE TABLE?

View 3 Replies View Related

SQL & PL/SQL :: Differences Among Records Tables And Tables Of Records

Sep 16, 2010

In Oracle database pl/sql 11.2: describe the differences between :

records,
tables, and
tables of records.

View 11 Replies View Related

SQL & PL/SQL :: Same Key For Few Tables

Aug 25, 2011

I've got table tab1 with sequence id column.

Also I've got tables tab2 and tab3 with indicatorid column in each table.

My need is to prevent inserting values into indicatorid column in tables tab2 and tab3 which are absent in id column in table tab1.

For example, at this moment id column in table tab1 has two values, 1 and 2. I can insert value 3 into indicatorid column in tables tab2 and tab3. I need to prevent from inserting value 3 into indicatorid column in tables tab2 and tab3 while it is absent in id column of tab1 table.

How do I perform it?

View 9 Replies View Related

Calling Tables From PL / SQL

Jan 22, 2013

I have the Table name Location and synonym named Location in my DB. I am trying to create the proc where I am tryting to call the table. But its not working.

Example:
CREATE PROCEDURE TESTPROC
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('testing');
select count(*) from LOCATION;
END;

Compile error:
Error(5,22): PL/SQL: ORA-00942: table or view does not exist

View 2 Replies View Related

Joining Two Tables

May 19, 2011

can we link a table that contains a foreign key with a sequence table? i have a table that has a sequence bookid and i want to link this table to another table that contains a foreign key of bookid.can i link this two table and how?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved