Dropping large columns in database - ORACLE

alter table table_name set unused 

There may be a situation where you want to drop a column that has a huge data 10 Million rows .It will take lot of time to drop that column and the worst part is that Oracle will place a lock on that tables until With the " alter table set unused " command you can make that column invisible to users. at a later point of time. when you set the column to unused it will be stored in sys as unused.

MARKING UNUSED COLUMN

sql> 

desc abc_test




Name       Null Type         

---------- ---- ------------ 

NAME            VARCHAR2(20) 

TOTAL_ROWS      NUMBER                                                                                                 

sql>  

alter table abc_test add (lname varchar2(20))




table ABC_TEST altered.

sql>  

desc abc_test




Name       Null Type         

---------- ---- ------------ 

NAME            VARCHAR2(20) 

TOTAL_ROWS      NUMBER       

LNAME           VARCHAR2(20) 

sql> 

alter table abc_test set unused (lname)

table ABC_TEST altered.

sql>  

desc abc_test

Name       Null Type         

---------- ---- ------------ 

NAME            VARCHAR2(20) 

TOTAL_ROWS      NUMBER                                                                                                 




Once this is done the columns will no longer be visible to the user. If at a later date you have time to physically delete the columns this can be done using the following. 

Note : 

Dropping the unused column will still put a lock on the base table. I suggest you to drop 
unused column during maintenance period, to avoid locking.

ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 250;

The DBA_UNUSED_COL_TABS view can be used to view the number of unused columns per table.

Physically dropping column 

To physically drop a column you can use one of the following syntaxes, depending on whether you wish to drop a single or multiple columns.

ALTER TABLE table_name DROP COLUMN column_name; -- 1 column
ALTER TABLE table_name DROP (column_name1, column_name2);  -- multiple columns

Connecting to CDB and PDB - Oracle 12c

Creation on a CDB (Container database) creates a service named is the CDB name. This is a side effect of creating a PDB (Pluggable Database) in the CDB, a service is created inside it with a property that identifies it as the initial current container. The service is also started as a side effect of creating the PDB. Although its metadata is recorded inside the PDB, the invariant is maintained so that a service name is unique within the entire CDB.



Use the Easy Connect syntax to connect to the root unless a net service name is configured in the tnsnames for the root service.




. oraenv

[enter cdb1 at the prompt]


sqlplus sys/oracle@localhost:1521/cdb1 as sysdba

show con_name

show con_id

Connect to the root by using OS authentication.



connect / as sysdba

show con_name

show con_id 






Display the list of available services for the root and the PDBs.



select name, con_id from v$active_services order by 1;



Use the Easy Connect syntax to connect to the PDB unless a net service name is configured in the tnsnames for the PDB service.



connect sys/oracle@localhost:1521/pdb1 as sysdba

show con_name

show con_id

exit

OCA 1Z0-051 dumps

1Z0-051 dump pdf free download - Oracle 11g SQL Fundamentals I 

 

Download Here: 1Z0-051-1

Download Here: 1Z0-051-2

 

Exam Details :  Oracle Database 10g and 11g: SQL Fundamentals I 1Z0-051
Exam Number: 1Z0-051 Associated Certifications: Oracle Database 11g Administrator Certified Associate  Exam Product Version: SQL and PL/SQL,   Exam Price: US$ 125 Duration: 120 minutes Number of Questions: 70 Passing Score: 60% Validated Against: Exam 051 has been validated against Oracle Database 10g and 11g Release 2 version 11.2.0.1.0. format: Multiple Choice

 Link to get more info on certification: OCA-1Z0-051

For more info on Certification program rules click: here

Exam Topics:

Retrieving Data Using the SQL SELECT Statement 

• List the capabilities of SQL SELECT statements
• Execute a basic SELECT statement

Restricting and Sorting Data

• Limit the rows that are retrieved by a query
• Sort the rows that are retrieved by a query 
• Use ampersand substitution to restrict and sort output at runtime

Using Single-Row Functions to Customize Output 

• Describe various types of functions available in SQL
• Use character, number, and date functions in SELECT statements

Using Conversion Functions and Conditional Expressions

• Describe various types of conversion functions that are available in SQL
• Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions 
• Apply conditional expressions in a SELECT statement

Reporting Aggregated Data Using the Group Functions 

• Identify the available group functions
• Describe the use of group functions 
• Group data by using the GROUP BY clause 
• Include or exclude grouped rows by using the HAVING clause

Displaying Data from Multiple Tables 

• Write SELECT statements to access data from more than one table using equijoins and nonequijoins
• Join a table to itself by using a self-join 
• View data that generally does not meet a join condition by using outer joins 
• Generate a Cartesian product of all rows from two or more tables

Using Subqueries to Solve Queries 

• Define subqueries
• Describe the types of problems that the subqueries can solve 
• List the types of subqueries 
• Write single-row and multiple-row subqueries

Using the Set Operators 

• Describe set operators
• Use a set operator to combine multiple queries into a single query 
• Control the order of rows returned

Manipulating Data 

• Describe each data manipulation language (DML) statement
• Insert rows into a table 
• Update rows in a table 
• Delete rows from a table 
• Control transactions

Using DDL Statements to Create and Manage Tables

• Categorize the main database objects
• Review the table structure 
• List the data types that are available for columns 
• Create a simple table 
• Explain how constraints are created at the time of table creation 
• Describe how schema objects work

Creating Other Schema Objects

• Create simple and complex views
• Retrieve data from views 
• Create, maintain, and use sequences 
• Create and maintain indexes 
• Create private and public synonyms

Create a sample schema with 1millions rows in Oracle

We always to test the database for various reasons we will need to create a tables 

with a lot of data in it.We can use below script that will allow us to create a table

and populate the table with 10,00,000 rows with simple plsql block.

This is just for demo purpose and have been tested. 

-- Create and populate a small table.

 CREATE TABLE lookup (
  id            NUMBER(10),
  description   VARCHAR2(50)
);

ALTER TABLE lookup ADD (
  CONSTRAINT lookup_pk PRIMARY KEY (id)
);

INSERT INTO lookup (id, description) VALUES (1, 'ONE');
INSERT INTO lookup (id, description) VALUES (2, 'TWO');
INSERT INTO lookup (id, description) VALUES (3, 'THREE');
COMMIT; 

 -- here we will create and populate a larger table that we will later partition.

 CREATE TABLE big_table (
  id            NUMBER(10),
  created_date  DATE,
  lookup_id     NUMBER(10),
  data          VARCHAR2(50)
);

DECLARE
  l_lookup_id    lookup.id%TYPE;
  l_create_date  DATE;
BEGIN
  FOR i IN 1 .. 1000000 LOOP
    IF MOD(i, 3) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -24);
      l_lookup_id   := 2;
    ELSIF MOD(i, 2) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -12);
      l_lookup_id   := 1;
    ELSE
      l_create_date := SYSDATE;
      l_lookup_id   := 3;
    END IF;
    
    INSERT INTO big_table (id, created_date, lookup_id, data)
    VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
  END LOOP;
  COMMIT;
END;
/

-- Lets apply some constraints to the table.
ALTER TABLE big_table ADD (
  CONSTRAINT big_table_pk PRIMARY KEY (id)
);

CREATE INDEX bita_created_date_i ON big_table(created_date);

CREATE INDEX bita_look_fk_i ON big_table(lookup_id);

ALTER TABLE big_table ADD (
  CONSTRAINT bita_look_fk
  FOREIGN KEY (lookup_id)
  REFERENCES lookup(id)
);

Note : If you find any data on this site irrelavant or copy right issues 

please let me know . I will delete the content. 

This post is just for education purpose.