Connect two remote oracle databases as a schemas on each other

This Question is about Oracle Database.

I have two remote databases DB_1 and DB_2, and I have two schemas on each database:
Schema DB_1 and Schema DB_2 on database DB_1, and Schema DB_1 and Schema DB_2 on database DB_2.
I want to reach database DB_2 Schema DB_2 from database DB_1 Schema DB_1.
For example: when I try this query in DB_1 from schema DB_1:

SELECT * FROM DB_2.example_table

I got an error because there is not table “example_table” in DB_2 Schema on the DB_1 database.
But, when I try the same query on DB_2 Schema DB_2, I got the correct result (some data).

So, I need to tune my databases in some way so I can SELECT * FROM DB_2.example_TABLE from DB_1 Database.

But, there is another problem, I don’t have permission to CREATE LINK.
However, when I query SELECT * FROM ALL_LINKS; on my production database DB_1 schema DB_1 – there are no links at all. So, I need to figure out which way my production Databases was linked (unfortunately my colleagues don’t know either). On my production database DB_1 schema DB_1 I can fetch data with the query:

SELECT * FROM DB_2.example_table

And the data I got is the same if I try to query the same on DB_2 production database DB_2 schema.

But there are no publicprivate Links on DB1/DB2 production databases at all. Is there another way to create a “connect” between my databases? (I queried SELECT * FROM ALL_LINKS from DB_1 schema DB_1, is this possible so I can fetch some other data if I try the query on DB_1 schema DB_2?)

Thanks in anticipation!

DB2 Databases vs Schemas

Suppose I have a DB2 database instance that manages a single database with 2 Schemas, say A and B
What would be the benefits (if any) of having the instance instead manage 2 databases with one database containing Schema A and the other containing Schema B?

powershell – How to modify the same attribute of multiple lists with exact schemas in Sharepoint online through coding or ms flow

I have several lists with the same exact schemas in SharePoint Online. How can I do the following?

1- How to add/remove a column to/from all of them?

2- How to add/remove an option to a choice/multichoice column?

I think there should be a way to accomplish this in C#/Python or through Powershell. I prefer coding as it makes life much easier and faster. MS flow is fine.

Please let me know if it would be better to break this question into two seprate questions.

2d – Place Buildings in certain schemas?

I try to convert an array of Buildings into a representation of a small village. There are several different buildings in a village, like factories that are located on the outside of the village, houses that are placed around churches in the center and so on. What would be a good approach to solve such a problem? I thought about using noise but this didn’t give me the expected results.

I made a little drawing on what should happen:

I put an array of Buildings like for example this through a function

{ houses: 20, churches: 3, factories: 7 }

And i recieve a output similar to this:
Template

Thank you for your help and ideas

Problemas con Models y Schemas de MongoDB usando mongoose en NodeJs

Estoy intentando realizar una ingesta de datos en MongoDB Atlas a través de una REST Api que estoy desarrollando en NodeJs, utilizando mongoose para crear Schemas y Models.

Este es el modelo que estoy utilizando (models/scopes):

const { Schema, model } = require('mongoose');

const Title = {
    lang_id: String,
    value: String
};

const Description = {
    lang_id: String,
    value: String
};

const scopeSchema = new Schema({
    id: String,
    title: (Title),
    description: (Description)
});

module.exports = model('Scopes', scopeSchema);

Y esta es la petición post para poder adquirir los datos: routes/scopes

router.post('/pi-scopes', (req, res) => {
    const { id, title, description } = req.body;
    const newScope = new Scope({
        id,
        title: {
            lang_id: title.lang_id,
            value: title.value
        },
        description: {
            lang_id: description.lang_id,
            value: description.value
        }
    });
    console.log(newScope);
    res.send('registered');
});

Si realizo la petición POST enviando unos datos como estos:

{
        "id": "xxxxxxxxxxxxxxxxxxx",
        "title": (
            {
                "lang_id": "xx",
                "value": "xxx"
            }
        ),
        "description": (
            {
                "lang_id": "xxx",
                "value": "xxx"
            }
        )
    }

El console.log(newScope) no me imprime lo deseado, le falta información:

introducir la descripción de la imagen aquí

Debería de imprimirme el objeto newScope incluyendo los campos title: { lang_id, value } y description: { lang_id, value } Pero no me aparecen los campos correctamente.

¿Alguien ve el error que estoy cometiendo?

Muchas gracias de antemano por leer hasta aquí, un saludo.

sql server – Schemas permission in SQL DB

I am a fresh admin in SQL DB and I need to learn how to establish the security settings in SQL DB. I have encountered an issue that is somehow unclear or even weird for me.

I know that if one user has Fixed Database-Level Roles db_datareader, he or she can perform “SELECT” on all user tables in DB.
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles

But in my SQL DB, even the user which has already been added to that role can not perform “SELECT” on any table/view unless the “SELECT” permission is explicitly granted in “Schemas settings” which implies db_datareader role does not work at all.

Could anyone please give me advice on this? Many thanks

sql – Basic schemas for database and tables creation, bulk import from .csv and basic queries enhancement and improvement

The following SQL code is for the purpose of asesing basic SQL Server literacy for university assignment.

I am creating database and tables schemas, inserting data from .csv files.
Constructing 8 simple qeries as answers to assignment tasks.

The code works well, but as I am very new to MSSQL Server I need some advise on the following:

  1. Are there any better ways for constructing database and table schemas?
  2. Are there any better ways to populate tables from .csv files?
  3. Best practices for error-handling. Print on external file or as query output.
  4. How the syntax can be improved, but provide somewhat foolproofness if the wrong database is in use. Practical and convenient, or complicated and accurate?

Suggestions are welcome.

Assesment questions can be found commented-out prior to every query.

Full code provided below. Comma-separated sample datasets can be found at the bottom.

Thank you in advance.

USE master
GO

---------------------------------------------------------------------------------------
-- Create database

IF NOT EXISTS (
        SELECT
            *
        FROM
            SYS.DATABASES
        WHERE
            NAME = N'TelenorAssignment'
)BEGIN
    CREATE DATABASE (TelenorAssignment) ON PRIMARY (
        NAME = N'TelenorAssignment',
        FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLDATATelenorAssignment.mdf',
        SIZE = 10096KB,
        FILEGROWTH = 1024KB
        ) LOG ON (
            NAME = N'Products_log',
            FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLDATATelenorAssignment.ldf',
            SIZE = 1024KB,
            FILEGROWTH = 10 %
        )
END

USE (TelenorAssignment)
GO

---------------------------------------------------------------------------------------
-- Create Customers table 

IF NOT EXISTS (
    SELECT
        *
    FROM
        SYS.OBJECTS
    WHERE
        OBJECT_ID = OBJECT_ID(N'(dbo).(Customers)') 
        AND TYPE IN (N'U')
)BEGIN
    CREATE TABLE (dbo).(Customers) (
        customerid INT not null,
        firstname VARCHAR (50) null,
        lastname VARCHAR (50) null,
        city VARCHAR (50) null,
        state VARCHAR (50) null,
        CONSTRAINT PK_Customers_customerid PRIMARY KEY CLUSTERED (customerid)
    )
END
GO

---------------------------------------------------------------------------------------
-- Populate Customers table from .csv

BEGIN TRY
    BULK INSERT (dbo).(Customers)
    FROM
        'C:customers_data.csv' WITH (  -- <--- fit for purpose
            FIRSTROW = 1,
            FIELDTERMINATOR = ',',
            ROWTERMINATOR = 'n',
            ERRORFILE = 'C:CustomersErrorRows.csv',
            TABLOCK
        )
END TRY
BEGIN CATCH
  SELECT
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO

--SELECT * FROM (dbo).(Customers)

---------------------------------------------------------------------------------------
-- Create Customer_Purchases table

IF NOT EXISTS(
    SELECT
        *
    FROM
        SYS.OBJECTS
    WHERE
        OBJECT_ID = OBJECT_ID(N'(dbo).(Customer_Purchases)') 
        AND TYPE IN (N'U')
) BEGIN
    CREATE TABLE (dbo).(Customer_Purchases) (
        customerid INT NOT NULL,
        order_date DATE NULL,
        item VARCHAR (200) NULL,
        quantity INT NULL,
        price DECIMAL (20,2) NULL,
        CONSTRAINT FK_CustomerPurchases_CustomerID FOREIGN KEY (customerid)
            REFERENCES (dbo).(Customers)(customerid)
            ON DELETE CASCADE
            ON UPDATE CASCADE
    )
END
GO

---------------------------------------------------------------------------------------
-- Populate Customer_Purchases table from .csv

BEGIN TRY
    BULK INSERT (dbo).(Customer_Purchases)
    FROM
        'C:customer_purchases_data.csv' WITH (  -- <--- fit for purpose
            FIRSTROW = 1,
            FIELDTERMINATOR = ',',
            ROWTERMINATOR = 'n',
            ERRORFILE = 'C:CustomerPurchasesErrorRows.csv',
            TABLOCK
        )
END TRY
BEGIN CATCH
  SELECT
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO

SELECT * FROM (dbo).(Customers)
SELECT * FROM (dbo).(Customer_Purchases)



---------------------------------------------------------------------------------------
-- 1. Count clients by state and show in descending order. 

SELECT 
    (TelenorAssignment).(dbo).(Customers).(state), 
    COUNT((TelenorAssignment).(dbo).(Customers).(customerid)) AS (customercount) 
FROM 
    (TelenorAssignment).(dbo).(Customers)
WHERE 
    (TelenorAssignment).(dbo).(Customers).(state) IS NOT NULL 
GROUP BY (TelenorAssignment).(dbo).(Customers).(state)
ORDER BY 
    (customercount) DESC

---------------------------------------------------------------------------------------
-- 2. Show first and last name of the customers who are presented with their first name more than once. 

SELECT
    (TelenorAssignment).(dbo).(Customers).(customerid),
    (TelenorAssignment).(dbo).(Customers).(firstname), 
    (TelenorAssignment).(dbo).(Customers).(lastname),
    (TelenorAssignment).(dbo).(Customers).(city),
    (TelenorAssignment).(dbo).(Customers).(state)
FROM
    (TelenorAssignment).(dbo).(Customers)
WHERE
    (TelenorAssignment).(dbo).(Customers).(firstname) IN(
        SELECT
            (TelenorAssignment).(dbo).(Customers).(firstname)
        FROM
            (TelenorAssignment).(dbo).(Customers)
        GROUP BY
            (TelenorAssignment).(dbo).(Customers).(firstname)
        HAVING
            COUNT((TelenorAssignment).(dbo).(Customers).(firstname)) > 1
      )
GROUP BY
    (TelenorAssignment).(dbo).(Customers).(customerid),
    (TelenorAssignment).(dbo).(Customers).(firstname), 
    (TelenorAssignment).(dbo).(Customers).(lastname),
    (TelenorAssignment).(dbo).(Customers).(city),
    (TelenorAssignment).(dbo).(Customers).(state)

---------------------------------------------------------------------------------------
-- 3. How many flashlights are bought by John Gray? 

SELECT 
    c.(customerid),
    c.(firstname), 
    c.(lastname),
    cp.(item), 
    SUM(cp.(quantity)) AS (quantity_bought)
FROM 
    (TelenorAssignment).(dbo).(Customers) AS c
INNER JOIN (TelenorAssignment).(dbo).(Customer_Purchases) AS cp
ON c.(customerid) = cp.(customerid)
WHERE 
    cp.(item) IN (
        SELECT
            (TelenorAssignment).(dbo).(Customer_Purchases).(item)
        FROM
            (TelenorAssignment).(dbo).(Customer_Purchases)
        WHERE 
            (TelenorAssignment).(dbo).(Customer_Purchases).(item) LIKE 'Flashlight'
        HAVING
            count(*) > 1
    ) 
AND c.(customerid) = 10101
GROUP BY 
    c.(customerid),
    c.(firstname), 
    c.(lastname),
    cp.(item)

---------------------------------------------------------------------------------------
-- 4. Show 5 random customers and purchases (item and quantity). 

SELECT TOP 5
    c.(customerid),
    c.(firstname), 
    c.(lastname),
    c.(city),
    c.(state), 
    cp.(item), 
    cp.(quantity)
FROM (TelenorAssignment).(dbo).(Customers) AS c
INNER JOIN (TelenorAssignment).(dbo).(Customer_Purchases) AS cp
ON cp.(customerid) = c.(customerid)
ORDER BY 
    RAND(CHECKSUM(*) * RAND())

---------------------------------------------------------------------------------------
-- 5. What is the earliest item purchased by Isabela Moore? 

SELECT TOP 1
    c.(customerid), 
    c.(firstname), 
    c.(lastname), 
    cp.(item), 
    cp.(order_date)
FROM (TelenorAssignment).(dbo).(Customers) AS c
LEFT JOIN (TelenorAssignment).(dbo).(Customer_Purchases) AS cp
ON c.(customerid) = cp.(customerid)
WHERE 
    c.(customerid) = 10449
GROUP BY 
    c.(customerid), 
    c.(firstname), 
    c.(lastname), 
    cp.(item), 
    cp.(order_date)
ORDER BY 
    cp.(order_date)

---------------------------------------------------------------------------------------
-- 6. What did Isabela Moore purchase in the period 01 August to 31 December?

SELECT 
    cp.(customerid), 
    c.(firstname), 
    c.(lastname), 
    cp.(item), 
    cp.(order_date)
FROM (TelenorAssignment).(dbo).(Customers) AS c
LEFT JOIN (TelenorAssignment).(dbo).(Customer_Purchases) AS cp
ON c.(customerid) = cp.(customerid)
WHERE
    c.(customerid) = 10449
    AND cp.(order_date)
        BETWEEN '2009-08-13' 
        AND '2009-12-31'
GROUP BY 
    cp.(customerid), 
    c.(firstname), 
    c.(lastname), 
    cp.(item), 
    cp.(order_date)
ORDER BY 
    cp.(order_date)

---------------------------------------------------------------------------------------
-- 7. Show the total number of items, total amount and weighted average price by state.

SELECT 
    c.(state),
    SUM(cp.(quantity)) AS (total_items),
    SUM(cp.(price) * cp.(quantity)) AS (total_amount),
    SUM(cp.(price) * cp.(quantity)) / SUM(cp.(quantity)) AS (weighted_average_price)
FROM (TelenorAssignment).(dbo).(Customers) AS c
INNER JOIN (TelenorAssignment).(dbo).(Customer_Purchases) AS cp
ON c.(customerid) = cp.(customerid)
GROUP BY 
    c.(state)

---------------------------------------------------------------------------------------
-- 8. Show in separate columns Isabela Moore`s purchases item and order date.

SELECT 
    cp.(item), 
    cp.(order_date)
FROM (TelenorAssignment).(dbo).(Customers) AS c
INNER JOIN (TelenorAssignment).(dbo).(Customer_Purchases) AS cp
ON c.(customerid) = cp.(customerid)
WHERE 
    c.(customerid) = 10449
GROUP BY 
    cp.(item), 
    cp.(order_date)

Customers Dataset:

10101,John,Gray,Lynden,Washington
10298,Leroy,Brown,Pinetop,Arizona
10299,Elroy,Keller,Snoqualmie,Washington
10315,Lisa,Jones,Oshkosh,Wisconsin
10325,Ginger,Schultz,Pocatello,Idaho
10329,Kelly,Mendoza,Kailua,Hawaii
10330,Shawn,Dalton,Cannon Beach,Oregon
10338,Michael,Howell,Tillamook,Oregon
10339,Anthony,Sanchez,Winslow,Arizona
10408,Elroy,Cleaver,Globe,Arizona
10410,Mary Ann,Howell,Charleston,South Carolina
10413,Donald,Davids,Gila Bend,Arizona
10419,Linda,Sakahara,Nogales,Arizona
10429,Sarah,Graham,Greensboro,North Carolina
10438,Kevin,Smith,Durango,Colorado
10439,Conrad,Giles,Telluride,Colorado
10449,Isabela,Moore,Yuma,Arizona

Customer Purchases Dataset:

10330,30-Jun-09,Pogo stick,1,28
10101,30-Jun-09,Raft,1,58
10298,01-Jul-09,Skateboard,1,33
10101,01-Jul-09,Life Vest,4,125
10299,06-Jul-09,Parachute,1,1250
10339,27-Jul-09,Umbrella,1,4.5
10449,13-Aug-09,Unicycle,1,180.79
10439,14-Aug-09,Ski Poles,2,25.5
10101,18-Aug-09,Rain Coat,1,18.3
10449,01-Sep-09,Snow Shoes,1,45
10439,18-Sep-09,Tent,1,88
10298,19-Sep-09,Lantern,2,29
10410,28-Oct-09,Sleeping Bag,1,89.22
10438,01-Nov-09,Umbrella,1,6.75
10438,02-Nov-09,Pillow,1,8.5
10298,01-Dec-09,Helmet,1,22
10449,15-Dec-09,Bicycle,1,380.5
10449,22-Dec-09,Canoe,1,280
10101,30-Dec-09,Hoola Hoop,3,14.75
10330,01-Jan-00,Flashlight,4,28
10101,02-Jan-10,Lantern,1,16
10299,18-Jan-10,Inflatable Mattress,1,38
10438,18-Jan-10,Tent,1,79.99
10413,19-Jan-10,Lawnchair,4,32
10410,30-Jan-10,Unicycle,1,192.5
10315,02-Feb-10,Compass,1,8
10449,28-Feb-10,Flashlight,1,4.5
10101,08-Mar-10,Sleeping Bag,2,88.7
10298,18-Mar-10,Pocket Knife,1,22.38
10449,19-Mar-10,Canoe paddle,2,40
10298,01-Apr-10,Ear Muffs,1,12.5
10330,19-Apr-10,Shovel,1,16.75

postgresql – How to GRANT the USAGE privilege on SCHEMAS that will be created in the future

I need a role to access a schema owned by another role, which can be dropped and re-created in the future. For the first part of my requirement, i.e. for an existing schema, the following would do:

GRANT USAGE ON SCHEMA existing_schema TO my_role;

For the second part of the requirement, i.e. for schemas that don’t exist yet but could be created in the future, the ALTER DEFAULT PRIVILEGES command, as documented here, looked promising:

ALTER DEFAULT PRIVILEGES FOR ROLE my_role
  GRANT USAGE ON SCHEMAS TO my_role;

but it doesn’t work as expected, i.e. after creating a new schema owned by another user:

CREATE SCHEMA new_schema AUTHORIZATION other_role;

and trying to access it as my_role I get:

my_role=> SELECT * FROM new_schema.some_table;
ERROR:  permission denied for schema new_schema

What am I doing wrong here?

PS I am aware of this answer but modifying system databases, such as template1 is not an option for me since I am neither the owner nor the only user of the database cluster in question.