Moving Table/PK to new FileGroup  

Posted by ReelTym

USE master
GO
ALTER DATABASE TEST ADD FILEGROUP TEST_DATA_1
GO
ALTER DATABASE TEST ADD FILEGROUP TEST_DATA_2
GO

ALTER DATABASE TEST
ADD FILE
(   NAME = TEST1,
    FILENAME = 'D:\SQL\Data\TEST_1.ndf',
    SIZE = 1MB,
    MAXSIZE = 10MB,
    FILEGROWTH = 1MB
)
TO FILEGROUP TEST_DATA_1
GO

ALTER DATABASE TEST
ADD FILE
(   NAME = TEST2,
    FILENAME = 'D:\SQL\Data\TEST_2.ndf',
    SIZE = 1MB,
    MAXSIZE = 10MB,
    FILEGROWTH = 1MB
)
TO FILEGROUP Test_DATA_2
GO

USE TEST
GO

CREATE TABLE TAB1
(
    TAB1_ID INT IDENTITY(1,1),
    TAB1_NAME VARCHAR(100),
    CONSTRAINT PK_TAB1 PRIMARY KEY(TAB1_ID)
) ON TEST_DATA_1 -- Filegroup we created.
GO

INSERT INTO TAB1(TAB1_NAME)
SELECT Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
GO

exec sp_help TAB1

/*
Selected output of above command is

Data_located_on_filegroup
—————————
TEST_DATA_1

index_name index_description
———- ——————————————————-
PK_TAB1 clustered, unique, primary key located on TEST_DATA_1

constraint_type constraint_name
————————– —————-
PRIMARY KEY (clustered) PK_TAB1

Above result indicates that PK_TAB1 constraint is used to create the clustered index on the table TAB1.
Please note that whenever a primary key constraint is defined, and clustered index does not exist on the
table, SQL Server will create clustered index for the primary key constraint. It is not the same for
UNIQUE constraint. Unique constraint/ index will always be non-clustered unless other wise it is specified
during creation of constraint or index. We had covered this before in the differences between a primary
key and a unique constraint blog post.

In order to move the table to a different file group, we need to use the drop constraint command along
with the MOVE TO option as shown below. Once the table is moved to a new file group, we can re-create
the primary key constraint.
*/
ALTER TABLE TAB1 DROP CONSTRAINT PK_TAB1 WITH (MOVE TO TEST_DATA_2)
GO

exec sp_help TAB1

ALTER TABLE TAB1 ADD CONSTRAINT PK_TAB1 PRIMARY KEY(TAB1_ID)
GO

exec sp_help TAB1

/*
After executing the above command, table TAB1 will now reside on filegroup TEST_DATA_2. If you re-execute
sp_help stored procedure, it will show the filegroup change for table TAB1.
*/

Get list of GRANT statements by User or Object in Oracle  

Posted by ReelTym

-- Get a list of grant statements by user:
  SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','ASTRO') FROM DUAL;
   
-- Returns:
  GRANT REFERENCES ON "METADOSSIER"."METADOSSIERS" TO "ASTRO";
  GRANT REFERENCES ON "ECT_CUSTOMIZATION"."AGENT_LOGIN_MAPPING" TO "ASTRO";
  GRANT REFERENCES ON "ECT_CUSTOMIZATION"."AGENT_ROLE_RLT" TO "ASTRO";
  GRANT REFERENCES ON "ECT_CUSTOMIZATION"."ECT_ROLE" TO "ASTRO";
  GRANT REFERENCES ON "COMPANY_STRUCTURE"."COMP_COMPANY" TO "ASTRO";
  GRANT REFERENCES ON "COMPANY_STRUCTURE"."USER_USER" TO "ASTRO";
  GRANT REFERENCES ON "WORKFLOW"."EVENTS_TYPE" TO "ASTRO";
  GRANT REFERENCES ON "WORKFLOW"."FUNCTIONAL_DOMAIN_REF" TO "ASTRO";
  GRANT REFERENCES ON "WORKFLOW"."HISTORY" TO "ASTRO";
  GRANT REFERENCES ON "WORKFLOW"."JOURNAL" TO "ASTRO";
  GRANT REFERENCES ON "WORKFLOW"."MESSAGE" TO "ASTRO";
  GRANT REFERENCES ON "WORKFLOW"."STATUS_REF" TO "ASTRO";
  GRANT REFERENCES ON "WORKFLOW"."TEMPLATE" TO "ASTRO";
  GRANT REFERENCES ON "WORKFLOW"."EVENTS_TYPE_HISTORY" TO "ASTRO";
  GRANT REFERENCES ON "WORKFLOW"."EVENT_CONFIG" TO "ASTRO";
  GRANT REFERENCES ON "SITE_WORK"."MESSAGING_EVENT_NAME" TO "ASTRO";
  GRANT REFERENCES ON "SITE_WORK"."NOTIF_CODE_EVENT_NAME" TO "ASTRO";
  GRANT REFERENCES ON "SITE_WORK"."UPDATE_ENTITY_ID" TO "ASTRO";
  GRANT REFERENCES ON "SITE_WORK"."NOTIFICATION_ERROR" TO "ASTRO";

-- Get a list of grant statements by object:
  SELECT DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','EVENT_CONFIG','WORKFLOW') FROM DUAL;
   
-- Returns:
  GRANT SELECT ON "WORKFLOW"."EVENT_CONFIG" TO "MID";
  GRANT DELETE ON "WORKFLOW"."EVENT_CONFIG" TO "ECT";
  GRANT INSERT ON "WORKFLOW"."EVENT_CONFIG" TO "ECT";
  GRANT SELECT ON "WORKFLOW"."EVENT_CONFIG" TO "ECT";
  GRANT UPDATE ON "WORKFLOW"."EVENT_CONFIG" TO "ECT";
  GRANT REFERENCES ON "WORKFLOW"."EVENT_CONFIG" TO "ECT";
  GRANT DELETE ON "WORKFLOW"."EVENT_CONFIG" TO "MIGRATION";
  GRANT INSERT ON "WORKFLOW"."EVENT_CONFIG" TO "MIGRATION";
  GRANT SELECT ON "WORKFLOW"."EVENT_CONFIG" TO "MIGRATION";
  GRANT UPDATE ON "WORKFLOW"."EVENT_CONFIG" TO "MIGRATION";
  GRANT REFERENCES ON "WORKFLOW"."EVENT_CONFIG" TO "MIGRATION";
  GRANT INSERT ON "WORKFLOW"."EVENT_CONFIG" TO "BILLINGCC";
  GRANT SELECT ON "WORKFLOW"."EVENT_CONFIG" TO "BILLINGCC";
  GRANT UPDATE ON "WORKFLOW"."EVENT_CONFIG" TO "BILLINGCC";
  GRANT DELETE ON "WORKFLOW"."EVENT_CONFIG" TO "WBMI";
  GRANT INSERT ON "WORKFLOW"."EVENT_CONFIG" TO "WBMI";
  GRANT SELECT ON "WORKFLOW"."EVENT_CONFIG" TO "WBMI";
  GRANT UPDATE ON "WORKFLOW"."EVENT_CONFIG" TO "WBMI";
  GRANT REFERENCES ON "WORKFLOW"."EVENT_CONFIG" TO "WBMI";
  GRANT REFERENCES ON "WORKFLOW"."EVENT_CONFIG" TO "ASTRO";
  GRANT SELECT ON "WORKFLOW"."EVENT_CONFIG" TO "INTREP";